常用函数
1、字符串函数
函数 | 说明 |
---|---|
CONCAT(S1,S2,…,Sn) | 连接参数字符串为一个字符串 |
INSERT(str,x,y,instr) | 将字符串 str 从第 x 位置开始,y 个字符长的子串替换为 instr |
LOWER(str) | 小写 |
UPPER(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 右侧的空格 |
REPEAT(str,x) | 返回 str 重复 x 次的结果 |
REPLACE(str,a,b) | 用字符串 b 替换字符串 str 中所有出现的字符串 a |
STRCMP(s1,s2) | 比较字符串 |
TRIM(str) | 去掉字符串行尾和行头的空格 |
SUBSTRING(str,x,y) | 返回从字符串 str x 位置起 y 个字符长度的子串 |
-
CONCAT(S1,S2,…,Sn) 函数:把传入的参数连接成为一个字符串
下面的例子把 “aaa”、“bbb”、“ccc” 3 个字符串连接成一个字符串 “aaabbbccc”。另外,任何字符串与 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) 函数:将字符串 str 从第 x 位置开始,y 个字符长的子串替换成字符串 instr
下面的例子把字符串 “beijing2008you” 中从第 12 个字符开始以后的 3 个字符替换成 “me”:
mysql> select insert('beijing2008you', 12, 3, 'me'); +---------------------------------------+ | insert('beijing2008you', 12, 3, 'me') | +---------------------------------------+ | beijing2008me | +---------------------------------------+ 1 row in set (0.00 sec)
-
LOWER(str) 和 UPPER(str) 函数:把字符串转换成小写或大写
在字符串比较中,通常要将比较的字符串全部转换为大写或小写:
mysql> select lower('BEIJING2008'), upper('beijing2008'); +----------------------+----------------------+ | lower('BEIJING2008') | upper('beijing2008') | +----------------------+----------------------+ | beijing2008 | BEIJING2008 | +----------------------+----------------------+ 1 row in set (0.00 sec)
-
LEFT(str,x) 和 RIGHT(str,x) 函数:分别返回字符串最左边的 x 个字符和最右边的 x 个字符。如果第二个参数是 NULL,那么将不返回任何字符串。
mysql> select left('beijing2008', 7), left('beijing2008', null), right('beijing2008', 4); +------------------------+---------------------------+-------------------------+ | left('beijing2008', 7) | left('beijing2008', null) | right('beijing2008', 4) | +------------------------+---------------------------+-------------------------+ | beijing | NULL | 2008 | +------------------------+---------------------------+-------------------------+ 1 row in set (0.00 sec)
-
LPAD(str,n,pad) 和 RPAD(str,n,pad) 函数:用字符串 pad 对 str 最左边和最右边进行填充,直到长度为 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(str) 和 RTRIM(str) 函数:去掉字符串 str 左侧和右侧的空格。
mysql> select ltrim(' |beijing'), rtrim('beijing| '); +----------------------+-----------------------+ | ltrim(' |beijing') | rtrim('beijing| ') | +----------------------+-----------------------+ | |beijing | beijing| | +----------------------+-----------------------+ 1 row in set (0.00 sec)
-
REPEAT(str,x) 函数:返回 str 重复 x 次的结果。
mysql> select repeat('mysql ', 3); +---------------------+ | repeat('mysql ', 3) | +---------------------+ | mysql mysql mysql | +---------------------+ 1 row in set (0.00 sec)
-
REPLACE(str,a,b) 函数:用字符串 b 替换字符串 str 中所有出现的字符串 a。
mysql> select replace('beijing_2010', '_2010', '2008'); +------------------------------------------+ | replace('beijing_2010', '_2010', '2008') | +------------------------------------------+ | beijing2008 | +------------------------------------------+ 1 row in set (0.00 sec)
-
STRCMP(s1,s2) 函数:比较字符串 s1 和 s2 的 ASCII 码值的大小。
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)
-
TRIM(str) 函数:去掉目标字符串的开头和结尾的空格。
mysql> select trim(' $ beijing 2008 $ '); +----------------------------------+ | trim(' $ beijing 2008 $ ') | +----------------------------------+ | $ beijing 2008 $ | +----------------------------------+ 1 row in set (0.00 sec)
-
SUBSTRING(str,x,y) 函数:返回从字符串 str 中的第 x 位置起 y 个字符长度的字符串。
mysql> select substring('beijing2008', 8, 4), substring('beijing2008', 1, 7); +--------------------------------+--------------------------------+ | substring('beijing2008', 8, 4) | substring('beijing2008', 1, 7) | +--------------------------------+--------------------------------+ | 2008 | beijing | +--------------------------------+--------------------------------+ 1 row in set (0.00 sec)
2、数值函数
函数 | 说明 |
---|---|
ABS(x) | 绝对值 |
CEIL(X) | 返回大于 x 的最小整数 |
FLOOR(x) | 返回小于 x 的最大整数 |
MOD(x,y) | 返回 x/y 的模 |
RAND() | 返回 0~1 的随机数 |
ROUND(x,y) | 返回 x 的四舍五入的有 y 位小数的值 |
TRUNCATE(x,y) | 返回数字 x 截断为 y 位小数的结果(不四舍五入) |
mysql> select round(1.1),round(1.1,2),round(1,2),round(1.1,3);
+------------+--------------+------------+--------------+
| round(1.1) | round(1.1,2) | round(1,2) | round(1.1,3) |
+------------+--------------+------------+--------------+
| 1 | 1.10 | 1 | 1.100 |
+------------+--------------+------------+--------------+
1 row in set (0.00 sec)
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)
3、日期和时间函数
函数 | 说明 |
---|---|
CURDATE() | 返回当前日期 |
CURTIME() | 返回当前时间 |
NOW() | 返回当前的日期和时间 |
UNIX_TIMESTAMP(date) | 返回日期 date 的 UNIX 时间戳 |
FROM_UNIXTIME | 返回 UNIX 时间戳的日期值 |
WEEK(date) | 返回日期 date 为一年中的第几周 |
YEAR(date) | 返回日期 date 的年份 |
HOUR(date) | 返回 time 的小时值 |
MINUTE(date) | 返回 time 的分钟值 |
MONTHNAME(date) | 返回 date 的月份名 |
DATE_FORMAT(date,fmt) | 返回按字符串 fmt 格式化日期 date 值 |
DATE_ADD(date,INTERVAL,expr type) | 返回上一个日期或时间值加上一个时间间隔的时间值 |
DATEDIFF(expr,expr2) | 返回起始时间 expr 和结束时间 expr2 之间的天数 |
-
UNIX_TIMESTAMP(date) 函数:返回日期 date 的 UNIX 时间戳。
mysql> select UNIX_TIMESTAMP(now()); +-----------------------+ | UNIX_TIMESTAMP(now()) | +-----------------------+ | 1615328046 | +-----------------------+ 1 row in set (0.00 sec)
-
FROM_UNIXTIME(unixtime) 函数:返回 UNIXTIME 时间戳的日期值,和 UNIX_TIMESTAMP(date) 函数互为逆操作。
mysql> select FROM_UNIXTIME(1615328046); +---------------------------+ | FROM_UNIXTIME(1615328046) | +---------------------------+ | 2021-03-10 06:14:06 | +---------------------------+ 1 row in set (0.00 sec)
-
WEEK(date) 和 YEAR(date) 函数:前者返回所给的日期是一年中的第几周,后者返回所给的日期是哪一年。
mysql> select WEEK(now()), YEAR(now()); +-------------+-------------+ | WEEK(now()) | YEAR(now()) | +-------------+-------------+ | 10 | 2021 | +-------------+-------------+ 1 row in set (0.00 sec)
-
MONTHNAME(date) 函数:返回 date 的英文月份名称。
mysql> select MONTHNAME(now()); +------------------+ | MONTHNAME(now()) | +------------------+ | March | +------------------+ 1 row in set (0.00 sec)
-
DATE_FORMAT(date,fmt) 函数
按照 fmt 格式化日期 date 的值,可用的格式符如下:
格式符 说明 %S 或 %s 两位数字形式的秒 %i 两位数字形式的分 %H 两位数字形式的小时,24 小时 %h 和 %I(大写 i) 两位数字形式的小时,12 小时 %k 数字形式的小时,24 小时 %l(小写 l) 数字形式的小时,12 小时 %T 24 小时的时间形式 %r 12 小时的时间形式 %p AM 或 PM %W 一周中每一天的名称 %a 一周中每一天名称的缩写 %d 两位数字表示月中的天数 %e 数字形式表示月中的天数 %D 英文后缀表示月中的天数 %w 以数字形式表示周中的天数 %j 3 位数字表示年中的天数 %U 周,其中 Sunday 为周中第一天 %u 周,其中 Monday 为周中第一天 %M 月名 %b 缩写的月名 %m 两位数字表示的月份 %c 数字表示的月份 %Y 4 位数字表示的年份 %y 两位数字表示的年份 %% 直接值 “%” mysql> select now(),DATE_FORMAT(now(),'%M,%D,%Y'),DATE_FORMAT(now(),'%M,%D,%Y,%H,%i,%s'); +---------------------+-------------------------------+----------------------------------------+ | now() | DATE_FORMAT(now(),'%M,%D,%Y') | DATE_FORMAT(now(),'%M,%D,%Y,%H,%i,%s') | +---------------------+-------------------------------+----------------------------------------+ | 2020-08-12 16:41:35 | August,12th,2020 | August,12th,2020,16,41,35 | +---------------------+-------------------------------+----------------------------------------+ 1 row in set (0.00 sec)
-
DATE_ADD(date,INTERVAL,expr type) 函数
其中 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:mm: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-08-12 16:52:32 | 2020-09-12 16:52:32 | 2021-10-12 16:52:32 | +---------------------+---------------------+------------------------+ 1 row in set (0.00 sec)
同样可以使用负数,返回之前的时间:
mysql> select now() current,date_add(now(),INTERVAL -31 day) after31days,date_add(now(),INTERVAL '-1_-2' year_month) after_oneyear_twomonth; +---------------------+---------------------+------------------------+ | current | after31days | after_oneyear_twomonth | +---------------------+---------------------+------------------------+ | 2020-08-12 16:53:38 | 2020-07-12 16:53:38 | 2019-06-12 16:53:38 | +---------------------+---------------------+------------------------+ 1 row in set (0.00 sec)
-
DATEDIFF(expr,expr2) 函数
计算两个日期之间相差的天数:
mysql> select DATEDIFF('2008-08-08',now()); +------------------------------+ | DATEDIFF('2008-08-08',now()) | +------------------------------+ | -4387 | +------------------------------+ 1 row in set (0.00 sec)
4、流程函数
函数 | 说明 |
---|---|
IF(value,t f) | 如果 value 为真,返回 t;否则返回 f |
IFNULL(value1,value2) | 如果 value1 不为空,返回 value1,否则返回 value2 |
CASE [expr] WHEN [value1] THEN [result1]…ELSE [default] END | 如果 value1 是真,返回 result1,否则返回 default |
CASE [expr] WHEN [value1] THEN [result1]… ELSE [default] END | 如果 expr 等于 value1,返回 result1,否则返回 default |
下面的例子模拟了对职员薪水进行分类,这里首先创建并初始化一个职员薪水表:
mysql> create table salary(userid int, salary decimal(9, 2));
Query OK, 0 rows affected (0.12 sec)
插入一些数据:
mysql> insert into salary values(1, 1000), (2, 2000), (3,3000), (4,4000), (5,5000), (1,null);
Query OK, 6 rows affected (0.00 sec)
Records: 6 Duplicates: 0 Warnings: 0
接下来,通过这个表来介绍各个函数的应用。
-
IF(value,t f) 函数:这里认为月薪在 2000 元以上的职员属于高薪,用 “high” 表示;否则,用 “low” 表示:
mysql> select userid, salary, if(salary>2000, 'high', 'low') as salary_level from salary; +--------+---------+--------------+ | userid | salary | salary_level | +--------+---------+--------------+ | 1 | 1000.00 | low | | 2 | 2000.00 | low | | 3 | 3000.00 | high | | 4 | 4000.00 | high | | 5 | 5000.00 | high | | 1 | NULL | low | +--------+---------+--------------+ 6 rows in set (0.00 sec)
-
IFNULL(value1,value2) 函数:这个函数一般用来替换 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.01 sec)
-
CASE [expr] WHEN [value1] THEN [result1]…ELSE [default] END 函数:这个是 case 的简单函数用法,case 后面跟列名或者列的表达式,when 后边枚举这个表达式所有可能的值,不能是值的范围。如果要实现上面例子中高薪低薪的问题,写法如下:
mysql> select userid, salary, case salary when 1000 then 'low' when 2000 then 'low' else 'high' end salary_level from salary; +--------+---------+--------------+ | userid | salary | salary_level | +--------+---------+--------------+ | 1 | 1000.00 | low | | 2 | 2000.00 | low | | 3 | 3000.00 | high | | 4 | 4000.00 | high | | 5 | 5000.00 | high | | 1 | NULL | high | +--------+---------+--------------+ 6 rows in set (0.00 sec)
-
CASE [expr] WHEN [value1] THEN [result1]… ELSE [default] END 函数:这是 case 的搜索函数用法,直接在 when 后面写条件表达式,并且只返回第一个符合条件的值,使用起来更加灵活,上例可以修改如下:
mysql> select userid, salary, case when salary<=2000 then 'low' else 'high' end as salary_level from salary; +--------+---------+--------------+ | userid | salary | salary_level | +--------+---------+--------------+ | 1 | 1000.00 | low | | 2 | 2000.00 | low | | 3 | 3000.00 | high | | 4 | 4000.00 | high | | 5 | 5000.00 | high | | 1 | NULL | high | +--------+---------+--------------+ 6 rows in set (0.00 sec)
5、JSON 函数
函数类型 | 名称 | 功能 |
---|---|---|
创建 JSON | JSON_ARRAY([val[, val] …]) | 创建 JSON 数组 |
JSON_OBJECT([key, val[,key, val] …]) | 创建 JSON 对象 | |
JSON_QUOTE(string)/JSON_UNQUOTE(string) | 加上/去掉 JSON 文档两边的双引号 | |
查询 JSON | JSON_CONTAINS(target, candidate[, path]) | 查询文档中是否包含指定的元素 |
JSON_CONTAINS_PATH(json_doc, one_or_all, path[, path] …) | 查询文档中是否包含指定的路径 | |
JSON_EXTRACT(json_doc, path[, path] …)/->/->> | 根据条件提取文档中的数据 | |
JSON_KEYS(json_doc[, path]) | 提取所有 key 的集合 | |
JSON_SEARCH(json_doc, one_or_all, search_str[, escape_char[, path] …]) | 返回所有符合条件的路径集合 | |
修改 JSON | JSON_MERGE()/JSON_MERGE_PRESERVE(json_doc, json_doc[, json_doc] …) | 将两个文档合并 |
JSON_ARRAY_APPEND(json_doc, path, val[, path, val] …) | 数组尾部追加元素 | |
SELECT JSON_ARRAY_INSERT(json_doc, path, val[, path, val] …); | 在数组的指定位置插入元素 | |
JSON_REMOVE(json_doc, path[, path] …) | 删除文档中指定位置的元素 | |
JSON_REPLACE(json_doc, path, val[, path, val] …) | 替换文档中指定位置的元素 | |
JSON_SET(json_doc, path, val[, path, val] …) | 给文档中指定位置的元素设置新值,如果元素不存在,则进行插入 | |
查询 JSON 元素数据 | JSON_DEPTH(json_doc) | JSON 文档的深度(元素最大嵌套层数) |
JSON_LENGTH(json_doc[, path]) | JSON 文档的长度(元素个数) | |
JSON_TYPE(json_val) | JSON 文档类型(数组、对象、标量类型) | |
JSON_VALID(val) | JSON 格式是否合法 | |
其他函数 | JSON_PRETTY(json_val) | 美化 JSON 格式 |
JSON_STORAGE_SIZE(json_val) | JSON 文档占用的存储空间 | |
JSON_STORAGE_FREE(json_val) | JSON 文档更新操作后剩余的空间 | |
JSON_TABLE() | 将 JSON 文档转换为表格 | |
JSON_ARRAYAGG() | 将聚合后参数中的多个值转换为 JSON 数组 | |
JSON_OBJECTAGG() | 把两个列或者是表达式解释为一个 key 和一个 value,返回一个 JSON 对象 |
5.1、创建 JSON 函数
-
JSON_ARRAY([val[, val] …])
创建一个包含数字、字符串、null、布尔、日期类型在内的混合数组,需要注意的是,参数中的 null 和 true/false 大小写不敏感:
mysql> select JSON_ARRAY(1, 'abc', NULL, TRUE, curtime()); +---------------------------------------------+ | JSON_ARRAY(1, 'abc', NULL, TRUE, curtime()) | +---------------------------------------------+ | [1, "abc", null, true, "07:14:30.000000"] | +---------------------------------------------+ 1 row in set (0.10 sec)
-
JSON_OBJECT([key, val[,key, val] …])
此函数可以返回参数中所有键值对的对象列表。参数中的 key 不能为 null,参数个数也不能为奇数,否则报语法错误:
mysql> select json_object('id', 100, 'name', 'jack'); +----------------------------------------+ | json_object('id', 100, 'name', 'jack') | +----------------------------------------+ | {"id": 100, "name": "jack"} | +----------------------------------------+ 1 row in set (0.00 sec) mysql> select json_object('id', 100, 'name'); # 参数个数为奇数 ERROR 1582 (42000): Incorrect parameter count in the call to native function 'json_object'
-
JSON_QUOTE(string)
此函数可以将参数中的 JSON 文档转换为双引号引起来的字符串,如果 JSON 文档包含双引号,则转换后的字符串自动加上转义字符 “\”:
mysql> select json_quote('[1, 2, 3]'), json_quote('"null"'); +-------------------------+----------------------+ | json_quote('[1, 2, 3]') | json_quote('"null"') | +-------------------------+----------------------+ | "[1, 2, 3]" | "\"null\"" | +-------------------------+----------------------+ 1 row in set (0.00 sec)
5.2、查询 JSON 函数
-
JSON_CONTAINS(target, candidate[, path])
此函数可以查询指定的元素(candidate)是否包含在目标 JSON 文档(target)中,包含则返回 1,否则返回 0,path 参数可选。如果有参数为 NULL 或 path 不存在,则返回 NULL。
mysql> select json_contains('[1, 2, 3, "abc", null]', '"abc"'); +--------------------------------------------------+ | json_contains('[1, 2, 3, "abc", null]', '"abc"') | +--------------------------------------------------+ | 1 | +--------------------------------------------------+ 1 row in set (0.01 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 这种格式。$.a 好理解,表示 key 为 a; $.a.b 通常用在 value 也是对象列表的情况,表示键 a 下层的键 b,比如 {“id”:{“id1”:1, “id2”:2}}。如果 JSON 文档为数组,则路径通常写为 $[i] 这种格式,表示数组中第 i 个元素。
在下例中,要查询 JSON 文档 j 中是否包含 value 为 10 的对象,并指定路径为 $.jack(key=‘jack’),如果包含则返回 1,如果不包含则返回 0。那么 SQL 代码可以这样写:
mysql> SET @j = '{"jack":10, "tom":20, "lisa":30}'; Query OK, 0 rows affected (0.00 sec) mysql> set @j2 = '10'; Query OK, 0 rows affected (0.00 sec) mysql> select json_contains(@j, @j2, '$.jack'); +----------------------------------+ | json_contains(@j, @j2, '$.jack') | +----------------------------------+ | 1 | +----------------------------------+ 1 row in set (0.00 sec)
返回 1,表示在路径 key=‘jack’ 下,存在 value 为 10 的值。将查询路径改为 tom 后,再次查询:
mysql> select json_contains(@j, @j2, '$.tom'); +---------------------------------+ | json_contains(@j, @j2, '$.tom') | +---------------------------------+ | 0 | +---------------------------------+ 1 row in set (0.00 sec)
-
JSON_CONTAINS_PATH(json_doc, one_or_all, path[, path] …)
此函数可以查询 JSON 文档中是否存在指定路径,存在则返回 1,否则则返回 0。one_or_all 只能取 one 或者 all,one 表示只要有一个存在即可;all 表示所有的都存在才行。如果有参数为 NULL 或 path 不存在,则返回 NULL。
比如,要查询给定的 3 个 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_doc, path[, path] …)
此函数可以从 JSON 文档中抽取数据。如果有参数有 NULL 或 path 不存在,则返回 NULL。如果抽取出多个 path,则返回的数据合并在一个 JSON_ARRAY 里:
mysql> select json_extract('[10, 20, [30, 40]]', '$[0]', '$[1]'); +----------------------------------------------------+ | json_extract('[10, 20, [30, 40]]', '$[0]', '$[1]') | +----------------------------------------------------+ | [10, 20] | +----------------------------------------------------+ 1 row in set (0.00 sec)
可以看到,返回的两个值以数组的形式进行了合并。如果要取第三个数组值,path 可以写为 $2 或者 $[2][*]:
mysql> select json_extract('[10, 20, [30, 40]]', '$[2]'); +--------------------------------------------+ | json_extract('[10, 20, [30, 40]]', '$[2]') | +--------------------------------------------+ | [30, 40] | +--------------------------------------------+ 1 row in set (0.00 sec) mysql> select json_extract('[10, 20, [30, 40]]', '$[2][*]'); +-----------------------------------------------+ | json_extract('[10, 20, [30, 40]]', '$[2][*]') | +-----------------------------------------------+ | [30, 40] | +-----------------------------------------------+ 1 row in set (0.00 sec)
一种更简单的函数 “->” 来替代 JSON_EXTRACT,语法如下:
column -> path
注意左边只能是列名,不能是表达式;右边是要匹配的 JSON 路径。
mysql> select id1, id1->"$[0]", id1->"$[1]" from t5 where id1->"$[0]"=10; +--------------------+-------------+-------------+ | id1 | id1->"$[0]" | id1->"$[1]" | +--------------------+-------------+-------------+ | [10, 20, [30, 40]] | 10 | 20 | +--------------------+-------------+-------------+ 1 row in set (0.00 sec)
如果 JSON 文档查询的结果是字符串,则显示结果默认会包含双引号,在很多情况下是不需要的,为了解决这个问题,MySQL 提供了另外两个 JSON_UNQUOTE 和 “->>”,用法类似于 JSON_EXTRACT 和 “->”:
mysql> insert into t5 values('{"k1":"jack", "k2":"tom", "k3":"lisa"}'); Query OK, 1 row affected (0.00 sec) mysql> select json_extract(id1, '$.k1'), json_unquote(id1->'$.k1'), id1->"$.k1", id1->>"$.k1" from t5 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_doc[, path])
此函数可以获取 JSON 文档在指定路径下的所有键值,返回一个 JSON ARRAY。如果有参数为 NULL 或 path 不存在,则返回 NULL。
mysql> SELECT JSON_KEYS('{"a":1, "b":{"c":30}}'); +------------------------------------+ | JSON_KEYS('{"a":1, "b":{"c":30}}') | +------------------------------------+ | ["a", "b"] | +------------------------------------+ 1 row in set (0.00 sec) mysql> SELECT JSON_KEYS('{"a":1, "b":{"c":30}}', '$.b'); +-------------------------------------------+ | JSON_KEYS('{"a":1, "b":{"c":30}}', '$.b') | +-------------------------------------------+ | ["c"] | +-------------------------------------------+ 1 row in set (0.00 sec)
-
JSON_SEARCH(json_doc, one_or_all, search_str[, escape_char[, path] …])
此函数可以查询包含指定字符串的路径,并作为一个 JSON ARRAY 返回。如果有参数为 NULL 或 path 不存在,则返回 NULL。各参数含义如下:
- one_or_all:one 表示查询到一个即返回;all 表示查询所有;
- search_str:要查询的字符串,可以用 LIKE 里的 ‘%’ 或 ‘__’ 匹配;
- path:表示在指定 path 下进行查询。
以下示例给出了如何查询 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)
可以看到,满足条件的第一个元素是 “k2”:”tom”,path 描述为 “$.k2”。
下面把条件 “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)
5.3、修改 JSON 的函数
-
JSON_ARRAY_APPEND(json_doc, path, val[, path, val] …)
此函数可以在指定 path 的 json array 尾部追加 val。如果指定 path 是一个 json object,则将其封装成一个 json array 再追加。如果参数为 NULL,则返回 NULL。
以下示例在 JSON 文档中的不同 path 处分别追加字符 “1”:
mysql> SELECT JSON_ARRAY_APPEND('["a", ["b", "c"], "d"]', '$[0]', "1"); +----------------------------------------------------------+ | JSON_ARRAY_APPEND('["a", ["b", "c"], "d"]', '$[0]', "1") | +----------------------------------------------------------+ | [["a", "1"], ["b", "c"], "d"] | +----------------------------------------------------------+ 1 row in set (0.00 sec) mysql> SELECT JSON_ARRAY_APPEND('["a", ["b", "c"], "d"]', '$[1]', "1"); +----------------------------------------------------------+ | JSON_ARRAY_APPEND('["a", ["b", "c"], "d"]', '$[1]', "1") | +----------------------------------------------------------+ | ["a", ["b", "c", "1"], "d"] | +----------------------------------------------------------+ 1 row in set (0.00 sec) mysql> SELECT JSON_ARRAY_APPEND('["a", ["b", "c"], "d"]', '$[1][0]', "1"); +-------------------------------------------------------------+ | JSON_ARRAY_APPEND('["a", ["b", "c"], "d"]', '$[1][0]', "1") | +-------------------------------------------------------------+ | ["a", [["b", "1"], "c"], "d"] | +-------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> SELECT JSON_ARRAY_APPEND('{"a":1, "b":[2, 3], "c":4}', '$.b', "1"); +-------------------------------------------------------------+ | JSON_ARRAY_APPEND('{"a":1, "b":[2, 3], "c":4}', '$.b', "1") | +-------------------------------------------------------------+ | {"a": 1, "b": [2, 3, "1"], "c": 4} | +-------------------------------------------------------------+ 1 row in set (0.00 sec)
-
SELECT JSON_ARRAY_INSERT(json_doc, path, val[, path, val] …)
此函数可以在 path 指定的 json array 元素插入 val,原位置以右的元素顺次右移。如果 path 指定的数据非 json array 元素,则略过此 val;如果指定的元素下标超过了 json array 的长度,则插入尾部。
mysql> SELECT JSON_ARRAY_INSERT('["a", ["b", "c"], "d"]', '$[0]', "1"); +----------------------------------------------------------+ | JSON_ARRAY_INSERT('["a", ["b", "c"], "d"]', '$[0]', "1") | +----------------------------------------------------------+ | ["1", "a", ["b", "c"], "d"] | +----------------------------------------------------------+ 1 row in set (0.00 sec) mysql> SELECT JSON_ARRAY_INSERT('["a", ["b", "c"], "d"]', '$[1]', "1"); +----------------------------------------------------------+ | JSON_ARRAY_INSERT('["a", ["b", "c"], "d"]', '$[1]', "1") | +----------------------------------------------------------+ | ["a", "1", ["b", "c"], "d"] | +----------------------------------------------------------+ 1 row in set (0.00 sec) mysql> SELECT JSON_ARRAY_INSERT('["a", ["b", "c"], "d"]', '$[1][0]', "1"); +-------------------------------------------------------------+ | JSON_ARRAY_INSERT('["a", ["b", "c"], "d"]', '$[1][0]', "1") | +-------------------------------------------------------------+ | ["a", ["1", "b", "c"], "d"] | +-------------------------------------------------------------+ 1 row in set (0.00 sec) 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 报错,提示路径不对,将 “$.b” 改为 “$[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.01 sec)
插入路径正确,但字符并没有插入到 JSON 文档中,因为所有元素都是对象,跳过忽略。
-
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 文档中 key 为 a 和 d 的对象的 value 分别替换为 “10” 和 “20”:
mysql> SELECT JSON_REPLACE('{"a":1, "b":[2, 3], "c":4}', "$.a", "10", '$.b', "20"); +----------------------------------------------------------------------+ | JSON_REPLACE('{"a":1, "b":[2, 3], "c":4}', "$.a", "10", '$.b', "20") | +----------------------------------------------------------------------+ | {"a": "10", "b": "20", "c": 4} | +----------------------------------------------------------------------+ 1 row in set (0.00 sec)
-
JSON_SET(json_doc, path, val[, path, val] …)
此函数可以设置指定路径的数据(不管是否存在)。如果有参数为 NULL,则返回 NULL。和 JSON_REPLACE 功能有些类似,最主要的区别是指定的路径不存在时,会在文档中自动添加:
mysql> SELECT JSON_SET('{"a":1, "b":[2, 3], "c":4}', '$.a', "10", '$.d', "20"); +------------------------------------------------------------------+ | JSON_SET('{"a":1, "b":[2, 3], "c":4}', '$.a', "10", '$.d', "20") | +------------------------------------------------------------------+ | {"a": "10", "b": [2, 3], "c": 4, "d": "20"} | +------------------------------------------------------------------+ 1 row in set (0.00 sec)
-
JSON_MERGE_PRESERVE(json_doc, json_doc[, json_doc] …)
此函数可以将多个 JSON 文档进行合并。合并规则如下:
- 如果都是 json array,则结果自动 merge 为一个 json array;
- 如果都是 json object,则结果自动 merge 为一个 json object;
- 如果有多种类型,则将非 json array 的元素封装成 json array 再按照规则一进行 merge。
下面的例子分别将两个数组合并、两个对象合并、数组和对象合并:
mysql> SELECT JSON_MERGE_PRESERVE('[1, 2]', '[3, 4]'); +-----------------------------------------+ | JSON_MERGE_PRESERVE('[1, 2]', '[3, 4]') | +-----------------------------------------+ | [1, 2, 3, 4] | +-----------------------------------------+ 1 row in set (0.00 sec) mysql> SELECT JSON_MERGE_PRESERVE('{"key1":"tom"}', '{"key2":"lisa"}'); +----------------------------------------------------------+ | JSON_MERGE_PRESERVE('{"key1":"tom"}', '{"key2":"lisa"}') | +----------------------------------------------------------+ | {"key1": "tom", "key2": "lisa"} | +----------------------------------------------------------+ 1 row in set (0.00 sec) mysql> SELECT JSON_MERGE_PRESERVE('[1, 2]', '{"key2":"lisa"}'); +--------------------------------------------------+ | JSON_MERGE_PRESERVE('[1, 2]', '{"key2":"lisa"}') | +--------------------------------------------------+ | [1, 2, {"key2": "lisa"}] | +--------------------------------------------------+ 1 row in set (0.00 sec)
-
JSON_REMOVE(json_doc, path[, path] …)
此函数可以移除指定路径的数据,如果某个路径不存在则略过此路径。如果有参数为 NULL,则返回 NULL。
mysql> SELECT JSON_REMOVE('[1, 2, 3, 4]', '$[1]', '$[2]'); +---------------------------------------------+ | JSON_REMOVE('[1, 2, 3, 4]', '$[1]', '$[2]') | +---------------------------------------------+ | [1, 3] | +---------------------------------------------+ 1 row in set (0.00 sec)
结果有些意外,’$[1]’,’$[2]’ 分别是 2 和 3,删除后不是应该是 [1, 4] 吗?这里需要注意,如果指定了多个 path,则删除操作是串行操作的,即先删除 ‘$[1]’ 后 JSON 文档变为 [1,3,4],然后在 [1,3,4] 上删除 ‘$[2]’ 后变成 [1,3]。
5.4、查询 JSON 原数据函数
-
JSON_DEPTH(json_doc)
此函数用来获取 JSON 文档的深度。
如果文档是空数组、空对象、null、true/false,则深度为 1;如果非空数组或者非空对象里面包含的都是深度为 1 的对象,则整个文档深度为 2;依次类推,整个文档的深度取决于最大元素的深度。
mysql> SELECT JSON_DEPTH('{}'), JSON_DEPTH('[]'), JSON_DEPTH('true'); +------------------+------------------+--------------------+ | JSON_DEPTH('{}') | JSON_DEPTH('[]') | JSON_DEPTH('true') | +------------------+------------------+--------------------+ | 1 | 1 | 1 | +------------------+------------------+--------------------+ 1 row in set (0.00 sec) mysql> SELECT JSON_DEPTH('[10, 20]'), JSON_DEPTH('[[], {}]'); +------------------------+------------------------+ | JSON_DEPTH('[10, 20]') | JSON_DEPTH('[[], {}]') | +------------------------+------------------------+ | 2 | 2 | +------------------------+------------------------+ 1 row in set (0.00 sec) mysql> SELECT JSON_DEPTH('[10, {"a":20}]'); +------------------------------+ | JSON_DEPTH('[10, {"a":20}]') | +------------------------------+ | 3 | +------------------------------+ 1 row in set (0.00 sec)
-
JSON_LENGTH(json_doc[, path])
此函数用来获取指定路径下的文档长度。长度计算规则如下:
- 标量(字符串、数字)的长度为 1;
- json array 的长度为元素的个数;
- json object 的长度为元素的个数;
- 嵌套数组或者嵌套对象不计算长度。
mysql> SELECT JSON_LENGTH('1'), JSON_LENGTH('[1, 2, [3, 4]]'), JSON_LENGTH('{"KEY":"TOM"}'); +------------------+-------------------------------+------------------------------+ | JSON_LENGTH('1') | JSON_LENGTH('[1, 2, [3, 4]]') | JSON_LENGTH('{"KEY":"TOM"}') | +------------------+-------------------------------+------------------------------+ | 1 | 3 | 1 | +------------------+-------------------------------+------------------------------+ 1 row in set (0.00 sec)
-
JSON_TYPE(json_val)
此函数可以获取 JSON 文档的具体类型,可以是数组、对象或者标量类型。
mysql> SELECT JSON_TYPE('[1, 3]'), JSON_TYPE('{"id":"tom"}'); +---------------------+---------------------------+ | JSON_TYPE('[1, 3]') | JSON_TYPE('{"id":"tom"}') | +---------------------+---------------------------+ | ARRAY | OBJECT | +---------------------+---------------------------+ 1 row in set (0.00 sec) mysql> SELECT JSON_TYPE('1'), JSON_TYPE('"abc"'), JSON_TYPE('null'), JSON_TYPE('true'); +----------------+--------------------+-------------------+-------------------+ | JSON_TYPE('1') | JSON_TYPE('"abc"') | JSON_TYPE('null') | JSON_TYPE('true') | +----------------+--------------------+-------------------+-------------------+ | INTEGER | STRING | NULL | BOOLEAN | +----------------+--------------------+-------------------+-------------------+ 1 row in set (0.00 sec)
-
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)
5.5、JSON 工具函数
-
JSON_PRETTY(json_val)
此函数用来美化 JSON 的输出格式,使得结果更加易读。
mysql> SELECT JSON_PRETTY('{"a":"10", "b":"15", "x":{"x1":1, "x2":2, "x3":3}}'); +----------------------------------------------------------------------------------+ | JSON_PRETTY('{"a":"10", "b":"15", "x":{"x1":1, "x2":2, "x3":3}}') | +----------------------------------------------------------------------------------+ | { "a": "10", "b": "15", "x": { "x1": 1, "x2": 2, "x3": 3 } } | +----------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
-
JSON_STORAGE_SIZE(json_val)
JSON_STORAGE_SIZE(json_val) 函数可以获取 JSON 文档占用的存储空间(byte)。
6、其他常用函数
函数 | 说明 |
---|---|
DATABASE() | 返回当前数据库名 |
VERSION() | 返回当前数据库版本 |
USER() | 返回当前登陆者名字 |
INET_ATON(IP) | 返回 IP 地址的数字表示 |
INET_NTOA(num) | 返回数字代表的 IP 地址 |
PASSWORD(str) | 返回字符串 str 的加密密码 |
MD5() | 返回字符串 str 的 MD5 值 |
-
DATABASE() 函数:返回当前数据库名。
mysql> SELECT DATABASE(); +------------+ | DATABASE() | +------------+ | test1 | +------------+ 1 row in set (0.00 sec)
-
VERSION() 函数:返回当前数据库版本。
mysql> SELECT VERSION(); +-----------+ | VERSION() | +-----------+ | 5.7.32 | +-----------+ 1 row in set (0.00 sec)
-
USER() 函数:返回当前登录用户名。
mysql> SELECT USER(); +----------------+ | USER() | +----------------+ | root@localhost | +----------------+ 1 row in set (0.00 sec)
-
INET_ATON(IP) 函数:返回当前 IP 地址的网络字节序表示。
mysql> SELECT INET_ATON('192.168.1.1'); +--------------------------+ | INET_ATON('192.168.1.1') | +--------------------------+ | 3232235777 | +--------------------------+ 1 row in set (0.00 sec)
-
INET_NTOA(num) 函数:返回网络字节序代表的 IP 地址。
mysql> SELECT INET_NTOA(3232235777); +-----------------------+ | INET_NTOA(3232235777) | +-----------------------+ | 192.168.1.1 | +-----------------------+ 1 row in set (0.00 sec)
-
PASSWORD(str) 函数:返回字符串 str 的加密版本,一个 41 位长的字符串。
此函数只用来设置系统用户的密码,但是不能用来对应用的数据加密。如果应用方面有加密的需求,可以使用 MD5 等加密函数来实现。
mysql> SELECT PASSWORD('123456@DFfdf'); +-------------------------------------------+ | PASSWORD('123456@DFfdf') | +-------------------------------------------+ | *88DA4CE42036AD467369487F1997D7A32EF2CCAB | +-------------------------------------------+ 1 row in set, 1 warning (0.00 sec)
-
MD5() 函数:返回字符串 str 的 MD5 值,常用来对应用中的数据进行加密。
mysql> SELECT MD5('123456@DFfdf'); +----------------------------------+ | MD5('123456@DFfdf') | +----------------------------------+ | fde70abedb357c1a1e8044c44c20e8de | +----------------------------------+ 1 row in set (0.00 sec)