MySQL中JSON字段數據類型詳解

Posted on by

前言

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字段數據類型內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet！

推薦閱讀：