MySQL学习笔记03-优快云博客

本文链接：https://blog.youkuaiyun.com/Zoey_ov/article/details/135897221

本文详细介绍了MySQL的常用函数，包括字符串、数值、日期和时间、流程、JSON、窗口等函数。阐述了各函数的用法、实例及注意事项，如字符串函数的连接、大小写转换，JSON函数的创建、查询与修改等，为开发人员编写SQL语句提供了帮助。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

五、MySQL常用函数

MySQL提供了多种内建函数帮助开发人员编写简单快捷的SQL语句，其中常用的函数有字符串函数、日期函数和数值函数。

在MySQL数据库中，函数可以用在SELECT语句及其子句中，也可用在UPDATE、DELETE及其子句中。

5.1字符串函数

字符串函数是最常用的一种函数。

表5-1 MySQL中的常用字符串函数
函数	功能
CONCAT(s1,s2,...,sn)	连接s1,s2,....sn为一个字符串
INSERT(str,x,y,instr)	将字符串str从第x位置开始，y个字符长的字串替换为字符串instr
LOWER(str)	将字符串str中所有的字符变为小写
UPPER(str)	将字符串str中所有的字符变为大写
LEFT(str,x)	返回字符串str最左边的x个字符
RIGHT(str,x)	返回字符串str最右边的x个字符
LPAD(str,n,pad)	用字符串pad对str最左边进行填充，直到长度为n个字符长度
RPAD(str,n,pad)	用字符串pad对str最右边进行填充，直到长度为n个字符长度
LTRIM(str)	去掉字符串str左侧的空格
RTRIM(str)	去掉字符串str右侧的空格
REPEAT(str)	返回字符串str重复x次的结果
REPLACE(str,a,b)	用字符串b替换字符串str中所有出现的字符串a
STRCMP(s1,s2)	比较字符串s1和s2
TRIM(str)	去掉字符串中行尾和行头的空格
SUBSTRING(str,x,y)	返回从字符串str的第x个位置（包含第x位置的字符）起y个字符长度的字串

下面给出每个函数的用法实例，在实际应用中可能需要综合几个或者几类函数才能实现相应的应用。

（1）CONCAT函数

注：任何字符串与NULL进行连接的结果都是NULL。

mysql> SELECT CONCAT('aaa','bbb','ccc'),CONCAT('aaa',null);
+---------------------------+--------------------+
| CONCAT('aaa','bbb','ccc') | CONCAT('aaa',null) |
+---------------------------+--------------------+
| aaabbbccc                 | NULL               |
+---------------------------+--------------------+
1 row in set (0.00 sec)

（2）INSERT函数

mysql> SELECT INSERT('beijing2008you',12,3,'me');
+------------------------------------+
| INSERT('beijing2008you',12,3,'me') |
+------------------------------------+
| beijing2008me                      |
+------------------------------------+
1 row in set (0.00 sec)

（3）LOWER函数和UPPER函数

mysql> SELECT LOWER('BEIJING2008'),UPPER('beijing2008');
+----------------------+----------------------+
| LOWER('BEIJING2008') | UPPER('beijing2008') |
+----------------------+----------------------+
| beijing2008          | BEIJING2008          |
+----------------------+----------------------+
1 row in set (0.00 sec)

（4）LEFT函数和RIGHT函数

注：如果函数中第二个参数是NULL，则不返回任何字符。

mysql> SELECT LEFT('beijing2008',7),LEFT('beijing2008',null),RIGHT('beijing2008',4);
+-----------------------+--------------------------+------------------------+
| LEFT('beijing2008',7) | LEFT('beijing2008',null) | RIGHT('beijing2008',4) |
+-----------------------+--------------------------+------------------------+
| beijing               | NULL                     | 2008                   |
+-----------------------+--------------------------+------------------------+
1 row in set (0.00 sec)

（5）LPAD函数和RPAD函数

mysql> SELECT LPAD('2008',30,'beijing'),RPAD('beijing',30,'2008');
+--------------------------------+--------------------------------+
| LPAD('2008',30,'beijing')      | RPAD('beijing',30,'2008')      |
+--------------------------------+--------------------------------+
| beijingbeijingbeijingbeiji2008 | beijing20082008200820082008200 |
+--------------------------------+--------------------------------+
1 row in set (0.00 sec)

（6）LTRIM函数和RTRIM函数以及TRIM函数

mysql> SELECT LTRIM('    |beijing'),RTRIM('beijing|    '),TRIM('    |beijing|    ');
+-----------------------+-----------------------+---------------------------+
| LTRIM('    |beijing') | RTRIM('beijing|    ') | TRIM('    |beijing|    ') |
+-----------------------+-----------------------+---------------------------+
| |beijing              | beijing|              | |beijing|                 |
+-----------------------+-----------------------+---------------------------+
1 row in set (0.00 sec)

（7）REPEAT函数

mysql> SELECT REPEAT('mysql ',3);
+--------------------+
| REPEAT('mysql ',3) |
+--------------------+
| mysql mysql mysql  |
+--------------------+
1 row in set (0.00 sec)

（8）REPLACE函数

mysql> SELECT REPLACE('beijing2010beijing2011beijing2010','2011','2010');
+------------------------------------------------------------+
| REPLACE('beijing2010beijing2011beijing2010','2011','2010') |
+------------------------------------------------------------+
| beijing2010beijing2010beijing2010                          |
+------------------------------------------------------------+
1 row in set (0.00 sec)

（9）STRCMP函数：比较字符串s1和s2的ASCII码值的大小，如果s1比s2小，返回-1,；如果s1

与s2相等，返回0；如果s1比s2大，返回1。

mysql> SELECT STRCMP('a','b'),STRCMP('b','b'),STRCMP('b','c');
+-----------------+-----------------+-----------------+
| STRCMP('a','b') | STRCMP('b','b') | STRCMP('b','c') |
+-----------------+-----------------+-----------------+
|              -1 |               0 |              -1 |
+-----------------+-----------------+-----------------+
1 row in set (0.00 sec)

（10）SUBSTRING函数

mysql> SELECT SUBSTRING('beijing2008',8,4),SUBSTRING('beijing2008',1,7);
+------------------------------+------------------------------+
| SUBSTRING('beijing2008',8,4) | SUBSTRING('beijing2008',1,7) |
+------------------------------+------------------------------+
| 2008                         | beijing                      |
+------------------------------+------------------------------+
1 row in set (0.00 sec)

5.2数值函数

表5-2 MySQL中常用数值函数
函数	功能
ABS(x)	返回x的绝对值
CEIL(x)	返回大于x的最小整数值
FLOOR(x)	返回小于x的最大整数值
MOD(x,y)	返回x/y的模
RAND(x)	返回0~1内的随机值
ROUND(x,y)	返回参数x的四舍五入的有y位小数的值
TRUNCATE(x,y)	返回数字x截断为y位小数的结果

下面给出这些函数的应用实例。

（1）ABS函数

mysql> SELECT ABS(-0.8),ABS(0.8);
+-----------+----------+
| ABS(-0.8) | ABS(0.8) |
+-----------+----------+
|       0.8 |      0.8 |
+-----------+----------+
1 row in set (0.00 sec)

（2）CEIL函数

mysql> SELECT CEIL(-0.8),CEIL(0.8);
+------------+-----------+
| CEIL(-0.8) | CEIL(0.8) |
+------------+-----------+
|          0 |         1 |
+------------+-----------+
1 row in set (0.00 sec)

（3）FLOOR函数

mysql> SELECT FLOOR(-0.8),FLOOR(0.8);
+-------------+------------+
| FLOOR(-0.8) | FLOOR(0.8) |
+-------------+------------+
|          -1 |          0 |
+-------------+------------+
1 row in set (0.00 sec)

（4）MOD函数

注：模数和被模数任何一个为NULL，结果都为NULL

mysql> SELECT MOD(15,10),MOD(1,11),MOD(NULL,10),MOD(10,NULL);
+------------+-----------+--------------+--------------+
| MOD(15,10) | MOD(1,11) | MOD(NULL,10) | MOD(10,NULL) |
+------------+-----------+--------------+--------------+
|          5 |         1 |         NULL |         NULL |
+------------+-----------+--------------+--------------+
1 row in set (0.00 sec)

（5）RAND函数

注：每次执行结果都不一样，利用该函数可以取任意指定范围内的随机数。

mysql> SELECT RAND(),RAND();
+--------------------+--------------------+
| RAND()             | RAND()             |
+--------------------+--------------------+
| 0.4727166318080543 | 0.9234484619679381 |
+--------------------+--------------------+
1 row in set (0.00 sec)

mysql> SELECT CEIL(100*RAND()),CEIL(500*RAND());
+------------------+------------------+
| CEIL(100*RAND()) | CEIL(500*RAND()) |
+------------------+------------------+
|               20 |              113 |
+------------------+------------------+
1 row in set (0.00 sec)

（6）ROUND(x,y)函数

注：如果x是整数，将会保留y位数量的0；如果不写y，默认y为0。

mysql> SELECT ROUND(1,1),ROUND(1.1,2),ROUND(1.65,2),ROUND(1.1);
+------------+--------------+---------------+------------+
| ROUND(1,1) | ROUND(1.1,2) | ROUND(1.65,2) | ROUND(1.1) |
+------------+--------------+---------------+------------+
|          1 |          1.1 |          1.65 |          1 |
+------------+--------------+---------------+------------+
1 row in set (0.00 sec)

（7）TRUNCATE(x,y)函数

注：仅仅进行截断，而不进行四舍五入。

mysql> SELECT TRUNCATE(1.2345,3),ROUND(1.2345,3);
+--------------------+-----------------+
| TRUNCATE(1.2345,3) | ROUND(1.2345,3) |
+--------------------+-----------------+
|              1.234 |           1.235 |
+--------------------+-----------------+
1 row in set (0.00 sec)

5.3日期和时间函数

