快捷導(dǎo)航

Mysql使用函數(shù)json_extract處理Json類型數(shù)據(jù)的方法實例

更新時間：2022年09月05日 09:04:21 作者：靖節(jié)先生

在日常業(yè)務(wù)開發(fā)中通常mysql數(shù)據(jù)庫中某個字段會需要存儲json格式字符串,下面這篇文章主要給大家介紹了關(guān)于Mysql使用函數(shù)json_extract處理Json類型數(shù)據(jù)的相關(guān)資料,需要的朋友可以參考下

1. 需求概述

業(yè)務(wù)開發(fā)中通常mysql數(shù)據(jù)庫中某個字段會需要存儲json格式字符串，查詢的時候有時json數(shù)據(jù)較大，每次全部取出再去解析查詢效率較低，也比較麻煩，則Mysql5.7版本提供提供函數(shù)json_extract，可以通過key查詢value值，比較方便。

2. json_extract簡介

2.1 函數(shù)簡介

Mysql5.7版本以后新增的功能，Mysql提供了一個原生的Json類型，Json值將不再以字符串的形式存儲，而是采用一種允許快速讀取文本元素（document elements）的內(nèi)部二進(jìn)制（internal binary）格式。在Json列插入或者更新的時候?qū)詣域炞CJson文本，未通過驗證的文本將產(chǎn)生一個錯誤信息。 Json文本采用標(biāo)準(zhǔn)的創(chuàng)建方式，可以使用大多數(shù)的比較操作符進(jìn)行比較操作，例如：=, <, <=, >, >=, <>, != 和 <=>。

2.2 使用方式

數(shù)據(jù)存儲的數(shù)據(jù)是json字符串（類型是vachar）。

想要查詢出來json中某個字段的值，用到方法是：JSON_EXTRACT()。

語法：

JSON_EXTRACT(json_doc, path[, path] …)

實際用法：

如果json字符串不是數(shù)組，則直接使用$.字段名即可

2.3 注意事項

JSON_EXTRACT性能驗證 , 通過查看執(zhí)行計劃，驗證全部都是全表掃描。

使用場景：數(shù)據(jù)量不大json字符串較大則可以采用，數(shù)據(jù)量較大不建議使用。

3. 實現(xiàn)驗證

3.1 建表查詢

-- 創(chuàng)建測試表
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;

-- 新增數(shù)據(jù)
-- {"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函數(shù)
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對應(yīng)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";  

-- 性能驗證 , 通過驗證全部都是全表掃描，使用場景：數(shù)據(jù)量不大json字符串較大則可以采用，數(shù)據(jù)量較大不建議使用。
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對應(yīng)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 查詢結(jié)果

總結(jié)

到此這篇關(guān)于Mysql使用函數(shù)json_extract處理Json類型數(shù)據(jù)的文章就介紹到這了,更多相關(guān)Mysql用函數(shù)json_extract處理Json內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: