前言

最近在做项目时,表 A 有多个字段,其中一个字段 info 把当前项目用不到的冗余的数据按照 JSON 格式都存了进来。随着项目的推进,有些冗余字段需要单独成一列。新增一列之后,需要把 info 中对应的数据刷入新增列,这就需要从 MySQL 中读取 JSON 数据。

当时想到的方法,就是写个程序,批量查询数据,把 info 字段查询出来后反序列化,取出其中的key-value,然后再存入数据库。后面查询资料,发现 MySQL 已经提供了从 JSON 数据中查找和比较的函数,极大地方便了数据处理!

这是在没有写这篇文章前,查询资料写出来的刷数据SQL,其实还有优化的空间,等文章最后我们一起看下吧!

1
2
3
4
5
6
select id,
       replace(replace(json_extract(`info`, '$.budget_mode'), '"', ''), 'null', ''),
       replace(replace(json_extract(`info`, '$.budget'), '"', ''), 'null', 0),
       replace(replace(json_extract(`info`, '$.bid'), '"', ''), 'null', 0),
from table
where code = 'xxx';

其次,为了方便后面的学习和测试,我们新建一张表,建表语句如下:

1
2
3
4
5
create table `userinfo`
(
    `id`   bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '主键' PRIMARY KEY,
    `info` longtext        NOT NULL COMMENT '用户信息'
);

接下来我们就看下MySQL提供的 JSON 查询和比较函数,比较常用的应该就是 JSON_EXTRACT 、column->path、column-»path 和 JSON_VALUE 四个函数,可以按需学习哦!

JSON_CONTAINS

该函数用于判断一个 JSON 文档是否包含另一个 JSON 文档。如果提供了路径,用于判断 JSON 文档相应路径下的数据是否包含另一个JSON 文档。

语法

JSON_CONTAINS(target,candidate[,path])

  • target: 必填。目标 JSON 文档

  • candidate: 必填。被包含的 JSON 文档

  • path: 可选。路径

返回值

  • 如果 target 或者 target 在 path 路径下的数据包含 candidate,返回 1;否则返回 0
  • 如果任意一个必填参数为 NULL,或者路径 path 在 target 中不存在,返回 NULL
  • 如果 target 或者 candidate 不是一个有效的JSON 文档,查询报错
  • 如果提供的 path 不是一个有效的路径表达式,或者 path 包含通配符 ‘*’ 或者 ‘**’ ,查询报错

规则

  • 对于两个简单类型的变量,如果两者类型相同、该类型可比较且值相等,则 target 包含 candidate
  • 对于两个数组类型的变量,如果 candidate 数组中的每个元素,都存在于 target 中的某些元素中,则 target 包含 candidate
  • 对于一个非数组类型 candidate 和数组类型 target,如果 candidate 存在于 target 的某些元素中,则 target 包含 candidate
  • 对于两个对象,如果 candidate 的每个 key 都在 target 中存在,且对应的 value 值也被包含,则 target 包含 candidate

测试

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
insert into userinfo (id, info) values (1,'{"a": 1, "b": 2, "c": {"d": 4},"d":[1,2,3]}');

select  JSON_CONTAINS(info,'1') from userinfo where id=1; # 0, target 不包含 JSON '1'

select  JSON_CONTAINS(info,'1','$.a') from userinfo where id=1; # 1, 两个简单类型, 1 包含 1

select  JSON_CONTAINS(info,'1','$.d') from userinfo where id=1; # 1, 非数组和数组类型比较, [1,2,3] 包含 1

select  JSON_CONTAINS(info,'[1,2]','$.d') from userinfo where id=1; # 1, 两个数组类型比较, [1,2,3] 包含 数组类型 [1,2]

select  JSON_CONTAINS(info,'[1,2,4]','$.d') from userinfo where id=1; # 0, 两个数组类型比较, [1,2,3] 不包含 数组类型 [1,2,4]

select  JSON_CONTAINS(info,'{"a":1}') from userinfo where id=1; # 1, 两个对象比较, target 中存在 key 'a', value 包含

select  JSON_CONTAINS(info,'{"a":2}') from userinfo where id=1; # 0, 两个对象比较, target 存在 key 'a', value 不包含

select  JSON_CONTAINS(info,'{"d":2}') from userinfo where id=1; # 1, 两个对象比较, target 存在 key 'd', value 包含

select  JSON_CONTAINS(info,'{"a":1,"d":2}') from userinfo where id=1; # 1, 两个对象比较, target 存在 key 'a'  'd' , value 均包含

select  JSON_CONTAINS(info,'{"a":1,"d":[2,3]}') from userinfo where id=1; # 1, 两个对象比较, target 存在 key 'a'  'd' , value 均包含

JSON_CONTAINS_PATH

该函数用于判断一个 JSON 文档是否包含一个或者多个路径 path

语法

JSON_CONTAINS_PATH(json_doc, one_or_all, path[, path…])]

  • json_doc: 必填。一个 JSON 文档
  • one_or_all: 必填。值为 ‘one’ 或者 ‘all’,指定至少一个还是所有 path 存在于 json_doc
  • path: 必填。至少填写一个路径

返回值

  • one_or_all = ‘one’ 时,如果存在一个 path 存在于 json_doc,返回 1 ; 否则返回 0
  • one_or_all = ‘all’ 时,所有 path 存在于 json_doc 返回 1 ; 否则返回 0
  • 如果有参数为 NULL,则返回 NULL
  • 如果 json_doc 不是有效的JSON数据,或者 path 不是合法的表达式,或者 one_or_all 参数 取值不是 ‘one’ 或者 ‘all’,返回 error