表5-3 MySQL中常用日期和时间函数
函数	功能
CURDATE()	返回当前日期
CURTIME()	返回当前时间
NOW()	返回当前的日期和时间
UNIX_TIMESTAMP(date)	返回日期date的UNIX时间戳
FROM_UNIXTIME(unixtime)	返回UNIX时间戳的日期值
WEEK(date)	返回日期date为一年中的第几周
YEAR(date)	返回日期date的年份
HOUR(time)	返回time的小时值
MINUTE(time)	返回time的分钟值
MONTHNAME(date)	返回date的月份名
DATE_FORMAT(date,fmt)	返回按字符串fmt格式化日期date值
DATE_ADD(date,INTERVAL expr type)	返回一个日期或时间值加上一个时间间隔的时间值
DATEDIFF(expr,expr2)	返回起始时间expr和结束时间expr2之间的天数

下面给出了每个函数的使用实例。

（1）CURDATE函数、CURTIME函数和NOW函数

mysql> SELECT CURDATE(),CURTIME(),NOW();
+------------+-----------+---------------------+
| CURDATE()  | CURTIME() | NOW()               |
+------------+-----------+---------------------+
| 2024-01-28 | 18:37:04  | 2024-01-28 18:37:04 |
+------------+-----------+---------------------+
1 row in set (0.00 sec)

（2）UNIX_TIMESTAMP函数

mysql> SELECT UNIX_TIMESTAMP(NOW());
+-----------------------+
| UNIX_TIMESTAMP(NOW()) |
+-----------------------+
|            1706438303 |
+-----------------------+
1 row in set (0.00 sec)

（3）FROM_UNIXTIME函数

注：FROM_UNIXTIME(unixtime)和UNIX_TIMESTAMP(date)互为逆操作。

mysql> SELECT fROM_UNIXTIME(1706438303);
+---------------------------+
| fROM_UNIXTIME(1706438303) |
+---------------------------+
| 2024-01-28 18:38:23       |
+---------------------------+
1 row in set (0.00 sec)

（4）WEEK函数和YEAR函数

mysql> SELECT WEEK(NOW()),YEAR(NOW());
+-------------+-------------+
| WEEK(NOW()) | YEAR(NOW()) |
+-------------+-------------+
|           4 |        2024 |
+-------------+-------------+
1 row in set (0.00 sec)

（5）HOUR函数和MINUTE函数

mysql> SELECT HOUR(CURTIME()),MINUTE(NOW());
+-----------------+---------------+
| HOUR(CURTIME()) | MINUTE(NOW()) |
+-----------------+---------------+
|              18 |            44 |
+-----------------+---------------+
1 row in set (0.00 sec)

（6） MONTHNAME函数

mysql> SELECT MONTHNAME(NOW());
+------------------+
| MONTHNAME(NOW()) |
+------------------+
| January          |
+------------------+
1 row in set (0.00 sec)

（7）DATE_FORMAT(date,fmt)函数：按字符串fmt格式化日期date值。

该函数能够按照指定的格式显示日期，可用的格式符如表5-4所示。

