📌 今日关键词：**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_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** 是 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（默认）|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 那种 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|该怎么做|
|:-:|:-:|:-:|
|需要 JOIN 关联查询|JSON 内部数据没法直接 JOIN|建子表，走外键关联|
|需要复杂聚合/排序|JSON 提取后再聚合，性能差且写法复杂|建子表，用原生聚合函数|
|字段需要加约束|JSON 内部不支持外键、唯一、CHECK|建子表，该加约束加约束|
|需要按多个维度频繁查询|每个查的属性都要加虚拟列索引，多了维护成本高|建子表更清晰|
|数据量大且结构完全固定|结构都确定了，用 JSON 纯属自找麻烦|建子表|

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

----

## 面试怎么答

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

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

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

>简单说就是：属性结构不固定的、经常变化的、不需要对内部字段做 JOIN 或复杂聚合的，用 JSON 就对了。商品的动态规格参数、配置中心、API 响应缓存，这些场景我实际用下来都觉得 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 坑的同学，评论区聊聊？咱们下篇见 👋

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

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

MySQL 8.0 的 JSON 类型不是摆设，很多场景比拆子表好用太多。本文从 JSON 类型和 TEXT 存 JSON 的本质区别讲起，覆盖 JSON 函数速查、虚拟列索引方案、三大实战场景、5 个常见坑，以及什么时候该老老实实建子表。附面试话术+避坑清单

编程语言

MySQL JSON类型详解：JSON与TEXT存储差异、JSON函数使用技巧、虚拟列索引方案及实战场景分析。深入解析JSON在电商商品规格、配置中心等场景的应用优势，同时指出5大常见坑点，帮助开发者合理选择JSON或子表方案，提升数据库设计效率。

关系型数据库

数据库

MySQL

JSON

腾讯云数据库特惠，新用户首单0.5折起


2026年中大促 | AI 领航 智绘未来

腾讯云大数据特惠新用户0.1折起


oceanus

腾讯云BI新客户首次购买9.9元起！


文章

问答

视频

教程

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云智能顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

EdgeOne AI 安全实战专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

聚焦“写作效率、视觉美观与运行性能”三方面进行全面升级，为您提供更高效、稳定的创作环境

社区富文本&Markdown编辑器全新改版上线，欢迎大家体验!

诚挚邀请您参与本次调研，分享您的真实使用感受与建议。您的反馈至关重要，感谢您的支持与参与！

社区新版编辑器体验调研

MySQL JSON 类型生产实战：部分更新、虚拟列索引、性能边界与 Schema 设计决策-腾讯云开发者社区-腾讯云

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

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

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