Mysql使用函数json_extract处理Json类型数据 · 常用笔记

# 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字符串较大则可以采用，数据量较大不建议使用。 ``` <pre class="calibre13">``` -- 创建测试表 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"; ``` ``` ![](https://img.kancloud.cn/87/d1/87d18577caa7f530786197cb26d9e4c7_799x374.png)