Mysql使用函數json_extract處理Json型別資料的方法範例

2022-09-05 14:01:47

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";