MySQL JSON 类型实战：存取、查询函数、生成列索引与 8.0 多值索引全解

个人主页 for_ever_love__ 欢迎各位大佬莅临其他栏目: 大模型开发从0到1 其他栏目: iOS项目总结大全 其他栏目: 我想学python了 其他栏目: iOS UI 文章目录MySQL JSON 类型实战存取、查询函数、生成列索引与 8.0 多值索引全解一、JSON 类型 vs 用 VARCHAR 存 JSON 字符串1.1 写入时会做规范化1.2 大小写敏感与转义1.3 默认值8.0.13 才放开二、路径表达式所有 JSON 函数的基础三、查询-、- 与核心函数3.1 - 和 -最常用也最容易踩坑的一对3.2 函数速查表3.3 JSON_CONTAINS / JSON_CONTAINS_PATH / JSON_SEARCH3.4 8.0.21 的 JSON_VALUE四、修改JSON_SET / JSON_INSERT / JSON_REPLACE 三兄弟4.1 更新表里的 JSON 列4.2 合并JSON_MERGE_PATCH vs JSON_MERGE_PRESERVE五、JSON_TABLE把 JSON 展开成关系表8.0 大杀器六、性能JSON 列不能建索引怎么办6.1 为什么慢6.2 破解办法一生成列 索引5.7 / 8.0 通用6.3 破解办法二多值索引8.0.17针对 JSON 数组6.4 破解办法三8.0 的部分更新七、JSON 的比较与排序规则八、什么时候不该用 JSON九、五个常见误区十、小结MySQL JSON 类型实战存取、查询函数、生成列索引与 8.0 多值索引全解扩展字段到底该不该用 JSON 存存了之后怎么查为什么WHERE json_col-$.id 1慢得离谱这篇把 MySQL 的 JSON 类型从存储原理讲到函数用法重点讲清楚它天生不能建索引这件事以及 5.7 和 8.0 各自的破解办法。一、JSON 类型 vs 用 VARCHAR 存 JSON 字符串MySQL 从5.7.8开始提供原生JSON类型。和把 JSON 当字符串塞进TEXT相比它的优势不是能存而是这四点能力JSON类型TEXT存 JSON 字符串写入校验自动校验非法 JSON 直接报错什么都能塞进去存储格式二进制内部格式可直接按键/下标定位子对象纯文本取值要先解析读取代价无需全文解析直接定位每次都要解析整串函数支持完整的 JSON 函数族只能LIKE/ 正则占用空间与LONGBLOB/LONGTEXT基本相当相当CREATETABLEt1(jdoc JSON);INSERTINTOt1VALUES({key1:value1});-- ✅INSERTINTOt1VALUES(stringvalue);-- ✅ 标量也是合法 JSONINSERTINTOt1VALUES([1, 2,);-- ERROR 3140: Invalid JSON text: Invalid value. at position 6 in value for column t1.jdoc校验是好事也是约束脏数据进不来但也意味着上游任何一次不规范拼接都会让整条 INSERT 失败。1.1 写入时会做规范化插入 JSON 列时 MySQL 会重新整理文档这点很多人不知道。-- 重复 key8.0.3 起采用 last duplicate key winsRFC 7159 规则INSERTINTOt1VALUES({x: 17, x: red});SELECTc1FROMt1;-- {x: red}-- ⚠️ 5.78.0.3 之前保留第一个{x: 17}-- key 会被排序、多余空格会被丢弃SELECTJSON_OBJECT(key3,v3,key1,v1,key2,v2);-- {key1: v1, key2: v2, key3: v3} 官方明确说 key 的排序结果可能变化不保证跨版本一致别依赖 JSON 的字段顺序做业务判断。1.2 大小写敏感与转义JSON 内部的null/true/false必须小写SQL 关键字反而不区分大小写SELECTJSON_VALID(null),JSON_VALID(Null),JSON_VALID(NULL);-- 1 | 0 | 0SELECTCAST(NullASJSON);-- ERROR: Invalid JSON textSELECTISNULL(null),ISNULL(Null),ISNULL(NULL);-- 1 | 1 | 1想在 JSON 里存字面量双引号通过 SQL 字符串字面量插入时要双反斜杠否则 SQL 层先把它吃掉-- ❌ ERROR 3140: Missing a comma or }INSERTINTOfactsVALUES({mascot: a dolphin named \Sakila\.});-- ✅ 双反斜杠INSERTINTOfactsVALUES({mascot: a dolphin named \\Sakila\\.});-- ✅ 更推荐用 JSON_OBJECT() 构造不手写字符串INSERTINTOfactsVALUES(JSON_OBJECT(mascot,a dolphin named Sakila.));结论能用JSON_OBJECT()/JSON_ARRAY()构造就别手写字符串字面量。1.3 默认值8.0.13 才放开5.7 到 8.0.13 之前JSON 列不能有非 NULL 的默认值8.0.13 起可以用表达式默认值。生产上更常见的写法是允许为 NULL用 NULL 表示没有扩展信息CREATETABLEt(idBIGINTPRIMARYKEY,ext JSONNULL-- 推荐-- ext JSON NOT NULL DEFAULT (JSON_OBJECT()) -- 8.0.13 才支持);二、路径表达式所有 JSON 函数的基础路径以$开头表示整个文档后面接选择器语法含义例子$整个文档.key对象成员$.name.key with space含特殊字符的 key 必须加引号$.a fish[N]数组第 N 个元素从 0 开始$[1][*]数组所有元素 / 对象所有成员$.c[*]、$.*[M to N]数组范围8.0$[last-3 to last-1]last/**最后一个元素 / 递归通配$[last]、$**.cSELECTJSON_EXTRACT([3, {a: [5, 6], b: 10}, [99, 100]],$[1].a[1]);-- 6SELECTJSON_EXTRACT([3, {a: [5, 6], b: 10}, [99, 100]],$[3]);-- NULLSELECTJSON_EXTRACT([1,2,3,4,5],$[last-3 to last-1]);-- [2, 3, 4]三、查询-、-与核心函数3.1-和-最常用也最容易踩坑的一对-- - 等价于 JSON_EXTRACT()结果**带引号**仍是 JSON 类型SELECText-$.cityFROMt;-- 杭州-- - 等价于 JSON_UNQUOTE(JSON_EXTRACT())**去掉引号**SELECText-$.cityFROMt;-- 杭州⚠️这是最高频的踩坑点-- ❌ 永远不成立返回的是 JSON 字符串 杭州不是 杭州SELECT*FROMtWHEREext-$.city杭州;-- 0 行-- ✅SELECT*FROMtWHEREext-$.city杭州;SELECT*FROMtWHEREJSON_UNQUOTE(ext-$.city)杭州;-是 5.7.9 引入的-是5.7.13引入的5.7.9~5.7.12 只能写JSON_UNQUOTE(JSON_EXTRACT(...))。3.2 函数速查表函数作用典型用法JSON_EXTRACT(json, path)抽取路径上的值JSON_EXTRACT(ext, $.a.b)JSON_UNQUOTE(val)去掉外层引号JSON_UNQUOTE(ext-$.city)JSON_CONTAINS(t, cand[, path])是否包含某个值/对象判断数组里有没有某元素JSON_CONTAINS_PATH(j, one/all, p...)路径是否存在判断字段有没有填JSON_SEARCH(j, one/all, str[, esc[, p]])按值反查路径支持%_模糊找 keyJSON_KEYS(json[, path])返回 key 组成的数组看结构JSON_LENGTH(json[, path])数组元素数 / 对象成员数JSON_DEPTH/JSON_TYPE/JSON_VALID深度 / 类型 / 合法性校验JSON_PRETTY/JSON_STORAGE_SIZE格式化 / 实际字节数8.0调试、容量评估JSON_VALUE(json, path RETURNING type)抽取标量并转类型8.0.21见 3.43.3JSON_CONTAINS/JSON_CONTAINS_PATH/JSON_SEARCH-- CONTAINS判断对象里是否含某个键值SELECTJSON_CONTAINS({a: 1, b: 2},1,$.a);-- 1-- CONTAINS判断数组里是否含某元素最常见用法-- ext {tags: [vip, new]}SELECT*FROMtWHEREJSON_CONTAINS(ext,vip,$.tags);-- ⚠️ 第二个参数必须是合法 JSON 值字符串要带引号SELECT*FROMtWHEREJSON_CONTAINS(ext,JSON_QUOTE(vip),$.tags);-- 更安全的写法-- CONTAINS_PATH判断路径是否存在SELECTJSON_CONTAINS_PATH(ext,one,$.a,$.e);-- 1有一个存在即可SELECTJSON_CONTAINS_PATH(ext,all,$.a,$.e);-- 0必须都存在-- SEARCH按值反查路径支持 LIKE 通配SELECTJSON_SEARCH([abc,{x:abc}],all,abc);-- [$[0], $[1].x]SELECTJSON_SEARCH([abc,{x:abc}],all,%a%);-- [$[0], $[1].x]3.4 8.0.21 的JSON_VALUE-返回的都是字符串想拿数字/日期还得再CAST。JSON_VALUE一步到位-- ext {age: 28, join: 2024-03-05}SELECTJSON_VALUE(ext,$.ageRETURNINGSIGNED)ASage,JSON_VALUE(ext,$.joinRETURNINGDATE)ASjoin_dateFROMt;四、修改JSON_SET/JSON_INSERT/JSON_REPLACE三兄弟这三个最容易搞混区别只有一条路径存在与否时分别怎么办。函数路径已存在路径不存在JSON_SET替换新增JSON_INSERT忽略不动新增JSON_REPLACE替换忽略不动SETj[a, {b: [true, false]}, [10, 20]];SELECTJSON_SET(j,$[1].b[0],1,$[2][2],2);-- 替换 新增SELECTJSON_INSERT(j,$[1].b[0],1,$[2][2],2);-- 只新增SELECTJSON_REPLACE(j,$[1].b[0],1,$[2][2],2);-- 只替换4.1 更新表里的 JSON 列-- ❌ 经典错误直接传新文档把整列覆盖了UPDATEdeptSETjson_valueJSON_SET({a:1},$.deptName,部门1)WHEREid2;-- ✅ 第一个参数必须是列本身UPDATEdeptSETjson_valueJSON_SET(json_value,$.deptName,部门1)WHEREid2;UPDATEordersSETextJSON_SET(ext,$.hasInsurance,TRUE,$.channel,app)WHEREid10086;UPDATEordersSETextJSON_REMOVE(ext,$.tmpFlag)WHEREid10086;UPDATEordersSETextJSON_ARRAY_APPEND(ext,$.tags,vip)WHEREid10086;务必注意JSON_SET(ext, ...)的第一个参数必须是列本身写成JSON_SET({a:1}, ...)会把原来存的东西全部冲掉。这是生产事故常见来源。4.2 合并JSON_MERGE_PATCHvsJSON_MERGE_PRESERVEJSON_MERGE()在8.0.3 已废弃取而代之的是两个语义清晰的函数SELECTJSON_MERGE_PRESERVE({a: 1, b: 2},{a: 3, c: 4})ASp,JSON_MERGE_PATCH({a: 1, b: 2},{a: 3, c: 4})ASq;-- Preserve: {a: [1, 3], b: 2, c: 4} 重复 key 的值组成数组保留-- Patch: {a: 3, b: 2, c: 4} 重复 key 取最后一个RFC 7386 打补丁做只更新传入的字段这类接口时JSON_MERGE_PATCH最合适。五、JSON_TABLE把 JSON 展开成关系表8.0 大杀器8.0.4 起提供JSON_TABLE能把 JSON 数组展开成虚拟表之后就能正常 JOIN / 聚合-- orders.ext {items: [{sku: A1, qty: 2}, {sku: B3, qty: 1}]}SELECTo.id,jt.sku,jt.qtyFROMorders o,JSON_TABLE(o.ext,$.items[*]COLUMNS(skuVARCHAR(32)PATH$.skuDEFAULTunknownONEMPTY,qtyINTPATH$.qtyDEFAULT1ONEMPTYDEFAULT0ONERROR,has_giftBOOLEXISTSPATH$.gift,ridFORORDINALITY))ASjtWHEREo.id10086;DEFAULT ... ON EMPTY处理字段缺失DEFAULT ... ON ERROR处理类型不匹配EXISTS PATH判断子路径是否存在FOR ORDINALITY输出行号。5.7 没有JSON_TABLE想展开 JSON 数组只能靠应用层或用数字辅助表 JSON_EXTRACT硬凑。这是 8.0 在 JSON 能力上最大的一次跃升。六、性能JSON 列不能建索引怎么办这是整篇最关键的一节。6.1 为什么慢-- ❌ 必然全表扫SELECT*FROMordersWHEREext-$.city杭州;原因ext是整体的一个列B 树索引只能建在列上不能建在列的某个路径上。ext-$.city是表达式MySQL 无法用任何索引定位它只能逐行解析 JSON 再比较。6.2 破解办法一生成列 索引5.7 / 8.0 通用把要查的路径抽成一个真实的列再给它建索引ALTERTABLEordersADDCOLUMNcityVARCHAR(32)GENERATED ALWAYSAS(ext-$.city)VIRTUAL,ADDINDEXidx_city(city);-- 现在走索引了SELECT*FROMordersWHEREcity杭州;生成列类型是否落盘说明VIRTUAL不存读取时计算推荐省空间索引里照样有值STORED真实存储写入时计算占空间读取更快⚠️ 优化器只有在查询里写的表达式和生成列定义完全一致时才会用上索引SELECT*FROMordersWHEREcity杭州;-- ✅ 走索引SELECT*FROMordersWHEREext-$.city杭州;-- ❌ 还是全表扫所以建了生成列之后一定要把 SQL 也改掉这点非常容易漏。数值类型还要显式 CAST否则抽出来是字符串ALTERTABLEordersADDCOLUMNamountDECIMAL(12,2)GENERATED ALWAYSAS(CAST(ext-$.amountASDECIMAL(12,2)))VIRTUAL,ADDINDEXidx_amount(amount);6.3 破解办法二多值索引8.0.17针对 JSON 数组要查数组里是否包含某个值时一个文档会抽出多个值生成列就不够用了。8.0.17 引入Multi-Valued Index-- ext {tags: [vip, new, app]}CREATEINDEXidx_tagsONcustomers((CAST(ext-$.tagsASCHAR(16)ARRAY)));-- 这三个函数可以走这个索引SELECT*FROMcustomersWHEREJSON_CONTAINS(ext,vip,$.tags);SELECT*FROMcustomersWHEREJSON_OVERLAPS(ext-$.tags,vip);SELECT*FROMcustomersWHEREvipMEMBEROF(ext-$.tags);特性生成列索引多值索引最低版本5.78.0.17适用抽取单个标量值数组多值匹配索引条目数每行 1 条每个数组元素 1 条支持的函数普通比较JSON_CONTAINS/JSON_OVERLAPS/MEMBER OF5.7 要实现对数组元素的高效检索只能拆成关联子表——最正统也最可控的做法。6.4 破解办法三8.0 的部分更新8.0 支持 JSON 的部分更新partial update只改大 JSON 里的某个字段时不必重写整个文档binlog 里也只记录变化的部分。SHOWVARIABLESLIKEbinlog_row_value_options;SETGLOBALbinlog_row_value_optionsPARTIAL_JSON;-- 默认关闭需 binlog_formatROW对大 JSON 频繁小改的场景收益巨大binlog 体积和主从传输量能降一个量级。5.7 没有这个能力。七、JSON 的比较与排序规则JSON 值可以用 ! 比较但跨类型比较靠类型优先级决定BLOB BIT OPAQUE DATETIME TIME DATE BOOLEAN ARRAY OBJECT STRING INTEGER/DOUBLE NULL几个必须记住的点字符串按utf8mb4_bin比较区分大小写A a排序受max_sort_length限制长字符串只比前 N 个字符BETWEEN/IN()/GREATEST()/LEAST()不支持 JSON 值要先 CAST非标量值对象/数组不支持排序会告警。排序最好显式转换SELECTJSON_TYPE(ext-$.age);-- INTEGERSELECT*FROMtORDERBYCAST(ext-$.ageASUNSIGNED);聚合时除MIN()/MAX()/GROUP_CONCAT()外的函数会把 JSON 值转数字对非数值 JSON 没有意义。八、什么时候不该用 JSONJSON 很好用但滥用就是灾难。以下情况应该拆成正规的关系表场景为什么不该用 JSON需要频繁作为查询条件的字段必须靠生成列补索引等于绕了一圈需要参与 JOIN 的字段JSON 路径无法做 JOIN 键需要外键约束 / 唯一约束JSON 内部的值无法加约束需要字段级并发控制JSON 是整列更新两个字段并发改会互相覆盖结构固定且字段数量明确直接建列可读性和性能都更好要做聚合分析每次都要解析走不了索引判断标准很简单这个字段是读多写少、结构不固定、只做整体存取吗是 → 用 JSON需要按内容检索、参与关联、做统计 → 建正规列或子表。典型的合理用法订单ext存渠道、设备、营销标签等只做展示和偶尔筛选的信息。典型的滥用把订单明细商品、数量、价格塞进 JSON然后天天按商品统计销量。九、五个常见误区误区 1WHERE ext-$.city 杭州能查出来——查不出来。-返回带引号的 JSON 字符串杭州和杭州不相等。用-。误区 2JSON 列可以直接建索引——不能。索引只能建在列上不能建在路径上。必须靠生成列索引5.7/8.0或多值索引8.0.17。误区 3JSON_SET只改我传的那个字段——它的第一个参数是源文档。写成JSON_SET({a:1}, ...)会把原值整个覆盖必须写JSON_SET(ext, ...)。误区 4JSON 里字段顺序会保持插入时的样子——不会。MySQL 会规范化key 排序、去重、去空格而且官方不保证排序规则跨版本一致。误区 5JSON 可以代替 EAV 和子表——短期省事长期是债。需要检索、约束、统计的字段还是得回到关系模型。十、小结JSON 类型的核心价值是写入校验 二进制存储 快速定位子对象不是能存 JSON路径表达式是所有函数的基础$/.key/[N]从 0 开始/[*]/**/last-带引号-不带引号。按值查询必须用-或JSON_UNQUOTEJSON_SET/JSON_INSERT/JSON_REPLACE的区别只在路径存在与否时的行为替换新增 / 只新增 / 只替换更新时第一个参数必须传列本身JSON_SET(ext, ...)不是JSON_SET({}, ...)JSON_MERGE_PATCH才是打补丁语义JSON_MERGE在 8.0.3 已废弃JSON 列不能直接建索引5.7/8.0 用生成列 索引8.0.17 对数组可用多值索引建了生成列还要改 SQL继续写原表达式依然走不上索引8.0 独占能力JSON_TABLE8.0.4、多值索引8.0.17、JSON_VALUE8.0.21、部分更新binlog_row_value_optionsPARTIAL_JSON、JSON_SCHEMA_VALID8.0.17别滥用需要检索、关联、约束、统计的字段请建正规列或子表一句话JSON 适合整体存取的扩展属性不适合要被查询和分析的业务数据