ITPub博客

首页 > 数据库 > MySQL > MySQL 字符串函数大全

MySQL 字符串函数大全

原创 MySQL 作者:神谕丶 时间:2015-10-09 11:04:09 0 删除 编辑
以下内容基于MySQL 5.6及更高,大部分函数5.5也基本适用,更低版本请参考对应版本手册,其内容整理自官方。

mysql常用函数被分为五类,分别为:

①String:字符串函数;

②Date and time:日期和时间;
http://blog.itpub.net/29773961/viewspace-1808967/

③Numeric:数字函数;
http://blog.itpub.net/29773961/viewspace-1813556/

④Control Flow:基于一个表达式的结果集选择不同的值(控制流函数);
http://blog.itpub.net/29773961/viewspace-1813557/

⑤Aggregate:基于一列的多个值返回单一值(聚合函数);
http://blog.itpub.net/29773961/viewspace-1813589/


String】:
ASCII():返回ASCII码
  1. mysql> SELECT ASCII('A');
  2. +------------+
  3. | ASCII('A') |
  4. +------------+
  5. | 65         |
  6. +------------+
  7. 1 row in set (0.00 sec)


BIN():返回二进制值,非数字则返回0
  1. mysql> SELECT BIN('HELLO'), BIN('8'), BIN(10);
  2. +--------------+----------+---------+
  3. | BIN('HELLO') | BIN('8') | BIN(10) |
  4. +--------------+----------+---------+
  5. | 0            | 1000     | 1010    |
  6. +--------------+----------+---------+
  7. 1 row in set (0.00 sec)


BIT_LENGTH():以bit为单位来返回字符串长度
  1. mysql> SELECT BIT_LENGTH('a'), BIT_LENGTH('HELLO');
  2. +-----------------+---------------------+
  3. | BIT_LENGTH('a') | BIT_LENGTH('HELLO') |
  4. +-----------------+---------------------+
  5. | 8               | 40                  |
  6. +-----------------+---------------------+
  7. 1 row in set (0.00 sec)


CHAR_LENGTH()与CHARACTER_LENGTH()返回字符串的字符数
  1. mysql> SELECT CHAR_LENGTH('hello world');
  2. +----------------------------+
  3. | CHAR_LENGTH('hello world') |
  4. +----------------------------+
  5. | 11                         |
  6. +----------------------------+
  7. 1 row in set (0.00 sec)


CHAR():将传入的数转换成正数并返回ACSII对应的字符
  1. mysql> SELECT CHAR(77,'121.9',83*1,81.3,'76');
  2. +---------------------------------+
  3. | CHAR(77,'121.9',83*1,81.3,'76') |
  4. +---------------------------------+
  5. | MySQL                           |
  6. +---------------------------------+
  7. 1 row in set, 1 warning (0.00 sec)

  8. mysql> SHOW WARNINGS;
  9. +---------+------+--------------------------------------------+
  10. | Level   | Code | Message                                    |
  11. +---------+------+--------------------------------------------+
  12. | Warning | 1292 | Truncated incorrect INTEGER value: '121.9' |
  13. +---------+------+--------------------------------------------+
  14. 1 row in set (0.00 sec)
  1. mysql> SELECT CHARSET(CHAR(0x65)), CHARSET(CHAR(0x65 USING utf8));
  2. +---------------------+--------------------------------+
  3. | CHARSET(CHAR(0x65)) | CHARSET(CHAR(0x65 USING utf8)) |
  4. +---------------------+--------------------------------+
  5. | binary              | utf8                           |
  6. +---------------------+--------------------------------+
  7. 1 row in set (0.00 sec)


CONCAT_WS():用特定字符连接参数组成一个字符串(拼接字符串)
  1. mysql> SELECT CONCAT_WS('_','hello','my','sql');
  2. +-----------------------------------+
  3. | CONCAT_WS('_','hello','my','sql') |
  4. +-----------------------------------+
  5. | hello_my_sql                      |
  6. +-----------------------------------+
  7. 1 row in set (0.00 sec)


CONCAT():连接传入的参数成一个字符串(拼接字符串)
  1. mysql> SELECT CONCAT('m','y','sql');
  2. +-----------------------+
  3. | CONCAT('m','y','sql') |
  4. +-----------------------+
  5. | mysql                 |
  6. +-----------------------+
  7. 1 row in set (0.00 sec)


