1. json_extract 使用場(chǎng)景說(shuō)明

在日常業(yè)務(wù)開(kāi)發(fā)中通常mysql數(shù)據(jù)庫(kù)中某個(gè)字段會(huì)需要存儲(chǔ)json格式字符串，查詢(xún)的時(shí)候有時(shí)json數(shù)據(jù)較大，每次全部取出再去解析查詢(xún)效率較低，也較麻煩.

好在Mysql5.7及之后的版本里提供了json_extract函數(shù)，可以通過(guò)key查詢(xún)value值(如果是json數(shù)組類(lèi)型,可以通過(guò)下標(biāo)獲取對(duì)應(yīng)位置的值)，非常方便。

2. MySQL json_extract 函數(shù)簡(jiǎn)介

2.1 函數(shù)簡(jiǎn)介

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

2.2 使用方式

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

想要查詢(xún)出來(lái)json中某個(gè)字段的值，用到方法是：JSON_EXTRACT()。

語(yǔ)法：

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

用法提示：

• 如果json字符串不是數(shù)組，則直接使用 $.字段名
• 如果json字符串是數(shù)組[Array]，則直接使用 $[對(duì)應(yīng)的索引ID]

2.3 注意事項(xiàng)

JSON_EXTRACT性能驗(yàn)證 , 通過(guò)查看執(zhí)行計(jì)劃，驗(yàn)證全部都是全表掃描。
使用場(chǎng)景：數(shù)據(jù)量不大json字符串較大則可以采用，數(shù)據(jù)量較大不建議使用。

3. 數(shù)據(jù)驗(yàn)證

3.1 提取普通json中的值

說(shuō)明:

• 普通字段使用 $.KEY 獲取
• 數(shù)組字段使用 $.KEY[index] 獲取,注意index從0開(kāi)始
• 實(shí)際使用中,如下json字符串只需要換成對(duì)應(yīng)的表字段即可,但要注意需對(duì)json判空和替換等ETL轉(zhuǎn)換操作.

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;

結(jié)果:

3.2 提取json數(shù)組的值

site_user表

提取用戶(hù)的第一個(gè)標(biāo)簽:

select 
	id,
	name,
	tags, 
	json_extract(if(LENGTH(tags)>0,tags, '[]'),"$[0]")  # 如果tags無(wú)數(shù)據(jù),賦值為空數(shù)組
from site_user;

結(jié)果:

總結(jié)

到此這篇關(guān)于MySQL中json_extract函數(shù)說(shuō)明及使用的文章就介紹到這了,更多相關(guān)MySQL json_extract函數(shù)使用內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論