MySQL JSON 教學：資料型、操作方法與最佳實踐完整指南│MySQL & MariaDBの世界

1. 前言

1.1 JSON 的重要性

在現代的網頁開發中，資料交換變得越來越複雜。JSON（JavaScript Object Notation）作為一種輕量且結構化的資料格式，被廣泛應用於資料傳輸與儲存。MySQL 自 5.7 版開始支援 JSON 資料型，使得在資料庫中操作 JSON 資料更加便利。

1.2 在 MySQL 中使用 JSON

本文將從 MySQL 中 JSON 的基本操作、效能考量，到實務應用進行詳細說明。無論是初學者還是進階使用者，都能透過本文掌握如何有效運用 JSON。

2. MySQL 中的 JSON 是什麼

2.1 JSON 的基礎

JSON 是一種以鍵值配對（Key-Value Pair）來組織資料的簡單格式。它被廣泛用於 Web API 與資料交換，特點是輕量且可讀性高。在 MySQL 中，可以使用 JSON 資料型將 JSON 直接儲存在資料庫中並進行操作。

2.2 MySQL 的 JSON 資料型

MySQL 5.7 引入了 JSON 資料型，其磁碟空間需求與 LONGBLOB 或 LONGTEXT 類似。此外，為了確保資料正確性，MySQL 在插入時會自動驗證 JSON 格式，避免錯誤資料寫入。

2.3 JSON 的使用情境

在 MySQL 中使用 JSON 的常見情境如下：

儲存複雜的資料結構
直接保存從 API 取得的原始資料
管理結構變動頻繁的資料

3. MySQL JSON 的基本操作

3.1 建立 JSON 欄位

要建立可儲存 JSON 的欄位，可以使用 json 資料型：

CREATE TABLE json_data (
    doc JSON
);

3.2 插入 JSON 資料

插入 JSON 資料時可使用 INSERT，MySQL 會檢查資料是否為正確 JSON 格式，否則回傳錯誤：

INSERT INTO json_data(doc) VALUES ('{"a": {"b": ["c", "d"]}, "e": "f"}');

也可利用 JSON_OBJECT 來動態生成 JSON：

INSERT INTO json_data(doc) VALUES (JSON_OBJECT('key1', 'value1', 'key2', 'value2'));

3.3 查詢 JSON 資料

查詢 JSON 內容可使用 JSON_EXTRACT：

SELECT * FROM json_data WHERE JSON_EXTRACT(doc, '$.e') = 'f';

簡寫語法則可使用 ->：

SELECT * FROM json_data WHERE doc->'$.e' = 'f';

3.4 更新 JSON 資料

要部分更新 JSON，可使用 JSON_SET：

UPDATE json_data SET doc = JSON_SET(doc, '$.a.b[0]', 'new_value');

4. 效能考量

4.1 插入效能

將資料插入 JSON 欄位與 TEXT 欄位的效能差異不大。測試結果顯示，在插入 5 萬筆資料時，JSON 型別與 TEXT 型別所需時間相近。

4.2 更新效能

使用 JSON_SET 可針對指定欄位進行更新，而非整筆覆蓋，能有效提升效能。在 5 萬筆資料的部分更新中，也能保持高效率。

5. MySQL 使用 JSON 的最佳實踐

5.1 適合使用 JSON 的場景

JSON 適合用於儲存複雜或結構變動頻繁的資料。但對於固定結構的資料，傳統關聯式表格仍較有效率。

5.2 JSON 資料的索引化

MySQL 可透過虛擬欄位（Virtual Column）來為 JSON 欄位建立索引，進一步提升查詢效能：

ALTER TABLE json_data ADD COLUMN e_value VARCHAR(255) AS (doc->'$.e'), ADD INDEX (e_value);

5.3 常見陷阱與避免方式

避免過度使用 JSON 欄位，應充分利用關聯式資料庫的優勢。
建立合適的索引來確保查詢效率。
避免 JSON 資料過於龐大，必要時進行正規化。

6. MySQL 的進階 JSON 函數

6.1 其他 JSON 函數

MySQL 提供多種 JSON 函數，例如 JSON_APPEND 可在尾端新增元素，JSON_REMOVE 可刪除指定欄位：

-- 新增資料
UPDATE json_data SET doc = JSON_APPEND(doc, '$.a.b', 'new_element');

-- 刪除資料
UPDATE json_data SET doc = JSON_REMOVE(doc, '$.a.b[0]');

6.2 與 SQL 函數結合

JSON 函數可與傳統 SQL 函數結合，撰寫更複雜的查詢。例如，可在 GROUP BY 或 ORDER BY 中使用 JSON：

SELECT JSON_EXTRACT(doc, '$.e') AS e_value, COUNT(*) FROM json_data GROUP BY e_value;

7. 結論

本文介紹了 MySQL JSON 的基本操作到進階功能。透過 MySQL 的 JSON 支援，能更方便地儲存與操作複雜資料。結合效能調校與最佳實踐，可進一步提升資料管理效率。

MySQL JSON 教學：資料型、操作方法與最佳實踐完整指南