ELT():返回列表的第n个元素
  1. mysql> SELECT ELT(1,'index1','index2','index3'),
  2.     -> ELT(3,'index1','index2','index3');
  3. +-----------------------------------+-----------------------------------+
  4. | ELT(1,'index1','index2','index3') | ELT(3,'index1','index2','index3') |
  5. +-----------------------------------+-----------------------------------+
  6. | index1                            | index3                            |
  7. +-----------------------------------+-----------------------------------+
  8. 1 row in set (0.01 sec)


EXPORT_SET(bits,expr1,expr2[,'分隔符','用于补零']):按bit的排列方式,当位等于1时,插入expr1,等于0时,插入expr2,从左向右排列字符。
  1. mysql> SELECT EXPORT_SET(2,'Y','N','_',5), EXPORT_SET(3,'Y','N','_',5), EXPORT_SET(4,'Y','N',',',5);
  2. +-----------------------------+-----------------------------+-----------------------------+
  3. | EXPORT_SET(2,'Y','N','_',5) | EXPORT_SET(3,'Y','N','_',5) | EXPORT_SET(4,'Y','N',',',5) |
  4. +-----------------------------+-----------------------------+-----------------------------+
  5. | N_Y_N_N_N                   | Y_Y_N_N_N                   | N,N,Y,N,N                   |
  6. +-----------------------------+-----------------------------+-----------------------------+
  7. 1 row in set (0.00 sec)


FIELD(v,v1,v2....):v值去与v1,v2……匹配,匹配到后返回该值位置,若无,则返回0
  1. mysql> SELECT FIELD('hey','un','hey','huh','hey'),
  2.     -> FIELD('bye','un','hey','huh','hey');
  3. +-------------------------------------+-------------------------------------+
  4. | FIELD('hey','un','hey','huh','hey') | FIELD('bye','un','hey','huh','hey') |
  5. +-------------------------------------+-------------------------------------+
  6. | 2                                   | 0                                   |
  7. +-------------------------------------+-------------------------------------+
  8. 1 row in set (0.00 sec)


FIND_IN_SET():查找字符在字符串中的位置
  1. mysql> SELECT FIND_IN_SET('b','a,b,c'),
  2.     -> FIND_IN_SET('y','a,b,c');
  3. +--------------------------+--------------------------+
  4. | FIND_IN_SET('b','a,b,c') | FIND_IN_SET('y','a,b,c') |
  5. +--------------------------+--------------------------+
  6. | 2                        | 0                        |
  7. +--------------------------+--------------------------+
  8. 1 row in set (0.00 sec)


FORMAT():将数字格式化成形如#,###,###形式,可限制其小数位,若不足则补0
  1. mysql> SELECT FORMAT(123456,2),
  2.     -> FORMAT(1234567.333,1);
  3. +------------------+-----------------------+
  4. | FORMAT(123456,2) | FORMAT(1234567.333,1) |
  5. +------------------+-----------------------+
  6. | 123,456.00       | 1,234,567.3           |
  7. +------------------+-----------------------+
  8. 1 row in set (0.00 sec)


FROM_BASE64():base64解码
TO_BASE64():base64解码
  1. mysql> SELECT TO_BASE64('abc'), FROM_BASE64(TO_BASE64('abc'));
  2. +------------------+-------------------------------+
  3. | TO_BASE64('abc') | FROM_BASE64(TO_BASE64('abc')) |
  4. +------------------+-------------------------------+
  5. | YWJj             | abc                           |
  6. +------------------+-------------------------------+
  7. 1 row in set (0.00 sec)


HEX():16进制编码
UNHEX():16进制解码
  1. mysql> SELECT 0x616263, HEX('abc'), UNHEX(HEX('abc'));
  2. +----------+------------+-------------------+
  3. | 0x616263 | HEX('abc') | UNHEX(HEX('abc')) |
  4. +----------+------------+-------------------+
  5. | abc      | 616263     | abc               |
  6. +----------+------------+-------------------+
  7. 1 row in set (0.00 sec)


INSERT():在指定位置替换字符/字符串
  1. mysql> SELECT INSERT('Quadratic',3,4,'What'),
  2.     -> INSERT('Quadratic',-1,4,'What'),
  3.     -> INSERT('Quadratic',3,100,'What');
  4. +--------------------------------+---------------------------------+----------------------------------+
  5. | INSERT('Quadratic',3,4,'What') | INSERT('Quadratic',-1,4,'What') | INSERT('Quadratic',3,100,'What') |
  6. +--------------------------------+---------------------------------+----------------------------------+
  7. | QuWhattic                      | Quadratic                       | QuWhat                           |
  8. +--------------------------------+---------------------------------+----------------------------------+
  9. 1 row in set (0.00 sec)


