MySQL JSON 类型生产实战：部分更新、虚拟列索引、性能边界与 Schema 设计决策

📌 今日关键词：MySQL JSON 类型、JSON vs TEXT、JSON_EXTRACT、JSON_SET、JSON_TABLE、虚拟列索引、Generated Column、Schema-Free、部分更新、数据库设计、面试必背

大家好，我是 数据库小学妹 👋

之前写 8.0 新特性那篇的时候，JSON_TABLE 我就带过了一下。当时有同学留言说："小学妹，JSON 类型你能不能展开讲讲？我们现在有个需求，每个商品的规格参数都不一样，到底该建子表还是用 JSON 存？"

这个问题其实我自己也纠结过。

上个月接手了一个配置中心的需求，产品要求不同业务线可以自定义配置项。当时我的第一反应是建子表——config_key、config_value，再加一张关联表。写完方案给前辈一看，他说："你这个场景用 JSON 一个字段就搞定了，干嘛拆三张表？"

我一开始还不服气。后来试了一下，发现他说得对。JSON 不是偷懒，有些场景用它确实比拆表合理。

但 JSON 也有它的局限，用错了地方后期维护起来比子表还痛苦。今天就把我这段时间踩的坑和学到的东西整理出来。

一、先搞清楚：JSON 类型和 TEXT 存 JSON 到底差在哪

很多人（包括之前的我）觉得：反正都是存一串 JSON 字符串，用 TEXT 不就行了？

不行，差别大了去了。

TEXT 存 JSON：MySQL 当纯字符串看

你往 TEXT 字段里塞什么，MySQL 完全不关心。你塞一个 {name: 小学妹} 这种非法 JSON 进去，它也照单全收。等到你用 JSON_EXTRACT 去取值的时候，才发现取不出来，报语法错误。

而且 TEXT 存的 JSON 每次更新都是整体重写。你有个 5KB 的 JSON，只改其中一个字段，也得把整条 5KB 重新写一遍。

JSON 类型：MySQL 认真对待它

MySQL 的 JSON 类型有三件事是 TEXT 做不到的。

第一件，写入时自动校验。非法 JSON 直接报错，不会让脏数据进库。

CREATE TABLE test_json (id INT PRIMARY KEY, info JSON);

-- 合法 JSON，写入成功
INSERT INTO test_json VALUES (1, '{"name": "小学妹", "skill": "MySQL"}');

-- 非法 JSON，直接报错
INSERT INTO test_json VALUES (2, '{name: 小学妹}');
-- ERROR 3140 (22032): Invalid JSON text

第二件，内部二进制存储。JSON 类型不是存原始字符串，而是转成一种叫 Opaque 的二进制格式。好处是读取某个字段时不需要解析整个字符串，直接通过偏移量定位，速度快很多。

第三件，支持部分更新。这个我觉得最实用。只改 JSON 里的某个字段，MySQL 只更新那一部分，不用重写整条记录。

-- 只改 name 字段，其他不动
UPDATE test_json 
SET info = JSON_SET(info, '$.name', '数据库小学妹') 
WHERE id = 1;

从 Binlog 角度看也有区别。TEXT 整体重写后，ROW 模式下 Binlog 记录的是整条前后镜像；JSON 部分更新时，Binlog 记录的变更更小。数据量大的时候，这个差异会直接影响主从同步的效率。

说白了，TEXT 存 JSON 就是"假装 JSON"，MySQL 根本不认它，你也没法用 JSON 的那些便利操作。想正经用 JSON，就老老实实用 JSON 类型。

二、JSON 函数速查表

说实话，JSON 函数一开始看得我头大，名字又长又像。后来我整理了一张速查表，按功能分类之后就好记多了。

读取类

最常用的是 ->>。-> 取出来的值带双引号（"小学妹"），->> 取出来是纯文本（小学妹）。一般查询用 ->> 就对了。

修改类

记忆技巧：JSON_SET = INSERT + REPLACE 的合体，最省心，不知道用啥就用它。

聚合类

这两个函数在做报表聚合的时候特别好用，直接在 SQL 层把结果拼成 JSON 返回，省得应用层再组装。

表函数

JSON_TABLE 是 8.0 的大招，48 号那篇我简单提过。它的作用是把 JSON 数组"展开"成一个虚拟表，然后就可以对它做 JOIN、WHERE、GROUP BY。

SELECT jt.*
FROM products,
JSON_TABLE(
    products.attrs,
    '$.specs[*]' COLUMNS (
        spec_name VARCHAR(50) PATH '$.name',
        spec_value VARCHAR(100) PATH '$.value'
    )
) AS jt
WHERE spec_name = '屏幕尺寸';