表5-4 MySQL中的日期和时间格式
格式符	格式说明
%S和%s	两位数字形式的秒（00,01,...,59）
%i	两位数字形式的分（00,01,...,59）
%H	两位数字形式的小时，24小时（00,01,...,23）
%h和%I(i的大写）	两位数字形式的小时，12小时（01,02,...,12）
%k	数字形式的小时，24小时（0,1,...,23）
%l（L的小写）	数字形式的小时，12小时（1,2,...,12）
%T	24小时的时间形式（hh:mm:ss）
%r	12小时的时间形式（hh:mm:ssAM或hh:mm:ssPM）
%p	AM或PM
%W	一周中每一天的名称（Sunday，Monday，...，Saturday）
%a	一周中每一天的名称的缩写（Sun，Mon，...，Sat）
%d	两位数字表示月中的天数（00,01，...，31）
%e	数字形式表示月中的天数（1,2,....31）
%D	英文后缀表示月中的天数（1st，2nd，3rd...）
%w	以数字形式表示周中的天数（0=Sunday，1=Monday，...6=Saturday）
%j	以3位数字表示年中的天数（001,002，...,366）
%U	周（0,1,52），其中Sunday为周中的第一天
%u	周（0,1,52），其中Monday为周中的第一天
%M	月名（January，February，....，December）
%b	缩写的月名（Jan，Feb，...，Dec）
%m	两位数字表示的月份（01,02，...，12）
%c	数字表示的月份
%Y	4位数字表示的年份
%y	两位数字表示的年份
%%	直接填“%”

mysql> SELECT DATE_FORMAT(NOW(),'%M,%D,%Y');
+-------------------------------+
| DATE_FORMAT(NOW(),'%M,%D,%Y') |
+-------------------------------+
| January,28th,2024             |
+-------------------------------+
1 row in set (0.00 sec)

（8）DATE_ADD(date,INTERVAL expr type)函数：返回与所给日期date相差INTERVAL时间段的日期。

INTERVAL是间隔类型关键字，expr是一个表达式，这个表达式对应后面的类型，type是间隔类型。MySQL提供了13中间隔类型，如表5-5所示。

表5-5 MySQL中的日期间隔
表达式类型	描述	格式
HOUR	小时	hh
MINUTE	分	mm
SECOND	秒	ss
YEAR	年	YY
MONTH	月	MM
DAY	日	DD
YEAR_MONTH	年和月	YY_MM
DAY_HOUR	日和小时	DD hh
DAY_MINUTE	日和分钟	DD hh:mm
DAY_SECOND	日和秒	DD hh:mm:ss
HOUR_MINUTE	小时和分	hh:mm
HOUR_SECOND	小时和秒	hh:ss
MINUTE_SECOND	分钟和秒	mm:ss

mysql> SELECT NOW() current,
DATE_ADD(NOW(),INTERVAL 31 DAY) after31days,
DATE_ADD(NOW(),INTERVAL '1_2' year_month) after_oneyear_twomonth;
+---------------------+---------------------+------------------------+
| current             | after31days         | after_oneyear_twomonth |
+---------------------+---------------------+------------------------+
| 2024-01-28 19:18:38 | 2024-02-28 19:18:38 | 2025-03-28 19:18:38    |
+---------------------+---------------------+------------------------+
1 row in set (0.00 sec)

同样可以使用负数返回之前的某个日期时间。

mysql> SELECT NOW() current,DATE_ADD(NOW(),INTERVAL -31 DAY) after31days,DATE_ADD(NOW(),INTERVAL '-1_-2' year_month) after_oneyear_twomonth;
+---------------------+---------------------+------------------------+
| current             | after31days         | after_oneyear_twomonth |
+---------------------+---------------------+------------------------+
| 2024-01-28 19:22:01 | 2023-12-28 19:22:01 | 2022-11-28 19:22:01    |
+---------------------+---------------------+------------------------+
1 row in set (0.00 sec)

(9)DATEDIFF函数：计算两个日期之间相差的天数。

mysql> SELECT DATEDIFF('2008-08-08',NOW());
+------------------------------+
| DATEDIFF('2008-08-08',NOW()) |
+------------------------------+
|                        -5651 |
+------------------------------+
1 row in set (0.00 sec)

2024年1月28日距离08年的奥运会开幕式已经过去了5651天。

5.4流程函数

使用流程函数可以在SQL语句中实现条件选择，能够提高语句的效率。

表5-6中列出了MySQL中跟条件选择有关的流程函数。

表5-6 MySQL中的流程函数
函数	功能
IF(value,t,f)	如果value是真，返回t；否则返回f
IFNULL(value1,value2)	如果value1不为空，返回value1，否则返回value2。
CASE WHEN [value1] THEN [result1] ... ELSE [default] END	如果value1是真，返回result1，否则返回default。
CASE [expr] WHEN [value1] THEN[result1] ...ELSE [default] END	如果expr等于value1，返回result1，否则返回default。

模拟职员薪水进行分类，介绍各个函数的应用。

创建薪水表。

mysql> CREATE TABLE salary (userid int, salary decimal(9,2));
Query OK, 0 rows affected (0.04 sec)

mysql> INSERT INTO salary VALUES(1,1000),(2,2000),(3,3000),(4,4000),(5,5000),(1,null);
Query OK, 6 rows affected (0.02 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM salary;
+--------+---------+
| userid | salary  |
+--------+---------+
|      1 | 1000.00 |
|      2 | 2000.00 |
|      3 | 3000.00 |
|      4 | 4000.00 |
|      5 | 5000.00 |
|      1 |    NULL |
+--------+---------+
6 rows in set (0.00 sec)

（1）IF函数，将2000以上的职员分类为高薪，用“high”表示，2000以下分类为低薪，用“low”表示

mysql> SELECT userid,salary,if(salary>2000,'high','low') as salary_level FROM salary;
+--------+---------+--------------+
| userid | salary  | salary_level |
+--------+---------+--------------+
|      1 | 1000.00 | low          |
|      2 | 2000.00 | low          |
|      3 | 3000.00 | high         |
|      4 | 4000.00 | high         |
|      5 | 5000.00 | high         |
|      1 |    NULL | low          |
+--------+---------+--------------+
6 rows in set (0.00 sec)

（2）IFNULL函数：这个函数一般用来替换NULL值。

mysql> SELECT userid,salary,ifnull(salary,0) FROM salary;
+--------+---------+------------------+
| userid | salary  | ifnull(salary,0) |
+--------+---------+------------------+
|      1 | 1000.00 |          1000.00 |
|      2 | 2000.00 |          2000.00 |
|      3 | 3000.00 |          3000.00 |
|      4 | 4000.00 |          4000.00 |
|      5 | 5000.00 |          5000.00 |
|      1 |    NULL |             0.00 |
+--------+---------+------------------+
6 rows in set (0.00 sec)

（3）CASE WHEN [value1] THEN [result1] ... ELSE [default] END函数：这是case简单函数用法，case后面跟列名或者列的表达式，when后面枚举这个表达式的所有可能值，但不能是值的范围。

mysql> SELECT userid,salary,CASE salary WHEN 1000 THEN 'low' WHEN 2000 THEN 'low' ELSE 'high' END salary_level FROM salary;
+--------+---------+--------------+
| userid | salary  | salary_level |
+--------+---------+--------------+
|      1 | 1000.00 | low          |
|      2 | 2000.00 | low          |
|      3 | 3000.00 | high         |
|      4 | 4000.00 | high         |
|      5 | 5000.00 | high         |
|      1 |    NULL | high         |
+--------+---------+--------------+
6 rows in set (0.00 sec)

（4）CASE [expr] WHEN [value1] THEN[result1] ...ELSE [default] END函数：这是case的搜索函数用法，直接在when后面写条件表达式，并且只返回第一个符合条件的值，使用起来更灵活。

mysql> SELECT userid,salary,CASE WHEN salary<=2000 THEN 'low' ELSE 'high' END AS salary_level FROM salary;
+--------+---------+--------------+
| userid | salary  | salary_level |
+--------+---------+--------------+
|      1 | 1000.00 | low          |
|      2 | 2000.00 | low          |
|      3 | 3000.00 | high         |
|      4 | 4000.00 | high         |
|      5 | 5000.00 | high         |
|      1 |    NULL | high         |
+--------+---------+--------------+
6 rows in set (0.00 sec)

5.5JSON函数

对于JSON文档的操作，除了简单的读写之外，通常还会有各种各样的查询、修改等需求。

表5-7 MySQL中的JSON函数
函数类型	名称	功能
创建JSON	JSON_ARRAY()	创建JSON数组
	JSON_OBJECT()	创建JSON对象
	JSON_QUOTE()/JSON_UNQUOTE()	加上/去掉JSON文档两边的双引号
查询JSON	JSON_CONTAINS()	查询文档中是否包含指定的元素
	JSON_CONTAINS_PATH()	查询文档中是否包含指定的路径
	JSON_EXTRACT()/->/->>	根据条件提取文档中的数据
	JSON_KEYS()	提取所有key的集合
	JSON_SEARCH()	返回所有符合条件的路径集合
修改JSON	JSON_MERGE()（MySQL5.7.22后删掉了） JSON_MERGE_PRESERVE	将两个文档合并
	JSON_ARRAY_APPEND()	数组尾部追加元素
	JSON_ARRAY_INSERT()	在数组的指定位置插入元素
	JSON_REMOVE()	删除文档中指定位置的元素
	JSON_REPLACE()	替换文档中指定位置的元素
	JSON_SET()	给文档中指定位置的元素设置新值，如果元素不存在，则进行插入
查询JSON元数据	JSON_DEPTH()	JSON文档的深度（元素最大嵌套层数）
	JSON_LENGTH()	JSON文档的长度（元素个数）
	JSON_TYPE()	JSON文档类型（数组、对象、标量类型）
	JSON_VALID()	JSON格式是否合法
其它函数	JSON_PRETTY()	美化JSON格式
	JSON_STORAGE_SIZE()	JSON文档占用的存储空间
	JSON_STOGAGE_FREE()	JSON文档更新操作后剩余的空间，MySQL8.0新增
	JSON_TABLE()	将JSON文档转换为表格，MySQL8.0新增
	JSON_ARRAYAGG()	将聚合后参数中的多个值转换为JSON数组
	JSON_OBJECTAGG()	将两个列或者是表达式解释为一个key和一个value，返回一个JSON对象。

5.5.1创建JSON函数

（1）JSON_ARRAY([val[,val]...])：返回包含参数中所有值列表的JSON数组。

创建了一个包含数字、字符串、null、布尔、日期类型在内的混合数组。注：参数中的null和true/false大小写不敏感。

mysql> SELECT JSON_ARRAY(1,"abc",NULL,TRUE,CURTIME());
+-------------------------------------------+
| JSON_ARRAY(1,"abc",NULL,TRUE,CURTIME())   |
+-------------------------------------------+
| [1, "abc", null, true, "20:18:58.000000"] |
+-------------------------------------------+
1 row in set (0.00 sec)

（2）JSON_OBJECT(key,val[,key,val]...])：此函数可以返回包含参数中所有键对值的对象列表，参数中的key不能为null，参数个数也不能为奇数，否则报语法错误。

mysql> SELECT JSON_OBJECT('id',100,'name','jack');
+-------------------------------------+
| JSON_OBJECT('id',100,'name','jack') |
+-------------------------------------+
| {"id": 100, "name": "jack"}         |
+-------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT JSON_OBJECT('id',100,'name');
ERROR 1582 (42000): Incorrect parameter count in the call to native function 'JSON_OBJECT'
mysql> SELECT JSON_OBJECT('id',100,null,1);
ERROR 3158 (22032): JSON documents may not contain NULL member names.

（3）JSON_QUOTE(string)：此函数可以将参数中的JSON文档转换为双引号引起来的字符串，如果JSON文档包含双引号，则转换后的字符串自动加上转义字符“\”。

mysql> SELECT JSON_QUOTE('[1,2,3]'),JSON_QUOTE('"NULL"');
+-----------------------+----------------------+
| JSON_QUOTE('[1,2,3]') | JSON_QUOTE('"NULL"') |
+-----------------------+----------------------+
| "[1,2,3]"             | "\"NULL\""           |
+-----------------------+----------------------+
1 row in set (0.00 sec)

如果需要将非JSON文档转换为JSON文档，或者反过来，可以使用CONVERT或者CAST函数进行强制转换，这两个函数可以实现不同数据类型之间的强制转换。

5.5.2查询JSON函数

（1）JSON_CONTAINS(target,candidate[,path])

此函数可以查询指定的元素（candidate）是否包含在目标JSON文档（target）中，包含则返回1，否则返回0，path参数可选。如果有参数为NULL或path不存在，则返回NULL。

mysql> SELECT JSON_CONTAINS('[1,2,3,"abc",null]','"abc"');
+---------------------------------------------+
| JSON_CONTAINS('[1,2,3,"abc",null]','"abc"') |
+---------------------------------------------+
|                                           1 |
+---------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT JSON_CONTAINS('[1,2,3,"abc",null]',null);
+------------------------------------------+
| JSON_CONTAINS('[1,2,3,"abc",null]',null) |
+------------------------------------------+
|                                     NULL |
+------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT JSON_CONTAINS('[1,2,3,"abc",null]','10');
+------------------------------------------+
| JSON_CONTAINS('[1,2,3,"abc",null]','10') |
+------------------------------------------+
|                                        0 |
+------------------------------------------+
1 row in set (0.00 sec)

元素是数组也是可以的。

mysql> SELECT JSON_CONTAINS('[1,2,3,"abc",null]','[1,3]');
+---------------------------------------------+
| JSON_CONTAINS('[1,2,3,"abc",null]','[1,3]') |
+---------------------------------------------+
|                                           1 |
+---------------------------------------------+
1 row in set (0.00 sec)

path参数是可选的，可以指定在特定的路径下查询。如果JSON文档为对象，则路径也是通常类似于$.a或者$.a.b这种格式。表示key为a；通常用在value也是对象列表的情况，表示键a下层的键b，比如{"id":{"id1":1,"id2":2}}。如果JSON文档为数组，则路径通常写为$[i]这种格式，表示数组中第i个元素。

实例：要查询JSON文档j中是否包含value为10的对象，并指定路径为$.jack(key='jack')，如果包含则返回1，如果不包含则返回0。

mysql> SET @j='{"jack":10,"tom":20,"lisa":30}';
Query OK, 0 rows affected (0.00 sec)

mysql> SET @j2='10';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT JSON_CONTAINS(@j,@j2,'$.jack');
+--------------------------------+
| JSON_CONTAINS(@j,@j2,'$.jack') |
+--------------------------------+
|                              1 |
+--------------------------------+
1 row in set (0.00 sec)

mysql> SELECT JSON_CONTAINS(@j,@j2,'$.tom');
+-------------------------------+
| JSON_CONTAINS(@j,@j2,'$.tom') |
+-------------------------------+
|                             0 |
+-------------------------------+
1 row in set (0.00 sec)

（2）JSON_CONTAINS_PATH(json_doc,one_or_all,path[,path]...)

此函数可以查询JSON文档中是否存在指定路径，存在则返回1，否则返回0。one_or_all只能取值one或all，one表示只要有一个存在即可，all表示所有的都存在才行。如果有参数为NULL或path不存在，则返回NULL。

mysql> SELECT JSON_CONTAINS_PATH('{"k1":"jack","k2":"tom","k3":"lisa"}','one','$.k1','$.k4') one_path;
+----------+
| one_path |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

mysql> SELECT JSON_CONTAINS_PATH('{"k1":"jack","k2":"tom","k3":"lisa"}','all','$.k1','$.k4') all_path;
+----------+
| all_path |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

（3）JSON_EXTRACT(json_doc,path[,path]...)

此函数可以从JSON文档里抽取数据。如果有参数为NULL或者path不存在，则返回NULL。如果抽取出多个path，则返回的数据合并在一个JSON ARRAY里。

示例：从JSON文档中的第一和第二个元素中提取出对应的value以及提取第三个元素的值

mysql> SELECT JSON_EXTRACT('[10,20,[30,40]]','$[0]','$[1]');
+-----------------------------------------------+
| JSON_EXTRACT('[10,20,[30,40]]','$[0]','$[1]') |
+-----------------------------------------------+
| [10, 20]                                      |
+-----------------------------------------------+
1 row in set (0.00 sec)


mysql> SELECT JSON_EXTRACT('[10,20,[30,40]]','$[2]');
+----------------------------------------+
| JSON_EXTRACT('[10,20,[30,40]]','$[2]') |
+----------------------------------------+
| [30, 40]                               |
+----------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT JSON_EXTRACT('[10,20,[30,40]]','$[2][*]');
+-------------------------------------------+
| JSON_EXTRACT('[10,20,[30,40]]','$[2][*]') |
+-------------------------------------------+
| [30, 40]                                  |
+-------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT JSON_EXTRACT('[10,20,[30,40]]','$[1][*]');
+-------------------------------------------+
| JSON_EXTRACT('[10,20,[30,40]]','$[1][*]') |
+-------------------------------------------+
| NULL                                      |
+-------------------------------------------+
1 row in set (0.00 sec)

在MySQL5.7.9版本之后，可以用一种更简单的函数“->”来替代JSON_EXTRACT,语法如下：

colunm->path

左边只能是列名，不能是表达式；右边是要匹配的JSON路径。上面的例子可以改写为：

mysql> INSERT INTO t1 VALUES('[10,20,[30,40]'); 
Query OK,1 row affected (0.00 sec)

mysql> SELECT id1,id1->"$[0]",id1->"$[1]" FROM t1 WHEN id1->"$[0]"=10;
+--------------------+---------------+--------------+
|id1                 | id1->"$[0]"   |  id1->"$[1]" |
+--------------------+---------------+--------------+
| [10, 20, [30, 40]] | 10            |  20          |
+--------------------+---------------+--------------+
1 row in set (0.00 sec)

如果JSON文档查询的结果是字符串，则显示结果默认会包含双引号，在很多情况下是不需要的，为了解决这个问题，MySQL提供了另外两个函数JSON_UNQUOTE和“->>”，用法类似于JSON_QUOTE和“->”。

下面三种写法的效果是一样的。

JSON_UNQUOTE(JSON_EXTRACT(column,path))
JSON_UNQUOTE(column->path)
column->>path

(4)JSON_KEYS(json_doc,[,path])

此函数可以获取JSON文档在指定路径下的所有键值，返回一个JSON ARRAY。如果有参数为NULL或者path不存在，则返回NULL。参数path通常使用在嵌套对象列表中。如果元素中都是数组，则返回NULL。

mysql> SELECT JSON_KEYS('{"a":1,"b":{"c":30}}');
+-----------------------------------+
| JSON_KEYS('{"a":1,"b":{"c":30}}') |
+-----------------------------------+
| ["a", "b"]                        |
+-----------------------------------+
1 row in set (0.00 sec)

mysql> SELECT JSON_KEYS('{"a":1,"b":{"c":30}}','$.b');
+-----------------------------------------+
| JSON_KEYS('{"a":1,"b":{"c":30}}','$.b') |
+-----------------------------------------+
| ["c"]                                   |
+-----------------------------------------+
1 row in set (0.00 sec)

（5）JSON_SEARCH(json_doc,one_or_all,search_str[,escape_char[,path]...])

此函数可以查询包含指定字符串的路径，并作为一个JSON ARRAY返回。如果有参数为NULL或者path不存在，则返回NULL。one_or_all：one表示查询到一个即返回；all表示查询所有；search_str表示要查询的字符串，可以用LIKE里的'%'或'_'匹配；path表示在指定路径path下进行查询。

mysql> SELECT JSON_SEARCH('{"k1":"jack","k2":"tom","k3":"lisa","k4":"tony"}','all','t%');
+----------------------------------------------------------------------------+
| JSON_SEARCH('{"k1":"jack","k2":"tom","k3":"lisa","k4":"tony"}','all','t%') |
+----------------------------------------------------------------------------+
| ["$.k2", "$.k4"]                                                           |
+----------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT JSON_SEARCH('{"k1":"jack","k2":"tom","k3":"lisa","k4":"tony"}','one','t%');
+----------------------------------------------------------------------------+
| JSON_SEARCH('{"k1":"jack","k2":"tom","k3":"lisa","k4":"tony"}','one','t%') |
+----------------------------------------------------------------------------+
| "$.k2"                                                                     |
+----------------------------------------------------------------------------+
1 row in set (0.00 sec)

如果把JSON文档改为数组，则返回路径也将成为数组的描述格式。

mysql> SELECT JSON_SEARCH('["tom","lisa","jack",{"name":"tony"}]','all','t%');
+-----------------------------------------------------------------+
| JSON_SEARCH('["tom","lisa","jack",{"name":"tony"}]','all','t%') |
+-----------------------------------------------------------------+
| ["$[0]", "$[3].name"]                                           |
+-----------------------------------------------------------------+
1 row in set (0.00 sec)

5.5.3修改JSON的函数

（1）JSON_ARRAY_APPEND(json_doc,path,val[,path,val]...)

此函数可以在指定path的json array尾部追加val。如果指定path是一个json object，则将其封装成一个json array再追加。如果有参数为NULL，则返回NULL。

mysql> SELECT JSON_ARRAY_APPEND('["a",["b","c"],"d"]','$[0]',"1");
+-----------------------------------------------------+
| JSON_ARRAY_APPEND('["a",["b","c"],"d"]','$[0]',"1") |
+-----------------------------------------------------+
| [["a", "1"], ["b", "c"], "d"]                       |
+-----------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT JSON_ARRAY_APPEND('["a",["b","c"],"d"]','$[1]',"1");
+-----------------------------------------------------+
| JSON_ARRAY_APPEND('["a",["b","c"],"d"]','$[1]',"1") |
+-----------------------------------------------------+
| ["a", ["b", "c", "1"], "d"]                         |
+-----------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT JSON_ARRAY_APPEND('["a",["b","c"],"d"]','$[1][0]',"1");
+--------------------------------------------------------+
| JSON_ARRAY_APPEND('["a",["b","c"],"d"]','$[1][0]',"1") |
+--------------------------------------------------------+
| ["a", [["b", "1"], "c"], "d"]                          |
+--------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT JSON_ARRAY_APPEND('{"a":1,"b":[2,3],"c":4}','$.b',"1");
+--------------------------------------------------------+
| JSON_ARRAY_APPEND('{"a":1,"b":[2,3],"c":4}','$.b',"1") |
+--------------------------------------------------------+
| {"a": 1, "b": [2, 3, "1"], "c": 4}                     |
+--------------------------------------------------------+
1 row in set (0.00 sec)

（2）JSON_ARRAY_INSERT(json_doc,path,val[,path,val]...)

此函数可以在path指定的json array元素插入val，原位置及以右的元素顺次右移。如果path指定的数据非json array元素，则略过次val；如果指定的元素下标超过json array的长度，则插入尾部。

mysql> SELECT JSON_ARRAY_INSERT('["a",["b","c"],"d"]','$[0]',"1");
+-----------------------------------------------------+
| JSON_ARRAY_INSERT('["a",["b","c"],"d"]','$[0]',"1") |
+-----------------------------------------------------+
| ["1", "a", ["b", "c"], "d"]                         |
+-----------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT JSON_ARRAY_INSERT('["a",["b","c"],"d"]','$[1]',"1");
+-----------------------------------------------------+
| JSON_ARRAY_INSERT('["a",["b","c"],"d"]','$[1]',"1") |
+-----------------------------------------------------+
| ["a", "1", ["b", "c"], "d"]                         |
+-----------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT JSON_ARRAY_INSERT('["a",["b","c"],"d"]','$[1][0]',"1");
+--------------------------------------------------------+
| JSON_ARRAY_INSERT('["a",["b","c"],"d"]','$[1][0]',"1") |
+--------------------------------------------------------+
| ["a", ["1", "b", "c"], "d"]                            |
+--------------------------------------------------------+
1 row in set (0.00 sec)

下面这个SQL报错，提示路径不对，将“$.b”改为“$[1]”，插入路径正确，但字符没有插入到JSON文档中去，因为所有元素都是对象，跳过忽略。

mysql>  SELECT JSON_ARRAY_INSERT('{"a":1,"b":[2,3],"c":4}','$.b',"1");
ERROR 3165 (42000): A path expression is not a path to a cell in an array.

mysql>  SELECT JSON_ARRAY_INSERT('{"a":1,"b":[2,3],"c":4}','$[1]',"1");
+---------------------------------------------------------+
| JSON_ARRAY_INSERT('{"a":1,"b":[2,3],"c":4}','$[1]',"1") |
+---------------------------------------------------------+
| {"a": 1, "b": [2, 3], "c": 4}                           |
+---------------------------------------------------------+
1 row in set (0.00 sec)

（3）JSON_REPLACE(json_doc,path,val[,path,val]...)

此函数可以替换指定路径的数据。如果某个路径不存在，则略过（存在才替换）。如果有参数为NULL，则返回NULL。

mysql> SELECT JSON_REPLACE('["a",["b","c"],"d"]','$[0]',"1",'$[1]',"2");
+-----------------------------------------------------------+
| JSON_REPLACE('["a",["b","c"],"d"]','$[0]',"1",'$[1]',"2") |
+-----------------------------------------------------------+
| ["1", "2", "d"]                                           |
+-----------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT JSON_REPLACE('{"a":1,"b":[2,3],"c":4}','$.a',"10",'$.d',"20");
+---------------------------------------------------------------+
| JSON_REPLACE('{"a":1,"b":[2,3],"c":4}','$.a',"10",'$.d',"20") |
+---------------------------------------------------------------+
| {"a": "10", "b": [2, 3], "c": 4}                              |
+---------------------------------------------------------------+
1 row in set (0.00 sec)

（4）JSON_SET(json_doc,path,val[,path,val]...)

此函数可以设置指定路径的数据（不管是否存在）。如果参数为NULL，则返回NULL。和JSON_REPLACE功能有些相似，最主要的区别是指定的路径不存在时，会在文档中自动添加。

mysql> SELECT JSON_SET('{"a":1,"b":[2,3],"c":4}','$.a',"10",'$.d',"20");
+-----------------------------------------------------------+
| JSON_SET('{"a":1,"b":[2,3],"c":4}','$.a',"10",'$.d',"20") |
+-----------------------------------------------------------+
| {"a": "10", "b": [2, 3], "c": 4, "d": "20"}               |
+-----------------------------------------------------------+
1 row in set (0.00 sec)

（5）JSON_MERGE_PRESERVE(json_doc,json_doc[,json_doc]...)

此函数可以将多个JSON文档进行合并，合并规则为：如果都是json array，则结果自动merge为一个json array；如果都是json object，则结果自动merge为一个json object；如果有多种类型，将非json array的元素封装成json array，再按照规则进行merge。

示例：分别将两个数组合并、两个对象合并、数组和对象合并。

mysql> SELECT JSON_MERGE_PRESERVE('[1,2]','[3,4]');
+--------------------------------------+
| JSON_MERGE_PRESERVE('[1,2]','[3,4]') |
+--------------------------------------+
| [1, 2, 3, 4]                         |
+--------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT JSON_MERGE_PRESERVE('{"key1":"tom"}','{"key2":"lisa"}');
+---------------------------------------------------------+
| JSON_MERGE_PRESERVE('{"key1":"tom"}','{"key2":"lisa"}') |
+---------------------------------------------------------+
| {"key1": "tom", "key2": "lisa"}                         |
+---------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT JSON_MERGE_PRESERVE('[1,2]','{"key1":"tom"}');
+-----------------------------------------------+
| JSON_MERGE_PRESERVE('[1,2]','{"key1":"tom"}') |
+-----------------------------------------------+
| [1, 2, {"key1": "tom"}]                       |
+-----------------------------------------------+
1 row in set (0.00 sec)

（6）JSON_REMOVE(json_doc,path[,path]...)

此函数可以移除指定路径的数据，如果某个路径不存在则略过次路径，如果有参数为NULL，则返回NULL。

mysql> SELECT JSON_REMOVE('[1,2,3,4]','$[1]','$[2]');
+----------------------------------------+
| JSON_REMOVE('[1,2,3,4]','$[1]','$[2]') |
+----------------------------------------+
| [1, 3]                                 |
+----------------------------------------+
1 row in set (0.00 sec)

注意：如果指定了多个path，则删除操作是串行操作的，即先删除'$[1]'后，JSON文档变为[1,3,4]，然后在[1,3,4]上删除'$[2]'后变为[1,3]。

5.5.4查询JSON元数据函数

（1）JSON_DEPTH(json_doc)

此函数用来获取JSON文档的深度。如果文档是空数组、空对象、null、true/false，则深度为1；如果非空数组或者非空对象里面包含的都是深度为1的对象，则整个文档深度为2；依次类推，整个文档的深度取决于最大元素的深度。

mysql> SELECT JSON_DEPTH('{}'),JSON_DEPTH('[]'),JSON_DEPTH('true');
+------------------+------------------+--------------------+
| JSON_DEPTH('{}') | JSON_DEPTH('[]') | JSON_DEPTH('true') |
+------------------+------------------+--------------------+
|                1 |                1 |                  1 |
+------------------+------------------+--------------------+
1 row in set (0.00 sec)

mysql> SELECT JSON_DEPTH('[10,20]'),JSON_DEPTH('[[],{}]');
+-----------------------+-----------------------+
| JSON_DEPTH('[10,20]') | JSON_DEPTH('[[],{}]') |
+-----------------------+-----------------------+
|                     2 |                     2 |
+-----------------------+-----------------------+
1 row in set (0.00 sec)

mysql> SELECT JSON_DEPTH('[10,{"a":20}]');
+-----------------------------+
| JSON_DEPTH('[10,{"a":20}]') |
+-----------------------------+
|                           3 |
+-----------------------------+
1 row in set (0.00 sec)

（2）JSON_LENGTH(json_doc[,path])

此函数可以获取指定路径下的文档长度，长度的计算规则如下：

标量（字符串、数字）的长度为1；

json array的长度为元素的个数；

json object的长度为对象的个数；

嵌套数组或者嵌套对象不计算长度。

mysql> SELECT JSON_LENGTH('1'),JSON_LENGTH('[1,2,[3,4]]'),JSON_LENGTH('{"key":"tom"}');
+------------------+----------------------------+------------------------------+
| JSON_LENGTH('1') | JSON_LENGTH('[1,2,[3,4]]') | JSON_LENGTH('{"key":"tom"}') |
+------------------+----------------------------+------------------------------+
|                1 |                          3 |                            1 |
+------------------+----------------------------+------------------------------+
1 row in set (0.00 sec)

（3）JSON_TYPE(json_val)

此函数可以获取JSON文档的具体类型，可以是数组、对象或者标量类型。

mysql> SELECT JSON_TYPE('[1,3]'),JSON_TYPE('{"id":"tom"}');
+--------------------+---------------------------+
| JSON_TYPE('[1,3]') | JSON_TYPE('{"id":"tom"}') |
+--------------------+---------------------------+
| ARRAY              | OBJECT                    |
+--------------------+---------------------------+
1 row in set (0.00 sec)

mysql> SELECT JSON_TYPE('1'),JSON_TYPE('"abc"'),JSON_TYPE('null'),JSON_TYPE('true');
+----------------+--------------------+-------------------+-------------------+
| JSON_TYPE('1') | JSON_TYPE('"abc"') | JSON_TYPE('null') | JSON_TYPE('true') |
+----------------+--------------------+-------------------+-------------------+
| INTEGER        | STRING             | NULL              | BOOLEAN           |
+----------------+--------------------+-------------------+-------------------+
1 row in set (0.00 sec)

（4）JSON_VALID(val)

此函数判断val是否为有效的JSON格式，有效为1，否则为0。

mysql> SELECT JSON_VALID('abc'),JSON_VALID('"abc"'),JSON_VALID('[1,2');
+-------------------+---------------------+--------------------+
| JSON_VALID('abc') | JSON_VALID('"abc"') | JSON_VALID('[1,2') |
+-------------------+---------------------+--------------------+
|                 0 |                   1 |                  0 |
+-------------------+---------------------+--------------------+
1 row in set (0.00 sec)

5.5.5JSON工具函数

（1）JSON_PRETTY(json_val)

此函数是MySQL5.7.22版本中新增的，用来美化JSON的输出格式，使得结果更加易读。对于数组、对象，每一行显示一个元素，多层嵌套的元素会在新行中进行缩进，清楚地显示层次关系。

mysql> SELECT JSON_PRETTY('{"a":"10","b":"15","x":{"x1":1,"x2":2,"x3":3}}');
+----------------------------------------------------------------------------------+
| JSON_PRETTY('{"a":"10","b":"15","x":{"x1":1,"x2":2,"x3":3}}')                    |
+----------------------------------------------------------------------------------+
| {
  "a": "10",
  "b": "15",
  "x": {
    "x1": 1,
    "x2": 2,
    "x3": 3
  }
} |
+----------------------------------------------------------------------------------+
1 row in set (0.00 sec)

（2）JSON_STORAGE_SIZE(json_val)/JSON_STORAGE_FREE(json_val)

JSON_STORAGE_SIZE函数可以获取JSON文档占用的存储空间（byte），而JSON_STORAGE_FREE函数可以获取由于JSON_SET、JSON_REPLACE、JSON_REMOVE操作导致释放的空间。JSON_STORAGE_FREE是MySQL8.0新增的函数。

JSON_STORAGE_SIZE(json_val)显示json_val所占的空间；对于JSON_STORAGE_FREE(json_val)，当json_val操作没有释放空间时，JSON_STORAGE_FREE(json_val)返回0；当json_val有字段更新时，JSON_STORAGE_FREE(json_val)返回更新等操作释放的空间。

但是由于MySQL规定局部更新（使用JSON_SET、JSON_REPLACE、JSON_REMOVE函数进行操作）后的文档存储只能大于等于更新前的size，如果更新的值大于原值，则JSON_STORAGE_SIZE会大于原文档size，并且由于没有释放空间，JSON_STORAGE_FREE返回为0。对于非局部更新，JSON_STORAGE_SIZE显示的是JSON文档的实际size，但是SON_STORAGE_FREE永远为0（因为非局部更新不满足该函数的条件）。

（3）JSON_TABLE(expr,path COLUMNS(column_list)[AS] alias)

此函数可以将JSON文档映射为表格。参数expr可以是表达式或者列；path是用来过滤的路径；COLUMNS是常量关键字；column list是转换后的字段列表。

该函数是MySQL8.0.4后新增的重要函数，可以将复杂的JSON文档转换为表格数据，转换后的表格可以像正常表一样做连接、排序等各种操作，对于JSON的数据展示，数据迁移等很多应用领域带来极大的灵活性和便利性。

下面的例子将JSON文档中的全部数据转换为表格，并按表格中的ac字段进行排序。

对例子中的参数简单介绍一下：

（1）expr，即JSON对象数组'[{"a":"3"},{"a":2},{"b":1},{"a":0},{"a":[1,2]}]'。

（2）过滤路径（path），其中"$[*]"表示文档中所有的数据，如果改为"$[0]"，则表示只转换文档中的第一个元素{"a":"3"}。

（3）column list包含以下4个部分的内容。

rowid FOR ORDINALITY：rowid是转换后的列名，FOR ORDINALITY表示按照序列顺序加一，类似于MySQL中的自增列，数据类型为UNSIGNED INT，初始值为1。

ac VARCHAR(100) PATH "$.a" DEFAULT '999' ON ERROR DEFAULT '111' ON EMPTY：ac是转换后的列名，VARCHAR(100)是转换后的列类型；PATH "$.a"说明此字段只记录对象的key="a"的value；DEFAULT '999' ON ERROR说明发生error，则转换为默认值999，DEFAULT '111' ON EMPTY说明对应的key不匹配'a'，此对象转换后为“111”，比如{"b":1}。

aj和ac类似，只是转换后的列类型为JSON。

bx INT EXISTS PATH "$.b" )：bx是转换后列名，如果存在路径"$.b" ，即key='b'的对象，则转换为1；否则为0。

