数据查询函数
11.1 MySQL函数简介
MySQL提供了丰富的内置函数，这些函数使得数据的维护与管理更加方便，能够更好地提供数据的分析与统计功能，在一定程度上提高了开发人员进行数据分析与统计的效率。同时，使用MySQL内置函数，在一定程度上不用编写复杂的查询和分析逻辑，通过SELECT查询语句并结合MySQL提供的函数，便可以完成相应的数据分析与统计。
MySQL提供的内置函数从实现的功能角度可以分为数学函数、字符串函数、日期和时间函数、流程处理函数、加密与解密函数、聚合函数、获取MySQL信息函数等。下面分别对MySQL内置的各个函数进行简单的介绍。
11.2 数学函数
MySQL内置的数学函数主要对数字进行处理，主要分为绝对值函数、圆周率函数、获取整数的函数、返回列表中的最大值与最小值函数、角度与弧度互换函数、三角函数、乘方与开方函数、对数函数、随机函数、四舍五入与数字截取函数、符号函数、数学运算函数。本节将对MySQL中提供的数学函数进行简单的介绍。
11.2.1 绝对值函数
函数ABS(X)获取X的绝对值，当X的值大于或等于0时，返回X本身的值；当X的值小于0时，返回X的绝对值。
使用绝对值函数ABS(X)求1、-1、0、3.14、-3.14的绝对值。
mysql> SELECT ABS(1), ABS(-1), ABS(0), ABS(3.14), ABS(-3.14);
+--------+---------+--------+-----------+------------+
| ABS(1) | ABS(-1) | ABS(0) | ABS(3.14) | ABS(-3.14) |
+--------+---------+--------+-----------+------------+
| 1 | 1 | 0 | 3.14 | 3.14 |
+--------+---------+--------+-----------+------------+
1 row in set (0.00 sec)
可以看到，无论是整数还是浮点数，使用ABS(X)函数求绝对值时，只要X的值大于或等于0时，返回X本身的值；只要X的值小于0时，返回X的绝对值。返回的结果数据总是一个大于或等于0的值。
11.2.2 圆周率函数
MySQL中的PI()函数用来获取圆周率的值，例如查看并返回圆周率PI的值。
mysql> SELECT PI();
+----------+
| PI() |
+----------+
| 3.141593 |
+----------+
1 row in set (0.00 sec)
可以看到，MySQL中默认将PI的值保留到小数点后6位。
11.2.3 获取整数的函数
1．CEIL(X)函数与CEILING(X)函数
CEIL(X)函数与CEILING(X)函数都可以获取大于或等于某个值的最小整数。例如获取大于或等于1、-1、3.14和-3.14的最小整数值。
mysql> SELECT CEIL(1), CEIL(-1), CEILING(3.14), CEILING(-3.14);
+---------+----------+---------------+----------------+
| CEIL(1) | CEIL(-1) | CEILING(3.14) | CEILING(-3.14) |
+---------+----------+---------------+----------------+
| 1 | -1 | 4 | -3 |
+---------+----------+---------------+----------------+
1 row in set (0.00 sec)
可以看到，大于或等1的最小整数为1，大于或等于-1的最小整数为-1，大于或等于3.14的最小整数为4，大于或等于-3.14的最小整数为-3。
2．FLOOR(X)函数
FLOOR(X)函数主要用来获取小于或等于某个值的最大整数，例如获取小于或等于1、-1、3.14和-3.14的最大整数值。
mysql> SELECT FLOOR(1), FLOOR(-1), FLOOR(3.14), FLOOR(-3.14);
+----------+-----------+-------------+--------------+
| FLOOR(1) | FLOOR(-1) | FLOOR(3.14) | FLOOR(-3.14) |
+----------+-----------+-------------+--------------+
| 1 | -1 | 3 | -4 |
+----------+-----------+-------------+--------------+
1 row in set (0.00 sec)
可以看到，小于或等于1的最大整数值为1，小于或等于-1的最大整数值为-1，小于或等于3.14的最大整数值为3，小于或等于-3.14的最大整数为-4。
11.2.4 返回列表中的最大值与最小值函数
1．LEAST(e1,e2,e3…)函数
LEAST(e1,e2,e3…)函数用于获取列表中的最小值，列表中的数据可以由数字组成，也可以由字符串组成。使用示例如下：
mysql> SELECT LEAST(2,3),LEAST(3.15, 2.16), LEAST('hello', 'world'), LEAST('a', 1);
+------------+-------------------+-------------------------+---------------+
| LEAST(2,3) | LEAST(3.15, 2.16) | LEAST('hello', 'world') | LEAST('a', 1) |
+------------+-------------------+-------------------------+---------------+
| 2 | 2.16 | hello | 1 |
+------------+-------------------+-------------------------+---------------+
1 row in set (0.01 sec)
2．GREATEST(e1,e2,e3…)函数
GREATEST(e1,e2,e3…)函数用于获取列表中的最大值，列表中的数据可以由数字组成，也可以由字符串组成。使用示例如下：
mysql> SELECT GREATEST(2,3), GREATEST(3.15, 2.16), GREATEST('hello', 'world'), GREATEST('a', 1);
+---------------+----------------------+----------------------------+------------------+
| GREATEST(2,3) | GREATEST(3.15, 2.16) | GREATEST('hello', 'world') | GREATEST('a', 1) |
+---------------+----------------------+----------------------------+------------------+
| 3 | 3.15 | world | a |
+---------------+----------------------+----------------------------+------------------+
1 row in set (0.00 sec)
注意：当列表中包含字符串时，比较的是字符串中每个字符的ANSI码。
11.2.5 角度与弧度互换函数
1．RADIANS(X)函数
RADIANS(X)函数用于将角度转化为弧度，其中，参数X为角度值。使用示例如下：
mysql> SELECT RADIANS(90),RADIANS(180),RADIANS(270);
+--------------------+-------------------+------------------+
| RADIANS(90) | RADIANS(180) | RADIANS(270) |
+--------------------+-------------------+------------------+
| 1.5707963267948966 | 3.141592653589793 | 4.71238898038469 |
+--------------------+-------------------+------------------+
1 row in set (0.00 sec)
可以看到，90°角对应的弧度值为1.5707963267948966，180°角对应的弧度值为3.141592653589793，也就是π的值，270°角对应的弧度值为4.71238898038469。
2．DEGREES(X)函数
DEGREES(X)函数可将弧度转化为角度，其中，参数X为弧度值。使用示例如下：
mysql> SELECT DEGREES(1.5707963267948966),DEGREES(3.141592653589793), DEGREES(4.71238898038469);
+-----------------------------+----------------------------+---------------------------+
| DEGREES(1.5707963267948966) | DEGREES(3.141592653589793) | DEGREES(4.71238898038469) |
+-----------------------------+----------------------------+---------------------------+
| 90 | 180 | 270 |
+-----------------------------+----------------------------+---------------------------+
1 row in set (0.00 sec)
11.2.6 三角函数
1．SIN(X)函数
SIN(X)函数返回X的正弦值，其中，参数X为弧度值。使用示例如下：
mysql> SELECT SIN(1), SIN(0), SIN(-1), SIN(PI());
+--------------------+--------+---------------------+------------------------+
| SIN(1) | SIN(0) | SIN(-1) | SIN(PI()) |
+--------------------+--------+---------------------+------------------------+
| 0.8414709848078965 | 0 | -0.8414709848078965 | 1.2246467991473532e-16 |
+--------------------+--------+---------------------+------------------------+
1 row in set (0.10 sec)
2．ASIN(X)函数
ASIN(X)函数返回X的反正弦值，即获取正弦为X的值，如果X的值不在-1到1之间，则结果返回NULL。使用示例如下：
mysql> SELECT ASIN(0.8414709848078965), ASIN(0), ASIN(-0.8414709848078965);
+--------------------------+---------+---------------------------+
| ASIN(0.8414709848078965) | ASIN(0) | ASIN(-0.8414709848078965) |
+--------------------------+---------+---------------------------+
| 1 | 0 | -1 |
+--------------------------+---------+---------------------------+
1 row in set (0.00 sec)
由结果可以看出，SIN(X)函数与ASIN(X)函数互为反函数。
3．COS(X)函数
COS(X)函数返回X的余弦值，其中，参数X为弧度值，使用示例如下：
mysql> SELECT COS(1), COS(0), COS(PI());
+--------------------+--------+-----------+
| COS(1) | COS(0) | COS(PI()) |
+--------------------+--------+-----------+
| 0.5403023058681398 | 1 | -1 |
+--------------------+--------+-----------+
1 row in set (0.00 sec)
4．ACOS(X)函数
ACOS(X)函数返回X的反余弦值，即返回余弦值为X的值，如果X的值不在-1到1之间，则返回NULL。使用示例如下：
mysql> SELECT ACOS(0.5403023058681398), ACOS(1), ACOS(-1);
+--------------------------+---------+-------------------+
| ACOS(0.5403023058681398) | ACOS(1) | ACOS(-1) |
+--------------------------+---------+-------------------+
| 1 | 0 | 3.141592653589793 |
+--------------------------+---------+-------------------+
1 row in set (0.00 sec)
可以看到，COS(X)函数与ACOS(X)函数互为反函数。
5．TAN(X)函数
TAN(X)函数返回X的正切值，其中，参数X为弧度值。使用示例如下：
mysql> SELECT TAN(1), TAN(0), TAN(0.5);
+--------------------+--------+--------------------+
| TAN(1) | TAN(0) | TAN(0.5) |
+--------------------+--------+--------------------+
| 1.5574077246549023 | 0 | 0.5463024898437905 |
+--------------------+--------+--------------------+
1 row in set (0.00 sec)
6．ATAN(X)函数
ATAN(X)函数返回X的反正切值，即返回正切值为X的值。使用示例如下：
mysql> SELECT ATAN(1.5574077246549023), ATAN(0), ATAN(0.5463024898437905);
+--------------------------+---------+--------------------------+
| ATAN(1.5574077246549023) | ATAN(0) | ATAN(0.5463024898437905) |
+--------------------------+---------+--------------------------+
| 1 | 0 | 0.5 |
+--------------------------+---------+--------------------------+
1 row in set (0.00 sec)
由结果数据可以看出，TAN(X)函数与ATAN(X)函数互为反函数。
7．ATAN2(M,N)函数
ATAN2(M,N)函数返回两个参数的反正切值。
与ATAN(X)函数相比，ATAN2(M,N)需要两个参数，例如有两个点point(x1,y1)和point(x2,y2)，使用ATAN(X)函数计算反正切值为ATAN((y2-y1)/(x2-x1))，使用ATAN2(M,N)计算反正切值则为ATAN2(y2-y1,x2-x1)。由使用方式可以看出，当x2-x1等于0时，ATAN(X)函数会报错，而ATAN2(M,N)函数则仍然可以计算。
ATAN2(M,N)函数的使用示例如下：
mysql> SELECT ATAN2(-0.8 ,1), ATAN2(1, 2);
+---------------------+--------------------+
| ATAN2(-0.8 ,1) | ATAN2(1, 2) |
+---------------------+--------------------+
| -0.6747409422235527 | 0.4636476090008061 |
+---------------------+--------------------+
1 row in set (0.00 sec)
8．COT(X)函数
COT(X)函数返回X的余切值，其中，X为弧度值。使用示例如下：
mysql> SELECT COT(1), COT(PI());
+--------------------+-----------------------+
| COT(1) | COT(PI()) |
+--------------------+-----------------------+
| 0.6420926159343306 | -8.165619676597685e15 |
+--------------------+-----------------------+
1 row in set (0.00 sec)
11.2.7 乘方与开方函数
1．POW(X,Y)函数
POW(X,Y)函数返回X的Y次方，使用示例如下：
mysql> SELECT POW(2, 4);
+-----------+
| POW(2, 4) |
+-----------+
| 16 |
+-----------+
1 row in set (0.00 sec)
2．POWER(X,Y)函数
POWER(X,Y)函数的作用与POW(X,Y)函数相同，不再赘述。
3．EXP(X)函数
EXP(X)函数返回e的X次方，其中e是一个常数，在MySQL中这个常数e的值为2.718281828459045。使用示例如下：
mysql> SELECT EXP(2), EXP(5);
+------------------+-------------------+
| EXP(2) | EXP(5) |
+------------------+-------------------+
| 7.38905609893065 | 148.4131591025766 |
+------------------+-------------------+
1 row in set (0.00 sec)
4．SQRT(X)的函数
SQRT(X)函数返回X的平方根，当X的值为负数时，返回NULL。使用示例如下：
mysql> SELECT SQRT(16), SQRT(0), SQRT(-16);
+----------+---------+-----------+
| SQRT(16) | SQRT(0) | SQRT(-16) |
+----------+---------+-----------+
| 4 | 0 | NULL |
+----------+---------+-----------+
1 row in set (0.00 sec)
11.2.8 对数函数
1．LN(X)函数
LN(X)函数返回以e为底的X的对数，当X的值小于或者等于0时，返回的结果为NULL。使用示例如下：
mysql> SELECT LN(100), LN(1), LN(0), LN(-1);
+-------------------+-------+-------+--------+
| LN(100) | LN(1) | LN(0) | LN(-1) |
+-------------------+-------+-------+--------+
| 4.605170185988092 | 0 | NULL | NULL |
+-------------------+-------+-------+--------+
1 row in set, 2 warnings (0.00 sec)
2．LOG(X)函数
LOG(X)函数的作用与LN(X)函数相同，不再赘述。
3．LOG10(X)函数
LOG10(X)函数返回以10为底的X的对数，当X的值小于或者等于0时，返回的结果为NULL。使用示例如下：
mysql> SELECT LOG10(100), LOG10(1), LOG10(0), LOG10(-1);
+------------+----------+----------+-----------+
| LOG10(100) | LOG10(1) | LOG10(0) | LOG10(-1) |
+------------+----------+----------+-----------+
| 2 | 0 | NULL | NULL |
+------------+----------+----------+-----------+
1 row in set, 2 warnings (0.00 sec)
4．LOG2(X)函数
LOG2(X)函数返回以2为底的X的对数，当X的值小于或等于0时，返回NULL。使用示例如下：
mysql> SELECT LOG2(100), LOG2(1), LOG2(0), LOG2(-1);
+-------------------+---------+---------+----------+
| LOG2(100) | LOG2(1) | LOG2(0) | LOG2(-1) |
+-------------------+---------+---------+----------+
| 6.643856189774724 | 0 | NULL | NULL |
+-------------------+---------+---------+----------+
1 row in set, 2 warnings (0.00 sec)
11.2.9 随机函数
1．RAND()函数
RAND()函数返回一个0到1之间的随机数。使用示例如下：
mysql> SELECT RAND(), RAND(), RAND();
+---------------------+--------------------+--------------------+
| RAND() | RAND() | RAND() |
+---------------------+--------------------+--------------------+
| 0.14092314908366943 | 0.6300797272753713 | 0.7276292198594931 |
+---------------------+--------------------+--------------------+
1 row in set (0.00 sec)
mysql> SELECT RAND(), RAND(), RAND();
+--------------------+--------------------+--------------------+
| RAND() | RAND() | RAND() |
+--------------------+--------------------+--------------------+
| 0.7479069482049969 | 0.5566471121801502 | 0.5395147470194053 |
+--------------------+--------------------+--------------------+
1 row in set (0.00 sec)
可以看到，每次执行的结果数据都不同，范围在0到1之间。
2．RAND(X)函数
RAND(X)函数返回一个范围在0到1之间的随机数，其中X的值用作种子值，相同的X值会产生重复的随机数。使用示例如下：
mysql> SELECT RAND(10),RAND(10),RAND(0), RAND(-10);
+--------------------+--------------------+---------------------+--------------------+
| RAND(10) | RAND(10) | RAND(0) | RAND(-10) |
+--------------------+--------------------+---------------------+--------------------+
| 0.6570515219653505 | 0.6570515219653505 | 0.15522042769493574 | 0.6533893371498113 |
+--------------------+--------------------+---------------------+--------------------+
1 row in set (0.00 sec)
11.2.10 四舍五入与数字截取函数
1．ROUND(X)函数
ROUND(X)函数返回一个对X的值进行四舍五入后，最接近于X的整数。使用示例如下：
mysql> SELECT ROUND(3.4), ROUND(3.5);
+------------+------------+
| ROUND(3.4) | ROUND(3.5) |
+------------+------------+
| 3 | 4 |
+------------+------------+
1 row in set (0.00 sec)
2．ROUND(X,Y)函数
ROUND(X,Y)函数返回一个对X的值进行四舍五入后最接近X的值，并保留到小数点后面Y位。如果Y的值为0，作用与ROUND(X)函数相同，如果Y的值为负数，则保留到小数点左边Y位。使用示例如下：
mysql> SELECT ROUND(3.145,2), ROUND(3.145, 0), ROUND(1308.789, -2);
+----------------+-----------------+---------------------+
| ROUND(3.145,2) | ROUND(3.145, 0) | ROUND(1308.789, -2) |
+----------------+-----------------+---------------------+
| 3.15 | 3 | 1300 |
+----------------+-----------------+---------------------+
1 row in set (0.00 sec)
3．TRUNCATE(X,Y)函数
TRUNCATE(X,Y)函数对X的值进行截断处理，保留到小数点后Y位。如果Y的值为0，则保留整数部分，如果Y的值为负数，则保留到小数点左边Y位。使用示例如下：
mysql> SELECT TRUNCATE(156.1516, 3), TRUNCATE(156.1516, 0), TRUNCATE(156.1516, -2);
+-----------------------+-----------------------+------------------------+
| TRUNCATE(156.1516, 3) | TRUNCATE(156.1516, 0) | TRUNCATE(156.1516, -2) |
+-----------------------+-----------------------+------------------------+
| 156.151 | 156 | 100 |
+-----------------------+-----------------------+------------------------+
1 row in set (0.00 sec)
注意：ROUND(X,Y)函数与TRUNCATE(X,Y)函数的区别如下。
·ROUND(X,Y)函数对X的值进行四舍五入操作，结果保留到小数点后Y位。如果Y的值为0，则保留整数部分，如果Y的值为负数，则保留到小数点左边Y位。
·TRUNCATE(X,Y)函数直接截断X的值，不进行四舍五入操作，结果保留到小数点后Y位。如果Y的值为0，则保留整数部分，如果Y的值为负数，则保留到小数点左边Y位。
11.2.11 符号函数
SIGN(X)函数将返回X的符号。如果X的值是一个正数，则结果返回1；如果X的值为0，则结果返回0；如果X的值是一个负数，则结果返回-1。使用示例如下：
mysql> SELECT SIGN(100), SIGN(0), SIGN(-100);
+-----------+---------+------------+
| SIGN(100) | SIGN(0) | SIGN(-100) |
+-----------+---------+------------+
| 1 | 0 | -1 |
+-----------+---------+------------+
1 row in set (0.00 sec)
mysql> SELECT SIGN(5), SIGN(0), SIGN(-200);
+---------+---------+------------+
| SIGN(5) | SIGN(0) | SIGN(-200) |
+---------+---------+------------+
| 1 | 0 | -1 |
+---------+---------+------------+
1 row in set (0.00 sec)
11.2.12 数学运算函数
1.DIV函数
DIV函数的使用方式为M DIV N，表示的含义为获取M除以N的整数结果值，当N为0时，将返回NULL。使用示例如下：
mysql> SELECT 16 DIV 5, 16 DIV -2, 16 DIV 0;
+----------+-----------+----------+
| 16 DIV 5 | 16 DIV -2 | 16 DIV 0 |
+----------+-----------+----------+
| 3 | -8 | NULL |
+----------+-----------+----------+
1 row in set, 1 warning (0.00 sec)
2．MOD(X,Y)函数
MOD(X,Y)函数返回X除以Y后的余数。当X能被Y整除时，返回0；当Y的值为0时，返回NULL。使用示例如下：
mysql> SELECT MOD(6, 4), MOD(6, 3), MOD(6, 0);
+-----------+-----------+-----------+
| MOD(6, 4) | MOD(6, 3) | MOD(6, 0) |
+-----------+-----------+-----------+
| 2 | 0 | NULL |
+-----------+-----------+-----------+
1 row in set, 1 warning (0.00 sec)
11.3 字符串函数
字符串函数主要用于处理数据库中的字符串数据，MySQL内置提供了丰富的字符串函数，极大方便了开发人员对于字符串的处理。下面分别介绍MySQL内置的这些字符串函数。
11.3.1 ASCII(S)函数
ASCII(S)函数返回字符串S中的第一个字符的ASCII码值。例如查看字符串abc和binghe的第一个字符的ASCII码。使用示例如下：
mysql> SELECT ASCII('abc'), ASCII('binghe');
+--------------+-----------------+
| ASCII('abc') | ASCII('binghe') |
+--------------+-----------------+
| 97 | 98 |
+--------------+-----------------+
1 row in set (0.00 sec)
可以看到，字符a的ASCII码为97，字符b的ASCII码为98。
11.3.2 CHAR_LENGTH(S)函数
CHAR_LENGTH(S)函数返回字符串S中的字符个数。使用示例如下：
mysql> SELECT CHAR_LENGTH('hello'), CHAR_LENGTH('你好'), CHAR_LENGTH(' ');
+----------------------+-----------------------+------------------+
| CHAR_LENGTH('hello') | CHAR_LENGTH('你好') | CHAR_LENGTH(' ') |
+----------------------+-----------------------+------------------+
| 5 | 2 | 1 |
+----------------------+-----------------------+------------------+
1 row in set (0.00 sec)
可以看到，一个字母、汉字和空格的字符个数都是1。
CHARACTER_LENGTH(S)函数的作用与CHAR_LENGTH(S)函数相同，不再赘述。
11.3.3 LENGTH(S)函数
LENGTH(S)函数返回字符串S的长度，这里的长度指的是字节数。使用示例如下：
mysql> SELECT LENGTH('hello'), LENGTH('你好'), LENGTH(' ');
+-----------------+------------------+-------------+
| LENGTH('hello') | LENGTH('你好') | LENGTH(' ') |
+-----------------+------------------+-------------+
| 5 | 6 | 1 |
+-----------------+------------------+-------------+
1 row in set (0.00 sec)
可以看到，当MySQL使用UTF-8编码或utf8mb4编码时，一个字母占用的长度为1个字节、一个汉字占用的长度为3个字节、一个空格占用的长度为1个字节。
11.3.4 CONCAT(S1,S2,…,Sn)函数
CONCAT(S1,S2,…Sn)函数将字符串S1,S2,…,Sn合并为一个字符串。使用示例如下：
mysql> SELECT CONCAT('hello', ' ', 'world');
+-------------------------------+
| CONCAT('hello', ' ', 'world') |
+-------------------------------+
| hello world |
+-------------------------------+
1 row in set (0.00 sec)
当函数中的任何一个字符串为NULL时，结果返回NULL。使用示例如下：
mysql> SELECT CONCAT('hello', NULL, 'world');
+--------------------------------+
| CONCAT('hello', NULL, 'world') |
+--------------------------------+
| NULL |
+--------------------------------+
1 row in set (0.00 sec)
11.3.5 CONCAT_WS(X,S1,S2,…,Sn)函数
CONCAT_WS(X,S1,S2,…,Sn)函数将字符串S1,S2,…,Sn拼接成一个以X分隔的字符串，其中，X可以是一个字符串，也可以是其他合法的参数。
mysql> SELECT CONCAT_WS(',','a','b');
+------------------------+
| CONCAT_WS(',','a','b') |
+------------------------+
| a,b |
+------------------------+
1 row in set (0.00 sec)
如果分隔符X为NULL，则结果返回NULL。
mysql> SELECT CONCAT_WS(NULL, 'a','b');
+--------------------------+
| CONCAT_WS(NULL, 'a','b') |
+--------------------------+
| NULL |
+--------------------------+
1 row in set (0.00 sec)
如果字符串S1,S2,…,Sn中的任何一个字符串为NULL，则函数会忽略为NULL的字符串。
mysql> SELECT CONCAT_WS(',','a',NULL,'b');
+-----------------------------+
| CONCAT_WS(',','a',NULL,'b') |
+-----------------------------+
| a,b |
+-----------------------------+
1 row in set (0.00 sec)
11.3.6 INSERT(oldstr,x,y,replacestr)函数
INSERT(oldstr,x,y,replacestr)函数将字符串oldstr从第x位置开始的y个字符长度的子字符串替换为replacestr。
将字符串hello world中的hello替换为hi。
mysql> SELECT INSERT('hello world',1,5,'hi');
+--------------------------------+
| INSERT('hello world',1,5,'hi') |
+--------------------------------+
| hi world |
+--------------------------------+
1 row in set (0.00 sec)
11.3.7 LOWER(S)函数
LOWER(S)函数将字符串S转化为小写。使用示例如下：
mysql> SELECT LOWER('HELLO WORLD'), LOWER('Hello World');
+----------------------+----------------------+
| LOWER('HELLO WORLD') | LOWER('Hello World') |
+----------------------+----------------------+
| hello world | hello world |
+----------------------+----------------------+
1 row in set (0.00 sec)
LCASE(S)函数的作用与LOWER(S)函数相同。使用示例如下：
mysql> SELECT LCASE('HELLO WORLD'), LCASE('Hello World');
+----------------------+----------------------+
| LCASE('HELLO WORLD') | LCASE('Hello World') |
+----------------------+----------------------+
| hello world | hello world |
+----------------------+----------------------+
1 row in set (0.00 sec)
可以看到，结果数据与LOWER(S)函数完全相同。
11.3.8 UPPER(S)函数
UPPER(S)函数将字符串S转化为大写。使用示例如下：
mysql> SELECT UPPER('hello world'), UPPER('Hello World');
+----------------------+----------------------+
| UPPER('hello world') | UPPER('Hello World') |
+----------------------+----------------------+
| HELLO WORLD | HELLO WORLD |
+----------------------+----------------------+
1 row in set (0.00 sec)
11.3.9 LEFT(str,x)函数
LEFT(str,x)函数返回字符串str最左边的x个字符组成的字符串，如果x的值为NULL，则返回NULL。使用示例如下：
mysql> SELECT LEFT('hello world', 5), LEFT('hello world', NULL);
+------------------------+---------------------------+
| LEFT('hello world', 5) | LEFT('hello world', NULL) |
+------------------------+---------------------------+
| hello | NULL |
+------------------------+---------------------------+
1 row in set (0.00 sec)
11.3.10 RIGHT(str,x)函数
RIGHT(str,x)函数返回字符串str最右边的x个字符组成的字符串，如果x的值为NULL，则返回NULL。使用示例如下：
mysql> SELECT RIGHT('hello world', 5), RIGHT('hello world', NULL);
+-------------------------+----------------------------+
| RIGHT('hello world', 5) | RIGHT('hello world', NULL) |
+-------------------------+----------------------------+
| world | NULL |
+-------------------------+----------------------------+
1 row in set (0.00 sec)
11.3.11 LPAD(str,n pstr)函数
LPAD(str,n pstr)函数使用字符串pstr对字符串str最左边进行填充，直到str字符串的长度达到n为止。使用示例如下：
mysql> SELECT LPAD('world', 11, 'hello ');
+-----------------------------+
| LPAD('world', 11, 'hello ') |
+-----------------------------+
| hello world |
+-----------------------------+
1 row in set (0.00 sec)
11.3.12 RPAD(str,n,pstr)函数
RPAD(str,n,pstr)函数使用字符串pstr对字符串str最右边进行填充，直到str字符串的长度达到n为止。使用示例如下：
mysql> SELECT RPAD('hello', 11, ' world');
+-----------------------------+
| RPAD('hello', 11, ' world') |
+-----------------------------+
| hello world |
+-----------------------------+
1 row in set (0.02 sec)
11.3.13 LTRIM(S)函数
LTRIM(S)函数用于去除字符串S左边的空格。使用示例如下：
mysql> SELECT LTRIM(' binghe');
+------------------+
| LTRIM(' binghe') |
+------------------+
| binghe |
+------------------+
1 row in set (0.00 sec)
11.3.14 RTRIM(S)函数
RTRIM(S)函数用于去除字符串S右边的空格。使用示例如下：
mysql> SELECT RTRIM('binghe ');
+------------------+
| RTRIM('binghe ') |
+------------------+
| binghe |
+------------------+
1 row in set (0.00 sec)
11.3.15 TRIM(S)函数
TRIM(S)函数用于去除字符串S两边的空格。使用示例如下：
mysql> SELECT TRIM(' binghe ');
+--------------------+
| TRIM(' binghe ') |
+--------------------+
| binghe |
+--------------------+
1 row in set (0.00 sec)
11.3.16 TRIM(substr FROM str)函数
TRIM(substr FROM str)函数用于删除字符串str首尾的子字符串substr，如果未指定substr，则默认删除空格。使用示例如下：
mysql> SELECT TRIM('hi' FROM 'hibinghehi'), TRIM(' binghe ');
+------------------------------+-------------------+
| TRIM('hi' FROM 'hibinghehi') | TRIM(' binghe ') |
+------------------------------+-------------------+
| binghe | binghe |
+------------------------------+-------------------+
1 row in set (0.00 sec)
11.3.17 REPEAT(str,x)函数
REPEAT(str,x)函数用于返回重复x次str的结果数据。使用示例如下：
mysql> SELECT REPEAT('binghe ', 4);
+------------------------------+
| REPEAT('binghe ', 4) |
+------------------------------+
| binghe binghe binghe binghe |
+------------------------------+
1 row in set (0.00 sec)
11.3.18 REPLACE(S,A,B)函数
REPLACE(S,A,B)函数用字符串B替换字符串S中出现的所有字符串A，并返回替换后的字符串。使用示例如下：
mysql> SELECT REPLACE('hello world, hello mysql', 'hello', 'hi');
+----------------------------------------------------+
| REPLACE('hello world, hello mysql', 'hello', 'hi') |
+----------------------------------------------------+
| hi world, hi mysql |
+----------------------------------------------------+
1 row in set (0.00 sec)
11.3.19 STRCMP(S1,S2)函数
STRCMP(S1,S2)函数用于比较字符串S1和字符串S2的ASCII码值的大小。如果S1的ASCII码值比S2的ASCII码值小，则返回-1；如果S1的ASCII码值与S2的ASCII码值相等，则返回0；如果S1的ASCII码值大于S2的ASCII码值，则返回1。使用示例如下：
mysql> SELECT STRCMP('a', 'b'), STRCMP('c', 'b'), STRCMP('a','a');
+------------------+------------------+-----------------+
| STRCMP('a', 'b') | STRCMP('c', 'b') | STRCMP('a','a') |
+------------------+------------------+-----------------+
| -1 | 1 | 0 |
+------------------+------------------+-----------------+
1 row in set (0.00 sec)
11.3.20 SUBSTR(S,X,Y)函数
SUBSTR(S,X,Y)函数返回从字符串S中从第X个位置开始，长度为Y的子字符串。当X的值小于0时，则将距离S结尾的第X个字符作为起始位置。使用示例如下：
mysql> SELECT SUBSTR('binghe',1,4), SUBSTR('binghe',1,6), SUBSTR('binghe', -2, 2);
+-------------------------+-------------------------+----------------------------+
| SUBSTR('binghe',1,4) | SUBSTR('binghe',1,6) | SUBSTR('binghe', -2, 2) |
+-------------------------+-------------------------+----------------------------+
| bing | binghe | he |
+-------------------------+-------------------------+----------------------------+
1 row in set (0.00 sec)
mysql> SELECT SUBSTR('binghe',0,2), SUBSTR('binghe',7,2), SUBSTR('binghe',1,0);
+-------------------------+-------------------------+-------------------------+
| SUBSTR('binghe',0,2) | SUBSTR('binghe',7,2) | SUBSTR('binghe',1,0) |
+-------------------------+-------------------------+-------------------------+
| | | |
+-------------------------+-------------------------+-------------------------+
1 row in set (0.00 sec)
SUBSTRING(S,X,Y)函数的作用与SUBSTR(S,X,Y)函数相同，不再赘述。
11.3.21 MID(S,X,Y)函数
MID(S,X,Y)函数的作用与SUBSTRING(S,X,Y)函数相同。使用示例如下：
mysql> SELECT MID('binghe',1,4), MID('binghe',1,6), MID('binghe', -2, 2);
+-------------------+-------------------+----------------------+
| MID('binghe',1,4) | MID('binghe',1,6) | MID('binghe', -2, 2) |
+-------------------+-------------------+----------------------+
| bing | binghe | he |
+-------------------+-------------------+----------------------+
1 row in set (0.00 sec)
mysql> SELECT MID('binghe',0,2), MID('binghe',7,2), MID('binghe',1,0);
+-------------------+-------------------+-------------------+
| MID('binghe',0,2) | MID('binghe',7,2) | MID('binghe',1,0) |
+-------------------+-------------------+-------------------+
| | | |
+-------------------+-------------------+-------------------+
1 row in set (0.00 sec)
可以看到，结果与SUBSTRING(S,X,Y)函数的结果相同。
11.3.22 SPACE(X)函数
SPACE(X)函数返回一个由X个空格组成的字符串。使用示例如下：
mysql> SELECT CONCAT('*', SPACE(6), '*');
+----------------------------+
| CONCAT('*', SPACE(6), '*') |
+----------------------------+
| * * |
+----------------------------+
1 row in set (0.00 sec)
可以看到，SPACE(6)返回了一个由6个空格组成的字符串。
11.3.23 LOCATE(substr,str)函数
LOCATE(substr,str)函数返回字符串substr在字符串str中的位置。使用示例如下：
mysql> SELECT LOCATE('he', 'binghe');
+------------------------+
| LOCATE('he', 'binghe') |
+------------------------+
| 5 |
+------------------------+
1 row in set (0.00 sec)
注意：MySQL中，字符串的位置是从1开始的。
POSITION(substr IN str)函数作用与LOCATE(substr,str)函数相同，返回字符串substr在字符串str中的位置。使用示例如下：
mysql> SELECT POSITION('he' IN 'binghe');
+----------------------------+
| POSITION('he' IN 'binghe') |
+----------------------------+
| 5 |
+----------------------------+
1 row in set (0.00 sec)
INSTR(str,substr)函数的作用与LOCATE(substr,str)函数相同，返回字符串substr在字符串str中的位置。使用示例如下：
mysql> SELECT INSTR('binghe','he');
+----------------------+
| INSTR('binghe','he') |
+----------------------+
| 5 |
+----------------------+
1 row in set (0.00 sec)
11.3.24 ELT(M,S1,S2,…,Sn)函数
ELT(M,S1,S2,…,Sn)函数返回指定指定位置的字符串，如果M=1，则返回S1，如果M=2，则返回S2，如果M=n，则返回Sn。使用示例如下：
mysql> SELECT ELT(2, 'hello', 'mysql');
+--------------------------+
| ELT(2, 'hello', 'mysql') |
+--------------------------+
| mysql |
+--------------------------+
1 row in set (0.00 sec)
11.3.25 FIELD(S,S1,S2,…,Sn)函数
FIELD(S,S1,S2,…,Sn)函数返回字符串S在字符串列表中第一次出现的位置。当字符串列表中不存在S时，则返回0；当S为NULL时，则返回0。使用示例如下：
mysql> SELECT FIELD('l', 'hello', 'world'), FIELD('a', 'hello', 'world'), FIELD(NULL, 'hello', 'world');
+------------------------------+------------------------------+-------------------------------+
| FIELD('l', 'hello', 'world') | FIELD('a', 'hello', 'world') | FIELD(NULL, 'hello', 'world') |
+------------------------------+------------------------------+-------------------------------+
| 0 | 0 | 0 |
+------------------------------+------------------------------+-------------------------------+
1 row in set (0.00 sec)
11.3.26 FIND_IN_SET(S1,S2)函数
FIND_IN_SET(S1,S2)函数返回字符串S1在字符串S2中出现的位置。其中，字符串S2是一个以逗号分隔的字符串。如果S1不在S2中，或者S2为空字符串，则返回0。当S1或S2为NULL时，返回NULL。使用示例如下：
mysql> SELECT FIND_IN_SET('bing', 'binghe,he,bing');
+---------------------------------------+
| FIND_IN_SET('bing', 'binghe,he,bing') |
+---------------------------------------+
| 3 |
+---------------------------------------+
1 row in set (0.01 sec)
mysql> SELECT FIND_IN_SET('bing', 'binghe,hello,world'), FIND_IN_SET('binghe','');
+-------------------------------------------+--------------------------+
| FIND_IN_SET('bing', 'binghe,hello,world') | FIND_IN_SET('binghe','') |
+-------------------------------------------+--------------------------+
| 0 | 0 |
+-------------------------------------------+--------------------------+
1 row in set (0.00 sec)
mysql> SELECT FIND_IN_SET(NULL, 'binghe,hello,world'), FIND_IN_SET('binghe',NULL);
+-----------------------------------------+----------------------------+
| FIND_IN_SET(NULL, 'binghe,hello,world') | FIND_IN_SET('binghe',NULL) |
+-----------------------------------------+----------------------------+
| NULL | NULL |
+-----------------------------------------+----------------------------+
1 row in set (0.00 sec)
11.3.27 REVERSE(S)函数
REVERSE(S)函数返回与字符串S顺序完全相反的字符串，即将字符串S反转。使用示例如下：
mysql> SELECT REVERSE('binghe');
+-------------------+
| REVERSE('binghe') |
+-------------------+
| ehgnib |
+-------------------+
1 row in set (0.00 sec)
11.3.28 NULLIF(value1,value2)函数
NULLIF(value1,value2)函数用于比较两个字符串，如果value1与value2相等，则返回NULL，否则返回value1。使用示例如下：
mysql> SELECT NULLIF('mysql','mysql'),NULLIF('mysql', '');
+-------------------------+---------------------+
| NULLIF('mysql','mysql') | NULLIF('mysql', '') |
+-------------------------+---------------------+
| NULL | mysql |
+-------------------------+---------------------+
1 row in set (0.00 sec)
11.4 日期和时间函数
MySQL中内置了大量的日期和时间函数，能够灵活、方便地处理日期和时间数据，本节就简单介绍一下MySQL中内置的日期和时间函数。
11.4.1 CURDATE()函数
CURDATE()函数用于返回当前日期，只包含年、月、日部分，格式为YYYY-MM-DD。使用示例如下：
mysql> SELECT CURDATE();
+------------+
| CURDATE() |
+------------+
| 2019-12-11 |
+------------+
1 row in set (0.00 sec)
CURRENT_DATE()函数的作用与CURDATE()函数相同，不再赘述。
11.4.2 CURTIME()函数
CURTIME()函数用于返回当前时间，只包含时、分、秒部分，格式为HH:MM:SS。使用示例如下：
mysql> SELECT CURTIME();
+-----------+
| CURTIME() |
+-----------+
| 11:27:44 |
+-----------+
1 row in set (0.00 sec)
CURRENT_TIME()函数的作用与CURTIME函数相同，不再赘述。
11.4.3 NOW()函数
NOW()函数用于返回当前日期和时间，包含年、月、日、时、分、秒，格式为YYYY-MM-DD HH:MM:SS。使用示例如下：
mysql> SELECT NOW();
+---------------------+
| NOW() |
+---------------------+
| 2019-12-15 11:29:22 |
+---------------------+
1 row in set (0.00 sec)
CURRENT_TIMESTAMP()函数、LOCALTIME()函数、LOCALTIMESTAMP()函数、SYSDATE()函数的作用与NOW()函数相同，不再赘述。
11.4.4 UNIX_TIMESTAMP(date)函数
将date转化为UNIX时间戳。使用示例如下：
mysql> SELECT UNIX_TIMESTAMP(now());
+-----------------------+
| UNIX_TIMESTAMP(now()) |
+-----------------------+
| 1576380910 |
+-----------------------+
1 row in set (0.01 sec)
mysql> SELECT UNIX_TIMESTAMP(CURDATE());
+---------------------------+
| UNIX_TIMESTAMP(CURDATE()) |
+---------------------------+
| 1576339200 |
+---------------------------+
1 row in set (0.00 sec)
mysql> SELECT UNIX_TIMESTAMP(CURTIME());
+---------------------------+
| UNIX_TIMESTAMP(CURTIME()) |
+---------------------------+
| 1576380969 |
+---------------------------+
1 row in set (0.00 sec)
11.4.5 FROM_UNIXTIME(timestamp)函数
FROM_UNIXTIME(timestamp)函数将UNIX时间戳转化为日期时间，格式为YYYY-MM-DD HH:MM:SS，与UNIX_TIMESTAMP(date)函数互为反函数。使用示例如下：
mysql> SELECT FROM_UNIXTIME(1576380910);
+---------------------------+
| FROM_UNIXTIME(1576380910) |
+---------------------------+
| 2019-12-15 11:35:10 |
+---------------------------+
1 row in set (0.00 sec)
11.4.6 UTC_DATE()函数
UTC_DATE()函数用于返回UTC日期。使用示例如下：
mysql> SELECT UTC_DATE();
+------------+
| UTC_DATE() |
+------------+
| 2019-12-15 |
+------------+
1 row in set (0.00 sec)
也可以返回YYYYMMDD格式的日期。使用示例如下：
mysql> SELECT UTC_DATE()+0;
+--------------+
| UTC_DATE()+0 |
+--------------+
| 20191215 |
+--------------+
1 row in set (0.00 sec)
11.4.7 UTC_TIME()函数
UTC_TIME()函数用于返回UTC时间。使用示例如下：
mysql> SELECT UTC_TIME();
+------------+
| UTC_TIME() |
+------------+
| 06:39:00 |
+------------+
1 row in set (0.00 sec)
11.4.8 YEAR(date)函数
YEAR(date)函数用于返回日期所在的年份，取值返回为1970～2069。使用示例如下：
mysql> SELECT YEAR(NOW());
+-------------+
| YEAR(NOW()) |
+-------------+
| 2019 |
+-------------+
1 row in set (0.00 sec)
注意：00～69会被转化为2000～2069，70～99会被转化为1970～1999。
11.4.9 MONTH(date)函数
MONTH(date)函数用于返回日期对应的月份，取值返回为1～12。使用示例如下：
mysql> SELECT MONTH(NOW());
+--------------+
| MONTH(NOW()) |
+--------------+
| 12 |
+--------------+
1 row in set (0.00 sec)
11.4.10 MONTHNAME(date)函数
MONTHNAME(date)函数用于返回日期所在月份的英文名称。使用示例如下：
mysql> SELECT MONTHNAME(NOW());
+------------------+
| MONTHNAME(NOW()) |
+------------------+
| December |
+------------------+
1 row in set (0.00 sec)
11.4.11 DAY(date)函数
DAY(date)函数只返回日期。使用示例如下：
mysql> SELECT DAY(NOW());
+------------+
| DAY(NOW()) |
+------------+
| 15 |
+------------+
1 row in set (0.00 sec)
11.4.12 DAYNAME(date)函数
DAYNAME(date)函数用于返回日期对应星期的英文名称。使用示例如下：
mysql> SELECT DAYNAME(NOW());
+----------------+
| DAYNAME(NOW()) |
+----------------+
| Sunday |
+----------------+
1 row in set (0.00 sec)
mysql> SELECT DAYNAME('2020-01-01');
+-----------------------+
| DAYNAME('2020-01-01') |
+-----------------------+
| Wednesday |
+-----------------------+
1 row in set (0.00 sec)
11.4.13 DAYOFWEEK(date)函数
DAYOFWEEK(date)函数用于返回日期对应的一周中的索引值。1表示星期日，2表示星期一，以此类推。使用示例如下：
mysql> SELECT DAYOFWEEK(NOW());
+------------------+
| DAYOFWEEK(NOW()) |
+------------------+
| 1 |
+------------------+
1 row in set (0.00 sec)
mysql> SELECT DAYOFWEEK('2020-01-01');
+-------------------------+
| DAYOFWEEK('2020-01-01') |
+-------------------------+
| 4 |
+-------------------------+
1 row in set (0.00 sec)
11.4.14 WEEKDAY(date)函数
WEEKDAY(date)函数返回日期对应的一周中的索引值。0表示星期一，1表示星期二，以此类推。使用示例如下：
mysql> SELECT WEEKDAY(NOW());
+----------------+
| WEEKDAY(NOW()) |
+----------------+
| 6 |
+----------------+
1 row in set (0.00 sec)
mysql> SELECT WEEKDAY('2020-01-01');
+-----------------------+
| WEEKDAY('2020-01-01') |
+-----------------------+
| 2 |
+-----------------------+
1 row in set (0.00 sec)
11.4.15 WEEK(date)函数
WEEK(date)函数返回给定日期是一年中的第几周。使用示例如下：
mysql> SELECT WEEK(NOW());
+-------------+
| WEEK(NOW()) |
+-------------+
| 50 |
+-------------+
1 row in set (0.00 sec)
11.4.16 WEEKOFYEAR(date)函数
WEEKOFYEAR(date)函数返回日期位于一年中的第几周。使用示例如下：
mysql> SELECT WEEKOFYEAR(NOW());
+-------------------+
| WEEKOFYEAR(NOW()) |
+-------------------+
| 50 |
+-------------------+
1 row in set (0.00 sec)
11.4.17 DAYOFYEAR(date)函数
DAYOFYEAR(date)函数返回日期是一年中的第几天。使用示例如下：
mysql> SELECT DAYOFYEAR(NOW());
+------------------+
| DAYOFYEAR(NOW()) |
+------------------+
| 349 |
+------------------+
1 row in set (0.00 sec)
11.4.18 DAYOFMONTH(date)函数
DAYOFMONTH(date)函数返回日期位于所在月份的第几天。使用示例如下：
mysql> SELECT DAYOFMONTH(NOW());
+-------------------+
| DAYOFMONTH(NOW()) |
+-------------------+
| 15 |
+-------------------+
1 row in set (0.00 sec)
11.4.19 QUARTER(date)函数
QUARTER(date)函数返回日期对应的季度，范围为1～4。使用示例如下：
mysql> SELECT QUARTER(NOW());
+----------------+
| QUARTER(NOW()) |
+----------------+
| 4 |
+----------------+
1 row in set (0.00 sec)
11.4.20 HOUR(time)函数
HOUR(time)函数返回指定时间的小时。使用示例如下：
mysql> SELECT HOUR(NOW());
+-------------+
| HOUR(NOW()) |
+-------------+
| 11 |
+-------------+
1 row in set (0.00 sec)
11.4.21 MINUTE(time)函数
MINUTE(time)函数返回指定时间的分钟，取值范围0～59。使用示例如下：
mysql> SELECT MINUTE(NOW());
+---------------+
| MINUTE(NOW()) |
+---------------+
| 45 |
+---------------+
1 row in set (0.00 sec)
11.4.22 SECOND(time)函数
SECOND(time)函数返回指定时间的秒数，取值范围0～59。使用示例如下：
mysql> SELECT SECOND(NOW());
+---------------+
| SECOND(NOW()) |
+---------------+
| 22 |
+---------------+
1 row in set (0.00 sec)
11.4.23 EXTRACT(type FROM date)函数
EXTRACT(type FROM date)函数返回指定日期中特定的部分，type指定返回的值。其中，type的取值如表11-1所示。
表11-1 EXTRACT(type FROM date)函数中type的取值与含义
注意：当EXTRACT(type FROM date)函数中的type取值为MINUTE_SECOND时，表示返回分钟和秒值，当date中的分钟为12，秒为12时，返回的结果为1212。也就是说，将分钟后面直接拼接上秒值。type取值为其他带有下划线的值时，也遵循同样的规律。
使用示例如下：
mysql> SELECT EXTRACT(HOUR_MINUTE FROM NOW());
+---------------------------------+
| EXTRACT(HOUR_MINUTE FROM NOW()) |
+---------------------------------+
| 2142 |
+---------------------------------+
1 row in set (0.00 sec)
11.4.24 TIME_TO_SEC(time)函数
TIME_TO_SEC(time)函数将time转化为秒并返回结果值。转化的公式为：小时*3600+分钟*60+秒。使用示例如下：
mysql> SELECT TIME_TO_SEC(NOW());
+--------------------+
| TIME_TO_SEC(NOW()) |
+--------------------+
| 78774 |
+--------------------+
1 row in set (0.00 sec)
11.4.25 SEC_TO_TIME(seconds)函数
SEC_TO_TIME(seconds)函数将seconds描述转化为包含小时、分钟和秒的时间。使用示例如下：
mysql> SELECT SEC_TO_TIME(78774);
+--------------------+
| SEC_TO_TIME(78774) |
+--------------------+
| 21:52:54 |
+--------------------+
1 row in set (0.12 sec)
11.4.26 DATE_ADD(date,INTERVAL expr type)函数
DATE_ADD(date,INTERVAL expr type)函数返回与date相差INTERVAL时间间隔的日期，本质上是日期的加操作。该函数中的type是间隔的类型，间隔类型如表11-2所示。
表11-2 DATE_ADD(date,INTERVAL expr type)函数中type的取值
使用示例如下：
mysql> SELECT DATE_ADD(NOW(), INTERVAL 1 DAY);
+---------------------------------+
| DATE_ADD(NOW(), INTERVAL 1 DAY) |
+---------------------------------+
| 2019-12-16 22:04:36 |
+---------------------------------+
1 row in set (0.00 sec)
ADDDATE(date,INTERVAL expr type)函数与DATE_ADD(date,INTERVAL expr type)函数的作用相同，不再赘述。
11.4.27 DATE_SUB(date,INTERVAL expr type)函数
DATE_SUB(date,INTERVAL expr type)函数返回与date相差INTERVAL时间间隔的日期，本质上是日期的减操作，其中type的取值见表11-2。使用示例如下：
mysql> SELECT DATE_SUB(NOW(), INTERVAL 1 DAY);
+---------------------------------+
| DATE_SUB(NOW(), INTERVAL 1 DAY) |
+---------------------------------+
| 2019-12-14 22:09:10 |
+---------------------------------+
1 row in set (0.00 sec)
SUBDATE(date,INTERVAL expr type)函数与DATE_SUB(date,INTERVAL expr type)函数作用相同，不再赘述。
注意：DATE_ADD、ADDDATE、DATE_SUB和SUBDATE这4个函数均可以指定负值。
11.4.28 ADDTIME(time1,time2)函数
ADDTIME(time1,time2)函数返回time1加上time2的时间。其中，time2是一个表达式，也可以是一个数字，当time2为一个数字时，代表的是秒。使用示例如下：
mysql> SELECT ADDTIME(NOW(), 50);
+---------------------+
| ADDTIME(NOW(), 50) |
+---------------------+
| 2019-12-15 22:17:47 |
+---------------------+
1 row in set (0.00 sec)
mysql> SELECT ADDTIME(NOW(), '1:1:1');
+-------------------------+
| ADDTIME(NOW(), '1:1:1') |
+-------------------------+
| 2019-12-15 23:18:46 |
+-------------------------+
1 row in set (0.00 sec)
ADDTIME(NOW(),'1:1:1')表示的含义为返回为当前时间加上1小时1分1秒之后的时间。
ADDTIME(time1,time2)函数中的time2的值也可以为负值。
mysql> SELECT ADDTIME(NOW(), '-1:-1:-1');
+----------------------------+
| ADDTIME(NOW(), '-1:-1:-1') |
+----------------------------+
| 2019-12-15 22:19:29 |
+----------------------------+
1 row in set, 1 warning (0.01 sec)
ADDTIME(NOW(),'-1:-1:-1')表示的含义为返回当前时间减去1小时1分1秒之后的时间。
11.4.29 SUBTIME(time1,time2)函数
SUBTIME(time1,time2)函数返回time1减去time2后的时间。其中，time2是一个表达式，也可以是一个数字，当time2为一个数字时，代表的是秒。使用示例如下：
mysql> SELECT SUBTIME(NOW(), 50);
+---------------------+
| SUBTIME(NOW(), 50) |
+---------------------+
| 2019-12-15 22:23:35 |
+---------------------+
1 row in set (0.00 sec)
mysql> SELECT SUBTIME(NOW(), '1:1:1');
+-------------------------+
| SUBTIME(NOW(), '1:1:1') |
+-------------------------+
| 2019-12-15 21:23:50 |
+-------------------------+
1 row in set (0.00 sec)
mysql> SELECT SUBTIME(NOW(), '-1:-1:-1');
+----------------------------+
| SUBTIME(NOW(), '-1:-1:-1') |
+----------------------------+
| 2019-12-15 22:25:11 |
+----------------------------+
1 row in set, 1 warning (0.00 sec)
11.4.30 DATEDIFF(date1,date2)函数
DATEDIFF(date1,date2)函数计算两个日期之间相差的天数。使用示例如下：
mysql> SELECT DATEDIFF(NOW(), '1970-01-01');
+-------------------------------+
| DATEDIFF(NOW(), '1970-01-01') |
+-------------------------------+
| 18245 |
+-------------------------------+
1 row in set (0.00 sec)
11.4.31 FROM_DAYS(N)函数
FROM_DAYS(N)函数返回从0000年1月1日起，N天以后的日期。使用示例如下：
mysql> SELECT FROM_DAYS(366);
+----------------+
| FROM_DAYS(366) |
+----------------+
| 0001-01-01 |
+----------------+
1 row in set (0.00 sec)
11.4.32 LAST_DAY(date)函数
LAST_DAY(date)函数返回date所在月份的最后一天的日期。使用示例如下：
mysql> SELECT LAST_DAY(NOW());
+-----------------+
| LAST_DAY(NOW()) |
+-----------------+
| 2019-12-31 |
+-----------------+
1 row in set (0.00 sec)
11.4.33 MAKEDATE(year,n)函数
MAKEDATE(year,n)函数针对给定年份与所在年份中的天数返回一个日期。使用示例如下：
mysql> SELECT MAKEDATE(2020,1);
+------------------+
| MAKEDATE(2020,1) |
+------------------+
| 2020-01-01 |
+------------------+
1 row in set (0.00 sec)
mysql> SELECT MAKEDATE(2020,32);
+-------------------+
| MAKEDATE(2020,32) |
+-------------------+
| 2020-02-01 |
+-------------------+
1 row in set (0.00 sec)
11.4.34 MAKETIME(hour,minute,second)函数
将给定的小时、分钟和秒组合成时间并返回。使用示例如下：
mysql> SELECT MAKETIME(1,1,1);
+-----------------+
| MAKETIME(1,1,1) |
+-----------------+
| 01:01:01 |
+-----------------+
1 row in set (0.00 sec)
11.4.35 PERIOD_ADD(time,n)函数
PERIOD_ADD(time,n)函数返回time加上n后的时间。使用示例如下：
mysql> SELECT PERIOD_ADD(20200101010101,1);
+------------------------------+
| PERIOD_ADD(20200101010101,1) |
+------------------------------+
| 20200101010102 |
+------------------------------+
1 row in set (0.00 sec)
11.4.36 TO_DAYS(date)函数
TO_DAYS(date)函数返回日期date距离0000年1月1日的天数。使用示例如下：
mysql> SELECT TO_DAYS(NOW());
+----------------+
| TO_DAYS(NOW()) |
+----------------+
| 737773 |
+----------------+
1 row in set (0.00 sec)
11.4.37 DATE_FORMAT(date,format)函数
DATE_FORMAT(date,format)函数按照指定的格式format来格式化日期date。其中，format常用的格式符如表11-3所示。
表11-3 DATE_FORMAT(date,format)函数中format常用的格式符
使用示例如下：
mysql> SELECT DATE_FORMAT(NOW(), '%H:%i:%s');
+--------------------------------+
| DATE_FORMAT(NOW(), '%H:%i:%s') |
+--------------------------------+
| 22:57:34 |
+--------------------------------+
1 row in set (0.00 sec)
11.4.38 TIME_FORMAT(time,format)函数
TIME_FORMAT(time,format)函数按照指定的格式format来格式化日期date。其中，format常用的格式符见表11-3。使用示例如下：
mysql> SELECT TIME_FORMAT(NOW(), '%H:%i:%s');
+--------------------------------+
| TIME_FORMAT(NOW(), '%H:%i:%s') |
+--------------------------------+
| 22:59:40 |
+--------------------------------+
1 row in set (0.00 sec)
11.4.39 GET_FORMAT(date_type,format_type)函数
GET_FORMAT(date_type,format_type)函数返回日期字符串的显示格式，其中date_type表示日期类型，format_type表示格式化类型。日期类型与格式化类型的取值如表11-4所示。
表11-4 GET_FORMAT函数返回的格式化字符串
使用示例如下：
mysql> SELECT GET_FORMAT(DATE, 'USA');
+-------------------------+
| GET_FORMAT(DATE, 'USA') |
+-------------------------+
| %m.%d.%Y |
+-------------------------+
1 row in set (0.00 sec)
11.4.40 STR_TO_DATE(str,format)函数
STR_TO_DATE(str,format)函数将字符串str按照format格式转化为日期或时间。其中，format的取值见表11-3。使用示例如下：
mysql> SELECT STR_TO_DATE('2020-01-01 00:00:00','%Y-%m-%d');
+-----------------------------------------------+
| STR_TO_DATE('2020-01-01 00:00:00','%Y-%m-%d') |
+-----------------------------------------------+
| 2020-01-01 |
+-----------------------------------------------+
1 row in set, 1 warning (0.00 sec)
11.5 流程处理函数
流程处理函数可以根据不同的条件，执行不同的处理流程，可以在SQL语句中实现不同的条件选择。MySQL中的流程处理函数主要包括IF()、IFNULL()和CASE()函数。
11.5.1 IF(value,value1,value2)函数
如果value的值为TRUE，则IF()函数返回value1，否则返回value2。使用示例如下：
mysql> SELECT IF(1<2, 1, 0), IF(1 > 2, 'yes', 'no');
+---------------+------------------------+
| IF(1<2, 1, 0) | IF(1 > 2, 'yes', 'no') |
+---------------+------------------------+
| 1 | no |
+---------------+------------------------+
1 row in set (0.00 sec)
11.5.2 IFNULL(value1,value2)函数
如果value1不为NULL，则IFNULL()函数返回value1，否则返回value2。使用示例如下：
mysql> SELECT IFNULL('hello', 'mysql'), IFNULL(NULL, 'mysql'), IFNULL(10/0, 'mysql');
+--------------------------+-----------------------+-----------------------+
| IFNULL('hello', 'mysql') | IFNULL(NULL, 'mysql') | IFNULL(10/0, 'mysql') |
+--------------------------+-----------------------+-----------------------+
| hello | mysql | mysql |
+--------------------------+-----------------------+-----------------------+
1 row in set, 1 warning (0.00 sec)
在数学运算中，0不能当作除数。在MySQL中，0当作除数时，结果返回NULL，所以，IFNULL(10/0,'mysql')返回mysql。
11.5.3 CASE WHEN THEN函数
对于CASE WHEN value1 THEN result1 [WHEN value2 THEN result2...] ELSE default END，如果WHEN后面的某个value值为TRUE，则返回当前WHEN条件对应的THEN语句后面的结果值；如果所有WHEN后面的value值都为FALSE，则返回ELSE后面的结果值。使用示例如下：
mysql> SELECT CASE WHEN 1 > 0 THEN 'yes' WHEN 1 <= 0 THEN 'no' ELSE 'unknown' END;
+---------------------------------------------------------------------+
| CASE WHEN 1 > 0 THEN 'yes' WHEN 1 <= 0 THEN 'no' ELSE 'unknown' END |
+---------------------------------------------------------------------+
| yes |
+---------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT CASE WHEN 1 < 0 THEN 'yes' WHEN 1 = 0 THEN 'no' ELSE 'unknown' END;
+--------------------------------------------------------------------+
| CASE WHEN 1 < 0 THEN 'yes' WHEN 1 = 0 THEN 'no' ELSE 'unknown' END |
+--------------------------------------------------------------------+
| unknown |
+--------------------------------------------------------------------+
1 row in set (0.00 sec)
11.5.4 CASE expr WHEN函数
对于CASE expr WHEN value1 THEN result1 [WHEN value2 THEN result2…] ELSE default END，如果expr的值与某个WHEN后面的值相等，则返回对应THEN后面的结果；如果expr的值与所有WHEN后面的值都不相等，则返回ELSE后面的结果。使用示例如下：
mysql> SELECT CASE 1 WHEN 0 THEN 0 WHEN 1 THEN 1 ELSE -1 END;
+------------------------------------------------+
| CASE 1 WHEN 0 THEN 0 WHEN 1 THEN 1 ELSE -1 END |
+------------------------------------------------+
| 1 |
+------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT CASE -1 WHEN 0 THEN 0 WHEN 1 THEN 1 ELSE -1 END;
+-------------------------------------------------+
| CASE -1 WHEN 0 THEN 0 WHEN 1 THEN 1 ELSE -1 END |
+-------------------------------------------------+
| -1 |
+-------------------------------------------------+
1 row in set (0.00 sec)
11.6 加密与解密函数
加密与解密函数主要用于对数据库中的数据进行加密和解密处理，以防止数据被他人窃取。MySQL中提供了内置的数据加密和解密函数，主要包括PASSWORD(value)函数、MD5(value)函数、ENCODE(value,password-seed)函数和DECODE(value,password-seed)函数。
11.6.1 PASSWORD(value)函数
PASSWORD(value)函数将明文密码value的值进行加密，返回加密后的密码字符串。如果value的值为NULL，则返回的结果为空。加密结果是单向、不可逆的。使用示例如下：
mysql> SELECT PASSWORD('mysql'), PASSWORD(NULL);
+-------------------------------------------+----------------+
| PASSWORD('mysql') | PASSWORD(NULL) |
+-------------------------------------------+----------------+
| *E74858DB86EBA20BC33D0AECAE8A8108C56B17FA | |
+-------------------------------------------+----------------+
1 row in set, 1 warning (0.00 sec)
11.6.2 MD5(value)函数
MD5(value)函数返回对value进行MD5加密后的结果值。如果value的值为NULL，则返回NULL。使用示例如下：
mysql> SELECT MD5('mysql'), MD5(NULL);
+----------------------------------+-----------+
| MD5('mysql') | MD5(NULL) |
+----------------------------------+-----------+
| 81c3b080dad537de7e10e0987a4bf52e | NULL |
+----------------------------------+-----------+
1 row in set (0.00 sec)
11.6.3 ENCODE(value,password_seed)函数
ENCODE(value,password_seed)函数返回使用password_seed作为密码加密value的结果值。使用示例如下：
mysql> SELECT ENCODE('mysql', 'mysql');
+--------------------------+
| ENCODE('mysql', 'mysql') |
+--------------------------+
| íg ¼ ìÉ |
+--------------------------+
1 row in set, 1 warning (0.01 sec)
11.6.4 DECODE(value,password_seed)函数
DECODE(value,password_seed)函数返回使用password_seed作为密码解密value的结果值。使用示例如下：
mysql> SELECT DECODE(ENCODE('mysql','mysql'),'mysql');
+-----------------------------------------+
| DECODE(ENCODE('mysql','mysql'),'mysql') |
+-----------------------------------------+
| mysql |
+-----------------------------------------+
1 row in set, 2 warnings (0.00 sec)
可以看到，ENCODE(value,password_seed)函数与DECODE(value,password_seed)函数互为反函数。
11.7 聚合函数
聚合函数是一类对数据库中的数据进行聚合统计的函数。MySQL中提供的聚合函数主要包括COUNT函数、MAX函数、MIN函数、SUM函数和AVG函数。
每个函数的作用不同，COUNT函数可以用来计算符合条件的数据表中的记录条数，MAX函数可以用来计算符合条件的最大值，MIN函数可以用来计算符合条件的最小值，SUM函数可以用来计算符合条件的记录的累加和，AVG函数可以用来计算符合条件的记录的平均值。为了更好地理解每个函数的作用，接下来将会以示例的形式详细说明每种函数的用法。在此之前，需要创建用于测试的数据库和数据表。首先创建名称为test的数据库，并在test数据库下创建employee数据表。
mysql> CREATE DATABASE test;
Query OK, 1 row affected (0.10 sec)
mysql> CREATE TABLE employee (
-> id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
-> name VARCHAR(50) NOT NULL DEFAULT '',
-> age INT NOT NULL DEFAULT 0
-> );
Query OK, 0 rows affected (0.13 sec)
接下来，向employee数据表中插入数据。
mysql> INSERT INTO employee(name, age)
-> VALUES('xiaoming', 20),
-> ('binghe',18),
-> ('xiaohong',25),
-> ('xiaoli', 19),
-> ('xiaogang', 29);
Query OK, 5 rows affected (0.01 sec)
Records: 5 Duplicates: 0 Warnings: 0
11.7.1 COUNT(*/字段名称)函数
COUNT(*/字段名称)函数的参数可以为“*”和数据表中的某个字段名称。使用示例如下：
mysql> SELECT COUNT(*), COUNT(id) FROM employee;
+----------+-----------+
| COUNT(*) | COUNT(id) |
+----------+-----------+
| 5 | 5 |
+----------+-----------+
1 row in set (0.06 sec)
11.7.2 MAX(字段名称)函数
MAX(字段名称)函数返回数据表中某列的最大值。使用示例如下：
mysql> SELECT MAX(age) FROM employee;
+----------+
| MAX(age) |
+----------+
| 29 |
+----------+
1 row in set (0.00 sec)
11.7.3 MIN(字段名称)函数
MIN(字段名称)函数返回数据表中某列的最小值。使用示例如下：
mysql> SELECT MIN(age) FROM employee;
+----------+
| MIN(age) |
+----------+
| 18 |
+----------+
1 row in set (0.00 sec)
11.7.4 SUM(字段名称)函数
SUM(字段名称)函数返回数据表中某列数据的求和结果。使用示例如下：
mysql> SELECT SUM(age) FROM employee;
+----------+
| SUM(age) |
+----------+
| 111 |
+----------+
1 row in set (0.00 sec)
11.7.5 AVG(字段名称)函数
AVG(字段名称)函数返回数据表中某列数据的平均值。使用示例如下：
mysql> SELECT AVG(age) FROM employee;
+----------+
| AVG(age) |
+----------+
| 22.2000 |
+----------+
1 row in set (0.00 sec)
使用聚合函数时需要注意以下几点：
·每个聚合函数需要传递一个参数，这个参数为数据表中的字段名称或者表达式（COUNT()函数也可以传递“*”作为参数）。
·统计的结果中默认会忽略字段为NULL的数据记录。
·如果需要数据表中字段为NULL的记录参与聚合函数的计算，则需要使用IFNULL(value1,value2)函数对数据表中字段为NULL的数据进行数据转换。
·聚合函数不能嵌套调用。比如不能出现类似“AVG(SUM(字段名称))”形式的调用。
11.8 获取MySQL信息函数
MySQL中内置了一些可以查询MySQL信息的函数，这些函数主要用于帮助数据库开发或运维人员更好地对数据库进行维护工作。
11.8.1 VERSION()函数
VERSION()函数返回当前MySQL的版本号。使用示例如下：
mysql> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 8.0.18 |
+-----------+
1 row in set (0.00 sec)
11.8.2 CONNECTION_ID()函数
CONNECTION_ID()函数返回当前MySQL服务器的连接数。使用示例如下：
mysql> SELECT CONNECTION_ID();
+-----------------+
| CONNECTION_ID() |
+-----------------+
| 8 |
+-----------------+
1 row in set (0.01 sec)
11.8.3 DATABASE()函数
DATABASE()函数返回MySQL命令行当前所在的数据库。使用示例如下：
mysql> SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| test |
+------------+
1 row in set (0.00 sec)
SCHEMA()函数的作用与DATABASE()函数相同。使用示例如下：
mysql> SELECT SCHEMA();
+----------+
| SCHEMA() |
+----------+
| test |
+----------+
1 row in set (0.00 sec)
11.8.4 USER()函数
USER()函数返回当前连接MySQL的用户名，返回结果格式为“主机名@用户名”。使用示例如下：
mysql> SELECT USER();
+----------------+
| USER() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)
另外，获取当前连接MySQL用户名的函数还有CURRENT_USER()、SYSTEM_USER()和SESSION_USER()。作用与USER()函数相同。使用示例如下：
mysql> SELECT USER(), CURRENT_USER(), SYSTEM_USER(),SESSION_USER();
+----------------+----------------+----------------+----------------+
| USER() | CURRENT_USER() | SYSTEM_USER() | SESSION_USER() |
+----------------+----------------+----------------+----------------+
| root@localhost | root@localhost | root@localhost | root@localhost |
+----------------+----------------+----------------+----------------+
1 row in set (0.00 sec)
11.8.5 LAST_INSERT_ID()函数
LAST_INSERT_ID()函数返回自增列最新的值。使用示例如下：
mysql> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
| 1 |
+------------------+
1 row in set (0.00 sec)
11.8.6 CHARSET(value)函数
CHARSET(value)函数用于查看MySQL使用的字符集。使用示例如下：
mysql> SELECT CHARSET('ABC');
+----------------+
| CHARSET('ABC') |
+----------------+
| utf8mb4 |
+----------------+
1 row in set (0.00 sec)
11.8.7 COLLATION(value)函数
COLLATION(value)函数用于返回字符串value的排序方式。使用示例如下：
mysql> SELECT COLLATION('ABC');
+--------------------+
| COLLATION('ABC') |
+--------------------+
| utf8mb4_general_ci |
+--------------------+
1 row in set (0.00 sec)
11.9 加锁与解锁函数
MySQL中提供了对数据进行加锁和解锁的函数，这些函数包括GET_LOCK(value,timeout)、RELEASE_LOCK(value)、IS_FREE_LOCK(value)和IS_USED_LOCK(value)函数。
11.9.1 GET_LOCK(value,timeout)函数
GET_LOCK(value,timeout)函数使用字符串value给定的名字获取锁，持续timeout秒。如果成功获取锁，则返回1，如果获取锁超时，则返回0，如果发生错误，则返回NULL。使用GET_LOCK(value,timeout)函数获取的锁，当执行RELEASE_LOCK(value)或断开数据库连接（包括正常断开和非正常断开），锁都会被解除。使用示例如下：
mysql> SELECT GET_LOCK('mysql',1000);
+------------------------+
| GET_LOCK('mysql',1000) |
+------------------------+
| 1 |
+------------------------+
1 row in set (0.00 sec)
获得一个名称为mysql，持续时间为1000s的锁。
11.9.2 RELEASE_LOCK(value)函数
RELEASE_LOCK(value)函数将以value命名的锁解除。如果解除成功，则返回1，如果线程还没有创建锁，则返回0，如果以value命名的锁不存在，则返回NULL。
mysql> SELECT RELEASE_LOCK('mysql');
+-----------------------+
| RELEASE_LOCK('mysql') |
+-----------------------+
| 1 |
+-----------------------+
1 row in set (0.10 sec)
注意：锁不存在包括两种情况。
·从未被GET_LOCK(value,timeout)函数获取过。
·锁已经被调用RELEASE_LOCK(value)函数释放过。
11.9.3 IS_FREE_LOCK(value)函数
IS_FREE_LOCK(value)函数判断以value命名的锁是否可以被使用。如果可以被使用，则返回1，如果不能使用，也就是说正在被使用，则返回0，如果发生错误，则返回NULL。使用示例如下：
mysql> SELECT IS_FREE_LOCK('mysql');
+-----------------------+
| IS_FREE_LOCK('mysql') |
+-----------------------+
| 1 |
+-----------------------+
1 row in set (0.00 sec)
11.9.4 IS_USED_LOCK(value)函数
IS_USED_LOCK(value)函数判断以value命名的锁是否正在被使用，如果正在被使用，则返回使用该锁的数据库连接ID，否则返回NULL。使用示例如下：
mysql> SELECT IS_USED_LOCK('mysql'), IS_USED_LOCK('test');
+-----------------------+----------------------+
| IS_USED_LOCK('mysql') | IS_USED_LOCK('test') |
+-----------------------+----------------------+
| NULL | 8 |
+-----------------------+----------------------+
1 row in set (0.00 sec)
11.10 JSON函数
JSON函数是对数据库中JSON数据类型的数据进行处理的函数，MySQL中内置了一系列的JSON函数。本节就简单介绍一下MySQL中常用的JSON函数。
为了更好地理解JSON函数的用法和作用，我们将会以示例的形式详细介绍每种JSON函数的作用和用法。首先，在名称为test的数据库中创建数据表test_json。
mysql> CREATE TABLE test_json (
-> id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
-> content JSON
-> );
Query OK, 0 rows affected (0.01 sec)
接下来，向test_json数据表中插入数据。
mysql> INSERT INTO test_json (content) VALUES('{"name":"binghe", "age":18, "address":{"province":
"sichuan", "city":"chengdu"}}');
Query OK, 1 row affected (0.00 sec)
可以使用“->”和“->>”查询JSON数据中指定的内容。
mysql> SELECT content->'$.name' FROM test_json WHERE id = 1;
+-------------------+
| content->'$.name' |
+-------------------+
| "binghe" |
+-------------------+
1 row in set (0.00 sec)
mysql> SELECT content->>'$.address.province' FROM test_json WHERE id = 1;
+--------------------------------+
| content->>'$.address.province' |
+--------------------------------+
| sichuan |
+--------------------------------+
1 row in set (0.00 sec)
11.10.1 JSON_CONTAINS(json_doc,value)函数
JSON_CONTAINS(json_doc,value)函数查询JSON类型的字段中是否包含value数据。如果包含则返回1，否则返回0。其中，json_doc为JSON类型的数据，value为要查找的数据。使用示例如下：
mysql> SELECT JSON_CONTAINS(content, '{"name":"binghe"}') FROM test_json WHERE id = 1;
+---------------------------------------------+
| JSON_CONTAINS(content, '{"name":"binghe"}') |
+---------------------------------------------+
| 1 |
+---------------------------------------------+
1 row in set (0.00 sec)
注意：value必须是一个JSON字符串。
11.10.2 JSON_SEARCH(json_doc ->> '$[*].key',type,value)函数
JSON_SEARCH(json_doc ->> '$[*].key',type,value)函数在JSON类型的字段指定的key中，查找字符串value。如果找到value值，则返回索引数据。使用示例如下：
mysql> SELECT JSON_SEARCH(content ->> '$.address', 'one', 'sichuan') FROM test_json WHERE id = 1;
+--------------------------------------------------------+
| JSON_SEARCH(content ->> '$.address', 'one', 'sichuan') |
+--------------------------------------------------------+
| "$.province" |
+--------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT JSON_SEARCH(content ->> '$.address', 'all', 'sichuan') FROM test_json WHERE id = 1;
+--------------------------------------------------------+
| JSON_SEARCH(content ->> '$.address', 'all', 'sichuan') |
+--------------------------------------------------------+
| "$.province" |
+--------------------------------------------------------+
1 row in set (0.00 sec)
注意：函数的第二个参数type，取值可以是one或者all。当取值为one时，如果找到value值，则返回value值的第一个索引数据；当取值为all时，如果找到value值，则返回value值的所有索引数据。
11.10.3 JSON_PRETTY(json_doc)函数
JSON_PRETTY(json_doc)函数以优雅的格式显示JSON数据。使用示例如下：
mysql> SELECT JSON_PRETTY(content) FROM test_json WHERE id = 1;
+----------------------------------------------------------------------+
| JSON_PRETTY(content) |
+----------------------------------------------------------------------+
| {
"age": 18,
"name": "binghe",
"address": {
"city": "chengdu",
"province": "sichuan"
}
} |
+----------------------------------------------------------------------+
1 row in set (0.00 sec)
11.10.4 JSON_DEPTH(json_doc)函数
JSON_DEPTH(json_doc)函数返回JSON数据的最大深度。使用示例如下：
mysql> SELECT JSON_DEPTH(content) FROM test_json WHERE id = 1;
+---------------------+
| JSON_DEPTH(content) |
+---------------------+
| 3 |
+---------------------+
1 row in set (0.05 sec)
11.10.5 JSON_LENGTH(json_doc[,path])函数
JSON_LENGTH(json_doc[,path])函数返回JSON数据的长度。使用示例如下：
mysql> SELECT JSON_LENGTH(content) FROM test_json WHERE id = 1;
+----------------------+
| JSON_LENGTH(content) |
+----------------------+
| 3 |
+----------------------+
1 row in set (0.00 sec)
11.10.6 JSON_KEYS(json_doc[,path])函数
JSON_KEYS(json_doc[,path])函数返回JSON数据中顶层key组成的JSON数组。使用示例如下：
mysql> SELECT JSON_KEYS(content) FROM test_json WHERE id = 1;
+----------------------------+
| JSON_KEYS(content) |
+----------------------------+
| ["age", "name", "address"] |
+----------------------------+
1 row in set (0.00 sec)
11.10.7 JSON_INSERT(json_doc,path,val[,path,val] ...)函数
JSON_INSERT(json_doc,path,val[,path,val] ...)函数用于向JSON数据中插入数据。使用示例如下：
mysql> SELECT JSON_INSERT(content, '$.address.zip_code','000000') FROM test_json WHERE id = 1;
+------------------------------------------------------------------------------------------------+
| JSON_INSERT(content, '$.address.zip_code','000000') |
+------------------------------------------------------------------------------------------------+
| {"age": 18, "name": "binghe", "address": {"city": "chengdu", "province": "sichuan", "zip_code": "000000"}} |
+------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
接下来，查看test_json表中的数据。
mysql> SELECT JSON_PRETTY(content) FROM test_json WHERE id = 1;
+---------------------------------------------------------------------+
| JSON_PRETTY(content) |
+---------------------------------------------------------------------+
| {
"age": 18,
"name": "binghe",
"address": {
"city": "chengdu",
"province": "sichuan"
}
} |
+---------------------------------------------------------------------+
1 row in set (0.00 sec)
可以看到，JSON_INSERT()函数并没有更新数据表中的数据，只是修改了显示结果。
11.10.8 JSON_REMOVE(json_doc,path[,path] ...)函数
JSON_REMOVE(json_doc,path[,path] ...)函数用于移除JSON数据中指定key的数据。使用示例如下：
mysql> SELECT JSON_REMOVE(content, '$.address.city') FROM test_json WHERE id = 1;
+-------------------------------------------------------------------+
| JSON_REMOVE(content, '$.address.city') |
+-------------------------------------------------------------------+
| {"age": 18, "name": "binghe", "address": {"province": "sichuan"}} |
+-------------------------------------------------------------------+
1 row in set (0.00 sec)
接下来，查看test_json表中的数据。
mysql> SELECT JSON_PRETTY(content) FROM test_json WHERE id = 1;
+---------------------------------------------------------------------+
| JSON_PRETTY(content) |
+---------------------------------------------------------------------+
| {
"age": 18,
"name": "binghe",
"address": {
"city": "chengdu",
"province": "sichuan"
}
} |
+---------------------------------------------------------------------+
1 row in set (0.00 sec)
可以看到，JSON_REMOVE()函数并没有更新数据表中的数据，只是修改了显示结果。
11.10.9 JSON_REPLACE(json_doc,path,val[,path,val] ...)函数
JSON_REPLACE(json_doc,path,val[,path,val] ...)函数用于更新JSON数据中指定Key的数据。使用示例如下：
mysql> SELECT JSON_REPLACE(content,'$.age',20) FROM test_json WHERE id = 1;
+--------------------------------------------------------------------------------------+
| JSON_REPLACE(content,'$.age',20) |
+--------------------------------------------------------------------------------------+
| {"age": 20, "name": "binghe", "address": {"city": "chengdu", "province": "sichuan"}} |
+--------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
接下来，查看test_json表中的数据。
mysql> SELECT JSON_PRETTY(content) FROM test_json WHERE id = 1;
+------------------------------------------------------------------------------------------------+
| JSON_PRETTY(content)
+------------------------------------------------------------------------------------------------+
| {
"age": 18,
"name": "binghe",
"address": {
"city": "chengdu",
"province": "sichuan"
}
} |
+------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
可以看到，JSON_REPLACE()函数并没有更新数据表中的数据，只是修改了显示结果。
11.10.10 JSON_SET(json_doc,path,val[,path,val] ...)函数
JSON_SET(json_doc,path,val[,path,val] ...)函数用于向JSON数据中插入数据。使用示例如下：
mysql> SELECT JSON_SET(content, '$.address.street', 'xxx街道') FROM test_json WHERE id = 1;
+------------------------------------------------------------------------------------------------+
| JSON_SET(content, '$.address.street', 'xxx街道') |
+------------------------------------------------------------------------------------------------+
| {"age": 18, "name": "binghe", "address": {"city": "chengdu", "street": "xxx街道", "province": "sichuan"}} |
+------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
接下来，查看test_json表中的数据。
mysql> SELECT JSON_PRETTY(content) FROM test_json WHERE id = 1;
+---------------------------------------------------------------------+
| JSON_PRETTY(content) |
+---------------------------------------------------------------------+
| {
"age": 18,
"name": "binghe",
"address": {
"city": "chengdu",
"province": "sichuan"
}
} |
+---------------------------------------------------------------------+
1 row in set (0.00 sec)
11.10.11 JSON_TYPE(json_val)函数
JSON_TYPE(json_val)函数用于返回JSON数据的JSON类型，MySQL中支持的JSON类型除了可以是MySQL中的数据类型外，还可以是OBJECT和ARRAY类型，其中OBJECT表示JSON对象，ARRAY表示JSON数组。使用示例如下：
mysql> SELECT JSON_TYPE(content) FROM test_json WHERE id = 1;
+--------------------+
| JSON_TYPE(content) |
+--------------------+
| OBJECT |
+--------------------+
1 row in set (0.00 sec)
11.10.12 JSON_VALID(value)函数
JSON_VALID(value)函数用于判断value的值是否是有效的JSON数据，如果是，则返回1，否则返回0，如果value的值为NULL，则返回NULL。使用示例如下：
mysql> SELECT JSON_VALID('{"name":"binghe"}'), JSON_VALID('name'), JSON_VALID(NULL);
+---------------------------------+--------------------+------------------+
| JSON_VALID('{"name":"binghe"}') | JSON_VALID('name') | JSON_VALID(NULL) |
+---------------------------------+--------------------+------------------+
| 1 | 0 | NULL |
+---------------------------------+--------------------+------------------+
1 row in set (0.00 sec)
注意：读者也可以到链接https://dev.mysql.com/doc/refman/8.0/en/json-function-reference.html中了解更多关于JSON函数的知识。
11.11 窗口函数
MySQL从8.0版本开始支持窗口函数，其中，窗口可以理解为数据的集合。窗口函数也就是在符合某种条件或者某些条件的记录集合中执行的函数，窗口函数会在每条记录上执行。窗口函数可以分为静态窗口函数和动态窗口函数，其中，静态窗口函数的窗口大小是固定的，不会因为记录的不同而不同；动态窗口函数的窗口大小会随着记录的不同而变化。
窗口函数总体上可以分为序号函数、分布函数、前后函数、首尾函数和其他函数，如表11-5所示。
表11-5 MySQL窗口函数分类
窗口函数的基本用法格式如下：
函数名 ([expr]) over子句
over关键字指定函数窗口的范围，如果省略后面括号中的内容，则窗口会包含满足WHERE条件的所有记录，窗口函数会基于所有满足WHERE条件的记录进行计算。如果over关键字后面的括号不为空，则可以使用如下语法设置窗口。
·window_name：为窗口设置一个别名，用来标识窗口。
·PARTITION BY子句：指定窗口函数按照哪些字段进行分组。分组后，窗口函数可以在每个分组中分别执行。
·ORDER BY子句：指定窗口函数按照哪些字段进行排序。执行排序操作使窗口函数按照排序后的数据记录的顺序进行编号。
·FRAME子句：为分区中的某个子集定义规则，可以用来作为滑动窗口使用。
以第8章中创建的t_goods数据表为例，向t_goods数据表中插入数据。
mysql> INSERT INTO t_goods (t_category_id, t_category, t_name, t_price, t_stock, t_upper_time) VALUES
-> (1, '女装/女士精品', 'T恤', 39.90, 1000, '2020-11-10 00:00:00'),
-> (1, '女装/女士精品', '连衣裙', 79.90, 2500, '2020-11-10 00:00:00'),
-> (1, '女装/女士精品', '卫衣', 89.90, 1500, '2020-11-10 00:00:00'),
-> (1, '女装/女士精品', '牛仔裤', 89.90, 3500, '2020-11-10 00:00:00'),
-> (1, '女装/女士精品', '百褶裙', 29.90, 500, '2020-11-10 00:00:00'),
-> (1, '女装/女士精品', '呢绒外套', 399.90, 1200, '2020-11-10 00:00:00'),
-> (2, '户外运动', '自行车', 399.90, 1000, '2020-11-10 00:00:00'),
-> (2, '户外运动', '山地自行车', 1399.90, 2500, '2020-11-10 00:00:00'),
-> (2, '户外运动', '登山杖', 59.90, 1500, '2020-11-10 00:00:00'),
-> (2, '户外运动', '骑行装备', 399.90, 3500, '2020-11-10 00:00:00'),
-> (2, '户外运动', '运动外套', 799.90, 500, '2020-11-10 00:00:00'),
-> (2, '户外运动', '滑板', 499.90, 1200, '2020-11-10 00:00:00');
Query OK, 12 rows affected (0.01 sec)
Records: 12 Duplicates: 0 Warnings: 0
下面针对t_goods表中的数据来验证每个窗口函数的功能。
11.11.1 序号函数
1．ROW_NUMBER()函数
ROW_NUMBER()函数能够对数据中的序号进行顺序显示。
例如，查询t_goods数据表中每个商品分类下价格最高的3种商品信息。
mysql> SELECT * FROM
-> (
-> SELECT
-> ROW_NUMBER() OVER(PARTITION BY t_category_id ORDER BY t_price DESC) AS row_num,
-> id, t_category_id, t_category, t_name, t_price, t_stock
-> FROM t_goods) t
-> WHERE row_num <= 3;
+---------+----+---------------+---------------------+-----------------+---------+---------+
| row_num | id | t_category_id | t_category | t_name | t_price | t_stock |
+---------+----+---------------+---------------------+-----------------+---------+---------+
| 1 | 6 | 1 | 女装/女士精品 | 呢绒外套 | 399.90 | 1200 |
| 2 | 3 | 1 | 女装/女士精品 | 卫衣 | 89.90 | 1500 |
| 3 | 4 | 1 | 女装/女士精品 | 牛仔裤 | 89.90 | 3500 |
| 1 | 8 | 2 | 户外运动 | 山地自行车 | 1399.90 | 2500 |
| 2 | 11 | 2 | 户外运动 | 运动外套 | 799.90 | 500 |
| 3 | 12 | 2 | 户外运动 | 滑板 | 499.90 | 1200 |
+---------+----+---------------+---------------------+-----------------+---------+---------+
6 rows in set (0.00 sec)
在名称为“女装/女士精品”的商品类别中，有两款商品的价格为89.90元，分别是卫衣和牛仔裤。两款商品的序号都应该为2，而不是一个为2，另一个为3。此时，可以使用RANK()函数和DENSE_RANK()函数解决。
2．RANK()函数
使用RANK()函数能够对序号进行并列排序，并且会跳过重复的序号，比如序号为1、1、3。
例如，使用RANK()函数获取t_goods数据表中类别为“女装/女士精品”的价格最高的4款商品信息。
mysql> SELECT * FROM
-> (
-> SELECT
-> RANK() OVER(PARTITION BY t_category_id ORDER BY t_price DESC) AS row_num,
-> id, t_category_id, t_category, t_name, t_price, t_stock
-> FROM t_goods) t
-> WHERE t_category_id = 1 AND row_num <= 4;
+---------+----+---------------+---------------------+--------------+---------+---------+
| row_num | id | t_category_id | t_category | t_name | t_price | t_stock |
+---------+----+---------------+---------------------+--------------+---------+---------+
| 1 | 6 | 1 | 女装/女士精品 | 呢绒外套 | 399.90 | 1200 |
| 2 | 3 | 1 | 女装/女士精品 | 卫衣 | 89.90 | 1500 |
| 2 | 4 | 1 | 女装/女士精品 | 牛仔裤 | 89.90 | 3500 |
| 4 | 2 | 1 | 女装/女士精品 | 连衣裙 | 79.90 | 2500 |
+---------+----+---------------+---------------------+--------------+---------+---------+
4 rows in set (0.00 sec)
可以看到，使用RANK()函数得出的序号为1、2、2、4，相同价格的商品序号相同，后面的商品序号是不连续的，跳过了重复的序号。
3．DENSE_RANK()函数
DENSE_RANK()函数对序号进行并列排序，并且不会跳过重复的序号，比如序号为1、1、2。
例如，使用DENSE_RANK()函数获取t_goods数据表中类别为“女装/女士精品”的价格最高的4款商品信息。
mysql> SELECT * FROM
-> (
-> SELECT
-> DENSE_RANK() OVER(PARTITION BY t_category_id ORDER BY t_price DESC) AS row_num,
-> id, t_category_id, t_category, t_name, t_price, t_stock
-> FROM t_goods) t
-> WHERE t_category_id = 1 AND row_num <= 3;
+---------+----+---------------+---------------------+--------------+---------+---------+
| row_num | id | t_category_id | t_category | t_name | t_price | t_stock |
+---------+----+---------------+---------------------+--------------+---------+---------+
| 1 | 6 | 1 | 女装/女士精品 | 呢绒外套 | 399.90 | 1200 |
| 2 | 3 | 1 | 女装/女士精品 | 卫衣 | 89.90 | 1500 |
| 2 | 4 | 1 | 女装/女士精品 | 牛仔裤 | 89.90 | 3500 |
| 3 | 2 | 1 | 女装/女士精品 | 连衣裙 | 79.90 | 2500 |
+---------+----+---------------+---------------------+--------------+---------+---------+
4 rows in set (0.00 sec)
可以看到，使用DENSE_RANK()函数得出的行号为1、2、2、3，相同价格的商品序号相同，后面的商品序号是连续的，并且没有跳过重复的序号。
11.11.2 分布函数
1．PERCENT_RANK()函数
PERCENT_RANK()函数是等级值百分比函数。按照如下方式进行计算。
(rank - 1) / (rows - 1)
其中，rank的值为使用RANK()函数产生的序号，rows的值为当前窗口的总记录数。
例如，计算t_goods数据表中名称为“女装/女士精品”的类别下的商品的PERCENT_RANK值。
mysql> SELECT
-> RANK() OVER w AS r,
-> PERCENT_RANK() OVER w AS pr,
-> id, t_category_id, t_category, t_name, t_price, t_stock
-> FROM t_goods
-> WHERE t_category_id = 1
-> WINDOW w AS (PARTITION BY t_category_id ORDER BY t_price DESC);
+---+-----+----+---------------+---------------------+--------------+---------+---------+
| r | pr | id | t_category_id | t_category | t_name | t_price | t_stock |
+---+-----+----+---------------+---------------------+--------------+---------+---------+
| 1 | 0 | 6 | 1 | 女装/女士精品 | 呢绒外套 | 399.90 | 1200 |
| 2 | 0.2 | 3 | 1 | 女装/女士精品 | 卫衣 | 89.90 | 1500 |
| 2 | 0.2 | 4 | 1 | 女装/女士精品 | 牛仔裤 | 89.90 | 3500 |
| 4 | 0.6 | 2 | 1 | 女装/女士精品 | 连衣裙 | 79.90 | 2500 |
| 5 | 0.8 | 1 | 1 | 女装/女士精品 | T恤 | 39.90 | 1000 |
| 6 | 1 | 5 | 1 | 女装/女士精品 | 百褶裙 | 29.90 | 500 |
+---+-----+----+---------------+---------------------+--------------+---------+---------+
6 rows in set (0.00 sec)
2．CUME_DIST()函数
CUME_DIST()函数主要用于查询小于或等于某个值的比例。
例如，查询t_goods数据表中小于或等于当前价格的比例。
mysql> SELECT
-> CUME_DIST() OVER(PARTITION BY t_category_id ORDER BY t_price DESC) AS cd,
-> id, t_category, t_name, t_price
-> FROM t_goods;
+---------------------+----+---------------------+-----------------+---------+
| cd | id | t_category | t_name | t_price |
+---------------------+----+---------------------+-----------------+---------+
| 0.16666666666666666 | 6 | 女装/女士精品 | 呢绒外套 | 399.90 |
| 0.5 | 3 | 女装/女士精品 | 卫衣 | 89.90 |
| 0.5 | 4 | 女装/女士精品 | 牛仔裤 | 89.90 |
| 0.6666666666666666 | 2 | 女装/女士精品 | 连衣裙 | 79.90 |
| 0.8333333333333334 | 1 | 女装/女士精品 | T恤 | 39.90 |
| 1 | 5 | 女装/女士精品 | 百褶裙 | 29.90 |
| 0.16666666666666666 | 8 | 户外运动 | 山地自行车 | 1399.90 |
| 0.3333333333333333 | 11 | 户外运动 | 运动外套 | 799.90 |
| 0.5 | 12 | 户外运动 | 滑板 | 499.90 |
| 0.8333333333333334 | 7 | 户外运动 | 自行车 | 399.90 |
| 0.8333333333333334 | 10 | 户外运动 | 骑行装备 | 399.90 |
| 1 | 9 | 户外运动 | 登山杖 | 59.90 |
+---------------------+----+---------------------+-----------------+---------+
12 rows in set (0.01 sec)
11.11.3 前后函数
1．LAG(expr,n)函数
LAG(expr,n)函数返回当前行的前n行的expr的值。
例如，查询t_goods数据表中前一个商品价格与当前商品价格的差值。
mysql> SELECT id, t_category, t_name, t_price, pre_price,
-> t_price - pre_price AS diff_price
-> FROM (
-> SELECT id, t_category, t_name, t_price,
-> LAG(t_price,1) OVER w AS pre_price
-> FROM t_goods
-> WINDOW w AS (PARTITION BY t_category_id ORDER BY t_price)) t;
+----+---------------------+-----------------+---------+-----------+------------+
| id | t_category | t_name | t_price | pre_price | diff_price |
+----+---------------------+-----------------+---------+-----------+------------+
| 5 | 女装/女士精品 | 百褶裙 | 29.90 | NULL | NULL |
| 1 | 女装/女士精品 | T恤 | 39.90 | 29.90 | 10.00 |
| 2 | 女装/女士精品 | 连衣裙 | 79.90 | 39.90 | 40.00 |
| 3 | 女装/女士精品 | 卫衣 | 89.90 | 79.90 | 10.00 |
| 4 | 女装/女士精品 | 牛仔裤 | 89.90 | 89.90 | 0.00 |
| 6 | 女装/女士精品 | 呢绒外套 | 399.90 | 89.90 | 310.00 |
| 9 | 户外运动 | 登山杖 | 59.90 | NULL | NULL |
| 7 | 户外运动 | 自行车 | 399.90 | 59.90 | 340.00 |
| 10 | 户外运动 | 骑行装备 | 399.90 | 399.90 | 0.00 |
| 12 | 户外运动 | 滑板 | 499.90 | 399.90 | 100.00 |
| 11 | 户外运动 | 运动外套 | 799.90 | 499.90 | 300.00 |
| 8 | 户外运动 | 山地自行车 | 1399.90 | 799.90 | 600.00 |
+----+---------------------+-----------------+---------+-----------+------------+
12 rows in set (0.00 sec)
2．LEAD(expr,n)函数
LEAD(expr,n)函数返回当前行的后n行的expr的值。
例如，查询t_goods数据表中后一个商品价格与当前商品价格的差值。
mysql> SELECT id, t_category, t_name, behind_price, t_price,
-> behind_price - t_price AS diff_price
-> FROM(
-> SELECT id, t_category, t_name, t_price,
-> LEAD(t_price, 1) OVER w AS behind_price
-> FROM t_goods
-> WINDOW w AS (PARTITION BY t_category_id ORDER BY t_price)) t;
+----+---------------------+-----------------+--------------+---------+------------+
| id | t_category | t_name | behind_price | t_price | diff_price |
+----+---------------------+-----------------+--------------+---------+------------+
| 5 | 女装/女士精品 | 百褶裙 | 39.90 | 29.90 | 10.00 |
| 1 | 女装/女士精品 | T恤 | 79.90 | 39.90 | 40.00 |
| 2 | 女装/女士精品 | 连衣裙 | 89.90 | 79.90 | 10.00 |
| 3 | 女装/女士精品 | 卫衣 | 89.90 | 89.90 | 0.00 |
| 4 | 女装/女士精品 | 牛仔裤 | 399.90 | 89.90 | 310.00 |
| 6 | 女装/女士精品 | 呢绒外套 | NULL | 399.90 | NULL |
| 9 | 户外运动 | 登山杖 | 399.90 | 59.90 | 340.00 |
| 7 | 户外运动 | 自行车 | 399.90 | 399.90 | 0.00 |
| 10 | 户外运动 | 骑行装备 | 499.90 | 399.90 | 100.00 |
| 12 | 户外运动 | 滑板 | 799.90 | 499.90 | 300.00 |
| 11 | 户外运动 | 运动外套 | 1399.90 | 799.90 | 600.00 |
| 8 | 户外运动 | 山地自行车 | NULL | 1399.90 | NULL |
+----+---------------------+-----------------+--------------+---------+------------+
12 rows in set (0.00 sec)
11.11.4 首尾函数
1．FIRST_VALUE(expr)函数
FIRST_VALUE(expr)函数返回第一个expr的值。
例如，按照价格排序，查询第1个商品的价格信息。
mysql> SELECT id, t_category, t_name, t_price, t_stock,
-> FIRST_VALUE(t_price) OVER w AS first_price
-> FROM t_goods
-> WINDOW w AS (PARTITION BY t_category_id ORDER BY t_price);
+----+---------------------+-----------------+---------+---------+-------------+
| id | t_category | t_name | t_price | t_stock | first_price |
+----+---------------------+-----------------+---------+---------+-------------+
| 5 | 女装/女士精品 | 百褶裙 | 29.90 | 500 | 29.90 |
| 1 | 女装/女士精品 | T恤 | 39.90 | 1000 | 29.90 |
| 2 | 女装/女士精品 | 连衣裙 | 79.90 | 2500 | 29.90 |
| 3 | 女装/女士精品 | 卫衣 | 89.90 | 1500 | 29.90 |
| 4 | 女装/女士精品 | 牛仔裤 | 89.90 | 3500 | 29.90 |
| 6 | 女装/女士精品 | 呢绒外套 | 399.90 | 1200 | 29.90 |
| 9 | 户外运动 | 登山杖 | 59.90 | 1500 | 59.90 |
| 7 | 户外运动 | 自行车 | 399.90 | 1000 | 59.90 |
| 10 | 户外运动 | 骑行装备 | 399.90 | 3500 | 59.90 |
| 12 | 户外运动 | 滑板 | 499.90 | 1200 | 59.90 |
| 11 | 户外运动 | 运动外套 | 799.90 | 500 | 59.90 |
| 8 | 户外运动 | 山地自行车 | 1399.90 | 2500 | 59.90 |
+----+---------------------+-----------------+---------+---------+-------------+
12 rows in set (0.00 sec)
2．LAST_VALUE(expr)函数
LAST_VALUE(expr)函数返回最后一个expr的值。
例如，按照价格排序，查询最后一个商品的价格信息。
mysql> SELECT id, t_category, t_name, t_price, t_stock,
-> LAST_VALUE(t_price) OVER w AS last_price
-> FROM t_goods
-> WINDOW w AS (PARTITION BY t_category_id ORDER BY t_price);
+----+---------------------+-----------------+---------+---------+------------+
| id | t_category | t_name | t_price | t_stock | last_price |
+----+---------------------+-----------------+---------+---------+------------+
| 5 | 女装/女士精品 | 百褶裙 | 29.90 | 500 | 29.90 |
| 1 | 女装/女士精品 | T恤 | 39.90 | 1000 | 39.90 |
| 2 | 女装/女士精品 | 连衣裙 | 79.90 | 2500 | 79.90 |
| 3 | 女装/女士精品 | 卫衣 | 89.90 | 1500 | 89.90 |
| 4 | 女装/女士精品 | 牛仔裤 | 89.90 | 3500 | 89.90 |
| 6 | 女装/女士精品 | 呢绒外套 | 399.90 | 1200 | 399.90 |
| 9 | 户外运动 | 登山杖 | 59.90 | 1500 | 59.90 |
| 7 | 户外运动 | 自行车 | 399.90 | 1000 | 399.90 |
| 10 | 户外运动 | 骑行装备 | 399.90 | 3500 | 399.90 |
| 12 | 户外运动 | 滑板 | 499.90 | 1200 | 499.90 |
| 11 | 户外运动 | 运动外套 | 799.90 | 500 | 799.90 |
| 8 | 户外运动 | 山地自行车 | 1399.90 | 2500 | 1399.90 |
+----+---------------------+-----------------+---------+---------+------------+
12 rows in set (0.00 sec)
11.11.5 其他函数
1．NTH_VALUE(expr,n)函数
NTH_VALUE(expr,n)函数返回第n个expr的值。
例如，查询t_goods数据表中排名第3和第4的价格信息。
mysql> SELECT id, t_category, t_name, t_price,
-> NTH_VALUE(t_price,2) OVER w AS second_price,
-> NTH_VALUE(t_price,3) OVER w AS third_price
-> FROM t_goods
-> WINDOW w AS (PARTITION BY t_category_id ORDER BY t_price);
+----+---------------------+-----------------+---------+--------------+-------------+
| id | t_category | t_name | t_price | second_price | third_price |
+----+---------------------+-----------------+---------+--------------+-------------+
| 5 | 女装/女士精品 | 百褶裙 | 29.90 | NULL | NULL |
| 1 | 女装/女士精品 | T恤 | 39.90 | 39.90 | NULL |
| 2 | 女装/女士精品 | 连衣裙 | 79.90 | 39.90 | 79.90 |
| 3 | 女装/女士精品 | 卫衣 | 89.90 | 39.90 | 79.90 |
| 4 | 女装/女士精品 | 牛仔裤 | 89.90 | 39.90 | 79.90 |
| 6 | 女装/女士精品 | 呢绒外套 | 399.90 | 39.90 | 79.90 |
| 9 | 户外运动 | 登山杖 | 59.90 | NULL | NULL |
| 7 | 户外运动 | 自行车 | 399.90 | 399.90 | 399.90 |
| 10 | 户外运动 | 骑行装备 | 399.90 | 399.90 | 399.90 |
| 12 | 户外运动 | 滑板 | 499.90 | 399.90 | 399.90 |
| 11 | 户外运动 | 运动外套 | 799.90 | 399.90 | 399.90 |
| 8 | 户外运动 | 山地自行车 | 1399.90 | 399.90 | 399.90 |
+----+---------------------+-----------------+---------+--------------+-------------+
12 rows in set (0.00 sec)
2．NTILE(n)函数
NTILE(n)函数将分区中的有序数据分为n个桶，记录桶编号。
例如，将t_goods表中的商品按照价格分为3组。
mysql> SELECT
-> NTILE(3) OVER w AS nt,
-> id, t_category, t_name, t_price
-> FROM t_goods
-> WINDOW w AS (PARTITION BY t_category_id ORDER BY t_price);
+------+----+---------------------+-----------------+---------+
| nt | id | t_category | t_name | t_price |
+------+----+---------------------+-----------------+---------+
| 1 | 5 | 女装/女士精品 | 百褶裙 | 29.90 |
| 1 | 1 | 女装/女士精品 | T恤 | 39.90 |
| 2 | 2 | 女装/女士精品 | 连衣裙 | 79.90 |
| 2 | 3 | 女装/女士精品 | 卫衣 | 89.90 |
| 3 | 4 | 女装/女士精品 | 牛仔裤 | 89.90 |
| 3 | 6 | 女装/女士精品 | 呢绒外套 | 399.90 |
| 1 | 9 | 户外运动 | 登山杖 | 59.90 |
| 1 | 7 | 户外运动 | 自行车 | 399.90 |
| 2 | 10 | 户外运动 | 骑行装备 | 399.90 |
| 2 | 12 | 户外运动 | 滑板 | 499.90 |
| 3 | 11 | 户外运动 | 运动外套 | 799.90 |
| 3 | 8 | 户外运动 | 山地自行车 | 1399.90 |
+------+----+---------------------+-----------------+---------+
12 rows in set (0.00 sec)
注意：读者也可以到MySQL官方网站了解窗口函数的使用，MySQL官方网站窗口函数的网址为https://dev.mysql.com/doc/refman/8.0/en/window-function-descriptions.html#function_row-number。
11.12 MySQL的其他函数
MySQL中有些函数无法对其进行具体的分类，但是这些函数在MySQL的开发和运维过程中也是不容忽视的，本节就简单介绍一下MySQL中无法对其进行具体分类的函数。
11.12.1 FORMAT(value,n)函数
FORMAT(value,n)函数返回对数字value进行格式化后的结果数据，其中n表示四舍五入后保留到小数点后n位。如果n的值小于或者等于0，则只保留整数部分。
mysql> SELECT FORMAT(123.123, 2), FORMAT(123.523, 0), FORMAT(123.123, -2);
+--------------------+--------------------+---------------------+
| FORMAT(123.123, 2) | FORMAT(123.523, 0) | FORMAT(123.123, -2) |
+--------------------+--------------------+---------------------+
| 123.12 | 124 | 123 |
+--------------------+--------------------+---------------------+
1 row in set (0.00 sec)
11.12.2 CONV(value,from,to)函数
CONV(value,from,to)函数将value的值进行不同进制之间的转换，value是一个整数，如果任意一个参数为NULL，则结果返回NULL。使用示例如下：
mysql> SELECT CONV(16, 10, 2), CONV(8888,10,16), CONV(NULL, 10, 2);
+-----------------+------------------+-------------------+
| CONV(16, 10, 2) | CONV(8888,10,16) | CONV(NULL, 10, 2) |
+-----------------+------------------+-------------------+
| 10000 | 22B8 | NULL |
+-----------------+------------------+-------------------+
1 row in set (0.00 sec)
11.12.3 INET_ATON(value)函数
INET_ATON(value)函数将以点分隔的IP地址转化为一个数字表示，其中，value为以点表示的IP地址。使用示例如下：
mysql> SELECT INET_ATON('192.168.1.100');
+----------------------------+
| INET_ATON('192.168.1.100') |
+----------------------------+
| 3232235876 |
+----------------------------+
1 row in set (0.00 sec)
以“192.168.1.100”为例，计算方式为192乘以256的3次方，加上168乘以256的2次方，加上1乘以256，再加上100。
11.12.4 INET_NTOA(value)函数
INET_NTOA(value)函数将数字形式的IP地址转化为以点分隔的IP地址。使用示例如下：
mysql> SELECT INET_NTOA(3232235876);
+-----------------------+
| INET_NTOA(3232235876) |
+-----------------------+
| 192.168.1.100 |
+-----------------------+
1 row in set (0.00 sec)
可以看到，INET_ATON(value)函数与INET_NTOA(value)函数互为反函数。
11.12.5 BENCHMARK(n,expr)函数
BENCHMARK(n,expr)函数将表达式expr重复执行n次，主要用于测试MySQL处理expr表达式所耗费的时间。使用示例如下：
mysql> SELECT BENCHMARK(1, MD5('mysql'));
+----------------------------+
| BENCHMARK(1, MD5('mysql')) |
+----------------------------+
| 0 |
+----------------------------+
1 row in set (0.00 sec)
可以看到，对字符串mysql执行1次MD5加密几乎没有耗时。
mysql> SELECT BENCHMARK(1000000, MD5('mysql'));
+----------------------------------+
| BENCHMARK(1000000, MD5('mysql')) |
+----------------------------------+
| 0 |
+----------------------------------+
1 row in set (0.20 sec)
可以看到，对字符串mysql执行1000000次MD5加密耗时0.20s。
11.12.6 CAST(value AS type)函数
CAST(value AS type)函数将value转换为type类型的值，其中type的取值如表11-6所示。
表11-6 CAST(value AS type)函数中type可取的值
使用示例如下：
mysql> SELECT CAST('123' AS SIGNED);
+-----------------------+
| CAST('123' AS SIGNED) |
+-----------------------+
| 123 |
+-----------------------+
1 row in set (0.00 sec)
CONVERT(value,type)函数的作用与CAST(value AS type)函数相同，不再赘述。
11.12.7 CONVERT(value USING char_code)函数
将value所使用的字符编码修改为char_code，使用示例如下：
mysql> SELECT CHARSET('mysql'), CHARSET(CONVERT('mysql' USING 'utf8'));
+------------------+----------------------------------------+
| CHARSET('mysql') | CHARSET(CONVERT('mysql' USING 'utf8')) |
+------------------+----------------------------------------+
| utf8mb4 | utf8 |
+------------------+----------------------------------------+
1 row in set, 1 warning (0.00 sec)
可以看到，MySQL对字符串默认使用的字符编码为utf8mb4，可以使用CONVERT(value USING char_code)函数将其修改为UTF-8。
- 点赞
- 收藏
- 关注作者
评论（0）