Mysql使用函数json_extract处理Json类型数据的方法实例-编程学习网

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

Mysql使用函数json_extract处理Json类型数据的方法实例

总结

到此这篇关于Mysql使用函数json_extract处理Json类型数据的文章就介绍到这了,更多相关Mysql用函数json_extract处理Json内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们！

文章详情