在現(xiàn)代應(yīng)用開發(fā)中，有效管理半結(jié)構(gòu)化數(shù)據(jù)至關(guān)重要。mysql 5.7 版本及以上版本內(nèi)置了對 json 數(shù)據(jù)類型的支持，為關(guān)系型數(shù)據(jù)庫中存儲、查詢和操作這類數(shù)據(jù)提供了強(qiáng)有力的工具。本文將介紹 mysql 提供的核心 json 函數(shù)，并結(jié)合實(shí)際案例進(jìn)行講解，幫助您快速上手。

為什么選擇 mysql 的 JSON 功能？

在關(guān)系型數(shù)據(jù)庫中使用 JSON 數(shù)據(jù)類型，可以簡化半結(jié)構(gòu)化或?qū)蛹墧?shù)據(jù)的處理流程，帶來諸多優(yōu)勢：

• 靈活性: json 結(jié)構(gòu)支持動態(tài)、層級數(shù)據(jù)的靈活存儲。
• 內(nèi)置函數(shù): MySQL 提供高效的 JSON 數(shù)據(jù)查詢、更新和驗(yàn)證函數(shù)。
• 集成性: 可以將關(guān)系型數(shù)據(jù)與 JSON 對象結(jié)合，實(shí)現(xiàn)混合數(shù)據(jù)模型。

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

利用 JSON_OBJECT() 和 JSON_ARRAY() 函數(shù)，可以方便地構(gòu)建 JSON 對象或數(shù)組。

示例：

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ù)類型定義數(shù)據(jù)庫列，即可存儲 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ù)中提取數(shù)據(jù)

MySQL 提供多種函數(shù)用于從 JSON 文檔中提取數(shù)據(jù)：

• JSON_EXTRACT()：使用 JSONPath 表達(dá)式獲取指定值。
• -> 運(yùn)算符：JSON_EXTRACT() 的簡寫形式。

示例：

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

輸出：

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

以下函數(shù)用于更新或添加 JSON 數(shù)據(jù)元素：

• JSON_SET()：插入或更新鍵值對。
• JSON_INSERT()：僅當(dāng)鍵不存在時插入。
• 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() 函數(shù)刪除 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 數(shù)據(jù)中搜索

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;

輸出：

["Bob"]

8. 驗(yàn)證 JSON 數(shù)據(jù)

JSON_VALID() 函數(shù)用于檢查字符串是否為有效的 JSON 數(shù)據(jù)。

示例：

SELECT      JSON_VALID('{"key": "value"}') AS is_valid,     JSON_VALID('invalid json') AS is_invalid;

輸出：

9. 格式化 JSON 輸出

JSON_PRETTY() 函數(shù)將 JSON 數(shù)據(jù)格式化為易于閱讀的格式。

示例：

SELECT JSON_PRETTY(details) AS pretty_json FROM users;

輸出：

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

其他 JSON 函數(shù)

MySQL 提供了豐富的 JSON 函數(shù)，本文僅介紹了部分常用函數(shù)。其他函數(shù)包括：JSON_ARRAY_APPEND()、JSON_ARRAY_INSERT()、JSON_CONTAINS_PATH()、JSON_DEPTH()、JSON_KEYS()、JSON_LENGTH()、JSON_MERGE_PATCH()、JSON_MERGE_PRESERVE()、JSON_OVERLAPS()、JSON_QUOTE()、JSON_SEARCH()、JSON_STORAGE_FREE()、JSON_STORAGE_SIZE()、JSON_TABLE()、JSON_TYPE()、JSON_UNQUOTE() 等。

MySQL 的 JSON 函數(shù)為關(guān)系型數(shù)據(jù)庫中半結(jié)構(gòu)化數(shù)據(jù)的管理提供了強(qiáng)大的支持，簡化了 JSON 數(shù)據(jù)的存儲、查詢和操作，為數(shù)據(jù)庫設(shè)計(jì)提供了新的思路。 熟練掌握這些函數(shù)，將極大地提高開發(fā)效率。