目录
- 1. 需求概述
- 2. json_extract简介
- 2.1 函数简介
- 2.2 使用方式
- 2.3 注意事项
- 3. 实现验证
- 3.1 建表查询
- 3.2 查询结果
- 总结
1. 需求概述
业务开发中通常mysql数据库中某个字段会需要存储json格式字符串，查询的时候有时json数据较大，每次全部取出再去解析查询效率较低，也比较麻烦，则Mysql5.7版本提供提供函数json_extract，可以通过key查询value值，比较方便。
2. json_extract简介
2.1 函数简介
Mysql5.7版本以后新增的功能，Mysql提供了一个原生的Json类型，Json值将不再以字符串的形式存储，而是采用一种允许快速读取文本元素（document elements）的内部二进制（internal binary）格式。 在Json列插入或者更新的时候将会自动验证Json文本，未通过验证的文本将产生一个错误信息。 Json文本采用标准的创建方式，可以使用大多数的比较操作符进行比较操作，例如：=, <, <=, >, >=, <>, != 和 <=>。
2.2 使用方式
数据存储的数据是json字符串（类型是vachar）。
想要查询出来json中某个字段的值，用到方法是：JSON_EXTRACT()。
语法：
JSON_EXTRACT(json_doc, path[, path] …)
实际用法：
如果json字符串不是数组，则直接使用$.字段名即可
2.3 注意事项
JSON_EXTRACT性能验证 , 通过查看执行计划，验证全部都是全表扫描。
使用场景：数据量不大json字符串较大则可以采用，数据量较大不建议使用。
3. 实现验证
3.1 建表查询
-- 创建测试表
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=utf8;
-- 新增数据
-- {"Tel": "132223232444", "name": "david", "address": "Beijing"}
-- {"Tel": "13390989765", "name": "Mike", "address": "Guangzhou"}
INSERT INTO `testdb`.`tab_json`(`id`, `data`) VALUES (1, '{\"Tel\": \"132223232444\", \"name\": \"david\", \"address\": \"Beijing\"}');
INSERT INTO `testdb`.`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": "女"}}');
-- 查询
select * from tab_json;
-- 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";
3.2 查询结果
