经常写代码的应该都知道函数的重要性，丰富的函数往往能使用户的工作事半功倍。函数可以帮助用户做很多事情，比如字符串的处理、数值的运算、日期的运算等，在这方面MySQL提供了多种内建函数帮助开发人员编写简单快捷的SQL语句，其中常用的函数有字符串函数、日期函数、和数值函数。
字符串函数
字符串函数是最常用的一种函数，在MySQL中，字符串函数是最丰富的一类函数。下面列出MySQL中常用的字符串函数以供参考:
| 函数 | 功能 |
|---|---|
CONCAT(s1,s1,...,sn) |
连接 s1,s2,…sn为一个字符串 |
INSERT(str,x,y,inster) |
将字符串str从第x个位置开始，y个字符长度的子串替换为字符串inster |
LOWER(str) |
将字符串str中所有字符转为小写 |
UPPER(str) |
将字符串str中所有字符转为大写 |
LEFT(str,x) |
返回字符串最左边的x个字符 |
RIGHT(str,x) |
返回字符串最右边的x个字符 |
LPAD(str,n,pad) |
用字符串pad对str最左边进行填充，直到长度为n个字符长度 |
RPAD(str,n,pad) |
用字符串pad对str最右边进行填充，直到长度为n个字符长度 |
LTRIM(str) |
去掉字符串str左侧的空格 |
RTRIM(str) |
去掉字符串str右侧的空格 |
TRIM(str) |
去除字符串头尾空格 |
REPEAT(str,x) |
返回字符串str重复x次的效果 |
REPLACE(str,a,b) |
用字符串b替换字符串str中的所有出现的字符串a |
STRCMP(s1,s2) |
比较字符串s1和s2 |
SUBSTRING(str,x,y) |
返回字符串str从x位置起y长度的字符串 |
下面通过实例介绍字符串函数的使用方法，需要注意的是，例子只是用于说明各个函数的使用方法，所以函数都是单独出现的，但在实际使用中，往往需要综合几个甚至几类函数才能实现相应的应用。
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)
INSERT 函数
将字符串str从x位置开始，将长度y的字符替换为instr，下面的例子吧字符串“beijing2008you”中从第12个字符开始，将后面的三个字符替换为“me”。
mysql> select INSERT("beijing2008you",12,3,"me");
+------------------------------------+
| INSERT("beijing2008you",12,3,"me") |
+------------------------------------+
| beijing2008me |
+------------------------------------+
1 row in set (0.00 sec)
LOWER 和 UPPER 函数
将字符串转换为大写或者小写：
mysql> select LOWER("BeiJing2008"),UPPER("BeiJing2008");
+----------------------+----------------------+
| LOWER("BeiJing2008") | UPPER("BeiJing2008") |
+----------------------+----------------------+
| beijing2008 | BEIJING2008 |
+----------------------+----------------------+
1 row in set (0.00 sec)
LEFT 和 RIGHT 函数
分别返回字符串从左边开始x个字符和右边开始x个字符：
mysql> select LEFT("beijing2008",7),RIGHT("beijing2008",4),LEFT("beijing2008",null);
+-----------------------+------------------------+--------------------------+
| LEFT("beijing2008",7) | RIGHT("beijing2008",4) | LEFT("beijing2008",null) |
+-----------------------+------------------------+--------------------------+
| beijing | 2008 | NULL |
+-----------------------+------------------------+--------------------------+
1 row in set (0.00 sec)
LPAD 和 RPAD 函数
对字符串的左边或右边进行填充，直到长度为n个字符长度：
mysql> select LPAD("2008",20,"beijing"),rpad("beijing",20,"2008");
+---------------------------+---------------------------+
| LPAD("2008",20,"beijing") | rpad("beijing",20,"2008") |
+---------------------------+---------------------------+
| beijingbeijingbe2008 | beijing2008200820082 |
+---------------------------+---------------------------+
1 row in set (0.00 sec)
LTRIM 和 RTRIM 函数
去掉字符串左侧或右侧的空格
mysql> select LTRIM(" |beijing"),RTRIM("beijing| ");
+----------------------+--------------------------+
| LTRIM(" |beijing") | RTRIM("beijing| ") |
+----------------------+--------------------------+
| |beijing | beijing| |
+----------------------+--------------------------+
TRIM 函数
去掉字符串开头和结尾的空格
mysql> select TRIM(" $beijing$ ");
+-------------------------+
| TRIM(" $beijing$ ") |
+-------------------------+
| $beijing$ |
+-------------------------+
1 row in set (0.00 sec)
REPEAT 函数
返回字符串重复x次的结果：
mysql> select REPEAT("mysql-",3);
+--------------------+
| REPEAT("mysql-",3) |
+--------------------+
| mysql-mysql-mysql- |
+--------------------+
1 row in set (0.00 sec)
REPLACE 函数
用字符串b替换字符串str中出现的所有a：
mysql> select REPLACE('2010beijing_2010','2010','2008');
+-------------------------------------------+
| REPLACE('2010beijing_2010','2010','2008') |
+-------------------------------------------+
| 2008beijing_2008 |
+-------------------------------------------+
1 row in set (0.00 sec)
STRCMP 函数
比较两个字符串s1和s2的ASCII码值的大小，如果s1比s2小，返回-1；如果s1和s2相等，则返回0；如果s1比s2大，则返回1；
mysql> select strcmp('a','b'),strcmp('b','b'),strcmp('c','b');
+-----------------+-----------------+-----------------+
| strcmp('a','b') | strcmp('b','b') | strcmp('c','b') |
+-----------------+-----------------+-----------------+
| -1 | 0 | 1 |
+-----------------+-----------------+-----------------+
1 row in set (0.00 sec)
SUBSTRIMG 函数
返回字符串str中从位置x起y个长度的字符：
mysql> select substring("beijing2008",8,4);
+------------------------------+
| substring("beijing2008",8,4) |
+------------------------------+
| 2008 |
+------------------------------+
1 row in set (0.00 sec)
其他字符串函数
| 函数 | 功能 |
|---|---|
FIND_IN_SET('str','strlist') |
字符串是否存在于字符串列表中，列表使用逗号分隔 例如： FIND_IN_SET('b','a,b,c,d');存在返回位置，不存在返回 0 |
LENGTH(str) |
返回字符串长度，以字节为单位。 |
TO_BASE64('str') |
对字符串进行BASE64编码 |
FROM_BASE64('YWFh'); |
BASE64 解码 |
GROUP_CONCAT() |
将查询的多个结果，使用逗号拼接 |
UUID_TO_BIN() |
MySQL8.0，将uuid转为二进制，转换后是一个 varchar(16) 的值。（做主键即能代替id自增，又能解决uuid 做主键性能不好的问题） |
BIN_TO_UUID() |
将二进制的uuid 转回uuid |
数值函数
MySQL中另外一类重要的函数就是数值函数，这些函数能处理很多数值方面的运算。如果没有这些函数的支持，开发者在编写有关数值运算的代码时，将会困难重重。下面列出了MySQL中会经常使用到的数值函数。
| 函数 | 功能 |
|---|---|
ABS(x) |
返回x的绝对值 |
CEIL(x) |
返回大于X的最小整数值 |
FLOOR(x) |
返回小于x的最大整数值 |
MOD(x,y) |
返回x/的模 |
RAND() |
返回 0 ~ 1 之间的随机数 |
ROUND(x,y) |
返回参数x的四舍五入到小数点y位的值 |
TRUNCATE(x,y) |
返回数字x截断为y位小数的结果 |
结合下面的示例对函数进行介绍：
ABS 函数
返回x的绝对值
mysql> select ABS(-0.8),ABS(0.8);
+-----------+----------+
| ABS(-0.8) | ABS(0.8) |
+-----------+----------+
| 0.8 | 0.8 |
+-----------+----------+
1 row in set (0.01 sec)
CEIL 函数
返回大于x的最小整数
+------------+-----------+
| CEIL(-0.8) | CEIL(0.8) |
+------------+-----------+
| 0 | 1 |
+------------+-----------+
1 row in set (0.01 sec)
FLOOR 函数
返回小于x的最大整数，与CEIL正好相反
mysql> select FLOOR(-0.8),FLOOR(0.8);
+-------------+------------+
| FLOOR(-0.8) | FLOOR(0.8) |
+-------------+------------+
| -1 | 0 |
+-------------+------------+
1 row in set (0.00 sec)
MOD 函数
返回x/y的模，与x%y效果相同。除数和被除数任何一个为NULL则结果均为NULL
mysql> select MOD(15,10),MOD(1,11),MOD(NULL,10);
+------------+-----------+--------------+
| MOD(15,10) | MOD(1,11) | MOD(NULL,10) |
+------------+-----------+--------------+
| 5 | 1 | NULL |
+------------+-----------+--------------+
1 row in set (0.01 sec)
RAND 函数
返回0 ~ 1的随机数
mysql> select RAND(),RAND();
+--------------------+--------------------+
| RAND() | RAND() |
+--------------------+--------------------+
| 0.8084540644739326 | 0.4821989056488489 |
+--------------------+--------------------+
1 row in set (0.00 sec)
可以使用此函数获取任意指定范围日内的随机数，如需要0~100的随机数可以如下操作：
mysql> select ceil(100*rand()),ceil(100*rand());
+------------------+------------------+
| ceil(100*rand()) | ceil(100*rand()) |
+------------------+------------------+
| 46 | 81 |
+------------------+------------------+
1 row in set (0.00 sec)
ROUND 函数
返回参数x的四舍五入到小数点y位的值
如果是整数，将会保留y位数量的0；如果不写，则默认y为0，既将x四舍五入后取整。适合于将所有数字保留同样小数位的情况：
mysql> select ROUND(1.1),ROUND(1.1,3),ROUND(1,2);
+------------+--------------+------------+
| ROUND(1.1) | ROUND(1.1,3) | ROUND(1,2) |
+------------+--------------+------------+
| 1 | 1.100 | 1 |
+------------+--------------+------------+
1 row in set (0.00 sec)
TRUNCATE 函数
返回数字截断y位小数的结果，注意TRUNCATE和ROUND的区别是只进行截断，而不进行四舍五入
mysql> select ROUND(1.235,2),TRUNCATE(1.235,2);
+----------------+-------------------+
| ROUND(1.235,2) | TRUNCATE(1.235,2) |
+----------------+-------------------+
| 1.24 | 1.23 |
+----------------+-------------------+
1 row in set (0.00 sec)
时间和日期函数
有时候我们会遇到这样的需求：当前时间的多少；下个月的今天是星期几；统计截止到当前日期前三天的收入总和；这些需求就需要时间和日期函数来实现，下面列出MySQL支持的常用时间和日期函数
| 函数 | 功能 |
|---|---|
CURDATE() |
返回当前日期 |
CURTIME() |
返回当前时间 |
NOW() |
返回当前日期和时间 |
UNIX_TIMESTAMP(date) |
返回日期date的UNIX时间戳 |
FROM_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之间的天数 |
CURDATE 函数
返回当前日期，只包含年、月、日。
mysql> select CURDATE();
+------------+
| CURDATE() |
+------------+
| 2020-10-25 |
+------------+
1 row in set (0.01 sec)
CURTIME 函数
返回当前时间，只包含时、分、秒。
mysql> select CURTIME();
+-----------+
| CURTIME() |
+-----------+
| 18:14:48 |
+-----------+
1 row in set (0.01 sec)
NOW 函数
返回当前的时间和日期，年月日时分秒全部包含。
mysql> select NOW();
+---------------------+
| NOW() |
+---------------------+
| 2020-10-25 18:18:11 |
+---------------------+
1 row in set (0.00 sec)
UNIX_TIMESTAMP 函数
返回日期date转换的UNIX时间戳
mysql> select UNIX_TIMESTAMP(NOW());
+-----------------------+
| UNIX_TIMESTAMP(NOW()) |
+-----------------------+
| 1603621278 |
+-----------------------+
1 row in set (0.01 sec)
FROM_UNIXTIME 函数
返回UNIXTIME时间戳的日期值，与UNIX_TIMESTAMP互为逆操作
mysql> select FROM_UNIXTIME(1603621278);
+---------------------------+
| FROM_UNIXTIME(1603621278) |
+---------------------------+
| 2020-10-25 18:21:18 |
+---------------------------+
1 row in set (0.01 sec)
WEEK 和 YEAR 函数
前者返回所给日期所在一年中的第几周，后者返回所给日期是那一年。
mysql> select WEEK(NOW()),YEAR(NOW());
+-------------+-------------+
| WEEK(NOW()) | YEAR(NOW()) |
+-------------+-------------+
| 43 | 2020 |
+-------------+-------------+
1 row in set (0.01 sec)
HOUR 和 MINUTE 函数
前者返回所给时间的小时，后者返回所给时间的分钟。
mysql> select HOUR(CURTIME()),MINUTE(CURTIME());
+-----------------+-------------------+
| HOUR(CURTIME()) | MINUTE(CURTIME()) |
+-----------------+-------------------+
| 18 | 50 |
+-----------------+-------------------+
1 row in set (0.00 sec)
MONTHNAME 函数
返回date的英文月份名称
mysql> select MONTHNAME(NOW());
+------------------+
| MONTHNAME(NOW()) |
+------------------+
| October |
+------------------+
1 row in set (0.00 sec)
DATE_FORMAT 函数
按照字符串fmt格式化日期date值，此函数能够按照指定的格式显示日期可以用到的格式符如下：
| 格式符 | 格式说明 |
|---|---|
%S 和 %s |
两位数字形式的秒，00,01,...,59 |
%i |
两位数字形式的分，00,01,...59 |
%H |
两位数字形式的小时，24小时，00,01,...,24 |
%h 和 %I |
两位数字形式的小时，12小时，00,01,...,12 |
%k |
数字形式的小时，24小时，0,1,...,23 |
%l |
数字形式的小时，12小时，0,1,...,12 |
%T |
24小时的时间格式，hh:mm:ss |
%r |
12小时的时间格式，hh:mm:ssAM 或hh:mm:ssPM |
%p |
AM 或 PM |
%W |
一周中每一天的名称，Sunday、Monday、Saturday |
%w |
数字形式表示周中的天数，0 ~ 6 |
%a |
一周中每一天的缩写，Sun、Mon、Sat |
%d |
两位数字表示月中的天数，00,01,...,31 |
%e |
数字形式表示月中的天数，0,1,...,31 |
%D |
英文后缀表示月中的天数，1st,2nd,3rd... |
%j |
以三位数字表示年中的天数，00,002,...,366 |
%U |
周0,1,...,53，其中周日为周中的第一天 |
%u |
周0,1,...,53，其中周一为周中的第一天 |
%M |
月名，January、February、December |
%b |
缩写的月名，Jan、Feb、Dec |
%m |
两位数字的月份，01,02,...,12 |
%c |
一位数字的月份，1,2,...,12 |
%Y |
四位数字的年份，2018、2019、2020 |
%y |
两位数字的年份、18、19、20 |
%% |
直接值 “%” |
使用下面的SQL，显示年月日：
mysql> select DATE_FORMAT(NOW(),'%M,%D,%Y');
+-------------------------------+
| DATE_FORMAT(NOW(),'%M,%D,%Y') |
+-------------------------------+
| October,25th,2020 |
+-------------------------------+
1 row in set (0.00 sec)
DATE_ADD 函数
返回与所给日期DATE相差INTERVAL时间段的日期。其中INTERVAL是间隔类型关键字，expr 是表达式，这个表达式对应后面的类型，type 是间隔时间类型。MySQL提供了13种时间间隔类型：
| 表达式 | 描述 | 格式 |
|---|---|---|
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 |
使用下面的例子，第一列返回当前日期，第二列返回31天后的日期，第三列返回一年零两个月后的日期
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 |
+---------------------+---------------------+------------------------+
| 2020-11-02 00:08:31 | 2020-12-03 00:08:31 | 2022-01-02 00:08:31 |
+---------------------+---------------------+------------------------+
1 row in set (0.00 sec)
DATEDIFF 函数
计算两个日期相差的天数，计算距离元旦还有几天：
mysql> select DATEDIFF('2021-01-01',now());
+------------------------------+
| DATEDIFF('2021-01-01',now()) |
+------------------------------+
| 60 |
+------------------------------+
1 row in set (0.00 sec)
SEC_TO_TIME 函数
秒换算成小时
mysql> select SEC_TO_TIME(360000);
+---------------------+
| SEC_TO_TIME(360000) |
+---------------------+
| 100:00:00 |
+---------------------+
1 row in set (0.00 sec)
流程函数
流程函数也是常用的一类函数，用户可以使用这类函数在一个SQL语句中实现条件选择，这样做能够提高语句的效率。下面列出MySQL中和条件选择有关的流程函数。
| 函数 | 功能 |
|---|---|
IF(value,t f) |
如果vuale为真返回 t ; 否则返回 f |
IFNULL(value1,value2) |
如果value1不为空，返回value1，否则返回value2 |
CASE WHEN [value1] THEN[result1]...ELSE [default]END |
如果value1为真，返回result1，否则返回default |
CASE [expr] WHEW [value1] THEN[result1]...ELSE[defalut]END |
如果expr 等于 value1 则返回result1，否则返回default |
下面的例子模拟对职员薪水进行分类，首先创建一个职员薪水表，并插入一些数据：
mysql> create table salary (userid int,salary decimal(9,2));
Query OK, 0 rows affected (0.02 sec)
mysql> insert into salary values(1,1000),(2,2000),(3,3000),(4,4000),(5,5000),(1,null);
Query OK, 6 rows affected (0.01 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)
IF 函数
如薪资在2000元以上的职员为高薪，用“high”表示。2000元以下职员属于低薪，用“low” 表示：
mysql> select userid,salary,if(salary>2000,'high','low') 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)
IFNULL 函数
这个函数一般是用来替换NULL的，我们知道NULL是不能参与运算的，下面这个语句就是把null值用0来替换：
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)
CASE 函数简单使用
CASE [expr] WHEN [value1] THEN [result1]...ELSE[default]END 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)
CASE 搜索函数用法
CASE WHEN [expr] THEN[reslut1]...ELSE[defalut]END ，直接在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)
JSON函数
之前已经提到过MySQL在5.7.8之后引入了JSON文档类型，对于JSON文档的操作，除了简单的读写外，通常还会有各种各样的查询、修改等需求，为此MySQL提供了相应的函数：
创建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() / 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_STORAGE_FREE() |
JSON更新操作后剩余的空间，MySQL8.0新增 |
JSON_TABLE() |
将JSON文档转为表格，MySQL8.0新增 |
JSON_ARRAYAGG() |
将聚合后参数中多个值转为JSON数组 |
JSON_OBJECTAGG() |
把两个列或表达式解释为一个key一个value，返回JSON对象 |
创建JSON数组
JSON_ARRAY 函数
JSON_ARRAY([val[,val]...])
此参数可以返回包含参数中所有值列表的JSON数组。
mysql> select JSON_ARRAY(1,'abc',NULL,TRUE,CURTIME());
+-------------------------------------------+
| JSON_ARRAY(1,'abc',NULL,TRUE,CURTIME()) |
+-------------------------------------------+
| [1, "abc", null, true, "22:00:08.000000"] |
+-------------------------------------------+
1 row in set (0.00 sec)
JSON_OBJECT 函数
JSON_OBJECT(key,value[,key,value])
此函数可以返回包含所有键值对的对象列表。
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)
JSON_QUOTE 函数
JSON_QUOTE(string)
此函数可以将参数中的JSON文档转额外i双引号引起来的字符串，如果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_CONTAINS 函数
JSON_CONTAONS(target,candidate[,path])
指定的元素是否包含在目标文档中，包含返回1，否则返回0；path参数可选。如果有参数为NULL或path不存在，则返回NULL。
一下示例分别查询元素 “abc”、1、10是否包含在JSON文档中
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]','1');
+-----------------------------------------+
| json_contains('[1,2,3,"abc",null]','1') |
+-----------------------------------------+
| 1 |
+-----------------------------------------+
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，或 为 a对象下的b对象中 。如果JSON文档为数组，则使用 $[i] 这种格式，i为下标。
mysql> set @j = '{"jack": 10,"tom": 20, "lisa": 30}';
Query OK, 0 rows affected (0.00 sec)
mysql> set @v = '10';
Query OK, 0 rows affected (0.00 sec)
mysql> select json_contains(@j,@v,'$.jack');
+-------------------------------+
| json_contains(@j,@v,'$.jack') |
+-------------------------------+
| 1 |
+-------------------------------+
1 row in set (0.00 sec)
mysql> select json_contains(@j,@v,'$.tom');
+------------------------------+
| json_contains(@j,@v,'$.tom') |
+------------------------------+
| 0 |
+------------------------------+
1 row in set (0.00 sec)
JSON_CONTAINS_PATH 函数
JSON_CONTAINS_PATH(json_doc,one_or_all,path[,path])
查询JSON文档中是否包含指定路径，包含返回1，否则返回0。one_or_all 只能取值one 或 all，one 表示存在一个即可，all表示必须全部存在。如果有参数为null或path不存在则返回NULL。
查询给定的三个path是否至少存在一个或者必须全部存在，可以这样写：
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)
JSON_EXTRACT 函数
JSON_EXTRACT(json_doc,path[,path]...)
此函数可以从JSON文档中抽取数据，如果有参数有NULL或path不存在，则返回NULL。如果抽取出多个path，则返回的数据合并在一个JSON数组里。
示例从JSON文档的第一个元素和第二个元素中抽取对应的value，“*” 代表所有key：
mysql> select JSON_EXTRACT('[10,20,[30,40]]','$[0]','$[1]','$[2][0]');
+---------------------------------------------------------+
| JSON_EXTRACT('[10,20,[30,40]]','$[0]','$[1]','$[2][0]') |
+---------------------------------------------------------+
| [10, 20, 30] |
+---------------------------------------------------------+
1 row in set (0.00 sec)
mysql> select JSON_EXTRACT('[10,20,[30,40]]','$[0]','$[1]','$[2][*]');
+---------------------------------------------------------+
| JSON_EXTRACT('[10,20,[30,40]]','$[0]','$[1]','$[2][*]') |
+---------------------------------------------------------+
| [10, 20, 30, 40] |
+---------------------------------------------------------+
1 row in set (0.00 sec)
在 MySQL 5.7.9 之后可以使用一种更简单的函数 -> 来代替 JSON_EXTRACT：
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 where id1->'$[0]' = 10;
+--------------------+-------------+-------------+
| id1 | id1->"$[0]" | id1->'$[1]' |
+--------------------+-------------+-------------+
| [10, 20, [30, 40]] | 10 | 20 |
+--------------------+-------------+-------------+
1 row in set (0.00 sec)
JSON_UNQUTE 函数
如果JSON文档的查询结果是字符串，则显示结果默认会包含双引号，在很多情况下是不需要的，为了解决这个问题，MySQL提供了另外两个函数 JSON_UNQUOTE() 和 ->> ，用法类似于 JSON_EXTRACT 和 -> 简单举例如下：
mysql> select JSON_EXTRACT(id1,'$.k1'),JSON_UNQUOTE(id1->'$.k1'),id1->'$.k1',id1->>'$.k1' from t1 where id1->'$.k1' = 'jack';
+--------------------------+---------------------------+-------------+--------------+
| JSON_EXTRACT(id1,'$.k1') | JSON_UNQUOTE(id1->'$.k1') | id1->'$.k1' | id1->>'$.k1' |
+--------------------------+---------------------------+-------------+--------------+
| "jack" | jack | "jack" | jack |
+--------------------------+---------------------------+-------------+--------------+
1 row in set (0.00 sec)
既下面三种写法都是一样的
JSON_UNQUOTE(JSON_EXTRACT(column,path))JSON_UNQUOTE(column->path)column->>path
JSON_KEYS 函数
JSON_KEYS(json_doc[,path])
获得指定路径下的所有键值，返回一个JSON ARRAY。参数为NULL或path不存在，则返回NULL
mysql> SELECT JSON_KEYS('{"a":1,"b":{"c":3}}');
+----------------------------------+
| JSON_KEYS('{"a":1,"b":{"c":3}}') |
+----------------------------------+
| ["a", "b"] |
+----------------------------------+
1 row in set (0.00 sec)
mysql> SELECT JSON_KEYS('{"a":1,"b":{"c":3}}','$.b');
+----------------------------------------+
| JSON_KEYS('{"a":1,"b":{"c":3}}','$.b') |
+----------------------------------------+
| ["c"] |
+----------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT JSON_KEYS('[1,2,3]');
+----------------------+
| JSON_KEYS('[1,2,3]') |
+----------------------+
| NULL |
+----------------------+
1 row in set (0.00 sec)
如果元素中都是数组，则返回为NULL
JSON_SEARCH 函数
JSON_SEARCH(json_doc,one_or_all,search_str[,secape_char[,path]...])
此函数可以查询包含指定字符串的路径，并作为一个JOSN ARRAY 返回。如果有参数为NULL或path不存在，则返回NULL。各参数含义如下：
one_or_allone 表示查询到一个即返回，all表示查询所有。search_str要查询的字符串 可以使用LIKE中的%和_匹配path表示在指定的path下进行查询
示例如何查询出JSON文档中以字母t开头的第一个键名:
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)
如果要查询出所有，则把one改成all：
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)
如果把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)
修改 JSON 的函数
JSON_ARRAY_APPEND 函数
JSON_ARRAY_APPEND(json_doc,path,val[,path,val]...)
此函数可以在指定path的JSON 数组尾部追加value。如果追加的是一个JSON对象，则封装成一个数组再追加，如果有参数为NULL，则返回NULL。
示例在JSON文档中的不同path处分别追加字符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)
JSON_ARRAY_INSERT 函数
JSON_ARRAY_INSERT(json_doc,path,val[,path,val]...)
此函数可以指定path的JSON数组插入val，原位置及以右的元素依次右移。如果path指定的数据非JSON数组元素，则略过此val；如果指定元素的下标超过JSON数组的长度，则插入尾部。
将上面四个SQL语句改为JSON_ARRAY_INSERT 看一下结果：
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)
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.
最后一个SQL报错，提示路径不对，改成 “$[0]”：
mysql> select JSON_ARRAY_INSERT('{"a":1,"b":[2,3],"c":4}','$[0]','1');
+---------------------------------------------------------+
| JSON_ARRAY_INSERT('{"a":1,"b":[2,3],"c":4}','$[0]','1') |
+---------------------------------------------------------+
| {"a": 1, "b": [2, 3], "c": 4} |
+---------------------------------------------------------+
1 row in set (0.00 sec)
执行成功，但JSON文档并没有改变，因为JSON文档都是对象，所以被略过了。
JSON_REPLACE 函数
JSON_REPLACE(json_doc,path,val[,path,val]...)
此函数可以替换指定路径的数据，如果某个路径不存在，则略过（存在才替换）。如果参数为NULL，则返回NULL。
将下列JSON文档中的第一个元素和第二个元素分别替换为1，和2：
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)
JSON_SET 函数
JSON_SET(json_doc,path,val[,path,val]...)
此函数可以设置指定路径的数据（不论是否存在）。如果有参数为NULL，则返回NULL。和JSON_PERLACE的区别是当路径不存在时，会添加而不是略过。
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)
JSON_MERGE_PRESERVE 函数
JSON_MERGE_PRESERVE(json_doc,json_doc,[,json_doc]...)
将多个JSON文档进行合并。合并规则如下：
- 如果全是JSON数组，则结果自动merge成为一个JSON数组
- 如果全是JSON对象，则结果自动merge成为一个JSON对象；
- 如果有多种类型，则将非JSON数组的元素封装成JSON数组，再按照规则1，进行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"}','{"kay2":"lisa"}');
+---------------------------------------------------------+
| JSON_MERGE_PRESERVE('{"key1":"tom"}','{"kay2":"lisa"}') |
+---------------------------------------------------------+
| {"kay2": "lisa", "key1": "tom"} |
+---------------------------------------------------------+
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)
JSON_REMOVE 函数
JSON_REMOVE(json_doc,path[,path])
此函数可以移除指定路径的数据，如果某个路径不存在则略过此路径。如果有参数为NULL，则返回NULL。
下例中把JSON文档中的第二个元素和第三个元素删除：
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)
由于删除操作是串行操作，既先删除 $[1] 后为JSON文档变为 [1,3,4]，在此基础上删除 $[2]，后变为 [1,3]
查询 JSON 元数据函数
JSON_DEPTH 函数
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)
JSON_LENGTH 函数
JSON_LENGTH(json_doc)
获取指定路径下的文档长度。计算规则如下：
- 标量（数字、字符串）的长度为1
- JSON数组的长度为元素的个数
- JSON对象的长度为对象的个数
- 嵌套数组或嵌套对象不计算长度
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)
JSON_TYPE 函数
JSON_TYPE(json_val)
获得JSON文本的具体类型，可以是数组、对象、标量类型
mysql> select json_type('[1,2]'),json_type('{"id":"tom"}');
+--------------------+---------------------------+
| json_type('[1,2]') | 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)
JSON_VALID 函数
JSON_VALID(val)
此函数可以判断val是否是有效的JSON格式，有效为1，否则为0。
mysql> select JSON_VALID('abc'),JSON_VALID('"abc"'),JSON_VALID('[1,2]'),JSON_VALID('[1,2');
+-------------------+---------------------+---------------------+--------------------+
| JSON_VALID('abc') | JSON_VALID('"abc"') | JSON_VALID('[1,2]') | JSON_VALID('[1,2') |
+-------------------+---------------------+---------------------+--------------------+
| 0 | 1 | 1 | 0 |
+-------------------+---------------------+---------------------+--------------------+
1 row in set (0.00 sec)
JSON 工具函数
JSON_PRETTY 函数
JSON_PRETTY(json_doc)
JSON文档格式化输出，此函数为MySQL5.7.22新增
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)
JSON_STORAGE_SIZE / JSON_STORAGE_FREE
JSON_STORAGE_SIZE(json_val)
此函数可以获取JSON文档占用的存储空间（byte）
JSON_STORAGE_FREE(json_doc)
此函数可以获取由于 JSON_SET() 、JSON_REPLACE()、JSON_REMOVE() 操作导致释放的空间。此函数是MySQL8.0 新增函数。
mysql> create table jtable (jcol JSON);
Query OK, 0 rows affected (0.34 sec)
mysql> insert into jtable values('{"Name":"Homer","Stupid":"True"}');
Query OK, 1 row affected (0.31 sec)
mysql> select json_storage_size(jcol),json_storage_free(jcol),jcol from jtable;
+-------------------------+-------------------------+-------------------------------------+
| json_storage_size(jcol) | json_storage_free(jcol) | jcol |
+-------------------------+-------------------------+-------------------------------------+
| 40 | 0 | {"Name": "Homer", "Stupid": "True"} |
+-------------------------+-------------------------+-------------------------------------+
1 row in set (0.00 sec)
mysql> update jtable set jcol=json_set(jcol,'$.Stupid',1);
Query OK, 1 row affected (0.30 sec)
Rows matched: 1 Changed: 1 Warnings: 0
json_storage_size 显示此文档占用的空间为40字节，由于没有字段更新， 所以 json_storage_size 显示为0。
update 更新后：
mysql> update jtable set jcol=json_set(jcol,'$.Stupid',1);
Query OK, 1 row affected (0.30 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select json_storage_size(jcol),json_storage_free(jcol),jcol from jtable;
+-------------------------+-------------------------+--------------------------------+
| json_storage_size(jcol) | json_storage_free(jcol) | jcol |
+-------------------------+-------------------------+--------------------------------+
| 40 | 5 | {"Name": "Homer", "Stupid": 1} |
+-------------------------+-------------------------+--------------------------------+
1 row in set (0.00 sec)
如果不使用 JSON_SET() 、JSON_REPLACE()、JSON_REMOVE() 操作数据，则意味着优化器无法就地执行更新。在这种情况下 JSON_STORAGE_FREE()返回0。
mysql> select json_storage_size(jcol),json_storage_free(jcol),jcol from jtable;
+-------------------------+-------------------------+----------------------+
| json_storage_size(jcol) | json_storage_free(jcol) | jcol |
+-------------------------+-------------------------+----------------------+
| 25 | 0 | {"a": "1", "b": "2"} |
+-------------------------+-------------------------+----------------------+
1 row in set (0.00 sec)
JSON文档的部分更新只能在列值上执行，对于存储JSON值的用户变量，即使使用JSON_SET 更新 ，返回值依然是0：
mysql> SET @j = '{"a": 10, "b": "wxyz", "c": "[true, false]"}';
Query OK, 0 rows affected (0.00 sec)
mysql> SET @j = JSON_SET(@j, '$.a', 10, '$.b', 'wxyz', '$.c', '1');
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @j, JSON_STORAGE_FREE(@j) AS Free;
+----------------------------------+------+
| @j | Free |
+----------------------------------+------+
| {"a": 10, "b": "wxyz", "c": "1"} | 0 |
+----------------------------------+------+
1 row in set (0.00 sec)
对于JSON字符串 返回值始终是0：
mysql> SELECT JSON_STORAGE_FREE('{"a": 10, "b": "wxyz", "c": "1"}') AS Free;
+------+
| Free |
+------+
| 0 |
+------+
1 row in set (0.00 sec)
JSON_TABLE 函数
JSON_TABLE(expr,path COLUMNS (column_list) [AS] alias)
此函数可以将JSON文档映射为表格。
expr表达式或者列path用来过滤JSON路径COLUMNS常量关键词column_list转换后的字段列表
此函数是MySQL8.0.4版本增加的一个重要的函数，可以将复杂的JSON文档转换为表格数据，转换后的表格可以像正常表一样的做连接、排序、CREATE table as select 等各种操作，对JSON的数据展示、数据迁移等很多应用领域带来极大的灵活性和便利性。
下面例子将JSON文档中的全部数据转换为表格，并按照表格中的ac字段进行排序：
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.34 sec)
对例子中的参数做简单介绍：
exprJSON对象数组- 过滤路径
path，其中 “[0]” 则表示只转换第一个元素 {“10”：“3”}
column list包含以下四部分内容：rowid FOR ORDINALITY- rowid 是转换后的列名，
- FOR ORDINALITY 表示按照序列顺序加1，类似于MySQL的自增列
- 数据类型为 UNSIGNED INT，初始值为1
ac VARCHAR(300) PATH "$.a" DEFAULT '999' ON ERROR DEFAULT '111' ON EMPTY- as 是转换后的列名
- VARCHAR(100) 是转换后的列类型，
- PATH “$.a” 说明此字段仅记录 key为A的 value，
- DEFAULT ‘999’ ON ERROR 表示如果发生error 转换为默认值 999，比如{“a”:[1,2]}，value 为数组，和 VARCHAR 类型不匹配，所以此对象转换后为999
- DEFAULT ‘111’ ON EMPTY 表示 对应的key 不匹配 ‘a’ 此对象转换后为 “111” ，比如 {“b”:1}
- aj 和 ac 类似，只是转换后的类型为JSON
bx INT EXISTS PATH '$.b'- bx是转换后的列名
- 如果存在路径 “$.b” 既 key = b的对象，则转换为1，否则转换为0
JSON_ARRAYAGG 函数
将聚合后参数中的多个值转化为JSON数组。
下面的例子按照o_id 聚合后的属性列表转换为一个字符串JSON数组：
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.01 sec)
mysql> SELECT o_id, JSON_ARRAYAGG(attribute) AS attribtes FROM t GROUP BY o_id;
+------+---------------------+
| o_id | attribtes |
+------+---------------------+
| 2 | ["color", "fabric"] |
| 3 | ["color", "shape"] |
+------+---------------------+
2 rows in set (0.00 sec)
JSON_OBJECTAGG 函数
把两个列或者表达式解释为一个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)
窗口函数
日常开发工作中经常会遇到如下需求：
- 去医院看病，怎么知道上次就医距现在的时长？
- 环比如何计算？
- 怎么得到各个部门工资篇排名前N名的员工列表？
- 如何查找组内人员没人工资占比总工资的百分比？
如果使用传统的SQL来解决，理论上是可以解决的，但逻辑却相当复杂。这类需求都有一个相同的特点，为了得到结果，都需要再在某个结果集内做一些特定的函数操作。 为了解决这一问题，MySQL8.0引入了窗口函数。
窗口的概念十分重要，它可以理解为记录合集，窗口函数也是满足某种条件的记录合集上执行特殊的函数，对于每条记录都要在此窗口内执行函数。有些函数随着记录不同，窗口大小都是固定的，这种属于静态窗口；有的函数则相反，不同的记录对应着不同的窗口，这种动态变化的窗口属于滑动窗口。
窗口函数和聚合函数有些类似，两者最大的区别是聚合函数是多行聚合为一行，窗口函数则是多行聚合为相同的行数，每行会多一个聚合后的新列。窗口函数在其他数据库（如Oracle）也被称为分析函数，功能也都大体相似。
MySQL中的窗口函数
| 函数 | 功能 |
|---|---|
ROW_NUMBER() |
分区中当前的行号 |
RANK() |
当前行在分区中的排名，含序号间隙 |
DENSE_RANK() |
当前行在分区中的排名，不含序号间隙 |
PERCENT_RANK() |
百分比等级值 |
CUME_DIST() |
累计分配值 |
FIRST_VALUE() |
窗口中的第一行的参数值 |
LAST_VALUE() |
窗口中最后一行的参数值 |
LAG() |
分区中指定行落后于当前行的参数值 |
LEAG() |
分区中领先当前行的参数值 |
NTH_VALUE() |
从第N行窗口框架的参数值 |
NTILE() |
分区当前存储的行号 |
窗口函数在 MySQL 8.0.22 版本 测试
以订单表 order_tab 为例，逐个讲解这些函数的使用。测试表中的如下数据，各字段含义顺序 分别为： 订单号、用户 id、订单金额、创建日期：
mysql> SELECT * FROM order_tab;
+----------+---------+--------+-------------+
| order_id | user_no | amount | create_date |
+----------+---------+--------+-------------+
| 1 | 001 | 100 | 2021-01-01 |
| 2 | 001 | 300 | 2021-01-02 |
| 3 | 001 | 500 | 2021-01-02 |
| 4 | 001 | 800 | 2021-01-03 |
| 5 | 001 | 900 | 2021-01-04 |
| 6 | 002 | 500 | 2021-01-03 |
| 7 | 002 | 600 | 2021-01-04 |
| 8 | 002 | 300 | 2021-01-10 |
| 9 | 002 | 800 | 2021-01-16 |
| 10 | 002 | 800 | 2021-01-22 |
+----------+---------+--------+-------------+
10 rows in set (0.05 sec)
ROW_NUMBER 函数
如果要查询每个用户的最新一笔订单，我们希望的结果是 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 | 2021-01-04 |
| 1 | 10 | 002 | 800 | 2021-01-22 |
+---------+----------+---------+--------+-------------+
2 rows in set (0.44 sec)
其中，row_number() 后面的 over 是关键字，用来指定函数执行的窗口范围，如果后面的括号中什么都写，则意味着窗口包含所有行，窗口函数在所有行上进行计算；如果不为空，则支持以下四种写法：
window_name给窗口指定一个别名，如果 SQL 中涉及的窗口较多，采用别名则更清晰易读，上面的例子中如果指定一个别名 w，则代码改写如下：SELECT * FROM ( SELECT row_number()over w AS row_num,order_id,user_no FROM order_tab WINDOW w as (partition BY user_no ORDER BY create_date DESC ) ) t WHERE row_num = 1
partition子句：窗口按照哪些字段进行分组， 窗口函数在不同的分组上分别执行，上面的例子就是按照用户id 分组。在每一个用户的消费记录上 分别从1 开始顺序编号。order by子句：按照哪些字段进行排序， 窗口函数将按照排序后的顺序进行编号，既可以和 parition 子句配合使用，也可以单独使用。frame子句：frame 是当前分区（分组后的数据，一组为一个分区）的一个子集，子句可以定义子集的规则，通常用来作为滑动窗口使用。例如要根据每个订单动态计算包括本订单按时间顺序前后两个订单的平均订单金额，则可以通过设置 frame 子句来创建滑动窗口：mysql> SELECT * FROM ( select order_id,user_no,amount, avg(amount) over w 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 | 2021-01-04 | | 4 | 001 | 800 | 666.6667 | 2021-01-03 | | 2 | 001 | 300 | 533.3333 | 2021-01-02 | | 3 | 001 | 500 | 300.0000 | 2021-01-02 | | 1 | 001 | 100 | 300.0000 | 2021-01-01 | | 10 | 002 | 800 | 800.0000 | 2021-01-22 | | 9 | 002 | 800 | 633.3333 | 2021-01-16 | | 8 | 002 | 300 | 566.6667 | 2021-01-10 | | 7 | 002 | 600 | 466.6667 | 2021-01-04 | | 6 | 002 | 500 | 550.0000 | 2021-01-03 | +----------+---------+--------+----------+-------------+ 10 rows in set (0.02 sec)
order_id 为 5的订单数据边界值，没有前面的一行，因此平均金额为 “(900+800) / 2 = 850”；order_id 为4 的订单前后都有订单，所以订单平均金额为 “(900+800+300) / 3 = 666.6667”，以此类推就可以得到一个基于滑动窗口的动态订单平均值。
对于滑动窗口的范围指定，有如下两种方式。
基于行：通常使用
BETWEEN frame_start AND frame_end语法来表示行范围，frame_start 和 frame_end 可以支持如下关键字，来确定不同的动态行记录：CURRENT ROW边界行是当前行，一般和其他关键字一起使用。UNBOUNDED PRECEDING边界是分区中的第一行UNBOUNDED FOLLOWING边界是分区中的最后一行expr PRECEDING边界是当前行减去 expr 的值expr FOLLOWING边界是当前行加上 expr 的值比如下面都是合法的范围：
-- 窗口范围是 当前行、前 1 行、后 1 行，共 3 行记录
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
-- 窗口范围是当前行到分区中的最后一行
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
-- 窗口的范围是当前分区中的所有行,等同于不写 (书中这么说，但发现不完全是)
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
如果 不写范围 但写了排序 则会按照排序顺序进行合集计算：
mysql> SELECT * FROM ( SELECT order_id, user_no, amount, sum(amount) over ( PARTITION BY user_no) sum_num1, sum(amount) over w sum_num2, create_date FROM order_tab WINDOW w AS ( PARTITION BY user_no ORDER BY create_date DESC) ) t; +----------+---------+--------+----------+----------+-------------+ | order_id | user_no | amount | sum_num1 | sum_num2 | create_date | +----------+---------+--------+----------+----------+-------------+ | 5 | 001 | 900 | 2600 | 900 | 2021-01-04 | | 4 | 001 | 800 | 2600 | 1700 | 2021-01-03 | | 2 | 001 | 300 | 2600 | 2500 | 2021-01-02 | | 3 | 001 | 500 | 2600 | 2500 | 2021-01-02 | | 1 | 001 | 100 | 2600 | 2600 | 2021-01-01 | | 10 | 002 | 800 | 3000 | 800 | 2021-01-22 | | 9 | 002 | 800 | 3000 | 1600 | 2021-01-16 | | 8 | 002 | 300 | 3000 | 1900 | 2021-01-10 | | 7 | 002 | 600 | 3000 | 2500 | 2021-01-04 | | 6 | 002 | 500 | 3000 | 3000 | 2021-01-03 | +----------+---------+--------+----------+----------+-------------+ 10 rows in set (0.03 sec)可以看到
sum_num1是分区内所有数据的合集，而sum_num2是按照排序叠加的合集- 基于范围： 和基础行类似，但有些范围不是直接可以用行数来表示的，比如窗口范围是一周前的订单开始，截止到当前行，则无法使用 rows 来直接表示，此时可以使用范围来表示窗口：
INTERVAL 7 DAY PRECEDING。
- 基于范围： 和基础行类似，但有些范围不是直接可以用行数来表示的，比如窗口范围是一周前的订单开始，截止到当前行，则无法使用 rows 来直接表示，此时可以使用范围来表示窗口：
RANK / DENSE_RANK 函数
RANK() 和 DENSE_RANK() 函数与 ROW_NUMBER() 函数非常相似，只是在出现重复值时处理逻辑有所不同。
假设需要查询不同用户的订单，按照订单金额进行排序，SQL 语句中使用 row_number()、rank()、dense_rank() 分别显示序号，看一下有什么区别：
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 | amount | create_date |
+----------+----------+----------+----------+---------+--------+-------------+
| 1 | 1 | 1 | 5 | 001 | 900 | 2021-01-04 |
| 2 | 2 | 2 | 4 | 001 | 800 | 2021-01-03 |
| 3 | 3 | 3 | 3 | 001 | 500 | 2021-01-02 |
| 4 | 4 | 4 | 2 | 001 | 300 | 2021-01-02 |
| 5 | 5 | 5 | 1 | 001 | 100 | 2021-01-01 |
| 1 | 1 | 1 | 9 | 002 | 800 | 2021-01-16 |
| 2 | 1 | 1 | 10 | 002 | 800 | 2021-01-22 |
| 3 | 3 | 2 | 7 | 002 | 600 | 2021-01-04 |
| 4 | 4 | 3 | 6 | 002 | 500 | 2021-01-03 |
| 5 | 5 | 4 | 8 | 002 | 300 | 2021-01-10 |
+----------+----------+----------+----------+---------+--------+-------------+
10 rows in set (0.00 sec)
row_number()在金额都是 800 的两条记录上随机排序，但序号按照1、2递增，后面金额为 600 的记录 继续递增为 3，序号之间不会产生间隙。rank()将两条金额都是 800 的记录序号设置为 1，后续的金额 为 600 的记录则 设置为 3，即即生成序号相同的记录，同时可能会产生间隙。dense_rank()将两条金额都是 800 的记录序号设置为 1，后续的金额 为 600 的记录则 设置为 2， 即生成序号相同的记录，但不会产生间隙。
PERCENT_RANK / CUME_DIST 函数
PERCENT_RANK() 和 CUME_DIST() 这两个函数都是计算数据分布的函数。
PERCENT_RANK()
PERCENT_RANK() 函数和之前的 RANK() 函数相关，每行按照下面的公式计算：
-- rank 为 RANK() 函数产生的序号
-- rows 为 当前窗口的记录总行数
(rank - 1) / (rows - 1)
使用上面的例子 修改如下：
mysql> select * from
-> (
-> select
-> rank() over w row_num,
-> percent_rank() over w 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 | 2021-01-04 |
| 2 | 0.25 | 4 | 001 | 800 | 2021-01-03 |
| 3 | 0.5 | 3 | 001 | 500 | 2021-01-02 |
| 4 | 0.75 | 2 | 001 | 300 | 2021-01-02 |
| 5 | 1 | 1 | 001 | 100 | 2021-01-01 |
| 1 | 0 | 9 | 002 | 800 | 2021-01-16 |
| 1 | 0 | 10 | 002 | 800 | 2021-01-22 |
| 3 | 0.5 | 7 | 002 | 600 | 2021-01-04 |
| 4 | 0.75 | 6 | 002 | 500 | 2021-01-03 |
| 5 | 1 | 8 | 002 | 300 | 2021-01-10 |
+---------+---------+----------+---------+--------+-------------+
10 rows in set (0.10 sec)
可以看到 percent 列按照公式代入了rank 和 rows 的值（user_no 为 001 和 002 的值均为 5），此函数主要用于分析领域，日常使用不多。
CUME_DIST()
相比 percent_rank() ，cume_dist() 的应用场景更多，它的作用是：分组内小于 当前 rank 值的行数/分区内的总行数。
在下面的代码中，统计大于等于当前订单金额的订单数，站总订单的比例：
mysql> SELECT * FROM
-> (
-> SELECT
-> rank() over w row_num,
-> cume_dist() over w 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 | 2021-01-04 |
| 2 | 0.4 | 4 | 001 | 800 | 2021-01-03 |
| 3 | 0.6 | 3 | 001 | 500 | 2021-01-02 |
| 4 | 0.8 | 2 | 001 | 300 | 2021-01-02 |
| 5 | 1 | 1 | 001 | 100 | 2021-01-01 |
| 1 | 0.4 | 9 | 002 | 800 | 2021-01-16 |
| 1 | 0.4 | 10 | 002 | 800 | 2021-01-22 |
| 3 | 0.6 | 7 | 002 | 600 | 2021-01-04 |
| 4 | 0.8 | 6 | 002 | 500 | 2021-01-03 |
| 5 | 1 | 8 | 002 | 300 | 2021-01-10 |
+---------+------+----------+---------+--------+-------------+
10 rows in set (0.00 sec)
NEILE 函数
NEILE(N) 函数的功能是对数据分区中的有序结果集进行划分，将其分为N个组，每一组分配一个唯一的编号。
针对上面的例子，将数据分为3组：
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 | 2021-01-04 |
| 1 | 4 | 001 | 800 | 2021-01-03 |
| 2 | 3 | 001 | 500 | 2021-01-02 |
| 2 | 2 | 001 | 300 | 2021-01-02 |
| 3 | 1 | 001 | 100 | 2021-01-01 |
| 1 | 9 | 002 | 800 | 2021-01-16 |
| 1 | 10 | 002 | 800 | 2021-01-22 |
| 2 | 7 | 002 | 600 | 2021-01-04 |
| 2 | 6 | 002 | 500 | 2021-01-03 |
| 3 | 8 | 002 | 300 | 2021-01-10 |
+------+----------+---------+--------+-------------+
10 rows in set (0.00 sec)
此函数在数据分析中用的比较多，比如由于数据量大，需要将数据分配到N个进程中分别计算，此时就可以使用 NFILE(N) 对数据进行分组，由于记录的数据不一定被N整除，所以每组的记录数量不一定完全一致，然后将不同组号的数据再分配。
NTH_VALUE 函数
NTH_VALUE(expr,N) 函数可以返回第 N 个 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 | 2021-01-04 |
| 1 | NULL | 4 | 001 | 800 | 2021-01-03 |
| 2 | 3 | 3 | 001 | 500 | 2021-01-02 |
| 2 | 3 | 2 | 001 | 300 | 2021-01-02 |
| 3 | 3 | 1 | 001 | 100 | 2021-01-01 |
| 1 | NULL | 9 | 002 | 800 | 2021-01-16 |
| 1 | NULL | 10 | 002 | 800 | 2021-01-22 |
| 2 | 7 | 7 | 002 | 600 | 2021-01-04 |
| 2 | 7 | 6 | 002 | 500 | 2021-01-03 |
| 3 | 7 | 8 | 002 | 300 | 2021-01-10 |
+------+------+----------+---------+--------+-------------+
10 rows in set (0.11 sec)
nth 列返回分组排序后的窗口中的 order_id 的第三个值，”001” 用户返回 3，”002” 用户返回7，而 对于前N-1的列，本函NULL。
经测试 如 返回的列的 rank() 值与 当前分组其他 rank() 值 一样时（或者说处于同一个滑动窗口中），则同时修改前面一个的nth：
(order_id 为 9 和10 的数据 rank() 的值都是 1，所以 9 的 nth 也会返回 10
mysql> SELECT * FROM
(
SELECT
rank() over w num,
ntile(3) over w as nf,
nth_value(order_id,2) 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;
+-----+----+------+----------+---------+--------+-------------+
| num | nf | nth | order_id | user_no | amount | create_date |
+-----+----+------+----------+---------+--------+-------------+
| 1 | 1 | NULL | 5 | 001 | 900 | 2021-01-04 |
| 2 | 1 | 4 | 4 | 001 | 800 | 2021-01-03 |
| 3 | 2 | 4 | 3 | 001 | 500 | 2021-01-02 |
| 4 | 2 | 4 | 2 | 001 | 300 | 2021-01-02 |
| 5 | 3 | 4 | 1 | 001 | 100 | 2021-01-01 |
| 1 | 1 | 10 | 9 | 002 | 800 | 2021-01-16 |
| 1 | 1 | 10 | 10 | 002 | 800 | 2021-01-22 |
| 3 | 2 | 10 | 7 | 002 | 600 | 2021-01-04 |
| 4 | 2 | 10 | 6 | 002 | 500 | 2021-01-03 |
| 5 | 3 | 10 | 8 | 002 | 300 | 2021-01-10 |
+-----+----+------+----------+---------+--------+-------------+
10 rows in set (0.04 sec)
LAG / LEAD 函数
LAG(expr,N) 和 LEAD(expr,N) 这两个函数的功能是获取当前行按照某种排序规则的上 N 行（LAG） 和下 N 行（LEAD）数据的某个字段。例如需要获取本订单距离上一个订单相隔几天：
mysql> SELECT t.*,DATEDIFF(create_date,last_date) as diffday 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 | diffday |
+----------+---------+--------+-------------+------------+---------+
| 1 | 001 | 100 | 2021-01-01 | NULL | NULL |
| 2 | 001 | 300 | 2021-01-02 | 2021-01-01 | 1 |
| 3 | 001 | 500 | 2021-01-02 | 2021-01-02 | 0 |
| 4 | 001 | 800 | 2021-01-03 | 2021-01-02 | 1 |
| 5 | 001 | 900 | 2021-01-04 | 2021-01-03 | 1 |
| 6 | 002 | 500 | 2021-01-03 | NULL | NULL |
| 7 | 002 | 600 | 2021-01-04 | 2021-01-03 | 1 |
| 8 | 002 | 300 | 2021-01-10 | 2021-01-04 | 6 |
| 9 | 002 | 800 | 2021-01-16 | 2021-01-10 | 6 |
| 10 | 002 | 800 | 2021-01-22 | 2021-01-16 | 6 |
+----------+---------+--------+-------------+------------+---------+
10 rows in set (0.05 sec)
FIRST_VALUE / LAST_VALUE 函数
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 | 2021-01-01 | 100 | 100 |
| 2 | 001 | 300 | 2021-01-02 | 100 | 500 |
| 3 | 001 | 500 | 2021-01-02 | 100 | 500 |
| 4 | 001 | 800 | 2021-01-03 | 100 | 800 |
| 5 | 001 | 900 | 2021-01-04 | 100 | 900 |
| 6 | 002 | 500 | 2021-01-03 | 500 | 500 |
| 7 | 002 | 600 | 2021-01-04 | 500 | 600 |
| 8 | 002 | 300 | 2021-01-10 | 500 | 300 |
| 9 | 002 | 800 | 2021-01-16 | 500 | 800 |
| 10 | 002 | 800 | 2021-01-22 | 500 | 800 |
+----------+---------+--------+-------------+--------------+-------------+
10 rows in set (0.00 sec)
聚合函数作为窗口函数
除了前面说的窗口函数外，聚合函数也可以作为窗口函数来使用，比如要统计每个用户截止到当前订单的累计订单金额、平均订单金额、最大订单金额、订单数等，可以使用聚合函数作为窗口函数来使用：
mysql> SELECT * FROM
-> (
-> 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)
-> ) t;
+----------+---------+--------+-------------+------+----------+------+------+--------+
| order_id | user_no | amount | create_date | sum1 | avg1 | max1 | min1 | count1 |
+----------+---------+--------+-------------+------+----------+------+------+--------+
| 1 | 001 | 100 | 2021-01-01 | 100 | 100.0000 | 100 | 100 | 1 |
| 2 | 001 | 300 | 2021-01-02 | 900 | 300.0000 | 500 | 100 | 3 |
| 3 | 001 | 500 | 2021-01-02 | 900 | 300.0000 | 500 | 100 | 3 |
| 4 | 001 | 800 | 2021-01-03 | 1700 | 425.0000 | 800 | 100 | 4 |
| 5 | 001 | 900 | 2021-01-04 | 2600 | 520.0000 | 900 | 100 | 5 |
| 6 | 002 | 500 | 2021-01-03 | 500 | 500.0000 | 500 | 500 | 1 |
| 7 | 002 | 600 | 2021-01-04 | 1100 | 550.0000 | 600 | 500 | 2 |
| 8 | 002 | 300 | 2021-01-10 | 1400 | 466.6667 | 600 | 300 | 3 |
| 9 | 002 | 800 | 2021-01-16 | 2200 | 550.0000 | 800 | 300 | 4 |
| 10 | 002 | 800 | 2021-01-22 | 3000 | 600.0000 | 800 | 300 | 5 |
+----------+---------+--------+-------------+------+----------+------+------+--------+
10 rows in set (0.00 sec)
其他常用函数
MySQL 提供的函数很丰富，除了前面介绍的字符串函数、数字函数、日期函数、流程函数以外，还有很多其他的函数，可以参考MySQL官方手册，下面列举了一些其他常用函数：
| 函数 | 功能 |
|---|---|
DATABASE() |
返回当前数据库名 |
VERSION() |
返回当前数据库版本 |
USER() |
返回当前登录用户名 |
INET_ATON(IP) |
返回IP地址的数字表示 |
INET_NTOA(num) |
返回数字代表的IP地址 |
PASSWORD(str) |
返回字符串str的 加密版本 |
MD5(str) |
返回字符串str的MD5值 |
DATABASE 函数
返回当前数据库名
mysql> SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| test |
+------------+
1 row in set (0.00 sec)
VERSION 函数
返回当前数据库版本
mysql> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 5.7.30 |
+-----------+
1 row in set (0.00 sec)
USRE 函数
返回当前登录的用户名
mysql> SELECT USER();
+----------------+
| USER() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)
INET_ATON 函数
返回IP地址的网络字节序表示
mysql> SELECT INET_ATON('192.168.0.1');
+--------------------------+
| INET_ATON('192.168.0.1') |
+--------------------------+
| 3232235521 |
+--------------------------+
1 row in set (0.00 sec)
INET_NTOA 函数
返回网络字节序代表的IP地址
mysql> SELECT INET_NTOA(3232235521);
+-----------------------+
| INET_NTOA(3232235521) |
+-----------------------+
| 192.168.0.1 |
+-----------------------+
1 row in set (0.00 sec)
INET_ATON(IP) 和 INET_NTOA(num) 函数主要作用是将字符串的IP地址转换为数字表示的网络字节序，这样可以方便的进行IP或网段之间的比较，如果想要在下面的ip表中，知道 192.168.1.1 ~ 192.168.1.20 之间有多少IP地址，可以这么做：
mysql> SELECT * FROM ip;
+--------------+
| ip |
+--------------+
| 192.168.1.1 |
| 192.168.1.3 |
| 192.168.1.6 |
| 192.168.1.10 |
| 192.168.1.20 |
| 192.168.1.30 |
+--------------+
6 rows in set (0.00 sec)
按照正常思维，我们应该用字符串来比较：
mysql> SELECT * FROM ip WHERE ip >= '192.168.1.3' AND ip <= '192.168.1.20';
Empty set (0.00 sec)
结果是个空集。
其原因就在于字符串的比较是一个字符一个字符的比较，当字符串相同时，就比较下一个，直到遇到能真正能区分出大小的字符才停止比较，后面的字符也将忽略。显然，在此比例中，”192.168.1.3” 其实比 “192.168.1.20” 要 “大”，因为 3 比 2 大，而不能用我们日常的思维 3<20 ，所以是个空集。
这时就可以使用 INET_ATON 函数来实现，将IP地址转换为字节序后再比较：
mysql> SELECT * FROM ip WHERE INET_ATON(ip) >= INET_ATON('192.168.1.3') AND INET_ATON(ip) <= INET_ATON('192.168.1.20');
+--------------+
| ip |
+--------------+
| 192.168.1.3 |
| 192.168.1.6 |
| 192.168.1.10 |
| 192.168.1.20 |
+--------------+
4 rows in set (0.00 sec)
PASSWORD 函数
PASSWORD(str)
返回字符串str的加密版本，一个41位的字符串。
此函数只用来设置系统用户的密码，但是不能用来对应用的数据加密，如果应用方面有加密的需求，可以使用MD5等加密函数实现。
mysql> SELECT PASSWORD('123456');
+-------------------------------------------+
| PASSWORD('123456') |
+-------------------------------------------+
| *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+-------------------------------------------+
1 row in set, 1 warning (0.00 sec)
MD5 函数
MD5(str)
返回字符串str的MD5值，常用来对应用中的数据加密：
mysql> SELECT MD5('123456');
+----------------------------------+
| MD5('123456') |
+----------------------------------+
| e10adc3949ba59abbe56e057f20f883e |
+----------------------------------+
1 row in set (0.00 sec)
若有收获，就点个赞吧
0 人点赞
