前言

昨天上線后通過系統(tǒng)報警發(fā)現(xiàn)了一個bug，于是緊急進行了回滾操作，但是期間有用戶下單，數(shù)據(jù)產(chǎn)生了影響，因此需要排查影響了哪些訂單，并對數(shù)據(jù)進行修復。

1. 問題現(xiàn)象

由于bug導致了訂單表的customer_extra_info字段的hasFreightInsurance誤更新成了“是”，因此需要查詢回滾前一共有多少被誤更新為“是”的訂單，如下圖：

于是查看訂單表中customer_extra_info字段類型發(fā)現(xiàn)是JSON類型的

2. 解決方案

查詢資料發(fā)現(xiàn)mysql5.7以后提供了一種新的字段格式-json。

對JSON類型的數(shù)據(jù)MySQL提供了相關(guān)的查詢操作。

先給出查詢SQL，后面在介紹MySQL對JSON類型字段的查詢操作

SELECT
	* 
FROM
	( SELECT id, customer_extra_info -> '$.hasFreightInsurance' AS Insurance FROM oms_order_list WHERE project_id = 1 AND update_time > '2022-04-15 16:30:17' ) t 
WHERE
	JSON_CONTAINS ( Insurance, JSON_OBJECT ( "value", "是" ) )

3. JSON數(shù)據(jù)查詢

3.1 一般基礎(chǔ)查詢操作

1、使用 json字段名->’$.json屬性’ 進行查詢條件

SELECT
	id,
	customer_extra_info
FROM
	oms_order_list 
WHERE
	project_id = 1 
	AND update_time > '2022-04-15 16:30:17'
	AND customer_extra_info -> '$.maxClaimAmount'=10

查詢結(jié)果如下：

 2、關(guān)聯(lián)表查詢

json字段也支持關(guān)聯(lián)表的查詢，這里只寫出使用方法，不做實例展示。其中deptLeaderId和id分別是dept,dept_leader兩個表中的關(guān)聯(lián)字段。

SELECT * from dept,dept_leader WHERE dept.json_value->'$.deptLeaderId'=dept_leader.json_value->'$.id' ;

3.2 一般函數(shù)查詢操作

寫到這里大家都發(fā)現(xiàn)了，我們查詢的json都是整條json數(shù)據(jù)，這樣看起來不是很方便，那么如果我們只想看json中的某個字段怎么辦？

這樣就引入了我們的第一個函數(shù):json_extract(字段名,json字段名)

在詳細介紹用法之前我們可以看看官網(wǎng)的函數(shù)介紹：

 咱們可以看到官網(wǎng)介紹json_extract（）這個函數(shù)很詳細：Return data from JSON document

從json中返回字段

1、函數(shù) json_extract（）：從json中返回想要的字段

用法：json_extract(字段名,$.json字段名)
實例：

SELECT
	id,
	json_extract ( customer_extra_info, '$.hasFreightInsurance' ) AS hasFreightInsurance 
FROM
	oms_order_list 
WHERE
	project_id = 1 
	AND update_time > '2022-04-15 16:30:17'

查詢結(jié)果如下：

2、函數(shù)JSON_CONTAINS()：JSON格式數(shù)據(jù)是否在字段中包含特定對象 

用法: JSON_CONTAINS(target, candidate[, path])
實例：

SELECT
	id,
	customer_extra_info 
FROM
	oms_order_list 
WHERE
	project_id = 1 
	AND update_time > '2022-04-15 16:30:17' 
	AND JSON_CONTAINS ( customer_extra_info, JSON_OBJECT ( "maxClaimAmount", 10 ) )

查詢結(jié)果如下：

3、函數(shù)JSON_OBJECT():將一個鍵值對列表轉(zhuǎn)換成json對象 

比如我們想查詢某個對象里面的值等于多少

我們可以看到hasFreightInsurance中還有一個對象，里面還有name和value兩個屬性字段，那么我們應該怎么查詢value=否的訂單呢。

用法：JSON_OBJECT([key, val[, key, val] …])
實例：

SELECT
	* 
FROM
	( SELECT id, customer_extra_info -> '$.hasFreightInsurance' AS Insurance FROM oms_order_list WHERE project_id = 1 AND update_time > '2022-04-15 16:30:17' ) t 
WHERE
	JSON_CONTAINS ( Insurance, JSON_OBJECT ( "value", "否" ) )

查詢結(jié)果如下：

4、函數(shù)JSON_ARRAY():創(chuàng)建JSON數(shù)組

?用法：JSON_ARRAY([val[, val] …])

實例：我們要查詢deptName包含1的數(shù)據(jù)

SELECT
	id,
	customer_extra_info 
FROM
	oms_order_list 
WHERE
	project_id = 1 
	AND update_time > '2022-04-15 16:30:17' 
	AND JSON_CONTAINS ( customer_extra_info -> '$.deptName', JSON_ARRAY ( "1" ) )

查詢結(jié)果如下：

