前言：MySQL5.7 开始正式支持原生JSON字段，8.0进一步增强JSON函数能力。很多开发会直接把JSON存成VARCHAR，但原生JSON会校验格式，并且提供内置函数方便读取、修改、删除JSON内的key和数组。这篇博客包含建表、查询、新增、修改、删除JSON数据，附带常用函数和避坑点。

一、测试表准备

CREATETABLEproduct(idINTPRIMARYKEYAUTO_INCREMENT,nameVARCHAR(100)NOTNULL,attr JSONCOMMENT'商品扩展JSON属性');-- 插入测试数据INSERTINTOproduct(name,attr)VALUES('手机','{"color":"黑色","price":3999,"tags":["旗舰","5G"],"detail":{"weight":"200g","screen":"6.7英寸"}}'),('耳机','{"color":"白色","price":299,"tags":["蓝牙","降噪"],"detail":{"weight":"8g","screen":null}}');

二、JSON查询操作

1. -> 和 ->> 提取JSON字段值（最常用）

• ->：返回带双引号的JSON原始值
• ->>：返回去掉双引号的字符串，日常查询优先用 ->>

-- 查询商品名称、颜色、价格SELECTname,attr->>'$.color'AScolor,attr->>'$.price'ASpriceFROMproduct;

读取嵌套对象：

SELECTname,attr->>'$.detail.weight'ASweightFROMproduct;

读取数组指定下标元素：

-- 获取tags数组第0个元素SELECTname,attr->>'$.tags[0]'AStag1FROMproduct;

2. JSON_EXTRACT()

->本质就是JSON_EXTRACT()的简写形式

-- 等价 attr->>'$.color'SELECTJSON_UNQUOTE(JSON_EXTRACT(attr,'$.color'))FROMproduct;

3. 根据JSON内的值作为WHERE条件过滤

-- 查询颜色为黑色的商品SELECTnameFROMproductWHEREattr->>'$.color'='黑色';-- 查询价格大于1000SELECTnameFROMproductWHEREattr->>'$.price'>1000;

4. JSON数组包含判断 JSON_SEARCH

查找数组中是否包含指定元素

-- 查询tags数组里面包含"降噪"的商品SELECTnameFROMproductWHEREJSON_SEARCH(attr,'one','降噪',null,'$.tags')ISNOTNULL;

参数说明：one找到第一个匹配；all查找全部匹配。

5. JSON_KEYS 获取JSON对象所有key

SELECTJSON_KEYS(attr)FROMproduct;

三、JSON写入（插入）

直接传入标准JSON字符串，或者使用JSON_OBJECT、JSON_ARRAY构造JSON：

-- 方式1：直接字符串INSERTINTOproduct(name,attr)VALUES('平板','{"color":"灰色","price":2499}');-- 方式2：函数构造，不用手动写引号，不容易出错INSERTINTOproduct(name,attr)VALUES('手表',JSON_OBJECT("color","银色","price",1299,"tags",JSON_ARRAY("智能","防水")));

四、JSON更新操作

JSON_SET：有则更新，无则新增（开发首选）

UPDATEproductSETattr=JSON_SET(attr,'$.color','深空灰','$.stock',100)WHEREid=1;

JSON_REPLACE：只更新已存在key，不存在则不处理

UPDATEproductSETattr=JSON_REPLACE(attr,'$.price',3699)WHEREid=1;

JSON_INSERT：只新增key，已有key不会覆盖

UPDATEproductSETattr=JSON_INSERT(attr,'$.stock',200)WHEREid=1;

JSON数组追加：JSON_ARRAY_APPEND

UPDATEproductSETattr=JSON_ARRAY_APPEND(attr,'$.tags','快充')WHEREid=1;

JSON数组指定位置插入：JSON_ARRAY_INSERT

UPDATEproductSETattr=JSON_ARRAY_INSERT(attr,'$.tags[0]','新品')WHEREid=1;

五、JSON删除操作 JSON_REMOVE

删除JSON对象key、数组元素

-- 删除stock字段UPDATEproductSETattr=JSON_REMOVE(attr,'$.stock')WHEREid=1;-- 删除数组下标1的元素UPDATEproductSETattr=JSON_REMOVE(attr,'$.tags[1]')WHEREid=1;

⚠️ 删除数组多个元素：从大下标往小下标删，删除后数组下标自动前移，顺序正向删除会导致下标错乱。

六、JSON生成列 + 索引（性能优化重点）

原生JSON字段内部key查询不走索引，大数据量查询会全表扫描。可以创建生成列并建立索引：

-- 创建生成列，提取color字段ALTERTABLEproductADDCOLUMNcolorVARCHAR(50)GENERATED ALWAYSAS(attr->>'$.color')STORED;-- 建立索引CREATEINDEXidx_product_colorONproduct(color);

STORED：实际存储数据；VIRTUAL（默认）不占用存储空间，查询时实时计算，不支持加索引。

七、常见踩坑汇总

1. 路径语法 $ 不能省略，$.key，嵌套$.obj.key(.obj.key；数组)$.arr[0]
2. ->带引号，->>不带引号，做等值判断WHERE条件必须用 ->>
3. JSON函数返回新JSON文档，UPDATE时必须赋值回字段attr=JSON_SET(...)，否则不生效
4. JSON字段本身为NULL时，JSON_SET / JSON_REMOVE都会返回NULL，可用IFNULL(attr, JSON_OBJECT())兜底
5. JSON数组查找JSON_SEARCH只能匹配字符串；数字类型无法直接搜索，需要转换
6. 不要用LIKE模糊匹配JSON字符串，JSON内部空格、顺序变化会导致匹配失效
7. 频繁查询JSON内部字段，优先拆成独立字段；JSON适合存储不常检索的扩展属性

八、函数速查表