在 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中文网其他相关文章！