快捷導(dǎo)航

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

更新時間：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定義的本機JSON數(shù)據(jù)類型，該類型可有效訪問 JSON(JavaScript 對象 table 示法)文檔中的數(shù)據(jù)。與將 JSON 格式的字符串存儲在字符串列中相比，JSON數(shù)據(jù)類型具有以下優(yōu)點：

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

創(chuàng)建JSON值

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

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

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

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

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

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

下 table 概述了在 JSON 值和其他類型的值之間進(jìn)行轉(zhuǎn)換時 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é)果是由單個標(biāo)量值組成的 JSON 文檔。	如果 JSON 文檔由目標(biāo)類型的單個標(biāo)量值組成，并且該標(biāo)量值可以轉(zhuǎn)換為目標(biāo)類型，則成功。否則，返回`NULL`并產(chǎn)生警告。

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

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

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

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

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

JSON 值的匯總

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