在 MySQL 中使用 JSON

在現代應用程式開發中，管理半結構化資料是一個常見的挑戰。 MySQL 在 5.7 版本中引入了對 JSON 的支持，提供了一種在關聯式資料庫中儲存、查詢和操作此類資料的強大方法。這篇文章探討了 MySQL 提供的基本 JSON 函數，並透過實際範例來幫助入門。

為什麼 MySQL 中要使用 JSON？

在關聯式資料庫中儲存 JSON 資料可以簡化半結構化或分層資料的處理。它允許：

• 彈性： JSON 允許動態和分層資料儲存。
• 內建功能：高效查詢、更新和驗證 JSON 資料。
• 整合：將關聯式資料與 JSON 物件結合以實現混合用例。

1. 建立 JSON 數據

您可以使用 JSON_OBJECT() 和 JSON_ARRAY() 函式建構 JSON 物件或陣列。

範例：

SELECT 
    JSON_OBJECT('id', 1, 'name', 'Alice', 'roles', JSON_ARRAY('admin', 'editor')) AS json_data;

輸出：

{"id": 1, "name": "Alice", "roles": ["admin", "editor"]}

2. 儲存JSON數據

要儲存 JSON 數據，請使用列的 JSON 資料類型。

範例：

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    details JSON NOT NULL
);

INSERT INTO users (details) 
VALUES ('{"name": "Bob", "age": 30, "roles": ["viewer", "editor"]}');

3. 從 JSON 擷取數據

MySQL提供了幾個從JSON文件中提取資料的函數：

• JSON_EXTRACT()：使用 JSON 路徑取得值。
• ->運算子：JSON_EXTRACT() 的簡寫。

範例：

SELECT 
    JSON_EXTRACT(details, '$.name') AS name,
    details->'$.age' AS age
FROM users;

輸出：

4. 修改JSON數據

您可以使用以下方法更新或新增元素到 JSON 資料：

• JSON_SET()：插入或更新金鑰。
• JSON_INSERT()：如果 key 不存在則插入。
• JSON_REPLACE()：僅更新現有金鑰。

範例：

UPDATE users
SET details = JSON_SET(details, '$.city', 'New York')
WHERE id = 1;

SELECT details FROM users;

輸出：

{"name": "Bob", "age": 30, "roles": ["viewer", "editor"], "city": "New York"}

5. 刪除鍵或數值

使用 JSON_REMOVE() 從 JSON 文件中刪除元素。

範例：

UPDATE users
SET details = JSON_REMOVE(details, '$.roles')
WHERE id = 1;

SELECT details FROM users;

輸出：

{"name": "Bob", "age": 30, "city": "New York"}

6. 在 JSON 中搜尋

JSON_CONTAINS() 函數檢查 JSON 文件是否包含特定值。

範例：

SELECT 
    JSON_CONTAINS(details, '"New York"', '$.city') AS has_city
FROM users;

輸出：

7. 聚合 JSON 數據

JSON_ARRAYAGG() 和 JSON_OBJECTAGG() 函數有助於將查詢結果聚合到 JSON 結構中。

範例：

SELECT 
    JSON_ARRAYAGG(name) AS names
FROM (
    SELECT JSON_EXTRACT(details, '$.name') AS name FROM users
) AS subquery;

輸出：

SELECT 
    JSON_OBJECT('id', 1, 'name', 'Alice', 'roles', JSON_ARRAY('admin', 'editor')) AS json_data;

8. 驗證 JSON 數據

JSON_VALID() 函數檢查字串是否是有效的 JSON。

範例：

{"id": 1, "name": "Alice", "roles": ["admin", "editor"]}

輸出：

9. 漂亮的印刷 JSON

使用 JSON_PRETTY() 進行人類可讀的 JSON 格式。

範例：

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    details JSON NOT NULL
);

INSERT INTO users (details) 
VALUES ('{"name": "Bob", "age": 30, "roles": ["viewer", "editor"]}');

輸出：

SELECT 
    JSON_EXTRACT(details, '$.name') AS name,
    details->'$.age' AS age
FROM users;

附加 JSON 函數

MySQL 提供了廣泛的 JSON 函數，超出了詳細介紹的範圍。以下是其他 JSON 函數：

• JSON_ARRAY_APPEND()：將值附加到 JSON 陣列的結尾。
• JSON_ARRAY_INSERT()：將值插入 JSON 陣列的指定位置。
• JSON_CONTAINS_PATH()：檢查 JSON 文件中是否存在指定路徑。
• JSON_DEPTH()：傳回 JSON 文件的最大深度。
• JSON_KEYS()：從 JSON 物件中提取金鑰。
• JSON_LENGTH()：計算 JSON 陣列中的元素數量或 JSON 物件中的鍵數量。
• JSON_MERGE_PATCH()：合併多個 JSON 文檔，以最新值覆寫現有鍵。
• JSON_MERGE_PRESERVE()：組合多個 JSON 文檔，透過建立重複項數組來保留所有值。
• JSON_OVERLAPS()：確定兩個 JSON 文件是否有重疊元素。
• JSON_QUOTE()：轉義字串中的特殊字元並將其作為有效的 JSON 字串文字傳回。
• JSON_SEARCH()：傳回 JSON 文件中指定值的路徑。
• JSON_STORAGE_FREE()：傳回修改 JSON 欄位後釋放的空間量。
• JSON_STORAGE_SIZE()：提供 JSON 文件的儲存大小（以位元組為單位）。
• JSON_TABLE()：將JSON資料轉換為關聯式表格式以便進一步處理。
• JSON_TYPE()：傳回 JSON 值的類型（例如物件、陣列、字串等）。
• JSON_UNQUOTE()：從 JSON 字串中刪除引號。

MySQL 的 JSON 函數為管理關係型資料庫中的半結構化資料提供了強大的工具。它們使得直接在 SQL 中儲存、查詢和操作 JSON 變得容易。了解這些函數有助於簡化工作流程並開啟資料庫設計的新方法。

以上是在 MySQL 中使用 JSON的詳細內容。更多資訊請關注PHP中文網其他相關文章！