Mysql使用函数json_extract处理Json类型数据

╰+哭是因爲堅強的太久メ 2022-09-07 15:52 331阅读 0赞

### Mysql使用函数json\_extract处理Json类型数据 ###

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

![在这里插入图片描述][df1cb3ac10c44fc0bdb88e83d6db00b2.png]  
![在这里插入图片描述][5afddda1c8e94ca8b7f0fcd2d6aab5df.png]

[df1cb3ac10c44fc0bdb88e83d6db00b2.png]: /images/20220829/300bcb2185a04d1ba7893892429a971c.png
[5afddda1c8e94ca8b7f0fcd2d6aab5df.png]: /images/20220829/7381f1369ef84364a052ec9144775ce5.png