5、函數(shù)JSON_TYPE(）：查詢某個json字段屬性類型

用法：JSON_TYPE(json_val)
事例：比如我們想查詢deptName的字段屬性是什么

SELECT
	id,
	customer_extra_info -> '$.deptName',
	JSON_TYPE ( customer_extra_info -> '$.deptName' ),
	customer_extra_info -> '$.hasFreightInsurance',
	JSON_TYPE ( customer_extra_info -> '$.hasFreightInsurance' ) 
FROM
	oms_order_list 
WHERE
	project_id = 1 
	AND update_time > '2022-04-15 16:30:17'

查詢結(jié)果如下：

6、函數(shù)JSON_EXTRACT() ：從JSON文檔返回數(shù)據(jù) 

這也是我們開發(fā)中會經(jīng)常用到的一個函數(shù)

SELECT
	* 
FROM
	oms_order_list 
WHERE
	project_id = 1 
	AND update_time > '2022-04-15 16:30:17' 
	AND JSON_CONTAINS ( JSON_EXTRACT ( customer_extra_info, '$.hasFreightInsurance' ), JSON_OBJECT ( "value", "否" ) )

 查詢結(jié)果如下：

7、函數(shù)JSON_KEYS() ：JSON文檔中的鍵數(shù)組

 用法：JSON_KEYS(json_value)

實例：比如我們想查詢json格式數(shù)據(jù)中的所有key

SELECT
	id,
	JSON_KEYS ( customer_extra_info ) 
FROM
	oms_order_list 
WHERE
	project_id = 1 
	AND update_time > '2022-04-15 16:30:17'

 查詢結(jié)果如下：

4. JSON數(shù)據(jù)新增更新刪除

接下來的3種函數(shù)都是新增數(shù)據(jù)類型的：
JSON_SET(json_doc, path, val[, path, val] …)
JSON_INSERT(json_doc, path, val[, path, val] …)
JSON_REPLACE(json_doc, path, val[, path, val] …)

1、函數(shù)JSON_SET() ：將數(shù)據(jù)插入JSON格式中，有key則替換，無key則新增
這也是我們開發(fā)過程中經(jīng)常會用到的一個函數(shù)

用法：JSON_SET(json_doc, path, val[, path, val] …)

實例：比如我們想針對id=2的數(shù)據(jù)新增一組：newData:新增的數(shù)據(jù),修改deptName為新增的部門1
sql語句如下：

update dept set json_value=JSON_SET('{"deptName": "部門2", "deptId": "2", "deptLeaderId": "4"}','$.deptName','新增的部門1','$.newData','新增的數(shù)據(jù)') WHERE id=2;
 
select * from dept WHERE id =2

結(jié)果：

注意：json_doc如果不帶這個單元格之前的值，之前的值是會被新值覆蓋的，比如我們?nèi)绻碌恼Z句換成:

update dept set json_value=JSON_SET('{"a":"1","b":"2"}','$.deptName','新增的部門1','$.newData','新增的數(shù)據(jù)') WHERE id=2

我們可以看到這里json_doc是{“a”:“1”,“b”:“2”}，這樣的話會把之前的單元格值覆蓋后再新增/覆蓋這個單元格字段

結(jié)果:

2、函數(shù)JSON_INSERT():插入值（往json中插入新值，但不替換已經(jīng)存在的舊值）
用法：JSON_INSERT(json_doc, path, val[, path, val] …)

 實例：

UPDATE dept set json_value=JSON_INSERT('{"a": "1", "b": "2"}', '$.deptName', '新增的部門2','$.newData2','新增的數(shù)據(jù)2') 
WHERE id=2

結(jié)果：

 我們可以看到由于json_doc變化將之前的值覆蓋了，新增了deptName和newData2.
如果我們再執(zhí)行以下剛才的那個sql，只是換了value，我們會看到里面的key值不會發(fā)生變化。
因為這個函數(shù)只負責往json中插入新值，但不替換已經(jīng)存在的舊值。

3、函數(shù)JSON_REPLACE（）
用法：JSON_REPLACE(json_doc, path, val[, path, val] …)

用例：
如果我們要更新id=2數(shù)據(jù)中newData2的值為：更新的數(shù)據(jù)2

sql語句如下：

UPDATE dept set json_value=JSON_REPLACE('{"a": "1", "b": "2", "deptName": "新增的部門2", "newData2": "新增的數(shù)據(jù)2"}', '$.newData2', '更新的數(shù)據(jù)2') WHERE id =2;
 
select * from dept WHERE id =2

結(jié)果：

4、函數(shù)JSON_REMOVE() ：從JSON文檔中刪除數(shù)據(jù)
用法：JSON_REMOVE(json_doc, path[, path] …)

舉例：刪除key為a的字段。

UPDATE dept set json_value=JSON_REMOVE('{"a": "1", "b": "2", "deptName": "新增的部門2", "newData2": "更新的數(shù)據(jù)2"}','$.a') WHERE id =2;

結(jié)果：

5、函數(shù)JSON_SEARCH() ：用于在json格式中查詢并返回符合條件的節(jié)點
這是一個非常強大的函數(shù)

到此這篇關(guān)于MySQL對JSON類型字段數(shù)據(jù)進行提取和查詢的實現(xiàn)的文章就介紹到這了,更多相關(guān)MySQL JSON字段提取和查詢內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論