MySQL字符串與字符串比較函數學習

在mysql數據庫中字符串在數據庫中占有很重要的地位，下面我來介紹MySQL字符串比較函數的一些基礎學習，有需要的朋友可參考一下。

1：字符串連接函數

 代碼如下 復制代碼

      CONCAT(str1,str2,...)

      返回結果為連接參數產生的字符串。如有任何一個參數為NULL ，則返回值為 NULL。或許有一個或多個參數。 如果所有參數均為非二進制字符串，則結果為非二進制字符串。 如果自變量中含有任一二進制字符串，則結果為一個二進制字符串。一個數字參數被轉化為與之相等的二進制字符串格式；若要避免這種情況，可使用顯式類型 cast, 例如： SELECT CONCAT(CAST(int_col AS CHAR), char_col)

以下是代碼片段：

 代碼如下 復制代碼

mysql> SELECT CONCAT('My', 'S', 'QL');
        -> 'MySQL'

mysql> SELECT CONCAT('My', NULL, 'QL');
        -> NULL

mysql> SELECT CONCAT(14.3);
        -> '14.3'

 

      下面這個concat_ws是concat的變體：

      CONCAT_WS(separator,str1,str2,...)

      CONCAT_WS() 代表 CONCAT With Separator ，是CONCAT()的特殊形式。   第一個參數是其它參數的分隔符。分隔符的位置放在要連接的兩個字符串之間。分隔符可以是一個字符串，也可以是其它參數。如果分隔符為 NULL，則結果為 NULL。函數會忽略任何分隔符參數後的 NULL 值。

以下是代碼片段：

 代碼如下 復制代碼

mysql> SELECT CONCAT_WS(',','First name','Second name','Last Name');
        -> 'First name,Second name,Last Name'