这条 SQL 把 products 表里 attrs 字段中的 specs 数组展开成行，每行有 spec_name 和 spec_value 两列，然后正常做 WHERE 过滤。不需要在应用层手动解析 JSON 数组再循环查询。

三、JSON 索引方案：虚拟列 + B-Tree 索引

这是很多人不知道的一点：JSON 字段本身不能直接建索引。

你试试就知道了：

ALTER TABLE products ADD INDEX idx_brand (attrs->'$.brand');
-- ERROR 3152: JSON column '...' supports indexing only via virtual generated columns

MySQL 直接告诉你：要索引就走虚拟列。

方案：Generated Column（生成列）+ 索引

思路很简单：从 JSON 字段里提取你需要查询的属性，放到一个"虚拟列"里，然后对这个虚拟列建索引。

-- 第一步：加虚拟列，从 JSON 中提取 brand 字段
ALTER TABLE products 
ADD COLUMN brand VARCHAR(100) 
GENERATED ALWAYS AS (JSON_UNQUOTE(JSON_EXTRACT(attrs, '$.brand'))) VIRTUAL;

-- 第二步：在虚拟列上建索引
ALTER TABLE products ADD INDEX idx_brand (brand);

加完之后，正常查询就能走索引了：

-- 走索引！
SELECT * FROM products WHERE brand = 'Apple';

VIRTUAL vs STORED

生成列有两种模式，区别如下：

我的建议是大部分场景用 VIRTUAL 就够了。不额外占空间，索引也照常走。只有在你发现某个 JSON 属性查询特别频繁、计算开销明显的时候，再考虑换成 STORED。

完整实战示例：商品灵活属性表

不同商品有不同的规格参数，手机有"屏幕尺寸"和"处理器"，衣服有"尺码"和"面料"。如果每种商品都建一张规格子表，种类一多表就炸了。

用 JSON 一个字段搞定：

CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(200) NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    attrs JSON COMMENT '商品属性，不同品类结构不同',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- 手机
INSERT INTO products (name, price, attrs) VALUES 
('iPhone 16', 6999.00, '{"brand": "Apple", "screen": "6.1寸", "cpu": "A18", "color": ["黑色", "白色", "蓝色"]}');

-- 衣服
INSERT INTO products (name, price, attrs) VALUES 
('纯棉T恤', 89.00, '{"brand": "优衣库", "size": ["S", "M", "L", "XL"], "material": "100%棉"}');

给常用的查询属性加虚拟列索引：

ALTER TABLE products 
ADD COLUMN brand VARCHAR(100) GENERATED ALWAYS AS (JSON_UNQUOTE(JSON_EXTRACT(attrs, '$.brand'))) VIRTUAL,
ADD INDEX idx_brand (brand);

查询的时候就像查普通字段一样：

-- 按品牌查，走索引
SELECT name, price, attrs->>'$.screen' AS screen 
FROM products WHERE brand = 'Apple';

-- 查某个数组里是否包含特定值
SELECT name, price 
FROM products WHERE JSON_CONTAINS(attrs->'$.color', '"蓝色"');

四、三大实战场景

场景 1：用户动态属性（电商商品规格）

就是上面那个例子。不同品类的商品属性完全不同，硬建子表就得一个品类一张表，或者搞一张超宽的"万能属性表"（100 个列，90% 是 NULL）。

用 JSON 就一个字段搞定，新增属性不用 ALTER TABLE。

场景 2：配置中心表设计

