MySQL中JSON字段數(shù)據(jù)類型詳解

更新時(shí)間：2022年06月14日 11:40:13 作者：jacklin

JSON我相信大家都已經(jīng)很熟悉了,但在 MySQL中,直至 5.7 版本中,才正式引入 JSON數(shù)據(jù)類型,下面這篇文章主要給大家介紹了關(guān)于MySQL中JSON字段數(shù)據(jù)類型的相關(guān)資料,需要的朋友可以參考下

前言

JSON 類型是從 MySQL 5.7 版本開始支持的功能，MySQL 支持由RFC 7159定義的本機(jī)JSON數(shù)據(jù)類型，該類型可有效訪問 JSON(JavaScript 對(duì)象 table 示法)文檔中的數(shù)據(jù)。與將 JSON 格式的字符串存儲(chǔ)在字符串列中相比，JSON數(shù)據(jù)類型具有以下優(yōu)點(diǎn)：

自動(dòng)驗(yàn)證存儲(chǔ)在JSON列中的 JSON 文檔。無(wú)效的文檔會(huì)產(chǎn)生錯(cuò)誤。
優(yōu)化的存儲(chǔ)格式。存儲(chǔ)在JSON列中的 JSON 文檔將轉(zhuǎn)換為內(nèi)部格式，從而可以快速讀取文檔元素。
JSON列不能具有非NULL的默認(rèn)值。

創(chuàng)建JSON值

JSON類型字段可以是JSONObject，也可以是JSONArray類型，在 MySQL 中，JSON 值被編寫為字符串。 MySQL 會(huì)解析在需要 JSON 值的上下文中使用的任何字符串，如果該字符串作為 JSON 無(wú)效，則會(huì)產(chǎn)生錯(cuò)誤。這些上下文包括將值插入具有JSON數(shù)據(jù)類型的列中，以及將參數(shù)傳遞給需要 JSON 值的函數(shù)(在 MySQL JSON 函數(shù)的文檔中通常顯示為* json_doc 或 json_val *)。

JSON_TYPE()函數(shù)需要一個(gè) JSON 參數(shù)，并嘗試將其解析為 JSON 值。如果有效，則返回值的 JSON 類型，否則返回錯(cuò)誤：

搜索JSON類型數(shù)據(jù)

SELECT * FROM sd_school_faq WHERE faq_similar_question -> '$[*].*' LIKE '%課表在哪里？%';

路徑可以包含*或**通配符：
.[*]求值為 JSON 對(duì)象中所有成員的值。
- [*]計(jì)算 JSON 數(shù)組中所有元素的值。
- prefix**suffix計(jì)算所有以命名前綴開頭并以命名后綴結(jié)尾的路徑。
文檔中不存在的路徑(評(píng)估為不存在的數(shù)據(jù))評(píng)估為NULL。

在 JSON 和非 JSON 值之間轉(zhuǎn)換

下 table 概述了在 JSON 值和其他類型的值之間進(jìn)行轉(zhuǎn)換時(shí) MySQL 遵循的規(guī)則：

table11.3 JSON 轉(zhuǎn)換規(guī)則

other type	CAST(其他類型的 AS JSON)	CAST(JSON AS 其他類型)
JSON	No change	No change
utf8 字符類型(`utf8mb4`，`utf8`，`ascii`)	該字符串將解析為 JSON 值。	JSON 值被序列化為`utf8mb4`字符串。
其他字符類型	其他字符編碼將隱式轉(zhuǎn)換為`utf8mb4`，并按 utf8 字符類型所述進(jìn)行處理。	JSON 值被序列化為`utf8mb4`字符串，然后轉(zhuǎn)換為其他字符編碼。結(jié)果可能沒有意義。
`NULL`	結(jié)果為 JSON 類型的`NULL`值。	Not applicable.
Geometry types	通過調(diào)用`ST_AsGeoJSON()`將幾何值轉(zhuǎn)換為 JSON 文檔。	非法操作。解決方法：將`CAST(json_val AS CHAR)`的結(jié)果傳遞給`ST_GeomFromGeoJSON()`
所有其他類型	結(jié)果是由單個(gè)標(biāo)量值組成的 JSON 文檔。	如果 JSON 文檔由目標(biāo)類型的單個(gè)標(biāo)量值組成，并且該標(biāo)量值可以轉(zhuǎn)換為目標(biāo)類型，則成功。否則，返回`NULL`并產(chǎn)生警告。

JSON 值的ORDER BY和GROUP BY按照以下原則工作：

標(biāo)量 JSON 值的排序使用與前面的討論相同的規(guī)則。
對(duì)于升序排序，SQL NULL在所有 JSON 值(包括 JSON 空 Literals)之前進(jìn)行排序；對(duì)于降序排序，SQL NULL對(duì)所有 JSON 值(包括 JSON 空 Literals)進(jìn)行排序。
JSON 值的排序鍵受max_sort_length系統(tǒng)變量的值限制，因此僅在前max_sort_length個(gè)字節(jié)之后才不同的鍵比較相等。
當(dāng)前不支持對(duì)非標(biāo)量值進(jìn)行排序，并且會(huì)出現(xiàn)警告。

對(duì)于排序，將 JSON 標(biāo)量轉(zhuǎn)換為其他一些本機(jī) MySQL 類型可能是有益的。例如，如果名為jdoc的列包含 JSON 對(duì)象，其成員由id鍵和一個(gè)非負(fù)值組成，則使用此 table 達(dá)式按id值進(jìn)行排序：

ORDER BY CAST(JSON_EXTRACT(jdoc, '$.id') AS UNSIGNED)

如果碰巧有一個(gè)生成的列被定義為使用與ORDER BY中相同的 table 達(dá)式，則 MySQL 優(yōu)化器將識(shí)別出該列，并考慮將索引用于查詢執(zhí)行計(jì)劃。

JSON 值的匯總

對(duì)于 JSON 值的匯總，與其他數(shù)據(jù)類型一樣，將忽略 SQL NULL值。非NULL值將轉(zhuǎn)換為數(shù)字類型并進(jìn)行匯總，但MIN()，MAX()和GROUP_CONCAT()除外。盡管可能會(huì)發(fā)生截?cái)嗪途葥p失，但轉(zhuǎn)換為數(shù)字應(yīng)該為數(shù)字標(biāo)量的 JSON 值產(chǎn)生有意義的結(jié)果。轉(zhuǎn)換為其他 JSON 值的數(shù)量可能不會(huì)產(chǎn)生有意義的結(jié)果。