MySQL JSON 字段深度实战——JSON 函数、索引优化、与 NoSQL 的边界

适读人群：需要在 MySQL 中存储半结构化数据的后端工程师 | 阅读时长：约15分钟 | 核心价值：用对 MySQL JSON 字段，避免既踩了 NoSQL 的坑又破坏了 SQL 的优势

---

"用 JSON 字段代替 MongoDB"的冲动

我认识一个团队负责人老周，他的团队做的是一个问卷系统。问卷结构千变万化：有单选题、多选题、矩阵题、评分题，每种题型有不同的配置属性，而且还要支持用户自定义字段。

"我们要不要直接用 MongoDB？"老周问我。

我问他："你们有强事务需求吗？问卷提交后需要和其他表关联操作吗？"

"有，问卷提交后要更新用户积分、触发营销活动。"

"那先别换 MongoDB，用 MySQL 的 JSON 字段试试，把结构化的放结构化字段，不确定的配置放 JSON。"

他们最后确实用了这个方案，运行两年多，效果不错。但也踩了几个坑。今天把这些经验系统整理出来。

---

一、MySQL JSON 类型基础

MySQL 5.7.8 引入了原生 JSON 类型，8.0 进一步增强。JSON 字段的好处：

1. 自动校验：存入的 JSON 格式不合法会直接报错，不像 VARCHAR 存 JSON 不做校验
2. 内部二进制格式：存储时自动转为优化的二进制格式，查询效率比 VARCHAR 存 JSON 更高
3. 丰富的 JSON 函数：JSON_EXTRACT、JSON_SET、JSON_CONTAINS 等函数可以直接操作 JSON 内容

-- 创建包含 JSON 字段的表
CREATE TABLE surveys (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(200) NOT NULL,
    created_by BIGINT NOT NULL,
    -- JSON 字段存储问题列表（结构不固定）
    questions JSON NOT NULL,
    -- JSON 字段存储自定义配置
    config JSON,
    create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

---

二、核心 JSON 函数实战

2.1 读取 JSON 数据

-- 插入示例数据
INSERT INTO surveys (title, created_by, questions, config) VALUES (
    '用户满意度调查',
    1001,
    '[
        {"id": 1, "type": "single", "title": "整体满意度", "options": ["非常满意", "满意", "一般", "不满意"]},
        {"id": 2, "type": "text", "title": "您的具体建议"}
    ]',
    '{"allow_anonymous": true, "max_responses": 1000, "deadline": "2024-12-31"}'
);

-- 用 -> 操作符读取 JSON 字段（等价于 JSON_EXTRACT）
SELECT 
    title,
    config -> '$.allow_anonymous' AS allow_anonymous,
    config -> '$.max_responses' AS max_responses,
    JSON_LENGTH(questions) AS question_count
FROM surveys;

-- 用 ->> 操作符读取（去除引号，返回文本而不是 JSON 值）
SELECT config ->> '$.deadline' AS deadline FROM surveys;
-- 返回: 2024-12-31（不带引号）
-- 而 config -> '$.deadline' 返回: "2024-12-31"（带引号）

-- 查询 JSON 数组中的元素
SELECT id, JSON_UNQUOTE(JSON_EXTRACT(questions, '$[0].title')) AS first_question
FROM surveys;

2.2 修改 JSON 数据

-- JSON_SET：修改/新增属性（字段存在则修改，不存在则新增）
UPDATE surveys 
SET config = JSON_SET(config, '$.max_responses', 2000, '$.updated_at', NOW())
WHERE id = 1;

-- JSON_REMOVE：删除属性
UPDATE surveys
SET config = JSON_REMOVE(config, '$.deadline')
WHERE id = 1;

-- JSON_ARRAY_APPEND：向 JSON 数组追加元素
UPDATE surveys
SET questions = JSON_ARRAY_APPEND(
    questions, 
    '$',  -- 追加到数组末尾
    JSON_OBJECT('id', 3, 'type', 'rating', 'title', '推荐指数', 'max', 10)
)
WHERE id = 1;

2.3 JSON 条件查询

-- JSON_CONTAINS：检查 JSON 字段是否包含特定值
-- 查询配置了 allow_anonymous=true 的问卷
SELECT * FROM surveys 
WHERE JSON_CONTAINS(config, 'true', '$.allow_anonymous');

-- JSON_OVERLAPS (MySQL 8.0+)：两个 JSON 数组有交集
-- 查询包含 "single" 类型题目的问卷
SELECT * FROM surveys
WHERE JSON_OVERLAPS(
    JSON_EXTRACT(questions, '$[*].type'),
    '["single"]'
);

