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

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

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

前言

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

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

創(chuàng)建JSON值

JSON類型字段可以是JSONObject，也可以是JSONArray類型，在 MySQL 中，JSON 值被編寫為字符串。 MySQL 會解析在需要 JSON 值的上下文中使用的任何字符串，如果該字符串作為 JSON 無效，則會產生錯誤。這些上下文包括將值插入具有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計算所有以命名前綴開頭并以命名后綴結尾的路徑。
文檔中不存在的路徑(評估為不存在的數(shù)據(jù))評估為NULL。

在 JSON 和非 JSON 值之間轉換

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

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

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

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

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

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

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

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

JSON 值的匯總

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