萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL 5.7 JSON 資料類型深度解析:從建立到索引優化的完整指南

為什麼選擇原生 JSON 類型?

MySQL 5.7.8 引入原生 JSON 資料類型,相比舊做法(TEXT 存序列化字串)具有三大優勢:

  1. 自動驗證: 寫入時自動檢查 JSON 合法性,非法文檔直接報錯。
  2. 二進制優化存儲: 內部採用二進制格式,讀取時無需解析文本,支援直接按鍵/索引定位子物件/陣列元素。
  3. 豐富函數生態: 內建 20+ 函數支援建構、查詢、修改、索引、空間運算。

建立 JSON 值的多種方式

1. 字面值插入

CREATE TABLE t1 (jdoc JSON);
INSERT INTO t1 VALUES ('{"key1": "value1", "key2": "value2"}');
INSERT INTO t1 VALUES ('[1, 2, true, false, null, "text"]');

2. 建構函數

-- 陣列
SELECT JSON_ARRAY('a', 1, NOW()); 
-- → ["a", 1, "2024-01-15 10:30:00.000000"]

-- 物件
SELECT JSON_OBJECT('key1', 1, 'key2', 'abc');
-- → {"key1": 1, "key2": "abc"}

-- 合併
SELECT JSON_MERGE('[1, 2]', '{"key": "value"}');
-- → [1, 2, {"key": "value"}]

3. 類型轉換

SELECT CAST('{"a":1}' AS JSON);  -- 字串轉 JSON
SELECT CAST(json_col AS CHAR);   -- JSON 轉字串(utf8mb4)

JSON 路徑表達式:精準定位文檔內部

語法:$ 代表根文檔,後接選取器:

  • $.key → 物件成員
  • $[n] → 陣列第 n 個元素(0 起)
  • $.* → 所有成員/元素
  • $**.key → 遞歸搜尋所有層級的 key

範例


SET @j = '[3, {"a": [5,6], "b": 10}, [99,100]]';

-- $ [0] → 3
-- $ [1].a[1] → 6
-- $ [2][0] → 99
-- $ **.b → [10]  (遞歸搜尋所有 b)

查詢與修改函數實戰

提取值


SELECT JSON_EXTRACT(@j, '$[1].b');        -- → 10
SELECT @j->>'$[1].b';                     -- 簡寫,返回純文字(去引號)
SELECT JSON_UNQUOTE(JSON_EXTRACT(@j, '$[1].b')); -- 等同 ->

修改文檔

函數行為範例
JSON_SET(doc, path, val...)路徑存在則替換,不存在則新增JSON_SET(@j, '$[1].b', 20)
JSON_INSERT(doc, path, val...)僅新增,路徑存在則忽略JSON_INSERT(@j, '$[3]', 'new')
JSON_REPLACE(doc, path, val...)僅替換,路徑不存在則忽略JSON_REPLACE(@j, '$[1].b', 20)
JSON_REMOVE(doc, path...)刪除指定路徑JSON_REMOVE(@j, '$[2]')

實戰:動態更新商品屬性


UPDATE `products`
SET `attrs` = JSON_SET(`attrs`, '$.color', 'red', '$.size', 'XL')
WHERE `id` = 1001;

JSON 索引策略:虛擬生成欄位 + 索引

限制: JSON 欄位無法直接建立索引。
解法: 建立 虛擬生成欄位 提取關鍵值,再對生成欄位建索引。


ALTER TABLE `orders`
ADD COLUMN `customer_email` VARCHAR(100)
    GENERATED ALWAYS AS (JSON_UNQUOTE(JSON_EXTRACT(`extra`, '$.customer.email'))) VIRTUAL,
ADD INDEX `idx_customer_email` (`customer_email`);

-- 查詢自動走索引
SELECT * FROM `orders` WHERE `customer_email` = 'user@example.com';

比較與排序規則

  • 類型優先級:BLOB > BIT > OPAQUE > DATETIME > TIME > DATE > BOOLEAN > ARRAY > OBJECT > STRING > INTEGER/DOUBLE > NULL
  • 同類型比較:陣列按元素順序、物件按鍵排序後比較、字串按 utf8mb4_bin 區分大小寫字典序。
  • NULL (SQL) 排在所有 JSON 值前(ASC)或後(DESC)。

類型轉換規則表

來源類型CAST TO JSONCAST FROM JSON
JSON無變化無變化
UTF-8 字串解析為 JSON序列化為 utf8mb4 字串
其他字符集隱式轉 utf8mb4 再解析序列化後再轉目標字符集
NULLJSON null不支援
幾何類型ST_AsGeoJSON()需 CAST(... AS CHAR) → ST_GeomFromGeoJSON()
數值/布林/日期標量 JSON 文檔若為單一標量且類型相容則成功,否則 NULL + 警告

聚合函數行為

  • SUM/AVG/MIN/MAX:非數值標量轉數值後聚合,可能截斷/失精。
  • GROUP_CONCAT:將 JSON 序列化為字串後連接。
  • 非標量值(物件/陣列)參與聚合會發出警告。

最佳實踐建議

  1. 結構固定的欄位拆為獨立列: 如 user_id、created_at、status 等高頻查詢/索引欄位,不要塞進 JSON。
  2. 動態/稀疏屬性用 JSON: 商品屬性、用戶偏好、日誌上下文等結構多變、查詢不固定的場景。
  3. 控制文檔大小: 受限於 max_allowed_packet(預設 4MB/64MB),避免單文檔過大。
  4. 善用虛擬生成欄位索引: 將高頻過濾的 JSON 路徑提取為虛擬列建索引,獲得接近原生列的查詢效能。
  5. 避免在 WHERE 中對 JSON 欄位直接函數操作: WHERE JSON_EXTRACT(col, '$.k') = 'v' 無法用索引,必須走虛擬生成欄位。
copyright © 萬盛學電腦網 all rights reserved