在現(xiàn)代應(yīng)用程式開發(fā)中，管理半結(jié)構(gòu)化資料是一個常見的挑戰(zhàn)。 MySQL 在 5.7 版本中引入了對 JSON 的支持，提供了一種在關(guān)聯(lián)式資料庫中儲存、查詢和操作此類資料的強大方法。這篇文章探討了 MySQL 提供的基本 JSON 函數(shù)，並透過實際範例來幫助入門。

為什麼 MySQL 中要使用 JSON？

在關(guān)聯(lián)式資料庫中儲存 JSON 資料可以簡化半結(jié)構(gòu)化或分層資料的處理。它允許：

• 彈性： JSON 允許動態(tài)和分層資料儲存。
• 內(nèi)建功能：高效查詢、更新和驗證 JSON 資料。
• 整合：將關(guān)聯(lián)式資料與 JSON 物件結(jié)合以實現(xiàn)混合用例。

1. 建立 JSON 數(shù)據(jù)

您可以使用 JSON_OBJECT() 和 JSON_ARRAY() 函式建構(gòu) 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數(shù)據(jù)

要儲存 JSON 數(shù)據(jù)，請使用列的 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 擷取數(shù)據(jù)

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

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

範例：

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

輸出：

4. 修改JSON數(shù)據(jù)

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

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

範例：

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. 刪除鍵或數(shù)值

使用 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() 函數(shù)檢查 JSON 文件是否包含特定值。

範例：

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

輸出：

7. 聚合 JSON 數(shù)據(jù)

JSON_ARRAYAGG() 和 JSON_OBJECTAGG() 函數(shù)有助於將查詢結(jié)果聚合到 JSON 結(jié)構(gòu)中。

範例：

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 數(shù)據(jù)

JSON_VALID() 函數(shù)檢查字串是否是有效的 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 函數(shù)

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

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

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

以上是在 MySQL 中使用 JSON的詳細內(nèi)容。更多資訊請關(guān)注PHP中文網(wǎng)其他相關(guān)文章！