MySQL字符串比较函数学习(一)
发布于 2008-09-22 22:23 阅读:29,712 评论:0 标签: MySQL 字符串比较

       通览了一下手册,发现MySQL的字符串函数和PHP的函数在功能还是有很多地方相似的,很多都能在php中找到对应的函数。所以在这里,要向大家推荐一个文章:PHP 中执行排序与 MySQL 中排序。作者:明灵(dragon) , Fenng 。地址:http://www.dbanotes.net/arch/php_mysql_sort.html

      好,回到本文。先说两个特别的地方:

      1:函数中,假如结果的长度大于 max_allowed_packet 系统变量的最大值时,字符串值函数的返回值为NULL。请参见7.5.2节,“调节服务器参数”(http://dev.mysql.com/doc/refman/5.1/zh/optimization.html#server-parameters)。

      2:和PHP等编程语言不同,对于在字符串位置操作的函数,第一个位置的编号为 1。

      下面开始函数学习:

      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'

      php中类似的函数是:number_format