-- 查询 max_responses 大于 500 的问卷
SELECT * FROM surveys
WHERE config ->> '$.max_responses' > '500';
-- 注意：JSON 提取的值是字符串，需要比较时要注意类型
-- 更安全的做法：
WHERE CAST(config ->> '$.max_responses' AS UNSIGNED) > 500;

---

三、JSON 字段的索引优化

3.1 虚拟列索引（Generated Column）

JSON 字段本身不能直接建索引，但可以通过虚拟列来实现：

-- 添加虚拟列，从 JSON 中提取 allow_anonymous
ALTER TABLE surveys 
ADD COLUMN allow_anonymous TINYINT(1) 
    GENERATED ALWAYS AS (config ->> '$.allow_anonymous') VIRTUAL;

-- 在虚拟列上建索引
CREATE INDEX idx_allow_anonymous ON surveys (allow_anonymous);

-- 现在这个查询可以走索引
SELECT * FROM surveys WHERE allow_anonymous = 1;

-- 验证：EXPLAIN 显示 type=ref, key=idx_allow_anonymous
EXPLAIN SELECT * FROM surveys WHERE allow_anonymous = 1;

踩坑一：虚拟列与实际值的类型不匹配

现象：建了虚拟列索引，但查询还是全扫，索引不生效。

原因：JSON 提取的值类型与查询条件的类型不匹配。JSON 中 "allow_anonymous": true 提取后是字符串 "true" 或 "1"，而 TINYINT 列期望数字类型，发生了隐式转换。

解法：在定义虚拟列时明确类型转换：

-- 使用 CAST 确保类型正确
ADD COLUMN allow_anonymous TINYINT(1)
    GENERATED ALWAYS AS (CAST(config ->> '$.allow_anonymous' AS UNSIGNED)) VIRTUAL;

3.2 多值索引（MySQL 8.0.17+）

MySQL 8.0.17 新增了 Multi-Valued Index，专门用于索引 JSON 数组：

-- 假设 questions 字段包含类型数组
-- 建立多值索引，允许按题目类型快速查询
CREATE INDEX idx_question_types 
ON surveys ((CAST(questions -> '$[*].type' AS CHAR(20) ARRAY)));

-- 使用多值索引查询
SELECT * FROM surveys 
WHERE 'single' MEMBER OF (questions -> '$[*].type');

-- EXPLAIN 可以看到使用了多值索引
EXPLAIN SELECT * FROM surveys 
WHERE 'single' MEMBER OF (questions -> '$[*].type');

---

四、踩坑实录

踩坑二：JSON 字段内容过大导致行溢出

现象：某张表的查询越来越慢，InnoDB 报 Row size too large。

原因：一个问卷的 questions 字段随着题目增加，已经膨胀到 60KB。InnoDB 的行格式（Dynamic/Compact）对大字段会做"行外存储"，存储在 overflow page 里，每次读取都需要额外 IO。

解法：

1. 评估单行 JSON 数据的上限，超过 16KB 就要考虑拆表
2. 对不经常查询的大 JSON 字段，考虑单独存一张表，主表只存摘要信息

踩坑三：用 JSON_EXTRACT 查询不走索引，全扫

现象：WHERE JSON_EXTRACT(config, '$.status') = 'active' 即使建了 JSON 字段的虚拟列索引，这个查询也不走索引。

原因：查询条件用的是 JSON_EXTRACT 函数，而索引建在了 ->> 操作符对应的虚拟列上，MySQL 不认为两者等价。

解法：查询时用与虚拟列定义一致的表达式，或者直接查虚拟列：

-- 虚拟列定义：GENERATED ALWAYS AS (config ->> '$.status')
-- 查询也用 ->> 而不是 JSON_EXTRACT
WHERE config ->> '$.status' = 'active'  -- 走虚拟列索引
-- 或直接查虚拟列
WHERE status_col = 'active'             -- 走索引

---

五、MySQL JSON vs NoSQL：边界在哪里

我的判断标准：

用 MySQL JSON 字段的场景：

• 字段结构不固定，但业务量级适中（每表千万行以内）
• 需要与其他表做事务关联
• 对不固定字段的查询需求有限，主要是点查（不是全扫）

考虑 MongoDB/Elasticsearch 的场景：

• 需要对 JSON 的任意深层嵌套字段做全文检索
• JSON 字段的结构频繁变化，且变化影响查询方式
• 单表数据量超过 1 亿行，JSON 字段查询是主要访问模式
• 需要聚合分析 JSON 内部数据（MongoDB 的聚合管道更强）

MySQL JSON 是一个实用的工具，用对了场景事半功倍，用错了场景两头不讨好。