MySQL数据库JSON字段类型与JSON函数

1. MySQL JSON概述

1.1 JSON字段说明

Mysql5.7版本及其以后提供了一个原生的Json字段类型，Json类型的值将不再以字符串的形式存储，而是采用一种允许快速读取文本元素（document elements）的内部二进制（internal binary）格式。在Json列插入或者更新的时候将会自动验证Json文本，未通过验证的文本将产生一个错误信息。Json文本采用标准的创建方式，可以使用大多数的比较操作符进行比较操作，例如：=, <, <=, >, >=, <>, != 和 <=>。

MySQL JSON Functions：https://dev.mysql.com/doc/refman/5.7/en/json-functions.html

1.2 JSON函数说明

​ MySQL 5.7.22 及更高版本支持两个聚合 JSON 函数 JSON_ARRAYAGG()和 JSON_OBJECTAGG(). 看 第 12.20 节，“聚合函数” ，用于 这些的描述。

MySQL JSON 函数参考 ：https://dev.mysql.com/doc/refman/5.7/en/json-function-reference.html

2. MySQL JSON操作

-- 创建测试表
CREATE TABLE `tab_json` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键id',
  `data` json DEFAULT NULL COMMENT 'json字符串',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='json测试表';

-- 查询tab_json表
select * from tab_json;
-- 删除tab_json表
drop table tab_json;

-- ----------------------------------------------------------------
-- 新增数据
INSERT INTO `tab_json`(`id`, `data`) VALUES (1, '{"Tel": "132223232444", "name": "david", "address": "Beijing"}');
INSERT INTO `tab_json`(`id`, `data`) VALUES (2, '{"Tel": "13390989765", "name": "Mike", "address": "Guangzhou"}');
INSERT INTO `testdb`.`tab_json`(`id`, `data`) VALUES (3, '{"success": true,"code": "0","message": "","data": {"name": "jerry","age": "18","sex": "男"}}');
INSERT INTO `testdb`.`tab_json`(`id`, `data`) VALUES (4, '{"success": true,"code": "1","message": "","data": {"name": "tome","age": "30","sex": "女"}}');

-- ----------------------------------------------------------------
-- json_extract
select json_extract('{"name":"Zhaim","tel":"13240133388"}',"$.tel");
select json_extract('{"name":"Zhaim","tel":"13240133388"}',"$.name");

-- ----------------------------------------------------------------
-- 对tab_json表使用json_extract函数
select json_extract(data,'$.name') from tab_json;

#如果查询没有的key,那么是可以查询,不过返回的是NULL.
select json_extract(data,'$.name'),json_extract(data,'$.Tel') from tab_json;  
select json_extract(data,'$.name'),json_extract(data,'$.tel') from tab_json;  
select json_extract(data,'$.name'),json_extract(data,'$.address') from tab_json;

-- ----------------------------------------------------------------
-- 条件查询
select json_extract(data,'$.name'),json_extract(data,'$.Tel') from tab_json where json_extract(data,'$.name') = 'Mike';  

-- ----------------------------------------------------------------
-- 嵌套json查询
select * from tab_json where json_extract(data,'$.success') = true;  
select json_extract(data,'$.data') from tab_json where json_extract(data,'$.success') = true;  
-- 查询data对应json中key为name的值
select json_extract( json_extract(data,'$.data'),'$.name') from tab_json where json_extract(data,'$.code') = "1";  
select json_extract( json_extract(data,'$.data'),'$.name'),json_extract( json_extract(data,'$.data'),'$.age') from tab_json where json_extract(data,'$.code') = "0";  

-- ----------------------------------------------------------------
-- 性能验证 , 通过验证全部都是全表扫描，使用场景：数据量不大json字符串较大则可以采用，数据量较大不建议使用。
explain select * from tab_json where json_extract(data,'$.success') = true;  
explain select json_extract(data,'$.data') from tab_json where json_extract(data,'$.success') = true;  
-- 查询data对应json中key为name的值
explain select json_extract( json_extract(data,'$.data'),'$.name') from tab_json where json_extract(data,'$.code') = "1";  
explain select json_extract( json_extract(data,'$.data'),'$.name'),json_extract( json_extract(data,'$.data'),'$.age') from tab_json where json_extract(data,'$.code') = "0"; 

-- ----------------------------------------------------------------

-- 查询json对接集合对象
INSERT INTO `tab_json`(`id`, `data`) VALUES (5, '{"employee":[{"name": "zhangsan", "code": "20220407001", "age": "18"},{"name": "zhangsan2", "code": "20220407002", "age": "28"}]}');

INSERT INTO `tab_json`(`id`, `data`) VALUES (6, '{"employee":[{"name": "lisi", "code": "20220407003", "age": "16"},{"name": "lisi2", "code": "20220407004", "age": "26"}]}');

-- 查询data对应json值的employee集合对象中name为zhangsan的用户
select * from tab_json tj where JSON_CONTAINS(json_extract(tj.data,'$.employee'),JSON_OBJECT('name', "zhangsan"));
-- zhangsan3不存在返回空
select * from tab_json tj where JSON_CONTAINS(json_extract(tj.data,'$.employee'),JSON_OBJECT('name', "zhangsan3"));

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65