MySQL 5.7.8 引入原生 JSON 資料類型,相比舊做法(TEXT 存序列化字串)具有三大優勢:
CREATE TABLE t1 (jdoc JSON);
INSERT INTO t1 VALUES ('{"key1": "value1", "key2": "value2"}');
INSERT INTO t1 VALUES ('[1, 2, true, false, null, "text"]');
-- 陣列
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"}]
SELECT CAST('{"a":1}' AS JSON); -- 字串轉 JSON
SELECT CAST(json_col AS CHAR); -- JSON 轉字串(utf8mb4)
語法:$ 代表根文檔,後接選取器:
$.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 欄位無法直接建立索引。
解法: 建立 虛擬生成欄位 提取關鍵值,再對生成欄位建索引。
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 > NULLutf8mb4_bin 區分大小寫字典序。NULL (SQL) 排在所有 JSON 值前(ASC)或後(DESC)。| 來源類型 | CAST TO JSON | CAST FROM JSON |
|---|---|---|
| JSON | 無變化 | 無變化 |
| UTF-8 字串 | 解析為 JSON | 序列化為 utf8mb4 字串 |
| 其他字符集 | 隱式轉 utf8mb4 再解析 | 序列化後再轉目標字符集 |
| NULL | JSON null | 不支援 |
| 幾何類型 | ST_AsGeoJSON() | 需 CAST(... AS CHAR) → ST_GeomFromGeoJSON() |
| 數值/布林/日期 | 標量 JSON 文檔 | 若為單一標量且類型相容則成功,否則 NULL + 警告 |
SUM/AVG/MIN/MAX:非數值標量轉數值後聚合,可能截斷/失精。GROUP_CONCAT:將 JSON 序列化為字串後連接。user_id、created_at、status 等高頻查詢/索引欄位,不要塞進 JSON。max_allowed_packet(預設 4MB/64MB),避免單文檔過大。WHERE JSON_EXTRACT(col, '$.k') = 'v' 無法用索引,必須走虛擬生成欄位。