INSTR():
返回字符串的位置,类似POSITION()与LOCATION()
  1. mysql> SELECT INSTR('mysql','ql');
  2. +---------------------+
  3. | INSTR('mysql','ql') |
  4. +---------------------+
  5. | 4                   |
  6. +---------------------+
  7. 1 row in set (0.00 sec)


LEFT():返回字符串左侧的指定字符数的字符串
  1. mysql> SELECT LEFT('mysql',2);
  2. +-----------------+
  3. | LEFT('mysql',2) |
  4. +-----------------+
  5. | my              |
  6. +-----------------+
  7. 1 row in set (0.00 sec)


LENGTH()与OCTET_LENGTH()返回字符串的字符数
  1. mysql> SELECT LENGTH('mysql'), LENGTH('嗯');
  2. +-----------------+--------------+
  3. | LENGTH('mysql') LENGTH('嗯') |
  4. +-----------------+--------------+
  5. | 5               | 3            |
  6. +-----------------+--------------+
  7. 1 row in set (0.00 sec)


LOCATE()与POSITION()返回字符串所在的位置,类似INSTR。
  1. mysql> SELECT LOCATE('ql','mysql');
  2. +----------------------+
  3. | LOCATE('ql','mysql') |
  4. +----------------------+
  5. | 4                    |
  6. +----------------------+
  7. 1 row in set (0.00 sec)


LOWER()与LCASE()返回所有字符串的小写形式
  1. mysql> SELECT LOWER('MySQL');
  2. +----------------+
  3. | LOWER('MySQL') |
  4. +----------------+
  5. | mysql          |
  6. +----------------+
  7. 1 row in set (0.00 sec)
  1. mysql> SELECT LCASE('MYSQL');
  2. +----------------+
  3. | LCASE('MYSQL') |
  4. +----------------+
  5. | mysql          |
  6. +----------------+
  7. 1 row in set (0.00 sec)


LPAD():返回指定长度的字符串,在左侧用指定字符填充。
  1. mysql> SELECT LPAD('MySQL',10,'a');
  2. +----------------------+
  3. | LPAD('MySQL',10,'a') |
  4. +----------------------+
  5. | aaaaaMySQL           |
  6. +----------------------+
  7. 1 row in set (0.00 sec)


LTRIM()与RTRIM()在左侧或右侧去除空格并返回字符串,类似于TRIM()
  1. mysql> SELECT LTRIM(' _mysql') a, RTRIM('mysql_ ') b;
  2. +--------+--------+
  3. | a      | b      |
  4. +--------+--------+
  5. | _mysql | mysql_ |
  6. +--------+--------+
  7. 1 row in set (0.00 sec)


MAKE_SET()按bit的排列方式,当位等于1时,返回该值,等于0时,不返回,从左向右排列字符。
  1. mysql> SELECT MAKE_SET(2,'a','b','c','d'), 
  2.  -> MAKE_SET(3,'a','b','c','d'), 
  3.  -> MAKE_SET(4,'a','b','c','d'), 
  4.  -> MAKE_SET(5,'a','b','c','d')\G
  5. *************************** 1. row ***************************
  6. MAKE_SET(2,'a','b','c','d'): b
  7. MAKE_SET(3,'a','b','c','d'): a,b
  8. MAKE_SET(4,'a','b','c','d'): c
  9. MAKE_SET(5,'a','b','c','d'): a,c
  10. 1 row in set (0.00 sec)


OCT():八进制编码
  1. mysql> SELECT OCT(12);
  2. +---------+
  3. | OCT(12) |
  4. +---------+
  5. | 14      |
  6. +---------+
  7. 1 row in set (0.00 sec)


ORD():返回字符串的第一个字符的ASCII码
  1. mysql> SELECT ORD('a'), ORD('all'), ASCII('a');
  2. +----------+------------+------------+
  3. | ORD('a') | ORD('all') | ASCII('a') |
  4. +----------+------------+------------+
  5. | 97       | 97         | 97         |
  6. +----------+------------+------------+
  7. 1 row in set (0.00 sec)


QUOTE():用单引号将整个字符串包裹起来,并为字符串本身的特殊字符增加转义字符
  1. mysql> SELECT QUOTE('Don\'t!'),
  2.     -> QUOTE('\t');
  3. +------------------+-------------+
  4. | QUOTE('Don\'t!') | QUOTE('\t') |
  5. +------------------+-------------+
  6. | 'Don\'t!'        | '    '      |


