MySQL JSON 数据类型（5.7+）深入解析

为什么引入 JSON 类型

MySQL 5.7 之前，存储 JSON 只能放 VARCHAR/TEXT 里，应用层做序列化反序列化。问题是：
– 无法验证 JSON 合法性
– 无法高效查询内部字段
– 更新时需要读取整个字符串再写回

MySQL 5.7 正式引入 JSON 原生类型，从存储格式到查询函数全面优化。

存储与验证

自动验证

插入 JSON 值时，MySQL 自动校验格式合法性：

CREATE TABLE t (data JSON);
INSERT INTO t VALUES ('{"name": "蛋蛋", "age": 18}');  -- 成功
INSERT INTO t VALUES ('not json');  -- 报错：Invalid JSON text

内部存储格式

JSON 数据存储为二进制的内部格式（binary JSON 或 bjson），而不是纯文本：
– 键名只存一次（减少重复键名的存储开销）
– 解析后的值类型已知（数字、字符串、数组等）
– 查询时无需重新解析整个 JSON
– 支持快速随机读取（通过偏移量直接定位字段）

最大存储限制为 max_allowed_packet（默认 64MB）。

JSON 相关函数

查询函数

-- 提取 JSON 字段值（-> 操作符）
SELECT data->'$.name' FROM t;  -- 返回带引号的 JSON 字符串："蛋蛋"

-- 提取 JSON 字段值（->> 操作符，返回纯字符串）
SELECT data->>'$.name' FROM t;  -- 返回：蛋蛋

-- JSON_EXTRACT 函数
SELECT JSON_EXTRACT(data, '$.name') FROM t;

-- 路径语法
$.name           -- 根对象的 name 键
$.address.city   -- 嵌套对象的 city 键
$[0].title       -- 数组第一个元素的 title 键
$[*].name        -- 遍历数组所有元素的 name 值

判断与存在性检查

-- 判断 JSON 中是否存在指定路径
SELECT JSON_CONTAINS_PATH(data, 'one', '$.name') FROM t;
SELECT JSON_CONTAINS_PATH(data, 'all', '$.name', '$.age') FROM t;

-- 判断 JSON 中是否包含指定值
SELECT JSON_CONTAINS(data, '"蛋蛋"', '$.name') FROM t;

修改函数

-- JSON_SET：设置值（不存在则插入）
UPDATE t SET data = JSON_SET(data, '$.nickname', '鸭蛋');

-- JSON_INSERT：插入值（已存在则忽略）
UPDATE t SET data = JSON_INSERT(data, '$.nickname', '鸭蛋');

-- JSON_REPLACE：替换值（不存在则忽略）
UPDATE t SET data = JSON_REPLACE(data, '$.name', '蛋蛋2号');

-- JSON_REMOVE：删除键
UPDATE t SET data = JSON_REMOVE(data, '$.nickname');

-- 数组操作
UPDATE t SET data = JSON_ARRAY_APPEND(data, '$.tags', 'cool');
UPDATE t SET data = JSON_ARRAY_INSERT(data, '$.tags[0]', 'very');

重要：这些修改函数在内部会创建新的 JSON 文档并替换原有的，而不是原地修改。对于大 JSON，性能开销不可忽视。

虚拟列与索引

这是 JSON 类型在 MySQL 中最核心的性能特性。普通 JSON 列不能直接加索引，但可以通过生成列（Generated Column）间接索引：

CREATE TABLE users (
    data JSON,
    name VARCHAR(50) GENERATED ALWAYS AS (data->>'$.name') STORED,
    age INT GENERATED ALWAYS AS (data->>'$.age') UNSIGNED STORED,
    INDEX idx_name (name),
    INDEX idx_age (age)
);

• VIRTUAL 生成列：不占物理存储空间，但 MySQL 8.0 以前不支持在其上建索引
• STORED 生成列：占用存储空间，但可以索引
• MySQL 8.0.13+：支持在 VIRTUAL 列上创建函数索引

多值索引

MySQL 8.0.17 引入了多值索引，用于为 JSON 数组中的元素创建索引：

CREATE TABLE t (
    data JSON,
    INDEX idx_tags ((CAST(data->'$.tags' AS UNSIGNED ARRAY)))
);

这使得 JSON 数组的 JSON_CONTAINS 和 MEMBER OF 查询可以走索引。

性能考量

查询性能

• 不带索引的 JSON 查询：全表扫描 + 运行时逐行解析
• 虚拟列索引后：等值查询和范围查询性能接近普通索引
• 函数索引（MySQL 8.0.13+）：直接索引表达式，无需虚拟列

更新性能

• 部分更新（MySQL 8.0 优化）：只修改变更部分，而非整个 JSON 文档
• 使用 JSON_SET/JSON_REPLACE 时，只有被修改的部分需要重新编码
• 大 JSON 文档的更新仍然昂贵，建议 JSON 内只放少量字段

与 NoSQL 对比

MySQL 的 JSON 能力适合”关系型主数据附带少量灵活属性”的场景。

最佳实践

1. JSON 不是主数据：关系型核心字段放普通列，JSON 只放扩展属性
2. 控制 JSON 大小：每条 JSON 建议不超过几千字节，过大考虑拆分表
3. 识别高频查询路径：为 JSON 中频繁查询的字段建虚拟列+索引
4. 避免嵌套过深：超过三层嵌套就应考虑是否设计不合理
5. 不要存二进制数据：JSON 存 base64 大图会性能爆炸
6. 使用合适的函数：区分 ->（返回 JSON）和 ->>（返回字符串）

面试常问题

Q：JSON 类型和 VARCHAR 存 JSON 字符串有什么区别？
A：JSON 类型自动校验格式，内部以二进制格式存储（解析更快），支持 JSON 函数查询和部分更新，支持生成列索引。VARCHAR 存 JSON 字符串毫无优势。

Q：JSON 类型能建索引吗？
A：不能直接建，但可以通过生成列（虚拟列或存储列）间接索引，也可以通过 MySQL 8.0.17+ 的多值索引索引数组元素。

Q：MySQL JSON 使用中最大的坑是什么？
A：过度使用。把本应归范化的关系型数据塞进 JSON 列，导致查询复杂、无法用关系代数优化、数据完整性失控。

Q：JSON_SET 执行时会重建整个 JSON 吗？
A：MySQL 8.0.17+ 支持”部分更新”（partial update），如果操作不改变 JSON 大小，且行格式为 DYNAMIC 或 COMPRESSED，更新可以只写修改过的部分。这个特性通过 binlog_row_image=MINIMAL 也支持 binlog 记录部分更新。