本教程以MySQL8为主版本(内容兼顾MySQL5.7)。
所有MySQL文章的目录为:总目录 https://blog.csdn.net/zyplanke/article/details/102968014
MySQL内置了大量的函数,包括字符串函数、数值函数、日期时间函数、流程判断函数、其他函数。
同时,本文最后还附带了MySQL的类型自动转换规则。
字符串函数
平时我们工作中,字符串处理应该占了很大一部分。幸运的是,MySQL已经内置了大量的字符串函数。 我们选择其中比较常用的函数列表如下(全部的函数,请参考官方文档):
函数 | 功能说明 |
CONCAT(str1,str2,...) | 将多个参数内容连接成为一个字符串 |
CONCAT_WS(separator,str1,str2,...) | 将多个参数内容连接成为一个字符串,中间添加分隔符 |
INSERT(str,pos,len,newstr) | 将字符串str,从pos开始的len个字符的字符串替换newstr。 |
如果pos不在字符串长度内,则返回原始字符串。 | |
pos如果 len不在字符串其余部分的长度内,则从位置替换字符串的其余部 | |
LOWER(str) | 将所有字符更改为小写的字符串 |
UPPER(str) | 将所有字符更改为大写的字符串 |
LEFT(str,len) | 返回str字符串中最左边的len个字符 |
RIGHT(str,len) | 返回str字符串中最右边的len个字符 |
LPAD(str,len,padstr) | 以字符padstr,在字符串str左边进行填充,直到总长度为len |
RPAD(str,len,padstr) | 以字符padstr,在字符串str右边进行填充,直到总长度为len |
LTRIM(str) | 返回str删除了首部空格的字符串 |
RTRIM(str) | 返回str删除了尾部空格的字符串 |
TRIM(str) | 返回str删除了两头(首部和尾部)空格的字符串 |
REPEAT(str,count) | 返回将str重复count次结果。如果 count小于1,则返回一个空字符串 |
REPLACE(str,from_str,to_str) | 用字符串to_str去替换字符串str中所有出现的字符串from_str |
REVERSE(str) | 返回字符串str,其字符顺序颠倒。 |
SUBSTR(str,pos) | 返回从str中pos开始,直到str结尾的子串。如pos为负数,则从倒数pos位置开始,直到结尾。 |
SUBSTR(str,pos,len) | 返回从str中pos开始,长度为len的子串。Pos含义同上。如果len为小于1结果为空字符串 |
TO_BASE64(str) | 将str参数转换为base-64编码形式 |
INSTR(str,substr) | 返回substr第一次在str中出现的位置 |
LENGTH(str) | 返回字符串str的长度 ,以字节为单位 |
CHAR_LENGTH(str) | 返回字符串str的长度 ,以字符为单位 |
BIT_LENGTH(str) | 返回字符串str的长度 ,以位为单位 |
STRCMP(expr1,expr2) | 比较两个串 |
FORMAT(X,D[,locale]) | 将数字X格式化为'#,###,###.##',将其舍入到 D小数位,然后将结果作为字符串返回。 第三个参数指定语言环境,默认值为'en_US'。用于数字的小数点,千位分隔符以及分隔符之间的分组 |
在MySQL使用字符串函数的方法举例如下:
mysql> select CONCAT('aa', 'cccc', 'bbb');
+-----------------------------+
| CONCAT('aa', 'cccc', 'bbb') |
+-----------------------------+
| aaccccbbb |
+-----------------------------+
1 row in set (0.00 sec)
mysql> select lower('AADSFassdf中国asD@sd888d');
+-------------------------------------+
| lower('AADSFassdf中国asD@sd888d') |
+-------------------------------------+
| aadsfassdf中国asd@sd888d |
+-------------------------------------+
1 row in set (0.00 sec)
mysql> select substr('abcdefg', 2);
+----------------------+
| substr('abcdefg', 2) |
+----------------------+
| bcdefg |
+----------------------+
1 row in set (0.00 sec)
mysql> select TO_BASE64('straaaa');
+----------------------+
| TO_BASE64('straaaa') |
+----------------------+
| c3RyYWFhYQ== |
+----------------------+
1 row in set (0.00 sec)
mysql> SELECT INSTR('foobarbar', 'bar');
+---------------------------+
| INSTR('foobarbar', 'bar') |
+---------------------------+
| 4 |
+---------------------------+
1 row in set (0.00 sec)
mysql> select LENGTH('abc中国ABC'), CHAR_LENGTH('abc中国ABC'), BIT_LENGTH('abc中国ABC');
+------------------------+-----------------------------+----------------------------+
| LENGTH('abc中国ABC') | CHAR_LENGTH('abc中国ABC') | BIT_LENGTH('abc中国ABC') |
+------------------------+-----------------------------+----------------------------+
| 12 | 8 | 96 |
+------------------------+-----------------------------+----------------------------+
1 row in set (0.00 sec)
mysql> select FORMAT(1234554321.123456, 4);
+------------------------------+
| FORMAT(1234554321.123456, 4) |
+------------------------------+
| 1,234,554,321.1235 |
+------------------------------+
1 row in set (0.00 sec)
数值函数
MySQL同样内置了几十个处理数值的函数,极大的方便我们的工作,常用的函数如下:
函数 | 功能说明 |
ABS(X) | 返回X的绝对值 |
CEIL(X) | 不小于 X的最小整数值,也叫向上取整 |
FLOOR(X) | 不大于 X的最大整数值,也叫向下取整 |
MOD(N,M) | 返回N/M的模(余数) |
RAND() | 返回随机数,结果v范围0 <= v < 1。 |
ROUND(X,D) | 将X四舍五入,并保留D位小数。如果未指定参数D ,则为0 |
TRUNCATE(X,D) | 直接截断数字X,只保留D为小数。 |
SQRT(X) | 返回非负数的平方根 X |
POWER(X,Y) | 返回X的Y次幂 |
在MySQL使用数值串函数的方法举例如下:
mysql> select ABS(5), ABS(0), ABS(-5);
+--------+--------+---------+
| ABS(5) | ABS(0) | ABS(-5) |
+--------+--------+---------+
| 5 | 0 | 5 |
+--------+--------+---------+
1 row in set (0.00 sec)
mysql> select CEIL(6.2), CEIL(6.0), FLOOR(6.2), FLOOR(6.0);
+-----------+-----------+------------+------------+
| CEIL(6.2) | CEIL(6.0) | FLOOR(6.2) | FLOOR(6.0) |
+-----------+-----------+------------+------------+
| 7 | 6 | 6 | 6 |
+-----------+-----------+------------+------------+
1 row in set (0.00 sec)
mysql> select MOD(234,10), 234 % 10, 234 MOD 10, MOD(34.5,3);
+-------------+----------+------------+-------------+
| MOD(234,10) | 234 % 10 | 234 MOD 10 | MOD(34.5,3) |
+-------------+----------+------------+-------------+
| 4 | 4 | 4 | 1.5 |
+-------------+----------+------------+-------------+
1 row in set (0.00 sec)
mysql> select RAND(), RAND(), RAND(), RAND() ;
+-------------------+----------------------+--------------------+--------------------+
| RAND() | RAND() | RAND() | RAND() |
+-------------------+----------------------+--------------------+--------------------+
| 0.719894015900692 | 0.015165929696677188 | 0.9161476315149549 | 0.5352403992183883 |
+-------------------+----------------------+--------------------+--------------------+
1 row in set (0.00 sec)
mysql> SELECT ROUND(1.58), ROUND(1.47), ROUND(1.294, 1), ROUND(1.294, 2);
+-------------+-------------+-----------------+-----------------+
| ROUND(1.58) | ROUND(1.47) | ROUND(1.294, 1) | ROUND(1.294, 2) |
+-------------+-------------+-----------------+-----------------+
| 2 | 1 | 1.3 | 1.29 |
+-------------+-------------+-----------------+-----------------+
1 row in set (0.00 sec)
mysql> select ROUND(-1.237), ROUND(-1.546), ROUND(-1.231, 2), ROUND(-1.546, 2);
+---------------+---------------+------------------+------------------+
| ROUND(-1.237) | ROUND(-1.546) | ROUND(-1.231, 2) | ROUND(-1.546, 2) |
+---------------+---------------+------------------+------------------+
| -1 | -2 | -1.23 | -1.55 |
+---------------+---------------+------------------+------------------+
1 row in set (0.00 sec)
mysql> select SQRT(2), SQRT(16), POWER(3,5), POWER(1.44, 2);
+--------------------+----------+------------+----------------+
| SQRT(2) | SQRT(16) | POWER(3,5) | POWER(1.44, 2) |
+--------------------+----------+------------+----------------+
| 1.4142135623730951 | 4 | 243 | 2.0736 |
+--------------------+----------+------------+----------------+
1 row in set (0.00 sec)
日期时间函数
函数 | 功能说明 |
CURRENT_DATE() | 根据该函数是在字符串还是数字上下文中使用,以格式'YYYY-MM-DD'或 YYYYMMDD格式 返回当前日期 |
CURRENT_TIME() | 根据该函数是在字符串还是数字上下文中使用,以格式 'hh:mm:ss'或 hhmmss格式返回当前时间 |
CURRENT_TIMESTAMP() | 当前时间戳。包括日期和时间。 |
NOW([fsp]) | 根据该函数是在字符串还是数字上下文中使用,以对应的格式返回当前日期和时间 fsp参数指定从0到6的小数位秒精度,默认为0,建议取6。 |
DAYOFYEAR(date) | 返回该日期在一年中是第几天 |
DATE_FORMAT(date,format) | 对日期按指定格式进行格式化 |
DATE_ADD(date,INTERVAL expr unit) | 对data参数加上一个时间间隔的时间值 |
DATE_SUB(date,INTERVAL expr unit) | 对data参数减去一个时间间隔的时间值 |
DATEDIFF(expr1,expr2) | 两个日间之间间隔的天数 |
TIMEDIFF(expr1,expr2) | 两个时间之间的间隔 |
在MySQL使用日期时间函数的方法举例如下:
mysql> select CURRENT_DATE(), CURRENT_TIME(), CURRENT_DATE()+0, CURRENT_TIME()+0;
+----------------+----------------+------------------+------------------+
| CURRENT_DATE() | CURRENT_TIME() | CURRENT_DATE()+0 | CURRENT_TIME()+0 |
+----------------+----------------+------------------+------------------+
| 2019-11-07 | 22:12:48 | 20191107 | 221248 |
+----------------+----------------+------------------+------------------+
1 row in set (0.00 sec)
mysql> select NOW(), NOW()+0;
+---------------------+----------------+
| NOW() | NOW()+0 |
+---------------------+----------------+
| 2019-11-07 22:13:32 | 20191107221332 |
+---------------------+----------------+
1 row in set (0.00 sec)
mysql> select DAYOFYEAR( NOW() );
+--------------------+
| DAYOFYEAR( NOW() ) |
+--------------------+
| 311 |
+--------------------+
1 row in set (0.00 sec)
mysql> select DATE_FORMAT( NOW(), 'yyyy-mm-dd');
+-----------------------------------+
| DATE_FORMAT( NOW(), 'yyyy-mm-dd') |
+-----------------------------------+
| yyyy-mm-dd |
+-----------------------------------+
1 row in set (0.00 sec)
mysql> select DATE_FORMAT(NOW(),'%Y%m%d %H:%i:%S.%f'), DATE_FORMAT(NOW(6),'%Y%m%d %H:%i:%S.%f');
+-----------------------------------------+------------------------------------------+
| DATE_FORMAT(NOW(),'%Y%m%d %H:%i:%S.%f') | DATE_FORMAT(NOW(6),'%Y%m%d %H:%i:%S.%f') |
+-----------------------------------------+------------------------------------------+
| 20191107 22:31:23.000000 | 20191107 22:31:23.214515 |
+-----------------------------------------+------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT DATE_ADD('2018-05-01',INTERVAL 1 DAY), DATE_ADD('2018-05-01',INTERVAL 1 DAY);
+---------------------------------------+---------------------------------------+
| DATE_ADD('2018-05-01',INTERVAL 1 DAY) | DATE_ADD('2018-05-01',INTERVAL 1 DAY) |
+---------------------------------------+---------------------------------------+
| 2018-05-02 | 2018-05-02 |
+---------------------------------------+---------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT DATE_ADD('2018-05-01',INTERVAL 1 DAY), DATE_SUB('2018-05-01',INTERVAL 1 YEAR);
+---------------------------------------+----------------------------------------+
| DATE_ADD('2018-05-01',INTERVAL 1 DAY) | DATE_SUB('2018-05-01',INTERVAL 1 YEAR) |
+---------------------------------------+----------------------------------------+
| 2018-05-02 | 2017-05-01 |
+---------------------------------------+----------------------------------------+
1 row in set (0.00 sec)
mysql> select DATE_ADD('2100-12-31 23:59:59', INTERVAL '1:1' MINUTE_SECOND);
+---------------------------------------------------------------+
| DATE_ADD('2100-12-31 23:59:59', INTERVAL '1:1' MINUTE_SECOND) |
+---------------------------------------------------------------+
| 2101-01-01 00:01:00 |
+---------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> select DATE_SUB('2025-01-01 00:00:00',INTERVAL '1 1:1:1' DAY_SECOND);
+---------------------------------------------------------------+
| DATE_SUB('2025-01-01 00:00:00',INTERVAL '1 1:1:1' DAY_SECOND) |
+---------------------------------------------------------------+
| 2024-12-30 22:58:59 |
+---------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT DATEDIFF('2007-12-31 23:59:59','2007-12-30'), DATEDIFF('2010-11-30 23:59:59','2010-12-31');
+----------------------------------------------+----------------------------------------------+
| DATEDIFF('2007-12-31 23:59:59','2007-12-30') | DATEDIFF('2010-11-30 23:59:59','2010-12-31') |
+----------------------------------------------+----------------------------------------------+
| 1 | -31 |
+----------------------------------------------+----------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT TIMEDIFF('2000:01:01 00:00:00','2000:01:01 00:00:00.000001');
+--------------------------------------------------------------+
| TIMEDIFF('2000:01:01 00:00:00','2000:01:01 00:00:00.000001') |
+--------------------------------------------------------------+
| -00:00:00.000001 |
+--------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT TIMEDIFF('2008-12-31 23:59:59.000001','2008-12-30 01:01:01.000002');
+---------------------------------------------------------------------+
| TIMEDIFF('2008-12-31 23:59:59.000001','2008-12-30 01:01:01.000002') |
+---------------------------------------------------------------------+
| 46:58:57.999999 |
+---------------------------------------------------------------------+
1 row in set (0.00 sec)
流程判断函数
函数 | 功能说明 |
IF(expr1,expr2,expr3) | 判断expr1结果,如果true则返回expr2,如果false则返回expr3 |
IFNULL(expr1,expr2) | expr1不是 NULL,则返回 expr1; 否则返回 expr2 |
CASE WHEN …. | CASE语法,分支流程判断。如果....然后.... 否则..... |
用法举例如下:
mysql> SELECT IF(1>2, 2, 3), IF(1<2,'yes','no'), IF(STRCMP('test','test1'),'no','yes');
+---------------+--------------------+---------------------------------------+
| IF(1>2, 2, 3) | IF(1<2,'yes','no') | IF(STRCMP('test','test1'),'no','yes') |
+---------------+--------------------+---------------------------------------+
| 3 | yes | no |
+---------------+--------------------+---------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT IFNULL(1,0), IFNULL(NULL,10), IFNULL(1/0,10), IFNULL(1/0,'yes');
+-------------+-----------------+----------------+-------------------+
| IFNULL(1,0) | IFNULL(NULL,10) | IFNULL(1/0,10) | IFNULL(1/0,'yes') |
+-------------+-----------------+----------------+-------------------+
| 1 | 10 | 10.0000 | yes |
+-------------+-----------------+----------------+-------------------+
1 row in set, 2 warnings (0.00 sec)
mysql> SELECT CASE WHEN 1>0 THEN 'true' ELSE 'false' END;
+--------------------------------------------+
| CASE WHEN 1>0 THEN 'true' ELSE 'false' END |
+--------------------------------------------+
| true |
+--------------------------------------------+
1 row in set (0.00 sec)
其他常见函数
MySQL还提供了其他函数,是我们工作中的有利工具。常用的如下表
函数 | 功能说明 |
DATABASE() 或 SCHEMA() | 返回当前database名(schema) |
VERSION() | 返回MySQL服务端的版本,字符串形式 |
USER() | 返回当前登录用户(会话用户)名 |
CONNECTION_ID() | 返回当前连接的数字ID |
MD5(str) | 返回str字符串的MD5值 |
SHA1(str) | 计算SHA-1 160位校验和 |
MySQL类型自动转换规则
mysql> SELECT 1+'1', 1.3+'1', 1+'1.3', CONCAT(2,' test');
+-------+---------+---------+-------------------+
| 1+'1' | 1.3+'1' | 1+'1.3' | CONCAT(2,' test') |
+-------+---------+---------+-------------------+
| 2 | 2.3 | 2.3 | 2 test |
+-------+---------+---------+-------------------+
1 row in set (0.00 sec)
数字型和字符串两个是不同,不可能放在一起处理。 如上面所示,为何在MySQL中能正确运行呢? 是因为MySQL自动做了类型转换。也可以使用CAST()
函数进行显示明确转换。
MySQL自动转换(隐式转换)规则是(源自官方文档):
-
如果其中一个为
NULL
,则比较的结果为NULL
,除了NULL
-safe<=>
相等比较运算符外。对于NULL <=> NULL
,结果为true。无需转换。 -
如果两个都是字符串,不转换。 如果两个都是整数,也不转换。
-
如果不与数字比较,十六进制值将视为二进制字符串(不常用)。
-
其中一个是
TIMESTAMP
或DATETIME类型的列
,而另一个参数是常量(如日期时间的字符串形式),则讲常量将转换为时间戳。例这样做是为了使ODBC更友好。但为了安全起见,在进行比较时,请始终使用完整的日期时间,日期或时间字符串。例如,为了在BETWEEN
与日期或时间值一起使用时获得最佳结果 ,可使用CAST()
将值显式转换为所需的数据类型 -
在其他情况下,都将参数作为浮点数(实数)进行比较。例如,比较字符串和数字操作数就可以比较浮点数。
以上自动转换规则不好记? 简单粗暴,就两点(忽略不常用的规则):
①日期时间:字符串形式的日期时间,可自动转换为对应的日期时间类型。
②数:除非都是整数,否则都转换为浮点数(实数)处理。