在現代應用開發中，有效管理半結構化數據至關重要。mysql 5.7 版本及以上版本內置了對 json 數據類型的支持，為關系型數據庫中存儲、查詢和操作這類數據提供了強有力的工具。本文將介紹 mysql 提供的核心 json 函數，并結合實際案例進行講解，幫助您快速上手。
為什么選擇 mysql 的 JSON 功能？
在關系型數據庫中使用 JSON 數據類型，可以簡化半結構化或層級數據的處理流程，帶來諸多優勢：
- 靈活性: JSON 結構支持動態、層級數據的靈活存儲。
- 內置函數: MySQL 提供高效的 JSON 數據查詢、更新和驗證函數。
- 集成性: 可以將關系型數據與 JSON 對象結合，實現混合數據模型。
-
創建 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"]}
-
存儲 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"]}');
-
從 JSON 數據中提取數據
MySQL 提供多種函數用于從 JSON 文檔中提取數據：
示例：
SELECT JSON_EXTRACT(details, '$.name') AS name, details->'$.age' AS age FROM users;
輸出：
-
修改 JSON 數據
以下函數用于更新或添加 JSON 數據元素：
示例：
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"}
-
刪除鍵值對
使用 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"}
-
在 JSON 數據中搜索
JSON_CONTAINS() 函數用于檢查 JSON 文檔是否包含特定值。
示例：
SELECT JSON_CONTAINS(details, '"New York"', '$.city') AS has_city FROM users;
輸出：
-
JSON 數據聚合
JSON_ARRAYAGG() 和 JSON_OBJECTAGG() 函數可以將查詢結果聚合為 JSON 結構。
示例：
SELECT JSON_ARRAYAGG(name) AS names FROM ( SELECT JSON_EXTRACT(details, '$.name') AS name FROM users ) AS subquery;
輸出：
["Bob"]
-
驗證 JSON 數據
JSON_VALID() 函數用于檢查字符串是否為有效的 JSON 數據。
示例：
SELECT JSON_VALID('{"key": "value"}') AS is_valid, JSON_VALID('invalid json') AS is_invalid;
輸出：
-
格式化 JSON 輸出
JSON_PRETTY() 函數將 JSON 數據格式化為易于閱讀的格式。
示例：
SELECT JSON_PRETTY(details) AS pretty_json FROM users;
輸出：
{ "name": "Bob", "age": 30, "city": "New York" }
其他 JSON 函數
MySQL 提供了豐富的 JSON 函數，本文僅介紹了部分常用函數。其他函數包括：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 函數為關系型數據庫中半結構化數據的管理提供了強大的支持，簡化了 JSON 數據的存儲、查詢和操作，為數據庫設計提供了新的思路。 熟練掌握這些函數，將極大地提高開發效率。
