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 查询结果