mysql> SELECT CONCAT_WS(',','First name',NULL,'Last Name');
        -> 'First name,Last Name'

 

 

      CONCAT_WS()不會忽略任何空字符串。 (然而會忽略所有的 NULL）。

      這個函數和php的explode和implode、list、extract相似了。有興趣的可以看看php的手冊。

      2：字符串剪接函數

      INSERT(str,pos,len,newstr)

      這個函數，mysql中文手冊翻譯得太爛了，還是偶自己來吧：

      這個函數返回一個字符串：

      A：str字符串從位置pos（從1開始計算）開始（含第pos位），之後的pos位被字符串newstr取代；
      B：如果pos大於str的長度，則從str後面開始銜接；
      C：如果因len超出余後的長度，則位置pos之後（含第pos位）全被取代；
      D：如果pos為負數，返回str；
      E：如果len為負數，則情同len超出余後的長度，這和php的substr之類的函數不一樣。

以下是代碼片段：

 代碼如下 復制代碼

mysql> SELECT INSERT('Quadratic', 3, 4, 'What');
        -> 'QuWhattic'

mysql> SELECT INSERT('Quadratic', -1, 4, 'What');
        -> 'Quadratic'

mysql> SELECT INSERT('Quadratic', 3, 100, 'What');
        -> 'QuWhat'

 

      這個函數支持多字節字元。

      3：進制轉換函數

      CONV(N,from_base,to_base)

      不同數基間轉換數字。返回值為數字的N字符串表示，由from_base基轉化為 to_base 基。如有任意一個參數為NULL，則返回值為 NULL。自變量 N 被理解為一個整數，但是可以被指定為一個整數或字符串。最小基數為 2 ，而最大基數則為 36。 If to_base 是一個負數，則 N 被看作一個帶符號數。否則， N 被看作無符號數。 CONV() 的運行精確度為 64比特。

以下是代碼片段：

 代碼如下 復制代碼 mysql> SELECT CONV('a',16,2);
        -> '1010'
mysql> SELECT CONV('6E',18,8);
        -> '172'
mysql> SELECT CONV(-17,10,-18);
        -> '-H'
mysql> SELECT CONV(10+'10'+'10'+0xa,10,10);
        -> '40'

 

      請看PHP的：

      base_convert — 在任意進制之間轉換數字

      說明：
      string base_convert ( string $number , int $frombase , int $tobase )
      返回一字符串，包含 number 以 tobase 進制的表示。number 本身的進制由 frombase 指定。frombase 和 tobase 都只能在 2 和 36 之間（包括 2 和 36）。高於十進制的數字用字母 a-z 表示，例如 a 表示 10，b 表示 11 以及 z 表示 35。

      MySQL的進制轉換函數還有一個，是一個為16進制而定義的函數：

      HEX(N_or_S)

      如果N_OR_S 是一個數字，則返回一個 十六進制值 N 的字符串表示，在這裡，   N 是一個longlong (BIGINT)數。這相當於 CONV(N,10,16)。

      如果N_OR_S 是一個字符串，則返回值為一個N_OR_S的十六進制字符串表示， 其中每個N_OR_S 裡的每個字符被轉化為兩個十六進制數字。

以下是代碼片段：

 代碼如下 復制代碼

mysql> SELECT HEX(255);
        -> 'FF'
mysql> SELECT 0x616263;
        -> 'abc'
mysql> SELECT HEX('abc');
        -> 616263
 

      4：字符查找定位函數

      ELT(N,str1,str2,str3,...)

      若N = 1，則返回值為  str1 ，若N = 2，則返回值為 str2 ，以此類推。   若N 小於1或大於參數的數目，則返回值為 NULL 。 ELT() 是  FIELD()的補數。

以下是代碼片段：

 代碼如下 復制代碼

mysql> SELECT ELT(1, 'ej', 'Heja', 'hej', 'foo');
        -> 'ej'
mysql> SELECT ELT(4, 'ej', 'Heja', 'hej', 'foo');
        -> 'foo'
 

      看見上面這個函數，還以為主要起邏輯判斷用的，如：

以下是代碼片段：

 代碼如下 復制代碼 mysql> SELECT ELT(if(1, 2, 3), 'ej', 'Heja', 'hej', 'foo');      
+----------------------------------------------+
| ELT(if(1, 2, 3), 'ej', 'Heja', 'hej', 'foo') |
+----------------------------------------------+
| Heja                                         |
+----------------------------------------------+
1 row in set (0.00 sec)

 

      但是一看下面的函數，才明白不僅僅如此：

      FIELD(str,str1,str2,str3,...)

      返回值為str1, str2, str3,……列表中的str 指數。在找不到str 的情況下，返回值為 0 。

      如果所有對於FIELD() 的參數均為字符串，則所有參數均按照字符串進行比較。如果所有的參數均為數字，則按照數字進行比較。否則，參數按照雙倍進行比較。

      如果str 為NULL，則返回值為0 ，原因是NULL不能同任何值進行同等比較。FIELD() 是ELT()的補數。

以下是代碼片段：

 代碼如下 復制代碼 mysql> SELECT FIELD('ej', 'Hej', 'ej', 'Heja', 'hej', 'foo');
        -> 2
mysql> SELECT FIELD('fo', 'Hej', 'ej', 'Heja', 'hej', 'foo');
        -> 0

 

      這個函數與php中的array_search和array_key_exists、in_array、isset還是有相似作用的。

      再看一下下面這個，不禁拍案叫絕了，直接就是in子句了：

      FIND_IN_SET(str,strlist)

      假如字符串str 在由N 子鏈組成的字符串列表strlist 中， 則返回值的范圍在 1 到 N 之間 。一個字符串列表就是一個由一些被‘,’符號分開的自鏈組成的字符串。如果第一個參數是一個常數字符串，而第二個是type SET列，則   FIND_IN_SET() 函數被優化，使用比特計算。如果str不在strlist 或strlist 為空字符串，則返回值為 0 。如任意一個參數為NULL，則返回值為 NULL。 這個函數在第一個參數包含一個逗號(‘,’)時將無法正常運行。 

以下是代碼片段：

 代碼如下 復制代碼

mysql> SELECT FIND_IN_SET('b','a,b,c,d');
        -> 2
 

      5：數字格式化函數

      FORMAT(X,D)

      將數字X 的格式寫為'#,###,###.##',以四捨五入的方式保留小數點後 D 位， 並將結果以字符串的形式返回。若  D 為 0, 則返回結果不帶有小數點，或不含小數部分。

以下是代碼片段：

 代碼如下 復制代碼 mysql> SELECT FORMAT(12332.123456, 4);
        -> '12,332.1235'
mysql> SELECT FORMAT(12332.1,4);
        -> '12,332.1000'
mysql> SELECT FORMAT(12332.2,0);
        -> '12,332'

 
 字符串比較中有以下四個規則：

      1：MySQL 會自動將數字轉化為字符串，反之亦然。 
      2：若想要將數字明確地轉化為字符串，可使用 CAST()或 CONCAT()函數。
      3：若已經對一個字符串函數給定一個二進制字符串作為參數， 則所得到的結果字符串也是一個二進制字符串。
      4：一般而言, 若字符串比較中任意一個表達式是區分大小寫的，則執行比較時也區分大小寫。

      下面根據以上說的三類進行學習：

      一：使用like子句

expr LIKE pat [ESCAPE 'escape-char']
      模式匹配，使用SQL簡單正規表達式比較。返回1 (TRUE) 或 0 (FALSE)。 若 expr 或 pat 中任何一個為 NULL,則結果為 NULL。

      模式不需要為文字字符串。例如，可以被指定為一個字符串表達式或表列。

      在模式中可以同LIKE一起使用以下兩種通配符：

字符
 說明
 
%
 匹配任何數目的字符，甚至包括零字符 
 
_
 只能匹配一種字符
 

 

以下是代碼片段：

 代碼如下 復制代碼

mysql> SELECT 'David!' LIKE 'David_';
        -> 1

mysql> SELECT 'David!' LIKE '%D%v%';
        -> 1
 

      若要對通配符的文字實例進行檢驗, 可將轉義字符放在該字符前面。如果沒有指定 ESCAPE字符, 則假設為‘’。這個和一般的編程語言是一樣的。

字符串
 說明
 
%
 匹配一個  ‘%’字符
 
_
 匹配一個 ‘_’ 字符
 

以下是代碼片段：

 代碼如下 復制代碼

mysql> SELECT 'David!' LIKE 'David_';
        -> 0

mysql> SELECT 'David_' LIKE 'David_';
        -> 1
 

      也可以指定一個不同的轉義字符,需使用ESCAPE語句：

以下是代碼片段：

 代碼如下 復制代碼 mysql> SELECT 'David_' LIKE 'David|_' ESCAPE '|';
        -> 1

 

      以下兩個語句舉例說明了字符串比較不區分大小寫，除非其中一個操作數為二進制字符串：

 

以下是代碼片段：

 代碼如下 復制代碼

mysql> SELECT 'abc' LIKE 'ABC';
        -> 1

mysql> SELECT 'abc' LIKE BINARY 'ABC';
        -> 0

 

      在MySQL中, LIKE 允許出現在數字表達式中。 (這是標准SQL LIKE 的延伸）。

以下是代碼片段：

 代碼如下 復制代碼

mysql> SELECT 10 LIKE '1%';
        -> 1
 

      注釋： 由於 MySQL在字符串中使用 C轉義語法(例如, 用‘n’代表一個換行字符)，在LIKE字符串中，必須將用到的‘’雙寫。例如， 若要查找 ‘n’, 必須將其寫成 ‘\n’。而若要查找 ‘’, 則必須將其寫成 it as ‘\\’;原因是反斜線符號會被語法分析程序剝離一次，在進行模式匹配時，又會被剝離一次，最後會剩下一個反斜線符號接受匹配。

      二：使用正則表達式的like子句

expr REGEXP pat expr RLIKE pat
      執行字符串表達式 expr 和模式pat 的模式匹配。該模式可以被延伸為正則表達式。正則表達式的語法在附錄G：MySQL正則表達式中有詳細討論。若expr 匹配 pat，則返回 1; 否則返回0。若 expr 或 pat 任意一個為 NULL, 則結果為 NULL。 RLIKE 是REGEXP的同義詞, 作用是為mSQL 提供兼容性。

      模式不需要為文字字符串。例如,可以被指定為一個字符串表達式或表列。

      注釋：由於在字符串中， MySQL使用 C 轉義語法 (例如, 用‘n’來代表換行字符 ),在REGEXP字符串中必須將用到的‘’ 雙寫。

      REGEXP 不區分大小寫, 除非將其同二進制字符串同時使用。

 

以下是代碼片段：

 代碼如下 復制代碼

mysql> SELECT 'Monty!' REGEXP 'm%y%%';
        -> 0

mysql> SELECT 'Monty!' REGEXP '.*';
        -> 1

mysql> SELECT 'new*n*line' REGEXP 'new\*.\*line';
        -> 1

mysql> SELECT 'a' REGEXP 'A', 'a' REGEXP BINARY 'A';
        -> 1  0

mysql> SELECT 'a' REGEXP '^[a-d]';
        -> 1
 

      在確定字符類型時，REGEXP 和 RLIKE 使用當前字符集 (默認為cp1252 Latin1 )。 警告：這些操作符不支持多字節字元。

       三：使用函數：STRCMP

STRCMP(expr1,expr2)
      若所有的字符串均相同，則返回0；若根據當前分類次序，第一個參數小於第二個，則返回  -1，其它情況返回 1 。

以下是代碼片段：

 代碼如下 復制代碼

mysql> SELECT STRCMP('text', 'text2');
        -> -1

mysql> SELECT STRCMP('text2', 'text');
        -> 1

mysql> SELECT STRCMP('text', 'text');
        -> 0

 

      在執行比較時，STRCMP() 使用當前字符集。這使得默認的比較區分大小寫，當操作數中的一個或兩個都是二進制字符串時除外。

      說明：對於前兩個都有其反操作。見下

 代碼如下 復制代碼

expr NOT LIKE pat [ESCAPE 'escape-char']
          這相當於 NOT (expr LIKE pat [ESCAPE 'escape-char'])。

expr NOT REGEXP pat expr NOT RLIKE pat
          這相當於NOT (expr REGEXP pat)。