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

為什么 MySQL 中使用 JSON？

在關(guān)系數(shù)據(jù)庫中存儲(chǔ) JSON 數(shù)據(jù)可以簡化半結(jié)構(gòu)化或分層數(shù)據(jù)的處理。它允許：

• 靈活性： JSON 允許動(dòng)態(tài)和分層數(shù)據(jù)存儲(chǔ)。
• 內(nèi)置功能：高效查詢、更新和驗(yàn)證 JSON 數(shù)據(jù)。
• 集成：將關(guān)系數(shù)據(jù)與 JSON 對(duì)象相結(jié)合以實(shí)現(xiàn)混合用例。

1. 創(chuàng)建 JSON 數(shù)據(jù)

您可以使用 JSON_OBJECT() 和 JSON_ARRAY() 函數(shù)構(gòu)造 JSON 對(duì)象或數(shù)組。

示例：

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

輸出：

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

2. 存儲(chǔ)JSON數(shù)據(jù)

要存儲(chǔ) JSON 數(shù)據(jù)，請(qǐng)使用列的 JSON 數(shù)據(jù)類型。

示例：

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提供了幾個(gè)從JSON文檔中提取數(shù)據(jù)的函數(shù)：

• JSON_EXTRACT()：使用 JSON 路徑獲取值。
• ->運(yùn)算符：JSON_EXTRACT() 的簡寫。

示例：

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

輸出：

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

您可以使用以下方法更新或添加元素到 JSON 數(shù)據(jù)：

• 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. 刪除鍵或值

使用 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. 驗(yàn)證 JSON 數(shù)據(jù)

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

示例：

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

輸出：

9. 漂亮的打印 JSON

使用 JSON_PRETTY() 進(jìn)行人類可讀的 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ù)，超出了詳細(xì)介紹的范圍。以下是其他 JSON 函數(shù)：

• JSON_ARRAY_APPEND()：將值附加到 JSON 數(shù)組的末尾。
• JSON_ARRAY_INSERT()：將值插入到 JSON 數(shù)組的指定位置。
• JSON_CONTAINS_PATH()：檢查 JSON 文檔中是否存在指定路徑。
• JSON_DEPTH()：返回 JSON 文檔的最大深度。
• JSON_KEYS()：從 JSON 對(duì)象中提取密鑰。
• JSON_LENGTH()：計(jì)算 JSON 數(shù)組中的元素?cái)?shù)量或 JSON 對(duì)象中的鍵數(shù)量。
• JSON_MERGE_PATCH()：合并多個(gè) JSON 文檔，用最新值覆蓋現(xiàn)有鍵。
• JSON_MERGE_PRESERVE()：組合多個(gè) JSON 文檔，通過創(chuàng)建重復(fù)項(xiàng)數(shù)組來保留所有值。
• JSON_OVERLAPS()：確定兩個(gè) JSON 文檔是否有重疊元素。
• JSON_QUOTE()：轉(zhuǎn)義字符串中的特殊字符并將其作為有效的 JSON 字符串文字返回。
• JSON_SEARCH()：返回 JSON 文檔中指定值的路徑。
• JSON_STORAGE_FREE()：返回修改 JSON 列后釋放的空間量。
• JSON_STORAGE_SIZE()：提供 JSON 文檔的存儲(chǔ)大小（以字節(jié)為單位）。
• JSON_TABLE()：將JSON數(shù)據(jù)轉(zhuǎn)換為關(guān)系表格式以供進(jìn)一步處理。
• JSON_TYPE()：返回 JSON 值的類型（例如對(duì)象、數(shù)組、字符串等）。
• JSON_UNQUOTE()：從 JSON 字符串中刪除引號(hào)。

MySQL 的 JSON 函數(shù)為管理關(guān)系數(shù)據(jù)庫中的半結(jié)構(gòu)化數(shù)據(jù)提供了強(qiáng)大的工具。它們使得直接在 SQL 中存儲(chǔ)、查詢和操作 JSON 變得容易。了解這些函數(shù)有助于簡化工作流程并開辟數(shù)據(jù)庫設(shè)計(jì)的新方法。

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