mysql> SELECT * FROM JSON_TABLE(
    ->'[{"a":"3"},{"a":2},{"b":1},{"a":0},{"a":[1,2]}]',
    -> "$[*]"
    -> COLUMNS( 
    ->rowid FOR ORDINALITY,
    ->ac VARCHAR(100) PATH "$.a" DEFAULT '999' ON ERROR DEFAULT '111' ON EMPTY,
    ->aj JSON PATH "$.a" DEFAULT '{"x":333}' ON EMPTY,
    ->bx INT EXISTS PATH "$.b" ) 
    ->) AS tt order by ac;
+-------+------+------------+------+
| rowid | ac   | aj         | bx   |
+-------+------+------------+------+
|     4 | 0    | 0          |    0 |
|     3 | 111  | {"x": 333} |    1 |
|     2 | 2    | 2          |    0 |
|     1 | 3    | "3"        |    0 |
|     5 | 999  | [1, 2]     |    0 |
+-------+------+------------+------+
5 rows in set, 1 warning (0.01 sec)

（4）JSON_ARRAYAGG(col_or_expr)

此函数可以将聚合后参数中的多个值转换为JSON数组。

mysql> CREATE TABLE t (o_id int(1), attribute varchar(7),value varchar(7));
Query OK, 0 rows affected, 1 warning (0.02 sec)

