1. Introduction
需要在 MySQL 處理陣列式資料
在資料庫中，資料通常以關聯式設計儲存。然而，根據應用需求，有時將多個值存於同一欄位會更方便。此時，像「陣列」這樣的資料結構就能派上用場。
例如，考慮以下情境：
- 存儲使用者選擇的多個標籤。
- 為產品保存多個圖片 URL。
- 將歷史或日誌合併到一個欄位。
利用 JSON 類型的優勢
MySQL 並沒有直接的「陣列型別」，但透過 JSON 類型，即可處理陣列式資料。JSON 類型高度靈活，具備以下優點：
- 支援巢狀資料結構。
- 可在查詢中直接進行資料操作。
- 能在單一欄位管理多種資料格式。
本文將介紹如何使用 JSON 類型在 MySQL 中有效處理陣列式資料。
2. Basics of handling arrays with MySQL’s JSON type
MySQL 的 JSON 類型是什麼？
JSON（JavaScript Object Notation）是一種輕量級且簡單的資料交換格式。自 MySQL 5.7 版本起，支援原生 JSON 類型，可直接將 JSON 格式資料儲存並操作於資料庫中。
範例：以下資料可以存於 JSON 類型欄位：
{
"tags": ["PHP", "MySQL", "JSON"],
"status": "published"
}
JSON 類型的優點與使用案例
使用 JSON 類型的主要優點為：
- 靈活的資料結構：可處理可變長度資料，而不必改動關聯式資料表結構。
- 高效的資料操作：利用 MySQL 內建函式（例如
JSON_EXTRACT、JSON_ARRAY等）即可輕鬆操作資料。 - 可實現無模式設計：隨著應用規格變更，不必頻繁修改資料表結構。
使用案例：
- 為產品資訊指派多個分類。
- 儲存使用者自訂設定。
- 在網頁應用程式中處理巢狀 JSON 資料。
3. Basic operations on JSON arrays
建立 JSON 陣列
在 MySQL 中，您可以使用 JSON_ARRAY 函式輕鬆建立 JSON 格式的陣列。當您想在單一欄位中存放多個值時，陣列非常實用。
範例使用
以下查詢建立名為 tags 的 JSON 陣列。
SELECT JSON_ARRAY('PHP', 'MySQL', 'JavaScript') AS tags;
執行結果：
["PHP", "MySQL", "JavaScript"]
實際應用
以下示例透過 INSERT 語句將 JSON 陣列儲存到資料庫中。
CREATE TABLE articles (
id INT AUTO_INCREMENT PRIMARY KEY,
tags JSON
);
INSERT INTO articles (tags)
VALUES (JSON_ARRAY('PHP', 'MySQL', 'JavaScript'));
從 JSON 陣列中提取資料
要提取存於 JSON 陣列中的資料，請使用 JSON_EXTRACT 函式。它能讓您從陣列中擷取特定元素。
範例使用
以下示例擷取陣列中第二個元素（0 為基底索引）。
SELECT JSON_EXTRACT('["PHP", "MySQL", "JavaScript"]', '$[1]') AS second_tag;
執行結果：
"MySQL"
同時擷取多個元素
您也可以一次擷取多個元素。
SELECT JSON_EXTRACT('["PHP", "MySQL", "JavaScript"]', '$[0]', '$[2]') AS extracted_values;
加入、更新與刪除資料
將資料加入陣列
使用 JSON_ARRAY_APPEND 函式，您可將新資料追加至已存在的陣列中。
SET @tags = '["PHP", "MySQL"]';
SELECT JSON_ARRAY_APPEND(@tags, '$', 'JavaScript') AS updated_tags;
執行結果：
["PHP", "MySQL", "JavaScript"]
更新陣列內的資料
您可使用 JSON_SET 更新陣列內的指定元素。
SET @tags = '["PHP", "MySQL", "JavaScript"]';
SELECT JSON_SET(@tags, '$[1]', 'Python') AS updated_tags;
執行結果：
["PHP", "Python", "JavaScript"]
從陣列中移除資料
Use JSON_REMOVE to delete a specific element inside the array.
SET @tags = '["PHP", "MySQL", "JavaScript"]';
SELECT JSON_REMOVE(@tags, '$[1]') AS updated_tags;
結果:
["PHP", "JavaScript"]
4. 搜尋與過濾 JSON 陣列
搜尋包含特定資料的陣列
要判斷 JSON 陣列是否包含特定值，可使用 JSON_CONTAINS 函式。該函式會回傳指定的 JSON 陣列是否包含給定的值。
範例使用
以下範例中，我們檢查 JSON 陣列是否包含「MySQL」。
SELECT JSON_CONTAINS('["PHP", "MySQL", "JavaScript"]', '"MySQL"') AS is_present;
結果:
1 (if present)
0 (if not present)
應用範例：條件搜尋
若要查詢資料庫中包含特定值的 JSON 陣列列，您可以在 WHERE 子句中使用 JSON_CONTAINS。
SELECT *
FROM articles
WHERE JSON_CONTAINS(tags, '"MySQL"');
此查詢會檢索 tags 欄位包含「MySQL」的列。
獲取陣列長度
要取得 JSON 陣列中的元素數量，請使用 JSON_LENGTH 函式。此功能對資料分析或條件篩選很有用。
範例使用
以下範例中，我們取得陣列中的元素數量。
SELECT JSON_LENGTH('["PHP", "MySQL", "JavaScript"]') AS array_length;
結果:
3
實作範例：擷取符合條件的列
要擷取元素數量等於或大於某個值的列，請在 WHERE 子句中使用 JSON_LENGTH。
SELECT *
FROM articles
WHERE JSON_LENGTH(tags) >= 2;
此查詢會選擇 tags 欄位包含兩個或更多元素的列。
與條件查詢結合的實作範例
您可以結合多個條件以進行更進階的搜尋。以下查詢會找出 tags 陣列包含「JavaScript」且具有三個或以上元素的列。
SELECT *
FROM articles
WHERE JSON_CONTAINS(tags, '"JavaScript"')
AND JSON_LENGTH(tags) >= 3;
5. 實務使用案例：透過真實情境學習 JSON 陣列
如何將產品分類儲存為 JSON 陣列
在電商網站中，產品可能屬於多個分類。此時使用 JSON 陣列儲存分類資訊會更有效率。
範例：為產品儲存分類資料
以下示範建立一個名為 categories 的 JSON 欄位，並儲存多個分類。
CREATE TABLE products (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL,
categories JSON
);
INSERT INTO products (name, categories)
VALUES ('Laptop', JSON_ARRAY('Electronics', 'Computers')),
('Smartphone', JSON_ARRAY('Electronics', 'Mobile Devices'));
此資料結構即使產品屬於多個分類，也能簡潔儲存。
查詢屬於特定分類的產品
利用 JSON 類型，您可以輕鬆搜尋屬於特定分類的產品。
查詢範例
以下查詢會找出「電子產品」分類中的所有產品。
SELECT name
FROM products
WHERE JSON_CONTAINS(categories, '"Electronics"');
結果:
Laptop
Smartphone
此查詢允許您按分類靈活地檢索產品清單。
範例：按價格區間篩選
以下示範如何以 JSON 儲存價格資訊，並根據價格區間查詢產品。
資料範例
以下使用 JSON 類型為每個項目儲存產品價格資訊。
CREATE TABLE products_with_prices (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL,
details JSON
);
INSERT INTO products_with_prices (name, details)
VALUES ('Laptop', '{"price": 150000, "categories": ["Electronics", "Computers"]}'),
('Smartphone', '{"price": 80000, "categories": ["Electronics", "Mobile Devices"]}');
查詢範例
若要搜尋價格為 100,000 或以上的產品，您可使用 JSON_EXTRACT。
SELECT name
FROM products_with_prices
WHERE JSON_EXTRACT(details, '$.price') >= 100000;
結果：
Laptop
使用 JSON_TABLE 進行擴展與查詢範例
當您想以關聯式方式查詢 JSON 數據時，可以使用 JSON_TABLE。該函式允許您將 JSON 陣列擴展為虛擬表。
範例使用
以下是一個示例，將 JSON 陣列擴展，並將每個分類顯示為獨立列。
SELECT *
FROM JSON_TABLE(
'["Electronics", "Computers", "Mobile Devices"]',
'$[*]' COLUMNS(
category_name VARCHAR(100) PATH '$'
)
) AS categories_table;
結果：
category_name
--------------
Electronics
Computers
Mobile Devices
6. 使用 JSON 類型時的注意事項
效能優化重點
雖然 JSON 類型非常靈活，但若未妥善設計，可能會對資料庫效能產生負面影響。以下列出關鍵的效能優化要點。
1. 建立索引
在 MySQL 中，您無法直接對 JSON 欄位本身設定索引，但可以創建虛擬欄位並對特定鍵建立索引。
範例：透過虛擬欄位建立索引
在此示例中，我們對 JSON 數據中的 price 鍵進行索引。
ALTER TABLE products_with_prices
ADD COLUMN price INT AS (JSON_EXTRACT(details, '$.price')) STORED,
ADD INDEX idx_price (price);
透過使用虛擬欄位，可以大幅提升對 JSON 類型數據的搜尋效能。
2. 避免過於複雜的 JSON 結構
深度巢狀的 JSON 結構會降低查詢的可讀性並影響效能。在設計資料時，盡量採用盡可能簡單的 JSON 結構。
良好範例：
{
"categories": ["Electronics", "Computers"],
"price": 150000
}
應避免的結構：
{
"product": {
"details": {
"price": 150000,
"categories": ["Electronics", "Computers"]
}
}
}
如何有效使用索引
在使用虛擬欄位建立索引時，必須考慮以下要點：
- 虛擬欄位必須設定為
STORED。 - 使用
JSON_EXTRACT將特定鍵抽取到虛擬欄位。
例如，為了抽取 categories 鍵值並對其建立索引：
ALTER TABLE products
ADD COLUMN main_category VARCHAR(255) AS (JSON_EXTRACT(categories, '$[0]')) STORED,
ADD INDEX idx_main_category (main_category);
資料驗證的重要性
儘管 JSON 類型資料具有彈性，但也可能導致儲存不正確的格式。為維護資料完整性，請採用以下方式：
1. 使用 CHECK 約束
自 MySQL 8.0 起，您可以使用 CHECK 約束來驗證 JSON 資料的結構或內容。
ALTER TABLE products_with_prices
ADD CONSTRAINT check_price CHECK (JSON_EXTRACT(details, '$.price') >= 0);
2. 在應用程式層級進行驗證
插入資料時，應在應用程式層級驗證 JSON 格式。在 PHP 或 Python 等程式語言中，您可以使用標準函式庫進行 JSON 驗證。
7. MySQL 中使用陣列式資料的常見問答
Q1: MySQL 是否提供陣列類型？
A1: MySQL 並未提供直接的「陣列類型」。不過，您可以使用 JSON 類型處理陣列式資料。透過 JSON 類型，您可在一個欄位儲存多個值並透過查詢進行操作。
範例：
SELECT JSON_ARRAY('value1', 'value2', 'value3') AS example_array;
結果：
["value1", "value2", "value3"]
Q2: 您能為 JSON 類型資料設定索引嗎？
A2: 您無法直接對 JSON 類型本身設定索引。然而，您可以將特定鍵或值抽取為虛擬欄位，然後對該欄位設定索引。
範例：
ALTER TABLE products_with_prices
ADD COLUMN price INT AS (JSON_EXTRACT(details, '$.price')) STORED,
ADD INDEX idx_price (price);
%
Q3：JSON 資料是否有限制大小？
A3： MySQL 的 JSON 類型最多可儲存 4 GB 的資料。然而使用非常大的 JSON 資料可能會降低效能，因而正確的資料設計是必要的。
建議事項：
- 只儲存必要的最小資料。
- 避免過度巢狀的 JSON 結構。
Q4：如何更新 JSON 陣列中的特定元素？
A4： 您可以使用 JSON_SET 函數來更新 JSON 陣列中的特定元素。
範例：
SET @tags = '["PHP", "MySQL", "JavaScript"]';
SELECT JSON_SET(@tags, '$[1]', 'Python') AS updated_tags;
結果：
["PHP", "Python", "JavaScript"]
Q5：JSON 類型與傳統表格設計之比較
A5： JSON 類型提供高度彈性，但與傳統關聯式設計在本質上有差異。
| Item | JSON type | 傳統表格設計 |
|---|---|---|
| 彈性 | 高（不需要更改資料表結構） | 已修正（需要更改結構） |
| 效能 | 在某些操作中較差 | 優化 |
| 查詢複雜度 | 需要 JSON 函式 | Simple |
| 索引 | 部分支援虛擬欄位 | 完全支援 |
8. 摘要
使用 MySQL 的 JSON 類型處理陣列式資料的優點
本文說明了 MySQL 中處理陣列式資料的 JSON 類型。主要涵蓋的重點包括：
為什麼使用 JSON 類型
MySQL 缺乏直接的陣列類型，但透過使用 JSON 類型，您可以在單一欄位中儲存多個值並執行彈性的資料操作。基本 JSON 操作
- 學會如何建立 JSON 陣列、抽取資料、更新及刪除值。
- 使用
JSON_ARRAY、JSON_EXTRACT、JSON_SET等函式高效操作陣列式資料。
- 搜尋與過濾
- 使用
JSON_CONTAINS搜尋陣列內的特定值。 - 使用
JSON_LENGTH取得陣列長度並執行條件過濾。
實務用例
我們學到了具體的應用情境，例如管理產品類別與按價格過濾。注意事項與優化
- 討論如何透過虛擬欄位索引 JSON 資料，以及驗證 JSON 資料的重要性。
利用 JSON 類型的下一步
透過在 MySQL 中使用 JSON 類型，您可以比傳統關聯式資料庫設計更彈性地管理資料。然而，正確的設計與效能考量仍然是關鍵。
下一階段要學習的主題：
- 使用複合索引：設計將 JSON 類型欄位與常規欄位結合的索引。
- 進階 JSON 函式：使用
JSON_MERGE、JSON_OBJECT等函式進行更複雜的操作。 - 應用層資料操作：使用 PHP、Python 等語言高效操作 MySQL JSON 資料。
最終總結
透過本文，您已學會如何使用 MySQL 的 JSON 類型高效處理陣列式資料。將此知識應用於實務，可協助您設計更具彈性與可擴充性的資料庫系統。
