MySQL中JSON欄位資料型別詳解

2022-06-14 14:01:23

前言

JSON 型別是從 MySQL 5.7 版本開始支援的功能，MySQL 支援由RFC 7159定義的本機JSON資料型別，該型別可有效存取 JSON(JavaScript 物件 table 示法)檔案中的資料。與將 JSON 格式的字串儲存在字串列中相比，JSON資料型別具有以下優點：

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

建立JSON值

JSON型別欄位可以是JSONObject，也可以是JSONArray型別，在 MySQL 中，JSON 值被編寫為字串。 MySQL 會解析在需要 JSON 值的上下文中使用的任何字串，如果該字串作為 JSON 無效，則會產生錯誤。這些上下文包括將值插入具有JSON資料型別的列中，以及將引數傳遞給需要 JSON 值的函數(在 MySQL JSON 函數的檔案中通常顯示為* json_doc 或 json_val *)。

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

搜尋JSON型別資料

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

路徑可以包含*或**萬用字元：
.[*]求值為 JSON 物件中所有成員的值。
- [*]計算 JSON 陣列中所有元素的值。
- prefix**suffix計算所有以命名字首開頭並以命名字尾結尾的路徑。
檔案中不存在的路徑(評估為不存在的資料)評估為NULL。

在 JSON 和非 JSON 值之間轉換

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

table11.3 JSON 轉換規則

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 值的排序使用與前面的討論相同的規則。
對於升序排序，SQL NULL在所有 JSON 值(包括 JSON 空 Literals)之前進行排序；對於降序排序，SQL NULL對所有 JSON 值(包括 JSON 空 Literals)進行排序。
JSON 值的排序鍵受max_sort_length系統變數的值限制，因此僅在前max_sort_length個位元組之後才不同的鍵比較相等。
當前不支援對非標量值進行排序，並且會出現警告。

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

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

如果碰巧有一個生成的列被定義為使用與ORDER BY中相同的 table 達式，則 MySQL 優化器將識別出該列，並考慮將索參照於查詢執行計劃。

JSON 值的彙總

對於 JSON 值的彙總，與其他資料型別一樣，將忽略 SQL NULL值。非NULL值將轉換為數位型別並進行彙總，但MIN()，MAX()和GROUP_CONCAT()除外。儘管可能會發生截斷和精度損失，但轉換為數位應該為數位標量的 JSON 值產生有意義的結果。轉換為其他 JSON 值的數量可能不會產生有意義的結果。

總結

到此這篇關於MySQL中JSON欄位資料型別的文章就介紹到這了,更多相關MySQL JSON欄位資料型別內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！

MySQL中JSON欄位資料型別詳解

目錄

前言

建立JSON值

搜尋JSON型別資料

在 JSON 和非 JSON 值之間轉換

JSON 值的彙總

總結

熱門文章