CREATE TABLE app_config (
    app_name VARCHAR(50) PRIMARY KEY,
    config JSON NOT NULL,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

INSERT INTO app_config (app_name, config) VALUES 
('order-service', '{"timeout": 30, "retry": 3, "max_connections": 100, "features": {"dark_mode": true, "new_checkout": false}}');

一个字段存一整套配置。读的时候取出来直接就是 JSON，应用层不需要拼装。改某一项配置用 JSON_SET 部分更新，不用整条覆盖。

比起 config_key / config_value 那种 EAV 模式（Entity-Attribute-Value），JSON 方案查询快、维护简单，还不用写那么多 JOIN。

场景 3：第三方 API 响应缓存

对接外部接口的时候，返回的 JSON 往往结构不固定，或者经常变。与其每次收到响应都拆字段入库（字段变了还得改表），不如直接把原始 JSON 存进 JSON 列，常用的查询字段用虚拟列索引提取。

CREATE TABLE api_cache (
    id INT PRIMARY KEY AUTO_INCREMENT,
    source VARCHAR(50) NOT NULL,
    response JSON NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- 第三方接口返回啥就存啥
INSERT INTO api_cache (source, response) VALUES 
('weather_api', '{"city": "北京", "temp": 28, "humidity": 65, "forecast": [...]}');

后面想查某个城市的温度，虚拟列 + 索引就完事了。

五、JSON 的 5 个坑（踩过的都在这了）

坑 1：不能直接 WHERE json_col = xxx

-- 这样写不行！
SELECT * FROM products WHERE attrs = '{"brand": "Apple"}';

JSON 类型的比较是按二进制值来的，不是按内容。key 的顺序不同、空格不同，都算不相等。

正确写法是用 JSON_EXTRACT 或虚拟列：

SELECT * FROM products WHERE attrs->>'$.brand' = 'Apple';

坑 2：JSON 内部字段无法加 NOT NULL 约束

你可以保证 MySQL 表的字段不为空，但你没法保证 JSON 内部某个 key 一定存在。attrs->'$.brand' 可能返回 NULL，MySQL 不会帮你拦。

解法：如果某个 JSON 属性是必须的，要么用触发器做校验，要么在应用层保证。或者，该用传统列就用传统列，别硬塞 JSON。

坑 3：Schema-Free 反模式

这个坑我差点踩了。当时想着"反正 JSON 啥都能存，那我把所有字段都塞 JSON 里算了"。

前辈及时拉住了我：把所有字段都塞 JSON 里，等于主动放弃类型检查、约束、索引。那你用关系型数据库干啥？

JSON 适合存"不确定结构的扩展属性"，不适合存"确定结构的核心数据"。商品名称、价格、上架时间这些，该用普通列就用普通列。

坑 4：JSON 列排序按二进制值

你对 JSON 列做 ORDER BY，排的是二进制存储值，不是你肉眼看到的内容语义。想按 JSON 里的某个字段排序，得提取出来再排：

-- 按品牌名排序
SELECT * FROM products ORDER BY attrs->>'$.brand';

坑 5：大 JSON 文档的性能边界

JSON 字段太大，读写性能都会下降。我自己测下来的经验，单条 JSON 超过 10KB 就该考虑拆分了。

太大的 JSON 还有个隐形问题：临时表排序时如果包含 JSON 列，tmp_table_size 很容易超限，直接溢出到磁盘临时表，查询速度断崖式下降。

六、什么时候不该用 JSON（该建子表还是得建）

JSON 解决不了所有问题。以下几种情况，老老实实建子表才是正路：

判断标准其实不复杂：如果你只需要存和取，不需要对内部字段做复杂查询、关联、约束，那 JSON 就够用了。其他情况，建子表。

面试怎么答

面试官：MySQL 的 JSON 类型和直接用 TEXT 存 JSON 有什么区别？

主要讲两点。一是写入校验和存储方式的不同：JSON 类型写入时会校验合法性，非法 JSON 直接报错，内部是二进制存储，读取某个字段不需要解析整个字符串。TEXT 就是纯字符串，不做校验，每次都完整解析。二是更新方式：JSON 支持部分更新，只改某个字段不用重写整条记录；TEXT 必须整体覆盖。
还有个容易忽略的点：JSON 类型的字段不能直接建索引，需要通过虚拟列（Generated Column）提取属性后在虚拟列上建 B-Tree 索引。面试的时候提一下这个，显得你真的用过。

面试官：什么场景适合用 JSON，什么场景不适合？

简单说就是：属性结构不固定的、经常变化的、不需要对内部字段做 JOIN 或复杂聚合的，用 JSON 就对了。商品的动态规格参数、配置中心、API 响应缓存，这些场景我实际用下来都觉得 JSON 更合适。
反过来，需要关联查询的、需要加外键或唯一约束的、结构完全固定的，就别凑热闹了，建子表更清晰。JSON 语法方便不代表什么都能往里塞。

避坑清单

今天这篇就到这了。JSON 类型出来好几年了，但身边好些同事还是不太敢用，怕踩坑，或者用了之后发现坑比想象的多。

我自己总结的经验是：动态属性、配置、缓存这类场景，JSON 比拆子表省事太多，该用就用。但核心数据需要关联查询、需要约束的，子表才是正路，别硬塞 JSON。另外有一点容易忽略：用之前先想清楚索引方案，虚拟列 + 索引建表时就配好，别等查询慢了才想起来加。

有踩过 JSON 坑的同学，评论区聊聊？咱们下篇见 👋