MySQL JSON 类型操作：从入门到不踩坑
开场白
MySQL 5.7 加了 JSON 类型之后，很多人觉得终于可以在关系型数据库里存 JSON 了，不用再拆表了。但说实话，我一开始用 JSON 类型的时候也没少踩坑——查询语法记不住、索引不会建、JSON 路径表达式写错……后来用多了才发现，JSON 类型确实方便，但前提是搞清楚它的能力和边界。今天把常用操作和注意事项整理一下。
JSON 类型的基本概念
MySQL 的 JSON 类型有两个特点：
- 自动校验：插入的数据必须是合法的 JSON 格式，不合法直接报错
- 二进制存储：内部用二进制格式存储，查询时不需要重新解析，比 VARCHAR 存 JSON 快
-- 合法 JSONINSERTINTOusers(id,profile)VALUES(1,'{"name": "张三", "age": 25}');-- 不合法 JSON，直接报错INSERTINTOusers(id,profile)VALUES(2,'{name: 张三, age: 25}');建表的时候直接声明 JSON 类型就行：
CREATETABLEusers(idBIGINTPRIMARYKEYAUTO_INCREMENT,nameVARCHAR(50),profile JSON,settings JSON);```## 插入和更新 JSON 数据 ### 直接插入 ```sqlINSERTINTOusers(name,profile)VALUES('张三','{"age": 25, "city": "北京", "tags": ["java", "mysql"]}');```### 用函数构建 JSON ```sql-- JSON_OBJECT 构建 JSON 对象INSERTINTOusers(name,profile)VALUES('李四',JSON_OBJECT('age',30,'city','上海','tags',JSON_ARRAY('python','redis')));-- JSON_ARRAY 构建 JSON 数组INSERTINTOusers(name,settings)VALUES('王五',JSON_OBJECT('notifications',JSON_ARRAY('email','sms')));```### 更新 JSON 字段 ```sql-- 整体替换UPDATEusersSETprofile='{"age": 26, "city": "深圳"}'WHEREid=1;-- 只更新某个属性（推荐）UPDATEusersSETprofile=JSON_SET(profile,'$.age',26)WHEREid=1;-- 新增属性UPDATEusersSETprofile=JSON_SET(profile,'$.email','zhangsan@test.com')WHEREid=1;-- 删除属性UPDATEusersSETprofile=JSON_REMOVE(profile,'$.email')WHEREid=1;JSON_SET 和 JSON_REPLACE 的区别：
- JSON_SET：存在就更新，不存在就插入
- JSON_REPLACE：只更新已存在的，不存在的不处理
-- $.phone 不存在-- JSON_SET 会插入SELECTJSON_SET('{"age": 25}','$.phone','13800138000');-- 结果：{"age": 25, "phone": "13800138000"}-- JSON_REPLACE 不会插入SELECTJSON_REPLACE('{"age": 25}','$.phone','13800138000');-- 结果：{"age": 25}查询 JSON 数据
JSON_EXTRACT
最基本的查询函数，用路径表达式提取值：
-- 两种写法等价SELECTJSON_EXTRACT(profile,'$.age')FROMusersWHEREid=1;SELECTprofile->'$.age'FROMusersWHEREid=1;$.age是 JSON 路径表达式，$表示 JSON 文档的根。
箭头运算符 -> 和 ->>
->返回带引号的 JSON 格式值，->>返回去掉引号的纯文本：
SELECTprofile->'$.city'FROMusersWHEREid=1;-- 结果："北京"（带引号）SELECTprofile->>'$.city'FROMusersWHEREid=1;-- 结果：北京（不带引号）在 WHERE 条件里，推荐用->>，因为->返回的带引号字符串可能导致比较失败：
-- 这样写可能匹配不到SELECT*FROMusersWHEREprofile->'$.city'='北京';-- 推荐写法SELECT*FROMusersWHEREprofile->>'$.city'='北京';嵌套 JSON 查询
-- 假设 profile 是 {"name": "张三", "address": {"city": "北京", "district": "海淀"}}SELECTprofile->>'$.address.city'FROMusersWHEREid=1;JSON 数组查询
-- 假设 profile 是 {"tags": ["java", "mysql", "redis"]}-- 获取数组元素（下标从 0 开始）SELECTprofile->'$.tags[0]'FROMusersWHEREid=1;-- "java"-- 查询包含某个元素的记录SELECT*FROMusersWHEREJSON_CONTAINS(profile->'$.tags','"java"');-- 或者用 JSON_MEMBER OF（MySQL 8.0.17+）SELECT*FROMusersWHERE'java'MEMBEROF(profile->'$.tags');JSON_TABLE（MySQL 8.0+）
把 JSON 数组展开成关系型表，做关联查询非常方便：
SELECTu.name,jt.tagFROMusers u,JSON_TABLE(u.profile->'$.tags','$[*]'COLUMNS(tagVARCHAR(20)PATH'$'))ASjtWHEREu.id=1;```结果：name tag
张三 java
张三 mysql
张三 redis
这个功能在处理标签、属性列表时特别好用，比在应用层拆 JSON 数组高效多了。 ## JSON 字段的索引 JSON 字段不能直接建索引，但可以通过虚拟列+索引的方式实现： ### 方式一：生成列 + 索引 ```sql -- 添加虚拟生成列 ALTER TABLE users ADD COLUMN city VARCHAR(50) GENERATED ALWAYS AS (JSON_UNQUOTE(JSON_EXTRACT(profile, '$.city'))) VIRTUAL; -- 给生成列建索引 ALTER TABLE users ADD INDEX idx_city (city); -- 查询时自动走索引 SELECT * FROM users WHERE city = '北京';虚拟列不占存储空间，索引指向原表数据。查询时既可以用生成列名，也可以用原始 JSON 路径（MySQL 8.0 会自动优化）。
方式二：多值索引（MySQL 8.0.17+）
对 JSON 数组建索引：
CREATEINDEXidx_tagsONusers((CAST(profile->'$.tags'ASCHAR(20)ARRAY)));-- 查询走索引SELECT*FROMusersWHERE'java'MEMBEROF(profile->'$.tags');多值索引是 MySQL 8.0 的新特性，专门解决 JSON 数组的索引问题。
常用 JSON 函数速查
| 函数 | 作用 | 示例 |
|---|---|---|
| JSON_EXTRACT | 提取值 | JSON_EXTRACT(doc, ‘$.key’) |
| JSON_SET | 设置值（存在更新，不存在插入） | JSON_SET(doc, ‘$.key’, val) |
| JSON_REPLACE | 替换值（仅已存在的） | JSON_REPLACE(doc, ‘$.key’, val) |
| JSON_REMOVE | 删除属性 | JSON_REMOVE(doc, ‘$.key’) |
| JSON_CONTAINS | 是否包含指定值 | JSON_CONTAINS(doc, ‘“val”’, ‘$.key’) |
| JSON_SEARCH | 搜索值所在路径 | JSON_SEARCH(doc, ‘one’, ‘val’) |
| JSON_LENGTH | 数组长度或对象属性数 | JSON_LENGTH(doc, ‘$.key’) |
| JSON_TYPE | 值的类型 | JSON_TYPE(doc, ‘$.key’) |
| JSON_VALID | 是否合法 JSON | JSON_VALID(str) |
| JSON_MERGE_PRESERVE | 合并 JSON | JSON_MERGE_PRESERVE(doc1, doc2) |
踩坑记录
坑一：JSON 路径表达式里的中文键
-- 如果 JSON 的 key 是中文SELECTprofile->>'$."城市"'FROMusers;-- 必须用双引号包裹中文 key坑二：JSON 字段默认值
JSON 类型不能设默认值为字符串：
-- 报错ALTERTABLEusersALTERCOLUMNsettingsSETDEFAULT'{}';-- 正确写法ALTERTABLEusersALTERCOLUMNsettingsSETDEFAULT(JSON_OBJECT());MySQL 8.0 支持用表达式作为默认值，5.7 不行。
坑三：大 JSON 影响性能
JSON 字段虽然内部是二进制格式，但如果单个文档太大（几百 KB），更新时还是要读写整个字段。我之前有个表存了很大的 JSON 配置（平均 50KB），更新一个小属性都要把整个文档重写，性能很差。后来把热字段拆出来做独立列，JSON 只存冷数据，性能好多了。
小结
- JSON 类型适合存灵活的、非结构化的数据，不适合存高频更新的热数据
- 查询用
->>运算符比->更方便，返回不带引号的纯文本
- 查询用
- JSON 字段建索引要通过生成列或多值索引（8.0.17+）
- JSON_SET 和 JSON_REPLACE 的区别要搞清楚
- JSON_TABLE 是 8.0 的利器，把 JSON 数组展开成表做关联
- 大 JSON 文档别频繁更新，把热字段拆出来做独立列
相关阅读
- MySQL 官方文档 - JSON Functions
- JSON Table 详解
- 多值索引