测试

1
2
3
4
5
6
insert into userinfo (id, info) values (2,'{"a": 1, "b": 2, "c": {"d": 4}}');

select JSON_CONTAINS_PATH(info,'one','$.a') from userinfo where id=2; # 1 a 存在于 路径中
select JSON_CONTAINS_PATH(info,'one','$.a','$.e') from userinfo where id=2 ; # 1 至少一个存在即可,且路径 a 存在
select JSON_CONTAINS_PATH(info,'all','$.a','$.e') from userinfo where id=2; # 0 必须所有路径都存在,但路径 e 不存在中
select JSON_CONTAINS_PATH(info,'all','$.c.d') from userinfo where id=2; # 1 路径 c.d 存在

JSON_EXTRACT

该函数用于从 JSON 字段中查询路径 path 对应的 value 值

语法

JSON_EXTRACT(json_doc, path[,path…])

  • json_doc: 必填。一个 JSON 文档
  • path: 必填。至少填写一个路径

返回值

  • 如果只匹配到一个path,则返回对应的 value
  • 如果匹配到多个 path,则将所有的 value 组合成一个数组返回,value 在数组的顺序和 提供的 path 顺序保持一致
  • 如果参数为 NULL,或者未在 json_doc 中匹配到对应的 path,则返回NULL
  • 如果 json_doc 不是合法的 JSON 文档,或者 path 不是合法的路径表达式,则返回error
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
insert into userinfo (id, info) values (3,'{"a": 1, "b": 2, "c": {"d": 4},"d":[1,2,3],"e":{"name":"tom","age":12}}');


select json_extract(info,'$.a') from userinfo where id=3; # 1
select json_extract(info,'$.c.d') from userinfo where id=3; # 4
select json_extract(info,'$.d') from userinfo where id=3; # [1,2,3]
select json_extract(info,'$.d[0]') from userinfo where id=3; # 1
select json_extract(info,'$.d[3]') from userinfo where id=3; # NULL
select json_extract(info,'$.f') from userinfo where id=3; # NULL
select json_extract(info,'$.a','$.b','$.c','$.d','$.e.name','$.e.age','$.f') from userinfo where id=3; # [1, 2, {"d": 4}, [1, 2, 3], "tom", 12]

如果只查询一个 path,可以使用接下来介绍的 -> 操作符

column->path

JSON_EXTRACT 只有两个参数时的缩写。

如下两个查询是等价的:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
select info,info->'$.a' as info_a from userinfo where info->'$.a' >0 ;

select info,JSON_EXTRACT(info,'$.a') as info_a  from userinfo where JSON_EXTRACT(info,'$.a')>0;

+-----------------------------------------------------------------------+------+
|info                                                                   |info_a|
+-----------------------------------------------------------------------+------+
|{"a": 1, "b": 2, "c": {"d": 4},"d":[1,2,3]}                            |1     |
|{"a": 1, "b": 2, "c": {"d": 4}}                                        |1     |
|{"a": 1, "b": 2, "c": {"d": 4},"d":[1,2,3],"e":{"name":"tom","age":12}}|1     |
+-----------------------------------------------------------------------+------+

和列操作一样,这个符号可以用于 where条件、order by 条件等

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
select info,info->'$.a' as a, info->'$.c.d' as info_c_d from userinfo where info->'$.d' is not null ;
+-----------------------------------------------------------------------+-+--------+
|info                                                                   |a|info_c_d|
+-----------------------------------------------------------------------+-+--------+
|{"a": 1, "b": 2, "c": {"d": 4},"d":[1,2,3]}                            |1|4       |
|{"a": 1, "b": 2, "c": {"d": 4},"d":[1,2,3],"e":{"name":"tom","age":12}}|1|4       |
+-----------------------------------------------------------------------+-+--------+


select info,info->'$.a' as a,info->'$.c.d' as info_c_d from userinfo where info->'$.d[0]'>0 order by '$.a';

+-----------------------------------------------------------------------+-+--------+
|info                                                                   |a|info_c_d|
+-----------------------------------------------------------------------+-+--------+
|{"a": 1, "b": 2, "c": {"d": 4},"d":[1,2,3]}                            |1|4       |
|{"a": 1, "b": 2, "c": {"d": 4},"d":[1,2,3],"e":{"name":"tom","age":12}}|1|4       |
+-----------------------------------------------------------------------+-+--------+

column-»path

‘-»’ 符号相对于 ‘->',增加了去除引号的功能。如果一个 JSON 文档中,key 对应的 value 是字符串类型,那么如下三个表达式返回相同的结果:

  • JSON_UNQUOTE(JSON_EXTRACT(column,path))
  • JSON_UNQUOTE(column->path)
  • column-»path
1
2
3
4
select info->'$.e.name' as name from userinfo where id=3; # "tom"
select json_unquote(json_extract(info,'$.e.name')) as name from userinfo where id =3; # tom
select json_unquote(info->'$.e.name') as name from userinfo where id=3; # tom
select info->>'$.e.name' as name from userinfo where id=3; # tom

JSON_KEYS

该函数用于返回 JSON 文档或者指定 path 下最顶层的所有 key

语法

JSON_KEYS(json_doc,[path])

  • json_doc: 必填。一个 JSON 文档
  • path: 选填。路径

返回值

  • 返回 json_doc 或者指定 path 下最顶层的 key 数组
  • 如果任意参数为 NULL,或者 json_doc 不是一个对象(可能是个数组),或者根据 path 没有定位到数据,则返回NULL
  • 如果 json_doc不是 JSON 对象,或者指定的路径不合法,返回error

测试

 1
 2
 3
 4
 5