在现代应用中，非结构化数据如 JSON 格式广泛应用于存储复杂的配置信息、动态表单以及嵌套数据。MySQL 从 5.7 版本起正式支持 JSON 数据类型，允许开发者直接在数据库中存储和查询 JSON 格式的数据。
本文将介绍如何使用 MySQL 的 JSON 函数 处理复杂的嵌套 JSON 字段，解析和查询非结构化数据。
一、MySQL JSON 数据类型与函数概览
1. JSON 数据类型特点
- 灵活性：JSON 可存储复杂的嵌套对象和数组，字段动态可变。
- 自动校验：存储 JSON 时，MySQL 自动校验 JSON 格式，不合法的数据将拒绝写入。
- 二进制存储：MySQL 以高效的二进制格式存储 JSON，查询性能优于字符串处理。
2. 常用 JSON 函数
| 函数 | 说明 | 示例 |
|---|---|---|
| JSON_EXTRACT() | 提取 JSON 字段中的特定路径数据 | JSON_EXTRACT(config, '$.name') |
| JSON_UNQUOTE() | 去除提取的 JSON 字符串的引号 | JSON_UNQUOTE(JSON_EXTRACT(config, '$.name')) |
| JSON_SEARCH() | 在 JSON 字段中搜索特定值，返回路径 | JSON_SEARCH(config, 'one', 'admin') |
| JSON_CONTAINS() | 检查 JSON 中是否包含指定的键值 | JSON_CONTAINS(config, '{"role": "user"}') |
| JSON_KEYS() | 获取 JSON 对象中的所有键 | JSON_KEYS(config) |
| JSON_SET() | 更新或添加 JSON 字段的某个路径 | JSON_SET(config, '$.email', 'new@mail.com') |
| JSON_REMOVE() | 删除 JSON 字段中指定路径的键值 | JSON_REMOVE(config, '$.password') |
| JSON_ARRAY() | 创建 JSON 数组 | JSON_ARRAY('a', 'b', 'c') |
| JSON_OBJECT() | 创建 JSON 对象 | JSON_OBJECT('name', 'Alice') |
二、实战案例：解析与查询 JSON 字段
案例 1：查询用户配置中包含特定属性的记录
场景描述：
在用户管理系统中，users 表中的 config 字段存储用户配置信息，格式为 JSON。我们需要查找所有具有特定权限或角色的用户。
表结构 users
| user_id | name | config |
|---|---|---|
| 1 | 张三 | {“role”: “admin”, “email”: “zhangsan@example.com”, “active”: true} |
| 2 | 李四 | {“role”: “user”, “email”: “lisi@example.com”, “active”: false} |
| 3 | 王五 | {“role”: “admin”, “email”: “wangwu@example.com”, “active”: true} |
| 4 | 赵六 | {“role”: “guest”, “email”: “zhaoliu@example.com”, “active”: true} |
目标：
- 查询
role为admin的用户记录。
SQL 实现
sql">SELECT user_id, name, JSON_UNQUOTE(JSON_EXTRACT(config, '$.email')) AS email
FROM users
WHERE JSON_EXTRACT(config, '$.role') = 'admin';
查询结果
| user_id | name | |
|---|---|---|
| 1 | 张三 | zhangsan@example.com |
| 3 | 王五 | wangwu@example.com |
解释：
JSON_EXTRACT()提取 JSON 中的role字段，并与目标值admin进行比对。JSON_UNQUOTE()去除 JSON 提取结果中的引号，使返回值更符合普通字符串格式。
案例 2：查找启用状态的用户
需求描述：
查询 config 字段中 active 为 true 的用户记录。
SQL 实现
sql">SELECT user_id, name
FROM users
WHERE JSON_EXTRACT(config, '$.active') = 'true';
查询结果
| user_id | name |
|---|---|
| 1 | 张三 |
| 3 | 王五 |
| 4 | 赵六 |
案例 3：动态更新 JSON 配置字段
需求描述：
将所有用户的 active 状态修改为 false，以停用用户账号。
SQL 实现
sql">UPDATE users
SET config = JSON_SET(config, '$.active', false)
WHERE JSON_EXTRACT(config, '$.active') = 'true';
解释：
- 使用
JSON_SET()动态更新 JSON 字段中的某个属性，无需修改整个 JSON 字段。 - 此方法避免了复杂的字符串拼接操作，直接在 JSON 字段中更新。
案例 4：删除用户配置中的敏感信息
需求描述：
从 config 字段中删除 password 字段，提升数据安全性。
SQL 实现
sql">UPDATE users
SET config = JSON_REMOVE(config, '$.password')
WHERE JSON_SEARCH(config, 'one', 'password') IS NOT NULL;
解释：
JSON_REMOVE()删除 JSON 字段中指定路径的属性。JSON_SEARCH()查找包含指定属性password的记录，确保只处理包含该字段的用户。
案例 5：提取 JSON 字段中的键名列表
需求描述：
获取用户配置字段 config 的所有键名。
SQL 实现
sql">SELECT user_id, JSON_KEYS(config) AS keys_list
FROM users;
查询结果
| user_id | keys_list |
|---|---|
| 1 | [“role”, “email”, “active”] |
| 2 | [“role”, “email”, “active”] |
| 3 | [“role”, “email”, “active”] |
解释：
JSON_KEYS()返回 JSON 对象的所有键名，便于进一步解析和处理。
三、优化建议与注意事项
1. 添加 JSON 索引优化查询
虽然 JSON 提供了很高的灵活性，但直接查询 JSON 字段性能可能较低。可以通过创建虚拟列或生成列为 JSON 字段添加索引。
示例：
sql">ALTER TABLE users
ADD role VARCHAR(50) GENERATED ALWAYS AS (JSON_UNQUOTE(JSON_EXTRACT(config, '$.role'))) STORED,
ADD INDEX idx_role (role);
2. 设计层面避免嵌套过深
- JSON 嵌套层级过深会导致查询复杂且性能下降。尽量保持 JSON 结构扁平化。
四、总结
- MySQL 的 JSON 函数 提供了高效、灵活的方式处理非结构化数据，适用于动态配置、嵌套对象和复杂存储场景。
JSON_EXTRACT()和JSON_SET()是最常用的查询与更新工具，能够直接在 SQL 语句中操作 JSON 字段。- 通过动态创建索引和虚拟列，可以进一步优化 JSON 查询性能，使其在大数据量下依然保持高效。
