MySQL中查询和操作JSON类型的数据

前言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-$.colorAScolor,attr-$.priceASpriceFROMproduct;读取嵌套对象SELECTname,attr-$.detail.weightASweightFROMproduct;读取数组指定下标元素-- 获取tags数组第0个元素SELECTname,attr-$.tags[0]AStag1FROMproduct;2. JSON_EXTRACT()-本质就是JSON_EXTRACT()的简写形式-- 等价 attr-$.colorSELECTJSON_UNQUOTE(JSON_EXTRACT(attr,$.color))FROMproduct;3. 根据JSON内的值作为WHERE条件过滤-- 查询颜色为黑色的商品SELECTnameFROMproductWHEREattr-$.color黑色;-- 查询价格大于1000SELECTnameFROMproductWHEREattr-$.price1000;4. JSON数组包含判断 JSON_SEARCH查找数组中是否包含指定元素-- 查询tags数组里面包含降噪的商品SELECTnameFROMproductWHEREJSON_SEARCH(attr,one,降噪,null,$.tags)ISNOTNULL;参数说明one找到第一个匹配all查找全部匹配。5. JSON_KEYS 获取JSON对象所有keySELECTJSON_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有则更新无则新增开发首选UPDATEproductSETattrJSON_SET(attr,$.color,深空灰,$.stock,100)WHEREid1;JSON_REPLACE只更新已存在key不存在则不处理UPDATEproductSETattrJSON_REPLACE(attr,$.price,3699)WHEREid1;JSON_INSERT只新增key已有key不会覆盖UPDATEproductSETattrJSON_INSERT(attr,$.stock,200)WHEREid1;JSON数组追加JSON_ARRAY_APPENDUPDATEproductSETattrJSON_ARRAY_APPEND(attr,$.tags,快充)WHEREid1;JSON数组指定位置插入JSON_ARRAY_INSERTUPDATEproductSETattrJSON_ARRAY_INSERT(attr,$.tags[0],新品)WHEREid1;五、JSON删除操作 JSON_REMOVE删除JSON对象key、数组元素-- 删除stock字段UPDATEproductSETattrJSON_REMOVE(attr,$.stock)WHEREid1;-- 删除数组下标1的元素UPDATEproductSETattrJSON_REMOVE(attr,$.tags[1])WHEREid1;⚠️ 删除数组多个元素从大下标往小下标删删除后数组下标自动前移顺序正向删除会导致下标错乱。六、JSON生成列 索引性能优化重点原生JSON字段内部key查询不走索引大数据量查询会全表扫描。可以创建生成列并建立索引-- 创建生成列提取color字段ALTERTABLEproductADDCOLUMNcolorVARCHAR(50)GENERATED ALWAYSAS(attr-$.color)STORED;-- 建立索引CREATEINDEXidx_product_colorONproduct(color);STORED实际存储数据VIRTUAL默认不占用存储空间查询时实时计算不支持加索引。七、常见踩坑汇总路径语法 $ 不能省略$.key嵌套$.obj.key(.obj.key数组)$.arr[0]-带引号-不带引号做等值判断WHERE条件必须用 -JSON函数返回新JSON文档UPDATE时必须赋值回字段attrJSON_SET(...)否则不生效JSON字段本身为NULL时JSON_SET / JSON_REMOVE都会返回NULL可用IFNULL(attr, JSON_OBJECT())兜底JSON数组查找JSON_SEARCH只能匹配字符串数字类型无法直接搜索需要转换不要用LIKE模糊匹配JSON字符串JSON内部空格、顺序变化会导致匹配失效频繁查询JSON内部字段优先拆成独立字段JSON适合存储不常检索的扩展属性八、函数速查表函数作用JSON_EXTRACT提取JSON值JSON_SET更新/新增keyJSON_REPLACE仅更新已有keyJSON_INSERT仅新增keyJSON_REMOVE删除key/数组元素JSON_ARRAY_APPEND数组末尾追加JSON_ARRAY_INSERT数组指定位置插入JSON_SEARCH查找数组/对象内文本JSON_OBJECT构建JSON对象JSON_ARRAY构建JSON数组