MENU

MySQL 常用函数

December 1, 2020 • Read: 3222 • 编码,MySQL

经常写代码的应该都知道函数的重要性，丰富的函数往往能使用户的工作事半功倍。函数可以帮助用户做很多事情，比如字符串的处理、数值的运算、日期的运算等，在这方面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)

数值函数

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
%T24小时的时间格式，hh:mm:ss
%r12小时的时间格式，hh:mm:ssAM 或hh:mm:ssPM
%pAM 或 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_all one 表示查询到一个即返回，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)

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

  • expr JSON对象数组
  • 过滤路径 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()分区当前存储的行号

目前开发环境都到不了8.0 用不上，暂时跳过，(2020-12-01)。

其他常用函数

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)

笔记来自：《深入浅出MySQL：数据库开发，优化与管理维护（第三版）》

MySQL数据库阅读
Last Modified: January 15, 2021
Leave a Comment