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函数说明

Name	描述	介绍	已弃用
->	评估路径后从 JSON 列返回值； 相当于 JSON_EXTRACT()。
->>	评估路径和取消引用后从 JSON 列返回值 结果; 相当于 JSON_UNQUOTE(JSON_EXTRACT())。	5.7.13
JSON_APPEND()	将数据附加到 JSON 文档		是的
JSON_ARRAY()	创建 JSON 数组
JSON_ARRAY_APPEND()	将数据附加到 JSON 文档
JSON_ARRAY_INSERT()	插入 JSON 数组
JSON_CONTAINS()	JSON 文档是否在路径中包含特定对象
JSON_CONTAINS_PATH()	JSON 文档是否包含路径中的任何数据
JSON_DEPTH()	JSON 文档的最大深度
JSON_EXTRACT()	从 JSON 文档返回数据
JSON_INSERT()	将数据插入 JSON 文档
JSON_KEYS()	JSON 文档中的键数组
JSON_LENGTH()	JSON 文档中的元素数
JSON_MERGE()	合并 JSON 文档，保留重复键。 已弃用 JSON_MERGE_PRESERVE() 的同义词		5.7.22
JSON_MERGE_PATCH()	合并 JSON 文档，替换重复键的值	5.7.22
JSON_MERGE_PRESERVE()	合并 JSON 文档，保留重复键	5.7.22
JSON_OBJECT()	创建 JSON 对象
JSON_PRETTY()	以人类可读的格式打印 JSON 文档	5.7.22
JSON_QUOTE()	引用 JSON 文档
JSON_REMOVE()	从 JSON 文档中删除数据
JSON_REPLACE()	替换 JSON 文档中的值
JSON_SEARCH()	JSON 文档中值的路径
JSON_SET()	将数据插入 JSON 文档
JSON_STORAGE_SIZE()	用于存储 JSON 文档的二进制表示的空间	5.7.22
JSON_TYPE()	JSON 值的类型
JSON_UNQUOTE()	取消引用 JSON 值
JSON_VALID()	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"));