前言

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

1. 問(wèn)題現(xiàn)象

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

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

2. 解決方案

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

對(duì)JSON類(lèi)型的數(shù)據(jù)MySQL提供了相關(guān)的查詢(xún)操作。

先給出查詢(xún)SQL，后面在介紹MySQL對(duì)JSON類(lèi)型字段的查詢(xún)操作

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ù)查詢(xún)

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

1、使用 json字段名->’$.json屬性’ 進(jìn)行查詢(xún)條件

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

查詢(xún)結(jié)果如下：

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

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

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

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

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

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

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

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

從json中返回字段

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

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

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'

查詢(xún)結(jié)果如下：

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

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

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 ) )

查詢(xún)結(jié)果如下：

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

比如我們想查詢(xún)某個(gè)對(duì)象里面的值等于多少

我們可以看到hasFreightInsurance中還有一個(gè)對(duì)象，里面還有name和value兩個(gè)屬性字段，那么我們應(yīng)該怎么查詢(xún)value=否的訂單呢。

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

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", "否" ) )

查詢(xún)結(jié)果如下：

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

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

實(shí)例：我們要查詢(xún)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" ) )

查詢(xún)結(jié)果如下：

5、函數(shù)JSON_TYPE(）：查詢(xún)某個(gè)json字段屬性類(lèi)型

用法：JSON_TYPE(json_val)
事例：比如我們想查詢(xún)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'

查詢(xún)結(jié)果如下：

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

這也是我們開(kāi)發(fā)中會(huì)經(jīng)常用到的一個(gè)函數(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", "否" ) )

 查詢(xún)結(jié)果如下：

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

 用法：JSON_KEYS(json_value)

實(shí)例：比如我們想查詢(xún)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'

 查詢(xún)結(jié)果如下：

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

接下來(lái)的3種函數(shù)都是新增數(shù)據(jù)類(lèi)型的：
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則替換，無(wú)key則新增
這也是我們開(kāi)發(fā)過(guò)程中經(jīng)常會(huì)用到的一個(gè)函數(shù)

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

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

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

結(jié)果：

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

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

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

結(jié)果:

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

 實(shí)例：

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

結(jié)果：

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

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

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

sql語(yǔ)句如下：

UPDATE dept set json_value=JSON_REPLACE('{"a": "1", "b": "2", "deptName": "新增的部門(mén)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": "新增的部門(mén)2", "newData2": "更新的數(shù)據(jù)2"}','$.a') WHERE id =2;

結(jié)果：

5、函數(shù)JSON_SEARCH() ：用于在json格式中查詢(xún)并返回符合條件的節(jié)點(diǎn)
這是一個(gè)非常強(qiáng)大的函數(shù)

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

最新評(píng)論