开场白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: 深圳}WHEREid1;-- 只更新某个属性推荐UPDATEusersSETprofileJSON_SET(profile,$.age,26)WHEREid1;-- 新增属性UPDATEusersSETprofileJSON_SET(profile,$.email,zhangsantest.com)WHEREid1;-- 删除属性UPDATEusersSETprofileJSON_REMOVE(profile,$.email)WHEREid1;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)FROMusersWHEREid1;SELECTprofile-$.ageFROMusersWHEREid1;$.age是 JSON 路径表达式$表示 JSON 文档的根。箭头运算符 - 和 --返回带引号的 JSON 格式值-返回去掉引号的纯文本SELECTprofile-$.cityFROMusersWHEREid1;-- 结果北京带引号SELECTprofile-$.cityFROMusersWHEREid1;-- 结果北京不带引号在 WHERE 条件里推荐用-因为-返回的带引号字符串可能导致比较失败-- 这样写可能匹配不到SELECT*FROMusersWHEREprofile-$.city北京;-- 推荐写法SELECT*FROMusersWHEREprofile-$.city北京;嵌套 JSON 查询-- 假设 profile 是 {name: 张三, address: {city: 北京, district: 海淀}}SELECTprofile-$.address.cityFROMusersWHEREid1;JSON 数组查询-- 假设 profile 是 {tags: [java, mysql, redis]}-- 获取数组元素下标从 0 开始SELECTprofile-$.tags[0]FROMusersWHEREid1;-- java-- 查询包含某个元素的记录SELECT*FROMusersWHEREJSON_CONTAINS(profile-$.tags,java);-- 或者用 JSON_MEMBER OFMySQL 8.0.17SELECT*FROMusersWHEREjavaMEMBEROF(profile-$.tags);JSON_TABLEMySQL 8.0把 JSON 数组展开成关系型表做关联查询非常方便SELECTu.name,jt.tagFROMusers u,JSON_TABLE(u.profile-$.tags,$[*]COLUMNS(tagVARCHAR(20)PATH$))ASjtWHEREu.id1;结果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-$.tagsASCHAR(20)ARRAY)));-- 查询走索引SELECT*FROMusersWHEREjavaMEMBEROF(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是否合法 JSONJSON_VALID(str)JSON_MERGE_PRESERVE合并 JSONJSON_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.17JSON_SET 和 JSON_REPLACE 的区别要搞清楚JSON_TABLE 是 8.0 的利器把 JSON 数组展开成表做关联大 JSON 文档别频繁更新把热字段拆出来做独立列相关阅读MySQL 官方文档 - JSON FunctionsJSON Table 详解多值索引
