您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 綜合教學

MySQL 5.7 JSON 數據類型與操作函數完全指南

編輯：MySQL 綜合教學

為什麼選擇 JSON 類型？

MySQL 5.7 原生支持 JSON 類型，相比 VARCHAR/TEXT 存儲：

自動驗證：寫入時檢查 JSON 合法性，非法直接報錯
優化存儲：二進制格式，讀取無需解析文本
豐富函數：路徑提取、搜索、修改、合併、索引（虛擬列）全支持
文檔模型：半結構化數據靈活建模，適合標籤、屬性、配置等場景

建表示例：文章標籤系統

CREATE TABLE `article` (
  `id` MEDIUMINT(8) UNSIGNED NOT NULL AUTO_INCREMENT,
  `title` VARCHAR(200) NOT NULL,
  `tags` JSON DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

核心操作實戰

1. 插入 JSON 數據

-- 數組
INSERT INTO `article` (`title`, `tags`) VALUES 
('體驗 MySQL JSON', '["MySQL", "Database"]');

-- 對象
INSERT INTO `article` (`title`, `tags`) VALUES 
('配置示例', '{"env": "prod", "replicas": 3}');

2. 查詢與搜索

-- 包含特定標籤（數組精確匹配）
SELECT * FROM `article` WHERE JSON_CONTAINS(tags, '["MySQL"]');

-- 模糊搜索（返回路徑）
SELECT * FROM `article` WHERE JSON_SEARCH(tags, 'one', 'Data%') IS NOT NULL;

-- 提取標量值（->> 等價 JSON_UNQUOTE(JSON_EXTRACT())）
SELECT title, tags->>'$[0]' AS first_tag FROM `article`;

-- 提取對象字段
SELECT JSON_EXTRACT('{"id": 1, "name": "mysql"}', '$.name');  -- 返回 "mysql"

3. JSON Path 語法速查

Path	示例數據	結果
`$.num`	`{"num": 123}`	123
`$.arr`	`{"arr": [1,2]}`	[1,2]
`$.arr[1]`	`{"arr": [1,2]}`	2
`$.obj.a`	`{"obj": {"a":3}}`	3
`$**.b`	`{"obj": {"b":4}}`	4（遞歸下降）

4. 修改操作函數對比

函數	用途	特性
`JSON_SET(doc, path, val)`	插入或更新	路徑存在則替換，不存在則創建
`JSON_INSERT(doc, path, val)`	僅插入	路徑已存在則忽略
`JSON_REPLACE(doc, path, val)`	僅替換	路徑不存在則忽略
`JSON_MERGE(doc, val)`	合併數組/對象	數組去重合併，對象後者覆蓋同名鍵
`JSON_ARRAY_APPEND(doc, path, val)`	數組尾部追加	僅支持數組
`JSON_REMOVE(doc, path)`	刪除路徑	支持多路徑批量刪除

5. 實戰更新示例

-- 給包含 "MySQL" 且無 "dev" 的文章添加 "dev" 標簽
UPDATE `article` 
SET tags = JSON_MERGE(tags, '["dev"]')
WHERE JSON_SEARCH(tags, 'one', 'dev') IS NULL
  AND JSON_SEARCH(tags, 'one', 'MySQL') IS NOT NULL;

-- 將第一個標籤 "MySQL" 重命名
UPDATE `article` SET tags = JSON_SET(tags, '$[0]', 'MySQL 5.7.13');

進階：虛擬列索引實現 JSON 字段高效查詢

-- 添加虛擬列提取 tags 首個元素
ALTER TABLE `article` ADD COLUMN first_tag VARCHAR(50) 
  GENERATED ALWAYS AS (JSON_UNQUOTE(JSON_EXTRACT(tags, '$[0]'))) VIRTUAL;

-- 對虛擬列建立普通索引
CREATE INDEX idx_first_tag ON `article` (first_tag);

-- 查詢自動走索引
EXPLAIN SELECT * FROM `article` WHERE first_tag = 'MySQL';