MySQL 从 5.7.8 开始提供原生 JSON 类型：字段存二进制格式而非纯文本，可以用 JSON_OBJECT() / JSON_ARRAY() 构造，用 JSON_SET() / JSON_INSERT() / JSON_REPLACE() / JSON_REMOVE() 局部修改，用 -> 和 ->> 取值。JSON 列最大的限制是不能直接建索引，需要靠虚拟列绕过去。本文把增删改查的常用写法和几个容易踩的坑整理一遍。
一、建表
CREATE TABLE `test_user`
(
`id` INT PRIMARY KEY AUTO_INCREMENT,
`name` VARCHAR(50) NOT NULL,
`info` JSON
);几个前提要知道：
- JSON 列可以为
NULL（除非显式声明NOT NULL），且不能直接建索引，写ADD INDEX (info)会报错。 - JSON 列早期不允许有默认值；MySQL 8.0.13+ 只接受表达式形式的默认值，如
DEFAULT (JSON_OBJECT())，写DEFAULT '{}'不合法。 - 存储是规范化后的二进制格式：多余空格、键的书写顺序、重复键都会被处理掉，所以
SELECT出来的内容和你插入的字符串不一定字面相同（重复键以最后一个为准）。
二、插入
直接写入 JSON 文本：
INSERT INTO test_user(`name`, `info`)
VALUES ('xiaoming', '{
"sex": 1,
"age": 18,
"nick_name": "小萌"
}');更稳妥的做法是用构造函数拼装，避免手写 JSON 字符串出差错：
-- JSON_OBJECT()：构造对象
INSERT INTO test_user(`name`, `info`)
VALUES ('xiaohua', JSON_OBJECT("sex", 0, "age", 17));
-- JSON_ARRAY()：构造数组，可以嵌套在对象里
INSERT INTO test_user(`name`, `info`)
VALUES ('xiaozhang', JSON_OBJECT("sex", 1, "age", 19, "tag", JSON_ARRAY(3, 5, 90)));
-- 也可以把现成的 JSON 文本显式转换
INSERT INTO test_user(`name`, `info`)
VALUES ('xiaolei', CAST('{"sex": 1, "age": 20}' AS JSON));三、查询
1. -> 和 ->> 的区别
SELECT name,
info->'$.nick_name' AS nick_name_json, -- 返回 JSON，字符串带双引号
info->>'$.nick_name' AS nick_name, -- 返回去引号后的文本
info->>'$.sex' AS sex,
info->>'$.tag[2]' AS tag
FROM test_user;| 写法 | 等价函数 | 返回值 |
|---|---|---|
-> |
JSON_EXTRACT(info, '$.nick_name') |
JSON 值，"小萌" 会带双引号 |
->> |
JSON_UNQUOTE(JSON_EXTRACT(...)) |
去掉引号的文本，适合直接展示和比较 |
所以取字符串务必用 ->>；等价的老写法是套一层 JSON_UNQUOTE()：
SELECT name, JSON_UNQUOTE(info->'$.nick_name') AS nick_name
FROM test_user WHERE name = 'xiaoming';2. 路径语法
| 路径 | 含义 |
|---|---|
$ |
整个 JSON 文档 |
$.nick_name |
对象的一级键 |
$.address.city |
嵌套对象的键 |
$.tag[0] / $.tag[2] |
数组下标，从 0 开始（负下标不支持） |
$."nick name" |
键里含空格等特殊字符时用双引号包起来 |
$**.nick_name |
通配符递归匹配（8.0 起在 JSON_EXTRACT 等函数中支持） |
路径不存在时返回 NULL（路径语法写错才是报错），这点和 SQL 查询的习惯一致。
3. 用 JSON 属性作为查询条件
-- 推荐：取出文本再比较
SELECT name, info->>'$.nick_name' AS nick_name
FROM test_user
WHERE info->>'$.nick_name' = '小萌';WHERE info->'$.nick_name' = '小萌' 这种拿 JSON 值直接和字符串比较的写法在多数版本也能命中，但 JSON 与字符串的比较规则在 8.0.17 前后有过调整，跨版本迁移时容易出差异，统一写成 ->> 更保险。
需要判断「是否包含某个值 / 某个路径」时用专门函数：
-- 包含指定键值
SELECT * FROM test_user WHERE JSON_CONTAINS(info, '1', '$.sex');
SELECT * FROM test_user WHERE JSON_CONTAINS(info, '"小萌"', '$.nick_name');
-- 只判断路径是否存在
SELECT * FROM test_user WHERE JSON_CONTAINS_PATH(info, 'one', '$.tag');
-- 数组长度、所有键名、值的类型
SELECT JSON_LENGTH(info->'$.tag'), JSON_KEYS(info), JSON_TYPE(info->'$.sex') FROM test_user;4. 用虚拟列给 JSON 属性建索引
JSON 列不能直接加索引，实际生产里靠「把属性抽成虚拟列，再给虚拟列建索引」来加速：
-- 注意用 ->> 而不是 ->，否则表达式类型是 JSON，赋给 varchar 列不成立
ALTER TABLE `test_user`
ADD `nick_name` VARCHAR(50) GENERATED ALWAYS AS (info ->> '$.nick_name') VIRTUAL;
-- MySQL 5.7 / 8.0 的 InnoDB 都支持给虚拟列建二级索引
ALTER TABLE `test_user` ADD INDEX idx_nick_name (nick_name);
-- 建好之后按普通列查，能走索引
SELECT name, nick_name FROM test_user WHERE nick_name = '小萌';虚拟列不占数据文件空间（值在读取时计算），STORED 则会把计算结果落盘，可按需选择。JSON 数组类字段（如 tag）如果要在 JSON_CONTAINS 上走索引，MySQL 8.0.17+ 还支持多值索引：
ALTER TABLE test_user
ADD INDEX idx_tag ((CAST(info->'$.tag' AS UNSIGNED ARRAY)));四、更新
四个函数的语义完全不同，选错会「以为改了其实没改」：
| 函数 | 路径已存在 | 路径不存在 |
|---|---|---|
JSON_INSERT() |
保持原值不动 | 插入 |
JSON_SET() |
覆盖 | 插入 |
JSON_REPLACE() |
替换 | 忽略（不插入） |
JSON_REMOVE() |
删除 | 忽略 |
-- 只插入新值，已存在的字段不受影响
UPDATE test_user
SET info = JSON_INSERT(info, '$.sex', 1, '$.nick_name', '小花')
WHERE id = 2;
-- 插入新值并覆盖已存在的字段（最常用）
UPDATE test_user
SET info = JSON_SET(info, '$.sex', 2, '$.nick_name', '小张')
WHERE id = 3;
-- 只替换已存在的字段，路径不存在则跳过
UPDATE test_user
SET info = JSON_REPLACE(info, '$.sex', 1, '$.tag', JSON_ARRAY(1, 2, 3))
WHERE id = 2;这里有个高频坑：JSON 函数的参数是 SQL 值，字符串会原样变成 JSON 字符串，不会被当成 JSON 文本解析。所以 JSON_REPLACE(info, '$.tag', '[1,2,3]') 存进去的是字符串 "[1,2,3]"，而不是数组 [1,2,3]，后续 JSON_LENGTH、JSON_CONTAINS 都会出意外结果：
-- 想要数组，用 JSON_ARRAY() 构造
SET info = JSON_SET(info, '$.tag', JSON_ARRAY(1, 2, 3))
-- 或者显式转换已有的 JSON 文本
SET info = JSON_SET(info, '$.tag', CAST('[1,2,3]' AS JSON))另外注意上面第二条 UPDATE 的对象：id = 2 这行是 JSON_OBJECT("sex", 0, "age", 17) 插入的，没有 tag 字段，而 JSON_REPLACE 不会插入不存在的路径，所以这句里 $.tag 部分实际什么也没做——「改了没生效」通常就是这类静默忽略。
五、删除
-- 一次删除多个路径，路径不存在时忽略
UPDATE test_user
SET info = JSON_REMOVE(info, '$.sex', '$.tag')
WHERE id = 1;
-- 删除整个字段（含值）直接置 NULL 即可
UPDATE test_user SET info = NULL WHERE id = 1;
-- 只想删数组里的某个元素，也用 JSON_REMOVE 按下标操作
UPDATE test_user SET info = JSON_REMOVE(info, '$.tag[0]') WHERE id = 3;六、常见坑汇总
- JSON 列不能直接建索引，要建索引必须走虚拟列 / 多值索引。
- 取字符串用
->>，->返回的 JSON 值带双引号，直接展示或比较会出问题。 - JSON 函数里的字符串参数不是 JSON 文本，数组 / 对象要用
JSON_ARRAY()、JSON_OBJECT()或CAST(... AS JSON)。 JSON_INSERT不覆盖、JSON_REPLACE不插入，路径不存在时静默忽略，不报错。- 存储经过规范化：重复键去重、键顺序与空白会被重排，不能拿字符串比较来判断两个 JSON 是否「一样」——用
JSON_CONTAINS或先取字段值再比。 - 大 JSON 慎用：JSON 列不参与统计信息收集，属性又多又深时查询会退化成全表扫描 + 逐行解析，能拆成普通列的字段尽量拆出来。
七、速查卡
-- 取值：-> 返回 JSON（带引号），->> 返回文本
SELECT info->'$.a', info->>'$.a', info->>'$.arr[0]' FROM t;
-- 构造
JSON_OBJECT('k', 1), JSON_ARRAY(1, 2), CAST('{"a":1}' AS JSON)
-- 判断
JSON_CONTAINS(info, '1', '$.sex') -- 是否包含键值
JSON_CONTAINS_PATH(info, 'one', '$.tag') -- 路径是否存在
JSON_LENGTH(info->'$.tag'), JSON_KEYS(info), JSON_TYPE(info->'$.a')
-- 修改
JSON_INSERT(info, '$.a', 1) -- 只插入，不覆盖
JSON_SET(info, '$.a', 1) -- 插入或覆盖
JSON_REPLACE(info, '$.a', 1) -- 只替换，不插入
JSON_REMOVE(info, '$.a', '$.b') -- 删除路径
-- 索引：虚拟列 + 虚拟列索引
ALTER TABLE t ADD c VARCHAR(50) GENERATED ALWAYS AS (info->>'$.c') VIRTUAL;
ALTER TABLE t ADD INDEX idx_c (c);
评论区