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)