ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > 数据库 > 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)

  9. mysql> SHOW WARNINGS;
  10. +---------+------+--------------------------------------------+
  11. | Level   | Code | Message                                    |
  12. +---------+------+--------------------------------------------+
  13. | Warning | 1292 | Truncated incorrect INTEGER value: '121.9' |
  14. +---------+------+--------------------------------------------+
  15. 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('','com','cn');
  2. +--------------------------------------+
  3. | REPLACE('','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)

  9. mysql> SELECT SUBSTRING('ABCDEFG',3);    -- 可以用于字符串
  10. +--------------------------+
  11. | SUBSTRING('ABCDEFG','3') |
  12. +--------------------------+
  13. | CDEFG                    |
  14. +--------------------------+
  15. 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)

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

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

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

  25. mysql> SELECT SUBSTRING_INDEX('','.',-3);    -- 以.为分隔符，从右往左开始，返回.之前的第三个整部分字符串
  26. +-----------------------------------------+
  27. | SUBSTRING_INDEX('','.',-3) |
  28. +-----------------------------------------+
  29. | www.mysql.com                           |
  30. +-----------------------------------------+
  31. 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)

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

  17. mysql> SELECT TRIM(LEADING 'asd' FROM 'asdmysql') as A; -- LEADING用来移除字符串开始的指定字符
  18. +-------+
  19. | A     |
  20. +-------+
  21. | mysql |
  22. +-------+
  23. 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博客 ” ，链接：https://blog.itpub.net/29773961/viewspace-1813545/ ，如需转载，请注明出处，否则将追究法律责任。

0
0
上一篇： MySQL SQL_MODE详解
下一篇： MySQL 数字函数大全
请登录后发表评论 登录
全部评论
神谕丶
微信公众号：神谕的暗影长廊

注册时间：2014-07-28

  • 博文量
    161
  • 访问量
    1195125

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us 编辑准则

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593