MySQL中json_extract函式怎麼使用

1. json_extract 使用場景說明

在日常業務開發中通常mysql資料庫中某個欄位會需要儲存json格式字串，查詢的時候有時json資料較大，每次全部取出再去解析查詢效率較低，也較麻煩.

好在Mysql5.7及之後的版本裡提供了json_extract函數，可以透過key查詢value值(如果是json數組類型,可以透過下標取得對應位置的值)，非常方便。

2. MySQL json_extract 函數簡介

2.1 函數簡介

Mysql5.7版本以後新增的功能，Mysql提供了一個原生的Json類型，Json值將不再以字串的形式存儲，而是採用一種允許快速讀取文字元素（document elements）的內部二進位（internal binary）格式。在Json列插入或更新的時候將會自動驗證Json文本，未通過驗證的文本將產生一個錯誤訊息。 Json文本採用標準的創建方式，可以使用大多數的比較操作符進行比較操作，例如：=, 91eef5681cad6f0cc96f9adeb2931b45, >=, a8093152e673feb7aba1828c43532094, != 和<=> ;。

2.2 使用方式

資料儲存的資料是json字串（類型是vachar）。

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

語法：

**JSON_EXTRACT(json_doc, path[, path] …)**

用法提示：

• 如果json字串不是數組，則直接使用 $.字段名稱

• #如果json字串是數組[Array]，則直接使用 $[對應的索引ID]

2.3 注意事項

JSON_EXTRACT效能驗證, 透過檢視執行計劃，驗證全部都是全表掃描。
使用場景：資料量不大json字串較大則可以採用，資料量較大不建議使用。

3. 資料驗證

3.1 擷取一般json中的值

#說明:

• 一般欄位使用 $ .KEY 取得

• 陣列欄位使用 #$.KEY[index] 取得,注意index從0開始

• #實際使用中,如下json字串只需要換成對應的表格欄位即可,但要注意需對json判空和替換等ETL轉換操作.

select 
	json_extract('{"name":"zhangsan","tel_no":"136-6666-6666","hobbies":["basketball","run","sing"]}',"$.name") as name,
	json_extract('{"name":"zhangsan","tel_no":"136-6666-6666","hobbies":["basketball","run","sing"]}',"$.tel_no") as tel_no,
	json_extract('{"name":"zhangsan","tel_no":"136-6666-6666","hobbies":["basketball","run","sing"]}',"$.hobbies[0]") as hobby_1,
	json_extract('{"name":"zhangsan","tel_no":"136-6666-6666","hobbies":["basketball","run","sing"]}',"$.hobbies[1]") as hobby_2,
	json_extract('{"name":"zhangsan","tel_no":"136-6666-6666","hobbies":["basketball","run","sing"]}',"$.hobbies[2]") as hobby_3,
	json_extract('{"name":"zhangsan","tel_no":"136-6666-6666","hobbies":["basketball","run","sing"]}',"$.hobbies[3]") as hobby_4;

結果:

name	tel_no	#hobby_1	hobby_2	hobby_3	hobby_4
「zhangsan」	136-6666-6666	「basketball」	「 run”	“sing”	NULL

#3.2 擷取json陣列的值

site_user表

##[“VIP”]#3wangwu[「VVIP」,「PLATINUM」]4zhaoliu  

id	name	tags
1	zhangsan	[“COMMON”]
2	lisi

提取使用者的第一個標籤:

select 
	id,
	name,
	tags, 
	json_extract(if(LENGTH(tags)>0,tags, '[]'),"$[0]")  # 如果tags无数据,赋值为空数组
from site_user;

結果:

##id1234#

name	tags
zhangsan	“COMMON”
#lisi	“VIP”
wangwu	「VVIP」
zhaoliu	NULL

以上是MySQL中json_extract函式怎麼使用的詳細內容。更多資訊請關注PHP中文網其他相關文章！