REPEAT():重复输出字符串
  1. mysql> SELECT REPEAT('mysql',5);
  2. +---------------------------+
  3. | REPEAT('mysql',5)         |
  4. +---------------------------+
  5. | mysqlmysqlmysqlmysqlmysql |
  6. +---------------------------+
  7. 1 row in set (0.00 sec)


REPLACE():将字符串中的字符(串)换成另一个字符(串)
  1. mysql> SELECT REPLACE('www.google.com','com','cn');
  2. +--------------------------------------+
  3. | REPLACE('www.google.com','com','cn') |
  4. +--------------------------------------+
  5. | www.google.cn                        |
  6. +--------------------------------------+
  7. 1 row in set (0.00 sec)


REVERSE():返回字符串以置返的形式(置返字符串)
  1. mysql> SELECT REVERSE('123456');
  2. +-------------------+
  3. | REVERSE('123456') |
  4. +-------------------+
  5. | 654321            |
  6. +-------------------+
  7. 1 row in set (0.00 sec)


RIGHT()返回字符串右侧的指定字符数的字符串
  1. mysql> SELECT RIGHT('mysql',3);
  2. +------------------+
  3. | RIGHT('mysql',3) |
  4. +------------------+
  5. | sql              |
  6. +------------------+
  7. 1 row in set (0.00 sec)


RPAD():返回指定长度的字符串,在右侧用指定字符填充。
  1. mysql> SELECT RPAD('MySQL',10,'_');
  2. +----------------------+
  3. | RPAD('MySQL',10,'_') |
  4. +----------------------+
  5. | MySQL_____           |
  6. +----------------------+
  7. 1 row in set (0.00 sec)


SOUNDEX():返回soundex字符串(标准的为4字符),但是该函数可能返回一个稍长一点的字符串,利用字符的读音近似值所得。
目前是一个有限制的函数,只能用于英语,其他语言会产生无法预期的结果,官方表示将在未来版本(5.6以后)移除这些限制,可以在官方查找BUG #22638来获得更多信息。
  1. mysql> SELECT SOUNDEX('HELLO'), SOUNDEX('Quadratically');
  2. +------------------+--------------------------+
  3. | SOUNDEX('HELLO') | SOUNDEX('Quadratically') |
  4. +------------------+--------------------------+
  5. | H400             | Q36324                   |
  6. +------------------+--------------------------+
  7. 1 row in set (0.00 sec)


SPACE():返回N个空格
  1. mysql> SELECT SPACE(10) a, LENGTH(SPACE(10));
  2. +------------+-------------------+
  3. | a          | LENGTH(SPACE(10)) |
  4. +------------+-------------------+
  5. |            | 10                |
  6. +------------+-------------------+
  7. 1 row in set (0.00 sec)


STRCMP():返回字符串的比较结果,0=same,-1=smaller,1=other(结果说明不分大小写)
  1. mysql> SELECT STRCMP('mysql','MYSQL'), STRCMP('mysql','mysql'), STRCMP('mysql','oracle'), STRCMP('oracle','mysql')\G
  2. *************************** 1. row ***************************
  3.  STRCMP('mysql','MYSQL'): 0
  4.  STRCMP('mysql','mysql'): 0
  5.  STRCMP('mysql','oracle'): -1
  6.  STRCMP('oracle','mysql'): 1
  7. 1 row in set (0.00 sec)


SUBSTRING()与MID()与SUBSTR():返回从指定位置开始的字符串
  1. mysql> SELECT SUBSTRING(123456,3);    -- 可以用于数字
  2. +-----------------------+
  3. | SUBSTRING(123456,'3') |
  4. +-----------------------+
  5. | 3456                  |
  6. +-----------------------+
  7. 1 row in set (0.00 sec)

  8. mysql> SELECT SUBSTRING('ABCDEFG',3);    -- 可以用于字符串
  9. +--------------------------+
  10. | SUBSTRING('ABCDEFG','3') |
  11. +--------------------------+
  12. | CDEFG                    |
  13. +--------------------------+
  14. 1 row in set (0.00 sec)
  1. mysql> SELECT SUBSTRING('ABCDEFG',3,2);    -- 也可以指定返回几个,比如从3号位置开始返回,返回2个字符
  2. +--------------------------+
  3. | SUBSTRING('ABCDEFG',3,2) |
  4. +--------------------------+
  5. | CD                       |
  6. +--------------------------+
  7. 1 row in set (0.00 sec)


