在 MySQL 中使用 JSON-mysql教程-PHP中文网

在 MySQL 中使用 JSON

霞舞

发布： 2025-01-09 10:39:44

原创

1505人浏览过

在现代应用开发中，有效管理半结构化数据至关重要。mysql 5.7 版本及以上版本内置了对 json 数据类型的支持，为关系型数据库中存储、查询和操作这类数据提供了强有力的工具。本文将介绍 mysql 提供的核心 json 函数，并结合实际案例进行讲解，帮助您快速上手。

为什么选择 MySQL 的 JSON 功能？

在关系型数据库中使用 JSON 数据类型，可以简化半结构化或层级数据的处理流程，带来诸多优势：

灵活性: JSON 结构支持动态、层级数据的灵活存储。
内置函数: MySQL 提供高效的 JSON 数据查询、更新和验证函数。
集成性: 可以将关系型数据与 JSON 对象结合，实现混合数据模型。

创建 JSON 数据

利用 JSON_OBJECT() 和 JSON_ARRAY() 函数，可以方便地构建 JSON 对象或数组。

示例：

<code class="sql">SELECT 
    JSON_OBJECT('id', 1, 'name', 'Alice', 'roles', JSON_ARRAY('admin', 'editor')) AS json_data;</code>

登录后复制

输出：

<code class="json">{"id": 1, "name": "Alice", "roles": ["admin", "editor"]}</code>

登录后复制

存储 JSON 数据

使用 JSON 数据类型定义数据库列，即可存储 JSON 数据。

示例：

<code class="sql">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"]}');</code>

登录后复制

从 JSON 数据中提取数据

MySQL 提供多种函数用于从 JSON 文档中提取数据：

JSON_EXTRACT()：使用 JSONPath 表达式获取指定值。
-> 运算符：JSON_EXTRACT() 的简写形式。

示例：

<code class="sql">SELECT 
    JSON_EXTRACT(details, '$.name') AS name,
    details->'$.age' AS age
FROM users;</code>

登录后复制

输出：

在 MySQL 中使用 JSON

修改 JSON 数据

以下函数用于更新或添加 JSON 数据元素：

JSON_SET()：插入或更新键值对。
JSON_INSERT()：仅当键不存在时插入。
JSON_REPLACE()：仅更新已存在的键值对。

示例：

<code class="sql">UPDATE users
SET details = JSON_SET(details, '$.city', 'New York')
WHERE id = 1;

SELECT details FROM users;</code>

登录后复制

输出：

<code class="json">{"name": "Bob", "age": 30, "roles": ["viewer", "editor"], "city": "New York"}</code>

登录后复制

删除键值对

使用 JSON_REMOVE() 函数删除 JSON 文档中的元素。

示例：

<code class="sql">UPDATE users
SET details = JSON_REMOVE(details, '$.roles')
WHERE id = 1;

SELECT details FROM users;</code>

登录后复制

输出：

<code class="json">{"name": "Bob", "age": 30, "city": "New York"}</code>

登录后复制

在 JSON 数据中搜索

JSON_CONTAINS() 函数用于检查 JSON 文档是否包含特定值。

Find JSON Path Online

Easily find JSON paths within JSON objects using our intuitive Json Path Finder

查看详情

示例：

<code class="sql">SELECT 
    JSON_CONTAINS(details, '"New York"', '$.city') AS has_city
FROM users;</code>

登录后复制

输出：

在 MySQL 中使用 JSON

JSON 数据聚合

JSON_ARRAYAGG() 和 JSON_OBJECTAGG() 函数可以将查询结果聚合为 JSON 结构。

示例：

<code class="sql">SELECT 
    JSON_ARRAYAGG(name) AS names
FROM (
    SELECT JSON_EXTRACT(details, '$.name') AS name FROM users
) AS subquery;</code>

登录后复制

输出：

<code class="json">["Bob"]</code>

登录后复制

验证 JSON 数据

JSON_VALID() 函数用于检查字符串是否为有效的 JSON 数据。

示例：

<code class="sql">SELECT 
    JSON_VALID('{"key": "value"}') AS is_valid,
    JSON_VALID('invalid json') AS is_invalid;</code>

登录后复制

输出：

在 MySQL 中使用 JSON

格式化 JSON 输出

JSON_PRETTY() 函数将 JSON 数据格式化为易于阅读的格式。

示例：

<code class="sql">SELECT JSON_PRETTY(details) AS pretty_json FROM users;</code>

登录后复制

输出：

<code class="json">{
    "name": "Bob",
    "age": 30,
    "city": "New York"
}</code>

登录后复制

其他 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() 等。