mysql> INSERT INTO t VALUES(2,"color","red"),(2,"fabric","silk"),(3,"color","green"),(3,"shape","square");
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM t;
+------+-----------+--------+
| o_id | attribute | value  |
+------+-----------+--------+
|    2 | color     | red    |
|    2 | fabric    | silk   |
|    3 | color     | green  |
|    3 | shape     | square |
+------+-----------+--------+
4 rows in set (0.00 sec)

mysql> SELECT o_id,JSON_ARRAYAGG(attribute) AS attributes FROM t GROUP BY o_id;
+------+---------------------+
| o_id | attributes          |
+------+---------------------+
|    2 | ["color", "fabric"] |
|    3 | ["color", "shape"]  |
+------+---------------------+
2 rows in set (0.01 sec)

（5）JSON_OBJECTAGG(key,value)

此函数可以把两个列或是表达式解释为一个key和一个value，返回一个JSON对象。

mysql> SELECT o_id,JSON_OBJECTAGG(attribute,value) FROM t GROUP BY o_id;
+------+---------------------------------------+
| o_id | JSON_OBJECTAGG(attribute,value)       |
+------+---------------------------------------+
|    2 | {"color": "red", "fabric": "silk"}    |
|    3 | {"color": "green", "shape": "square"} |
+------+---------------------------------------+
2 rows in set (0.00 sec)