SUBSTRING_INDEX():返回字符串的部分,基于指定分隔符。(如果是正数就从左往右开始搜索,反之亦然)
  1. mysql> SELECT SUBSTRING_INDEX('abc@email.com','@',2);    -- 以@为分隔符,返回第二个整部分的字符串
  2. +----------------------------------------+
  3. | SUBSTRING_INDEX('abc@email.com','@',2) |
  4. +----------------------------------------+
  5. | abc@email.com                          |
  6. +----------------------------------------+
  7. 1 row in set (0.00 sec)

  8. mysql> SELECT SUBSTRING_INDEX('abc@email.com','@',1);    -- 以@为分隔符,返回第一部分的字符串
  9. +----------------------------------------+
  10. | SUBSTRING_INDEX('abc@email.com','@',1) |
  11. +----------------------------------------+
  12. | abc                                    |
  13. +----------------------------------------+
  14. 1 row in set (0.00 sec)
  1. mysql> SELECT SUBSTRING_INDEX('www.mysql.com','.',1);    -- 以.为分隔符,返回.之前的第一部分字符串
  2. +----------------------------------------+
  3. | SUBSTRING_INDEX('www.mysql.com','.',1) |
  4. +----------------------------------------+
  5. | www                                    |
  6. +----------------------------------------+
  7. 1 row in set (0.00 sec)

  8. mysql> SELECT SUBSTRING_INDEX('www.mysql.com','.',-1);    -- 以.为分隔符,从右往左开始,返回.之前的第一部分字符串
  9. +-----------------------------------------+
  10. | SUBSTRING_INDEX('www.mysql.com','.',-1) |
  11. +-----------------------------------------+
  12. | com                                     |
  13. +-----------------------------------------+
  14. 1 row in set (0.00 sec)

  15. mysql> SELECT SUBSTRING_INDEX('www.mysql.com','.',-2);    -- 以.为分隔符,从右往左开始,返回.之前的第二个整部分字符串
  16. +-----------------------------------------+
  17. | SUBSTRING_INDEX('www.mysql.com','.',-2) |
  18. +-----------------------------------------+
  19. | mysql.com                               |
  20. +-----------------------------------------+
  21. 1 row in set (0.00 sec)

  22. mysql> SELECT SUBSTRING_INDEX('www.mysql.com','.',-3);    -- 以.为分隔符,从右往左开始,返回.之前的第三个整部分字符串
  23. +-----------------------------------------+
  24. | SUBSTRING_INDEX('www.mysql.com','.',-3) |
  25. +-----------------------------------------+
  26. | www.mysql.com                           |
  27. +-----------------------------------------+
  28. 1 row in set (0.00 sec)


TRIM():移除字符串的空格或指定字符
  1. mysql> SELECT TRIM('   mysql       ') as A; -- 默认移除空格
  2. +-------+
  3. | A     |
  4. +-------+
  5. | mysql |
  6. +-------+
  7. 1 row in set (0.00 sec)

  8. mysql> SELECT TRIM(TRAILING 'asd' FROM 'mysqlasdasd') as A; -- TRAILING用来移除字符串末尾的指定字符
  9. +-------+
  10. | A     |
  11. +-------+
  12. | mysql |
  13. +-------+
  14. 1 row in set (0.00 sec)

  15. mysql> SELECT TRIM(LEADING 'asd' FROM 'asdmysql') as A; -- LEADING用来移除字符串开始的指定字符
  16. +-------+
  17. | A     |
  18. +-------+
  19. | mysql |
  20. +-------+
  21. 1 row in set (0.00 sec)


UPPER()与UCASE()返回所有字符串的大写形式
  1. mysql> SELECT UPPER('MySQL');
  2. +----------------+
  3. | UPPER('MySQL') |
  4. +----------------+
  5. | MYSQL          |
  6. +----------------+
  7. 1 row in set (0.00 sec)


WEIGHT_STRING():返回一个weight_string值
用于测试或调试collation。

作者公众号(持续更新)

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29773961/viewspace-1813545/,如需转载,请注明出处,否则将追究法律责任。

上一篇: MySQL SQL_MODE详解
请登录后发表评论 登录
全部评论
微信公众号:fantasy-life-

注册时间:2014-07-28

  • 博文量
    159
  • 访问量
    970603