? 本文关键词：MySQL JSON 类型、JSON vs TEXT、JSON_EXTRACT、JSON_SET、JSON_TABLE、虚拟列索引、Generated Column、Schema-Free、部分更新、数据库设计、面试必备知识点
从实际业务场景切入。之前有同学问：“每个商品的规格参数都不相同，到底是建立子表合适，还是用 JSON 字段存储？”这个问题非常有代表性。
前阵子接手了一个配置中心的需求，产品要求不同业务线可以自定义配置项。起初我打算建子表——config_key、config_value，再加一张关联表。方案写完后，前辈一句点拨：“你这个场景用 JSON 一个字段就搞定了，何必拆三张表？”说实话，一开始我不太服气，但实践之后发现他说得对。JSON 不是偷懒，在某些场景下它确实比拆表更合理。
当然，JSON 也有自己的局限性，用错了地方，后期维护起来比子表还麻烦。下面分享这段时间踩过的坑和积累的经验。
一、先搞清楚：JSON 类型和 TEXT 存 JSON 到底差在哪里
很多人（包括之前的我）会觉得：反正都是存一串 JSON 字符串，用 TEXT 不就行了？
其实差别很大。
TEXT 存 JSON：MySQL 把它当纯字符串处理
TEXT 字段对内容完全不关心。即使塞入一个 {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 函数一开始看起来确实令人头疼，名字又长又像。整理了一张速查表，按功能分类之后就好记多了。
读取类
| 函数 | 简写 | 功能说明 | 示例 |
|---|---|---|---|
| JSON_EXTRACT(json, '$.key') | -> | 取某个字段的值（带引号） | SELECT info->'$.name' FROM test_json |
| JSON_UNQUOTE(JSON_EXTRACT(...)) | ->> | 取某个字段的值（去引号） | SELECT info->>'$.name' FROM test_json |
| JSON_KEYS(json) | — | 列出所有顶层 key | SELECT JSON_KEYS(info) FROM test_json |
| JSON_LENGTH(json) | — | 数组长度或 key 数量 | SELECT JSON_LENGTH(info) FROM test_json |
| JSON_CONTAINS(json, val) | — | 判断是否包含某个值 | SELECT JSON_CONTAINS(info->'$.tags', '"MySQL"') |
最常用的是 ->>。-> 取出来的值带双引号（"小学妹"），->> 取出来是纯文本（小学妹）。一般查询用 ->> 就对了。
修改类
| 函数 | 功能说明 | 关键区别 |
|---|---|---|
| JSON_SET(json, path, val) | 设置值（存在就更新，不存在就新增） | 最常用，90%的场景用它 |
| JSON_INSERT(json, path, val) | 只在 key 不存在时插入 | 已有 key 不修改 |
| JSON_REPLACE(json, path, val) | 只在 key 存在时替换 | 没有 key 不操作 |
| JSON_REMOVE(json, path) | 删除某个 key | 删除不存在的 key 不报错 |
记忆技巧：JSON_SET = INSERT + REPLACE 的合体，最省心，不知道该用哪个的时候就选它。
聚合类
| 函数 | 功能说明 | 示例 |
|---|---|---|
| JSON_ARRAYAGG(col) | 把多行合并成一个 JSON 数组 | SELECT JSON_ARRAYAGG(username) FROM users |
| JSON_OBJECTAGG(key, val) | 把多行合并成一个 JSON 对象 | SELECT JSON_OBJECTAGG(id, username) FROM users |
这两个函数在做报表聚合时特别实用，直接在 SQL 层把结果拼成 JSON 返回，省得应用层再组装。
表函数
JSON_TABLE 是 MySQL 8.0 的大招，作用是把 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（默认） | STORED |
|---|---|---|
| 存储 | 不占磁盘空间，查询时实时计算 | 占磁盘空间，写入时计算并存储 |
| 写入性能 | 不影响 | 有额外开销 |
| 查询性能 | 计算一次（有索引时走索引） | 直接读值 |
| 能建索引 | 可以（MySQL 5.7+） | 可以 |
大部分场景用 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 这种 EA V 模式（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 | 该怎么做 |
|---|---|---|
| 需要 JOIN 关联查询 | JSON 内部数据没法直接 JOIN | 建子表，使用外键关联 |
| 需要复杂聚合/排序 | JSON 提取后再聚合，性能差且写法复杂 | 建子表，使用原生聚合函数 |
| 字段需要加约束 | JSON 内部不支持外键、唯一、CHECK | 建子表，该加约束就加约束 |
| 需要按多个维度频繁查询 | 每个查询的属性都要加虚拟列索引，多了维护成本高 | 建子表更清晰 |
| 数据量大且结构完全固定 | 结构已确定，用 JSON 纯属自找麻烦 | 建子表 |
判断标准其实不复杂：如果只需要存和取，不需要对内部字段做复杂查询、关联、约束，那 JSON 就够用了。其他情况，建子表。
面试怎么答
面试官可能会问：MySQL 的 JSON 类型和直接用 TEXT 存 JSON 有什么区别？
另一个常见问题：什么场景适合用 JSON，什么场景不适合？
避坑清单
| # | 坑 | 后果 | 正确做法 |
|---|---|---|---|
| 1 | 用 TEXT 存 JSON 当 JSON 类型用 | 没有校验、没有部分更新、没有性能优化 | MySQL 8.0 直接用 JSON 类型 |
| 2 | 所有字段都塞 JSON 里（Schema-Free） | 丢失类型检查、约束、索引优势 | 核心字段用普通列，扩展属性才用 JSON |
| 3 | 忘记 JSON 字段不能直接建索引 | 查询全表扫描，慢到怀疑人生 | 用虚拟列 + B-Tree 索引方案 |
| 4 | 单条 JSON 超过 10KB 不拆分 | 临时表溢出磁盘，查询性能断崖下降 | 定期审查 JSON 字段大小，该拆就拆 |
| 5 | 直接 WHERE json_col = xxx 比较 | 二进制比较，内容相同但格式不同就查不到 | 用 ->> 提取字段后比较 |
| 6 | 靠 JSON 内部字段做业务约束 | JSON 不支持 NOT NULL、唯一约束 | 必须约束的字段走普通列 |
| 7 | JSON_SET 路径写错不报错 | 新增了一个错误的 key，数据悄悄出错 | 写完 UPDATE 后立刻 SELECT 验证 |
JSON 类型已经推出好几年了，但身边好些同事还是不太敢用，怕踩坑，或者用了之后发现坑比想象的多。
总结一下：动态属性、配置、缓存这类场景，JSON 比拆子表省事太多，该用就用。但核心数据需要关联查询、需要约束的，子表才是正路，别硬塞 JSON。另外有一点容易被忽略：使用前先想清楚索引方案，虚拟列 + 索引在建表时就配好，别等查询慢了才想起来加。
有踩过 JSON 坑的同学，欢迎在评论区聊聊？