5.6 窗口函数

对于需要在某个结果集内做一些特定的函数操作的这类问题，MySQL8.0引入了窗口函数来解决。窗口可以理解为记录集合，窗口函数也就是在满足某种条件的记录集合上执行的特殊函数，对于每条记录都要在此窗口内执行函数。有的函数，随着记录不同，窗口大小都是固定的，这种属于静态窗口；有的函数则相反，不同的记录对应着不同的窗口，这种动态变化的窗口叫滑动窗口。

窗口函数和聚合函数有些类似，两者最大的区别是聚合函数是多行聚合成一行，窗口函数是多行聚合为相同的行数，每行会多一个聚合后的新列。窗口函数在其他数据库中（比如Oracle）也称分析函数，功能也都大体相似。

表5-8 MySQL中的窗口函数
函数	功能
ROW_NUMBER()	分区中的当前行号
RANK()	当前行在分区中的排名，含序号间隙
DENSE_RANK()	当前行在分区中的排名，没有序号间隙
PERCENT_RANK()	百分比等级值
CUME_DIST()	累计分配值
FIRST_VALUE()	窗口中第一行的参数值
LAST_VALUE()	窗口中最后一行的参数值
LAG()	分区中指定行落后于当前行的参数值
LEAD()	分区中领先当前行的参数值
NTH_VALUE()	从第N行窗口框架的参数值
NTILE(N)	分区中当前行的桶号

下面以订单表order_tab为例，逐个讲解这些函数的使用。

mysql> CREATE TABLE order_tab(order_id int(3),user_no varchar(3),amount int(3), create_date datetime);
Query OK, 0 rows affected, 2 warnings (0.02 sec)

mysql>  INSERT INTO order_tab VALUES(1,'001',100,'2018-01-01 00:00:00'),(2,'001',300,'2018-01-02 00:00:00'),(3,'001',500,'2018-01-02 00:00:00'),(4,'001',800,'2018-01-03 00:00:00'),(5,'001',900,'2018-01-04 00:00:00'),(6,'002',500,'2018-01-03 00:00:00'),(7,'002',600,'2018-01-04 00:00:00'),(8,'002',300,'2018-01-10 00:00:00'),(9,'002',800,'2018-01-16 00:00:00'),(10,'002',800,'2018-01-22 00:00:00');
Query OK, 10 rows affected (0.00 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM order_tab;
+----------+---------+--------+---------------------+
| order_id | user_no | amount | create_date         |
+----------+---------+--------+---------------------+
|        1 | 001     |    100 | 2018-01-01 00:00:00 |
|        2 | 001     |    300 | 2018-01-02 00:00:00 |
|        3 | 001     |    500 | 2018-01-02 00:00:00 |
|        4 | 001     |    800 | 2018-01-03 00:00:00 |
|        5 | 001     |    900 | 2018-01-04 00:00:00 |
|        6 | 002     |    500 | 2018-01-03 00:00:00 |
|        7 | 002     |    600 | 2018-01-04 00:00:00 |
|        8 | 002     |    300 | 2018-01-10 00:00:00 |
|        9 | 002     |    800 | 2018-01-16 00:00:00 |
|       10 | 002     |    800 | 2018-01-22 00:00:00 |
+----------+---------+--------+---------------------+
10 rows in set (0.00 sec)

5.6.1ROW_NUMBER()

如果要查询每个用户的最新一笔订单，希望得到的结果是order_id分别为5和10的记录。此时可以使用ROW_NUMBER()函数按照用户进行分组，并按照订单日期进行由大到小排序，最后查找每组中序号为1的记录。

mysql> SELECT * FROM (SELECT ROW_NUMBER() OVER(PARTITION BY user_no ORDER BY create_date DESC) AS row_num,order_id,user_no,amount,create_date FROM order_tab)t WHERE row_num=1;
+---------+----------+---------+--------+---------------------+
| row_num | order_id | user_no | amount | create_date         |
+---------+----------+---------+--------+---------------------+
|       1 |        5 | 001     |    900 | 2018-01-04 00:00:00 |
|       1 |       10 | 002     |    800 | 2018-01-22 00:00:00 |
+---------+----------+---------+--------+---------------------+
2 rows in set (0.00 sec)

row_number()后面的over是关键字，用来指定函数执行的窗口范围，如果括号中什么都不写，则意味着窗口包含所有行，窗口函数在所有行上进行计算；如果不为空，则支持以下四种语法：

（1）window_name：给窗口指定一个别名，当SQL中涉及的窗口较多时，采用别名更加清晰易读。

mysql> SELECT * FROM (SELECT ROW_NUMBER() OVER w AS row_num,order_id,user_no,amount,create_date FROM order_tab WINDOW w AS (PARTITION BY user_no ORDER BY create_date DESC))t WHERE row_num=1;
+---------+----------+---------+--------+---------------------+
| row_num | order_id | user_no | amount | create_date         |
+---------+----------+---------+--------+---------------------+
|       1 |        5 | 001     |    900 | 2018-01-04 00:00:00 |
|       1 |       10 | 002     |    800 | 2018-01-22 00:00:00 |
+---------+----------+---------+--------+---------------------+
2 rows in set (0.00 sec)

（2）partition子句：窗口按照哪些字段进行分组，窗口函数在不同的分组上分别执行。上面的例子就是按照用户id进行分组，在每个用户id上，分别执行从1开始的顺序编号。

（3）order by子句：按照哪些字段进行排序，窗口函数将按照排序后的记录顺序进行编号，既可以和partition子句配合使用，也可以单独使用。

（4）frame子句：frame是当前分区的一个子集，子句用来定义子集的规则，通常用来作为滑动窗口使用。

比如要根据每个订单动态计算包括本订单和按时间顺序前后两个订单的平均订单金额，则可以设置如下frame子句来创建滑动窗口：

mysql> SELECT * FROM (SELECT order_id,user_no,amount,
avg(amount) OVER w AS avg_num,
create_date 
FROM order_tab 
WINDOW w AS 
(PARTITION BY user_no ORDER BY create_date DESC ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) )t;
+----------+---------+--------+----------+---------------------+
| order_id | user_no | amount | avg_num  | create_date         |
+----------+---------+--------+----------+---------------------+
|        5 | 001     |    900 | 850.0000 | 2018-01-04 00:00:00 |
|        4 | 001     |    800 | 666.6667 | 2018-01-03 00:00:00 |
|        2 | 001     |    300 | 533.3333 | 2018-01-02 00:00:00 |
|        3 | 001     |    500 | 300.0000 | 2018-01-02 00:00:00 |
|        1 | 001     |    100 | 300.0000 | 2018-01-01 00:00:00 |
|       10 | 002     |    800 | 800.0000 | 2018-01-22 00:00:00 |
|        9 | 002     |    800 | 633.3333 | 2018-01-16 00:00:00 |
|        8 | 002     |    300 | 566.6667 | 2018-01-10 00:00:00 |
|        7 | 002     |    600 | 466.6667 | 2018-01-04 00:00:00 |
|        6 | 002     |    500 | 550.0000 | 2018-01-03 00:00:00 |
+----------+---------+--------+----------+---------------------+
10 rows in set (0.00 sec)

order_id为5订单属于边界值，没有前一行，因此平均订单金额为（900+800）/2=850；order_id为4的订单前后都有订单，所以平均订单金额为（900+800+300）/3=666.6667,依次类推就可以得到一个基于滑动窗口的动态平均订单值。

对于滑动窗口的范围指定，有如下两种方式：

（1）基于行：通常使用BETWEEN frame_start AND frame_end 语法来表示行范围，frame_start和frame_end可以支持如下关键字来确定不同的动态行记录：

CURRENT ROW  边界是当前行，一般和其他范围关键字一起使用
UNBOUNDED PRECEDING  边界是分区中的第一行
UNBOUNDED FOLLOWING  边界是分区中的最后一行
expr PRECEDING  边界是当前行减上expr的值
expr FOLLOWING  边界是当前行加上expr的值

比如下面都是合法范围：

rows BETWEEN 1 PRECEDING AND 1 FOLLOWING 窗口范围是当前行、前一行、后一行一共3行记录
rows UNBOUNDED FOLLOWDING 窗口范围是当前行到分区中的最后一行
rows BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING 窗口范围是当前分区中所有行，等同于不写

（2）基于范围：和基于行类似，但有些范围不是直接可以用行数来表示的，比如希望窗口范围是一周前的订单开始，截止到当前行，则无法使用rows来直接表示，此时就可以使用范围来表示窗口：INTERVAL 7 DAY PRECEDING。Linux中常见的计算最近1分钟、5分钟、15分钟负载就是一个典型的应用场景。

5.6.2RANK()/DENSE_RANK()

RANK()和DESE_RANK()这两个函数与row_number()非常类似，只是在出现重复值时处理逻辑有所不同。

示例：假设要查询不同用户的订单，按照订单金额进行排序，显示出相应的排名序号。

mysql> SELECT * FROM 
(SELECT 
ROW_NUMBER() OVER (PARTITION BY user_no ORDER BY amount DESC) AS row_num1,
rank() OVER(PARTITION BY user_no ORDER BY amount DESC) AS row_num2,
DENSE_RANK() OVER(PARTITION BY user_no ORDER BY amount DESC) AS row_num3, 
order_id,user_no,amount create_date 
FROM order_tab )t;
+----------+----------+----------+----------+---------+-------------+
| row_num1 | row_num2 | row_num3 | order_id | user_no | create_date |
+----------+----------+----------+----------+---------+-------------+
|        1 |        1 |        1 |        5 | 001     |         900 |
|        2 |        2 |        2 |        4 | 001     |         800 |
|        3 |        3 |        3 |        3 | 001     |         500 |
|        4 |        4 |        4 |        2 | 001     |         300 |
|        5 |        5 |        5 |        1 | 001     |         100 |
|        1 |        1 |        1 |        9 | 002     |         800 |
|        2 |        1 |        1 |       10 | 002     |         800 |
|        3 |        3 |        2 |        7 | 002     |         600 |
|        4 |        4 |        3 |        6 | 002     |         500 |
|        5 |        5 |        4 |        8 | 002     |         300 |
+----------+----------+----------+----------+---------+-------------+
10 rows in set (0.00 sec)

上面记录中导数第3、4、5行显示出了3个函数的区别，row_number()在amount都是800的两条记录上随机排序，但序号按照1,2递增，后面amount为600的序号继续递增为3，中间不会产生序号间隙；rank()/dense_rank()则把amount为800的两条记录序号都设置为1，但后续amount为600的需要分别设置为3（rank）和2（dense_rank）。即rank()会产生序号相同的记录，同时可能产生序号间隙，而dense_rank()也会产生序号相同的记录，但不会产生序号间隙。

5.6.3PRECENT_RANK()/CUME_DIST()

PRECENT_RANK()和CUME_DIST()这两个函数都是计算数据分布的函数，PRECENT_RANK()和之前的RANK()函数相关，每行按照以下公式进行计算：

$\frac{(rank-1)}{(rows-1)}$

其中rank为RANK()函数产生的序号，rows为当前窗口的记录总行数。PRECENT_RANK()主要应用在分析领域。

mysql> SELECT * FROM (
SELECT RANK() OVER w AS row_num, 
PERCENT_RANK() OVER w AS percent,
order_id,user_no,amount,create_date 
FROM order_tab 
WINDOW w AS (PARTITION BY user_no ORDER BY amount DESC) ) t;
+---------+---------+----------+---------+--------+---------------------+
| row_num | percent | order_id | user_no | amount | create_date         |
+---------+---------+----------+---------+--------+---------------------+
|       1 |       0 |        5 | 001     |    900 | 2018-01-04 00:00:00 |
|       2 |    0.25 |        4 | 001     |    800 | 2018-01-03 00:00:00 |
|       3 |     0.5 |        3 | 001     |    500 | 2018-01-02 00:00:00 |
|       4 |    0.75 |        2 | 001     |    300 | 2018-01-02 00:00:00 |
|       5 |       1 |        1 | 001     |    100 | 2018-01-01 00:00:00 |
|       1 |       0 |        9 | 002     |    800 | 2018-01-16 00:00:00 |
|       1 |       0 |       10 | 002     |    800 | 2018-01-22 00:00:00 |
|       3 |     0.5 |        7 | 002     |    600 | 2018-01-04 00:00:00 |
|       4 |    0.75 |        6 | 002     |    500 | 2018-01-03 00:00:00 |
|       5 |       1 |        8 | 002     |    300 | 2018-01-10 00:00:00 |
+---------+---------+----------+---------+--------+---------------------+
10 rows in set (0.00 sec)

SELECT * FROM (
SELECT NTILE(3) OVER w AS nf, 
NTH_VALUE(order_id,3) OVER w AS nth,
order_id,user_no,amount,create_date 
FROM order_tab 
WINDOW w AS (PARTITION BY user_no ORDER BY amount DESC) ) t;

相比PRECENT_RANK()，CUME_DIST() 函数应用场景更多，它的作用是分组内小于等于当前rank值的行数/分组内总行数。

示例：统计大于等于当前订单金额的订单数，占总订单数的比例。

mysql> SELECT * FROM (
    -> SELECT RANK() OVER w AS row_num,
    -> CUME_DIST() OVER w AS cume,
    -> order_id,user_no,amount,create_date
    -> FROM order_tab
    -> WINDOW w AS (PARTITION BY user_no ORDER BY amount DESC) ) t;
+---------+------+----------+---------+--------+---------------------+
| row_num | cume | order_id | user_no | amount | create_date         |
+---------+------+----------+---------+--------+---------------------+
|       1 |  0.2 |        5 | 001     |    900 | 2018-01-04 00:00:00 |
|       2 |  0.4 |        4 | 001     |    800 | 2018-01-03 00:00:00 |
|       3 |  0.6 |        3 | 001     |    500 | 2018-01-02 00:00:00 |
|       4 |  0.8 |        2 | 001     |    300 | 2018-01-02 00:00:00 |
|       5 |    1 |        1 | 001     |    100 | 2018-01-01 00:00:00 |
|       1 |  0.4 |        9 | 002     |    800 | 2018-01-16 00:00:00 |
|       1 |  0.4 |       10 | 002     |    800 | 2018-01-22 00:00:00 |
|       3 |  0.6 |        7 | 002     |    600 | 2018-01-04 00:00:00 |
|       4 |  0.8 |        6 | 002     |    500 | 2018-01-03 00:00:00 |
|       5 |    1 |        8 | 002     |    300 | 2018-01-10 00:00:00 |
+---------+------+----------+---------+--------+---------------------+
10 rows in set (0.00 sec)

5.6.4NTILE(N)

NTILE()函数的功能是对一个数据分区中的有序结果集进行划分，将其分成N个组，并为每个小组分配一个唯一的组编号。

示例：对每个用户的订单记录分为3组，NTILE()函数记录每组组编号。

mysql> SELECT * FROM (
    -> SELECT NTILE(3) OVER w AS nf,
    -> order_id,user_no,amount,create_date
    -> FROM order_tab
    -> WINDOW w AS (PARTITION BY user_no ORDER BY amount DESC) ) t;
+----+----------+---------+--------+---------------------+
| nf | order_id | user_no | amount | create_date         |
+----+----------+---------+--------+---------------------+
|  1 |        5 | 001     |    900 | 2018-01-04 00:00:00 |
|  1 |        4 | 001     |    800 | 2018-01-03 00:00:00 |
|  2 |        3 | 001     |    500 | 2018-01-02 00:00:00 |
|  2 |        2 | 001     |    300 | 2018-01-02 00:00:00 |
|  3 |        1 | 001     |    100 | 2018-01-01 00:00:00 |
|  1 |        9 | 002     |    800 | 2018-01-16 00:00:00 |
|  1 |       10 | 002     |    800 | 2018-01-22 00:00:00 |
|  2 |        7 | 002     |    600 | 2018-01-04 00:00:00 |
|  2 |        6 | 002     |    500 | 2018-01-03 00:00:00 |
|  3 |        8 | 002     |    300 | 2018-01-10 00:00:00 |
+----+----------+---------+--------+---------------------+
10 rows in set (0.00 sec)

此函数在数据分析领域应用较多，比如由于数据量大，需要将数据分配到N个并行的进程分别计算，此时就可以利用NTILE（N）对数据进行分组，由于记录数不一定被N整除，所以每组记录数不一定完全一致，然后将不同组号的数据再分配。

5.6.5 NTH_VALUE(expr,N)

NTH_VALUE(expr,N)函数可以返回窗口第N个expr的值，expr既可以是表达式，也可以是列名。

mysql> SELECT * FROM (
    -> SELECT NTILE(3) OVER w AS nf,
    -> NTH_VALUE(order_id,3) OVER w AS nth,
    -> order_id,user_no,amount,create_date
    -> FROM order_tab
    -> WINDOW w AS (PARTITION BY user_no ORDER BY amount DESC) ) t;
+----+------+----------+---------+--------+---------------------+
| nf | nth  | order_id | user_no | amount | create_date         |
+----+------+----------+---------+--------+---------------------+
|  1 | NULL |        5 | 001     |    900 | 2018-01-04 00:00:00 |
|  1 | NULL |        4 | 001     |    800 | 2018-01-03 00:00:00 |
|  2 |    3 |        3 | 001     |    500 | 2018-01-02 00:00:00 |
|  2 |    3 |        2 | 001     |    300 | 2018-01-02 00:00:00 |
|  3 |    3 |        1 | 001     |    100 | 2018-01-01 00:00:00 |
|  1 | NULL |        9 | 002     |    800 | 2018-01-16 00:00:00 |
|  1 | NULL |       10 | 002     |    800 | 2018-01-22 00:00:00 |
|  2 |    7 |        7 | 002     |    600 | 2018-01-04 00:00:00 |
|  2 |    7 |        6 | 002     |    500 | 2018-01-03 00:00:00 |
|  3 |    7 |        8 | 002     |    300 | 2018-01-10 00:00:00 |
+----+------+----------+---------+--------+---------------------+
10 rows in set (0.00 sec)

这个函数不太好理解，nth列返回来分组排序后的窗口中order_id的第三个值，“001”用户返回3，“002”用户返回7，对于前N-1列，本函数返回NULL。

5.6.6LAG(expr,N)/LEAD(expr,N)

LAG(expr,N)/LEAD(expr,N) 这两个函数的功能是获取当前数据行按照某种排序规则的上N行（LAG）/下N行（LEAD）数据的某个字段。

示例：每个订单中希望增加一个字段，用来记录本订单距离上一个订单的时间间隔，就可以用LAG函数来实现。

mysql> SELECT order_id,user_no,amount,create_date,last_date,datediff(create_date,last_date) AS diff 
FROM (
SELECT order_id,user_no,amount,create_date,
LAG(create_date,1) OVER w AS last_date 
FROM order_tab 
WINDOW w AS (PARTITION BY user_no ORDER BY create_date) 
)t;
+----------+---------+--------+---------------------+---------------------+------+
| order_id | user_no | amount | create_date         | last_date           | diff |
+----------+---------+--------+---------------------+---------------------+------+
|        1 | 001     |    100 | 2018-01-01 00:00:00 | NULL                | NULL |
|        2 | 001     |    300 | 2018-01-02 00:00:00 | 2018-01-01 00:00:00 |    1 |
|        3 | 001     |    500 | 2018-01-02 00:00:00 | 2018-01-02 00:00:00 |    0 |
|        4 | 001     |    800 | 2018-01-03 00:00:00 | 2018-01-02 00:00:00 |    1 |
|        5 | 001     |    900 | 2018-01-04 00:00:00 | 2018-01-03 00:00:00 |    1 |
|        6 | 002     |    500 | 2018-01-03 00:00:00 | NULL                | NULL |
|        7 | 002     |    600 | 2018-01-04 00:00:00 | 2018-01-03 00:00:00 |    1 |
|        8 | 002     |    300 | 2018-01-10 00:00:00 | 2018-01-04 00:00:00 |    6 |
|        9 | 002     |    800 | 2018-01-16 00:00:00 | 2018-01-10 00:00:00 |    6 |
|       10 | 002     |    800 | 2018-01-22 00:00:00 | 2018-01-16 00:00:00 |    6 |
+----------+---------+--------+---------------------+---------------------+------+
10 rows in set (0.00 sec)

5.6.7FIRST_VALUE(expr)/LAST_VALUE(expr)

FIRST_VALUE(expr)函数和LAST_VALUE(expr) 函数的功能分别是获得滑动窗口范围内的参数字段中第一个（FIRST_VALUE）和最后一个（LAST_VALUE）的值。

示例：查询每个用户在每个订单记录中截止到当前订单为止，按照日期排序最早订单和最晚订单的金额。

mysql> SELECT *
    -> FROM (
    -> SELECT order_id,user_no,amount,create_date,
    -> FIRST_VALUE(amount) OVER w AS first_amount,
    -> LAST_VALUE(amount) OVER w AS last_amount
    -> FROM order_tab
    -> WINDOW w AS (PARTITION BY user_no ORDER BY create_date)
    -> )t;
+----------+---------+--------+---------------------+--------------+-------------+
| order_id | user_no | amount | create_date         | first_amount | last_amount |
+----------+---------+--------+---------------------+--------------+-------------+
|        1 | 001     |    100 | 2018-01-01 00:00:00 |          100 |         100 |
|        2 | 001     |    300 | 2018-01-02 00:00:00 |          100 |         500 |
|        3 | 001     |    500 | 2018-01-02 00:00:00 |          100 |         500 |
|        4 | 001     |    800 | 2018-01-03 00:00:00 |          100 |         800 |
|        5 | 001     |    900 | 2018-01-04 00:00:00 |          100 |         900 |
|        6 | 002     |    500 | 2018-01-03 00:00:00 |          500 |         500 |
|        7 | 002     |    600 | 2018-01-04 00:00:00 |          500 |         600 |
|        8 | 002     |    300 | 2018-01-10 00:00:00 |          500 |         300 |
|        9 | 002     |    800 | 2018-01-16 00:00:00 |          500 |         800 |
|       10 | 002     |    800 | 2018-01-22 00:00:00 |          500 |         800 |
+----------+---------+--------+---------------------+--------------+-------------+
10 rows in set (0.00 sec)

注意这里是按照时间排序的最早订单和最晚订单，并不是最小金额和最大金额订单。

5.6.8聚合函数作为窗口函数

除了前面介绍的各类窗口函数外，也可以使用各种聚合函数（SUM/AVG/MAX/MIN/COUNT）作为窗口函数。

示例：比如要统计每个用户按照订单id，截止到当前的累计订单金额/平均订单金额/最大订单金额/最小订单金额/订单数是多少。

mysql> SELECT order_id,user_no,amount,create_date,
    -> SUM(amount) OVER w AS sum1,
    -> AVG(amount) OVER w AS avg1,
    -> MAX(amount) OVER w AS max1,
    -> MIN(amount) OVER w AS min1,
    -> COUNT(amount) OVER w AS count1
    -> FROM order_tab
    -> WINDOW w AS (PARTITION BY user_no ORDER BY create_date) ;
+----------+---------+--------+---------------------+------+----------+------+------+--------+
| order_id | user_no | amount | create_date         | sum1 | avg1     | max1 | min1 | count1 |
+----------+---------+--------+---------------------+------+----------+------+------+--------+
|        1 | 001     |    100 | 2018-01-01 00:00:00 |  100 | 100.0000 |  100 |  100 |      1 |
|        2 | 001     |    300 | 2018-01-02 00:00:00 |  900 | 300.0000 |  500 |  100 |      3 |
|        3 | 001     |    500 | 2018-01-02 00:00:00 |  900 | 300.0000 |  500 |  100 |      3 |
|        4 | 001     |    800 | 2018-01-03 00:00:00 | 1700 | 425.0000 |  800 |  100 |      4 |
|        5 | 001     |    900 | 2018-01-04 00:00:00 | 2600 | 520.0000 |  900 |  100 |      5 |
|        6 | 002     |    500 | 2018-01-03 00:00:00 |  500 | 500.0000 |  500 |  500 |      1 |
|        7 | 002     |    600 | 2018-01-04 00:00:00 | 1100 | 550.0000 |  600 |  500 |      2 |
|        8 | 002     |    300 | 2018-01-10 00:00:00 | 1400 | 466.6667 |  600 |  300 |      3 |
|        9 | 002     |    800 | 2018-01-16 00:00:00 | 2200 | 550.0000 |  800 |  300 |      4 |
|       10 | 002     |    800 | 2018-01-22 00:00:00 | 3000 | 600.0000 |  800 |  300 |      5 |
+----------+---------+--------+---------------------+------+----------+------+------+--------+
10 rows in set (0.00 sec)

5.7 其他常用函数

MySQL提供的函数很丰富，处理前面介绍的字符串函数、数字函数、日期函数、流程函数以外，还有很多其他的函数，有兴趣的话可以参考MySQL官方手册。

表5-9列举了一些其他常用的函数。

表5-9 MySQL中的其他常用函数
函数	功能
DATABASE()	返回当前数据库名
VERSION()	返回当前数据库版本
USER()	返回当前登录用户名
INET_ATON(IP)	返回IP地址的数字表示
INET_NTOA(num)	返回数字代表的IP地址
PASSWORD(str)	返回字符串str的加密版本
MD5(str)	返回字符串str的MD5值

（1）DATABASE()函数

mysql> SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| test1      |
+------------+
1 row in set (0.00 sec)

（2）VERSION()函数

mysql> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 8.0.36    |
+-----------+
1 row in set (0.00 sec)

（3）USER()函数

mysql> SELECT USER();
+----------------+
| USER()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

（4）INET_ATON(IP)函数：返回IP地址的网络字节序表示。

mysql> SELECT INET_ATON('192.168.1.1');
+--------------------------+
| INET_ATON('192.168.1.1') |
+--------------------------+
|               3232235777 |
+--------------------------+
1 row in set (0.00 sec)

（5）NET_NTOA(num)函数：返回网络字节序代表的IP地址。

mysql> SELECT INET_NTOA(3232235777);
+-----------------------+
| INET_NTOA(3232235777) |
+-----------------------+
| 192.168.1.1           |
+-----------------------+
1 row in set (0.00 sec)

注： INET_ATON(IP)和NET_NTOA(num)函数主要的用途是将字符串的IP地址转换为数字表示的网络字节序，这样可以方便进行IP或者网段的比较。

（6）PASSWORD(str)函数

此函数只用来设置系统用户的密码，不能用来对应用的数据加密。

mysql> SELECT PASSWORD('123456');
ERROR 1064 (42000): You have an error in your SQL syntax; 
check the manual that corresponds to 
your MySQL server version 
for the right syntax to use near '('123456')' at line 1

（7）MD5(str)函数

如果应用方面有加密的需求，可以使用MD5等加密函数来实现。

mysql> SELECT MD5('123456');
+----------------------------------+
| MD5('123456')                    |
+----------------------------------+
| e10adc3949ba59abbe56e057f20f883e |
+----------------------------------+
1 row in set (0.00 sec)