我做后端开发这几年MySQL的JSON数据类型原本在我眼里一直是个“花架子”。关系型数据库嘛讲究的是定义清晰、约束严格往表里塞一坨JSON图什么呢直到后来接手了一个动态属性特别多的业务模块用传统的EAV表方案改到想骂人才老老实实把JSON类型捡起来认真研究了一遍它的函数和用法然后越用越顺手。这篇文章就是一次系统的梳理从底层存储、核心函数、索引方案到实战坑点都过一遍。不管你是刚准备在项目里引入JSON的新手还是已经用了但想补全细节的老手应该都能从中找到一些可以直接落地的经验。MySQL从5.7开始原生支持JSON数据类型到8.0又补上了大量函数和优化能力到今天它已经不是一个“临时妥协方案”而是很成熟的工具了。但正因为功能多很多人容易两眼一抹黑或者简单粗暴地把所有数据都塞进JSON里最后查询慢、维护难、数据不一致的问题全冒出来。这篇文章会用实际案例把这些问题一个个拆开讲清楚JSON类型在MySQL里到底能干什么、不能干什么、什么时候用最合适。1. 为什么需要JSON数据类型1.1 业务痛点属性不确定的表该怎么设计先说说我遇到的那个场景。我们做的是一个数据接入平台的配置管理模块每条接入任务有大量“可选属性”有的任务需要配置回调地址有的需要指定数据源类型有的要设置调度时间而且这个属性列表几乎每个季度都在变。如果用传统关系模型来做要么建一张几十个字段的表里面一大半字段对多数记录都是NULL要么建一张EAV表Entity-Attribute-Value用一个实体表加一个属性表去存查询的时候要反复JOIN写起来极其痛苦性能也不好。我见过最野的做法是把所有属性拼成一个长字符串塞进TEXT字段查询全靠LIKE模糊匹配。这个方案最大的问题是你根本没法保证字符串里的内容是合法的也完全没办法对某个属性单独做提取和计算。一旦业务要求“按某个属性过滤”就只能全表扫描然后再在内存里解析数据量一上来就是灾难。JSON数据类型恰好补上了这个缺口。它允许你在一个字段里存结构化的嵌套数据同时又保留了关系型数据库的事务能力、备份机制和SQL查询能力。你不需要事先把所有属性都定义成列只需要在JSON文档里动态增加键值对就行。1.2 JSON类型和VARCHAR/TEXT本质区别我先直接说结论虽然JSON类型底层也是基于字符串存储但从功能上天差地别。存TEXT字段里的一串JSON文本只能叫“字符串”而JSON列是数据库能理解和操作的结构化数据。我们建了一张测试表来做对比验证结果非常清楚能力维度VARCHAR/TEXT存JSON原生JSON类型插入时合法性校验不校验垃圾数据也能进自动校验非法JSON直接报错数据提取靠程序代码解析SQL直接提取如JSON_EXTRACT修改某个内部字段整体读出→改字符串→整体写回JSON_SET局部更新只改需要的部分索引支持只能对完整字符串建索引通过虚拟列或函数建索引存储空间冗余空格和键名重复存储二进制格式相对紧凑但会额外存字典信息存JSON进TEXT字段还有一个隐患你今天写进去的字符串是合法的JSON明天某个同事手一抖拼接的字符串少了一个引号数据就彻底脏了而且不容易被发现。JSON列在写入时就会校验格式不合法的直接拒绝相当于把一半的脏数据风险挡在了入口。另外一个容易被忽略的点是查询能力。MySQL为JSON类型定制了一整套函数体系比如JSON_EXTRACT可以在数据库层面直接取到文档里某个键的值还可以在WHERE条件里用JSON_CONTAINS做包含判断。这些操作如果是基于TEXT存储要么靠应用层取出来再解析要么写正则去匹配效率和开发成本都差一大截。2. JSON数据类型底层原理与使用边界2.1 二进制存储结构MySQL对JSON列的存储方式并不是简单地把JSON文本原封不动塞进磁盘。它内部会做一层转换生成一种紧凑的二进制格式。这种格式和BSON有点像但具体实现是MySQL自己的方案。编译成二进制的好处主要体现在两个方面。第一读取的时候不需要对整段文本做语法解析直接读取二进制结构就能拿到对应键的值这在频繁提取数据时比文本解析快很多。第二MySQL会为JSON对象的键建立一份“字典”定位某个键的位置更快。这个设计有点像给一段文本做了索引再存起来代价是存储的时候需要额外的转换开销写入速度理论上比存TEXT略慢一点。这也就引出一个很重要的实操建议不要指望JSON列的二进制格式一定比存VARCHAR更省空间。它省掉了大量冗余空格和格式字符但增加了内部字典和结构信息。我实测过对于结构规整、没有多余空格的JSON文档二进制存储通常会比原文本大个10%-20%如果是带大量缩进的漂亮格式文本那二进制存储反而更小。所以你还真别把JSON列当成“压缩利器”来用。2.2 使用边界与约束JSON类型在MySQL里有一些硬性限制这些限制在官方文档里写得比较分散我整理一套经常用到的字符集要求JSON列默认使用utf8mb4不能单独指定其他字符集。如果你的连接字符集不是utf8mb4插入包含中文等非ASCII字符时可能出问题。做项目的时候连接串里加上characterEncodingutf8mb4Java侧或者连接后执行SET NAMES utf8mb4是个好习惯。大小限制单个JSON文档的大小受max_allowed_packet参数限制默认一般是64MB。对于绝大多数业务场景这个限制够用了但如果你想在JSON里存大文本或Base64图片编码就需要格外留意。不能直接建索引官方明确不允许对JSON列本身直接建索引执行CREATE INDEX会直接报错。想对JSON里的某个字段建立索引必须通过虚拟列或者MySQL 8.0的函数索引来实现。这个我后面专门写一节。JSON中的NULL区别于SQL NULLJSON文档里的null是一个值而列本身的NULL是“没有值”。这两个在查询时要用不同的方式判断SQL的IS NULL查不到JSON文档里的null值。键的顺序不保证MySQL存储JSON对象时不保证键的顺序和原始文本完全一致。所以写代码时永远不要依赖JSON对象里的键顺序。2.3 JSON路径表达式基础要熟练使用JSON函数必须先理解JSON路径表达式。它的语法是MongoDB和XPath的混合体核心规则其实不多路径示例含义$整个JSON文档$.name对象里的name属性$.tags[0]对象里tags数组的第一个元素$[0].id文档本身是数组时的第一个元素的id$.items[*].priceitems数组里所有元素的price$**.keyword递归查找文档任意层级中的keyword键路径表达式是几乎所有JSON函数的基础参数。JSON_EXTRACT、JSON_SET、JSON_REMOVE这些函数都要传路径进去路径写错很容易出现“数据没取到但也不报错”的诡异现象。我自己的排查经验是先用SELECT JSON_KEYS(json_col)或者直接SELECT json_col看一下整体结构再构造路径比靠猜快得多。3. JSON函数全解析与实操案例3.1 创建JSONJSON_ARRAY与JSON_OBJECT在写SQL时直接构造JSON最常用的是两个函数。JSON_ARRAY把参数拼成一个JSON数组JSON_OBJECT把键和值拼成一个JSON对象。它们的用法很有意思JSON_OBJECT接收的参数是键值交替出现的而且如果某个值为NULL它并不会把整个键忽略而是把该键的值设为JSON的null。-- 返回 [apple, 3, true] SELECT JSON_ARRAY(apple, 3, TRUE); -- 返回 {name: 张三, age: 28, address: null} SELECT JSON_OBJECT(name, 张三, age, 28, address, NULL);实际项目中这两个函数最常用的场景是把查询结果构造成自定义JSON结构。比如我从用户表查出数据直接在前端要以JSON数组返回就不需要在Java或PHP里再手动拼字符串了SELECT JSON_ARRAYAGG(JSON_OBJECT(id, id, name, name)) FROM users;这个写法把JSON_ARRAY和SQL的聚合逻辑结合得非常自然避免了ORM层二次处理。3.2 提取数据JSON_EXTRACT、-、-JSON_EXTRACT是提取数据最基础的函数接收两个参数JSON文档和路径。它返回的是一个JSON类型的值可能是字符串、数字、数组或对象。为了方便MySQL还提供了两个简写操作符。箭头函数-等价于JSON_EXTRACT而箭头箭头-等价于JSON_UNQUOTE(JSON_EXTRACT(...))也就是顺便把结果外层包裹的引号去掉直接返回纯字符串。-- 原始数据{name: 张三, tags: [a, b]} SELECT json_col - $.name FROM t; -- 返回 张三带引号JSON格式 SELECT json_col - $.name FROM t; -- 返回 张三纯文本 SELECT json_col - $.tags[0] FROM t; -- 返回 a这里我踩过一个坑用-取字段时返回值是带引号的JSON字符串很多新人在后面直接拼SQL做字符串比较怎么比都不相等卡了半天才发现值里面带着双引号。所以做条件判断的时候优先用-省掉一层JSON_UNQUOTE。但如果你的比较对方是一个JSON值比如JSON_CONTAINS这种函数反而需要保留JSON格式此时用-更合适。3.3 查询匹配JSON_CONTAINS与JSON_CONTAINS_PATH这两个函数是我在业务中使用频率最高的一对。JSON_CONTAINS用来判断一个JSON文档是否包含另一个候选JSON文档可以指定路径缩小搜索范围JSON_CONTAINS_PATH则判断指定的路径是否存在。-- 判断tags数组中是否包含字符串a SELECT * FROM t WHERE JSON_CONTAINS(json_col - $.tags, a); -- 判断是否存在detail.addr这个路径 SELECT * FROM t WHERE JSON_CONTAINS_PATH(json_col, one, $.detail.addr);注意JSON_CONTAINS的第二个参数是JSON格式的候选值字符串必须自己带上引号写成a而不是a这是最容易出错的地方。你可以理解为候选值本身就是一个合法的JSON所以字符串要加双引号包起来。JSON_CONTAINS_PATH的第二个参数有三个可选模式one表示多个路径只要有一个存在就为真all表示全部存在才为真。这个函数最常用于判断一个JSON文档中是否有某些可选字段避免直接取路径取到NULL时还要额外判断。3.4 修改文档JSON_SET、JSON_INSERT、JSON_REPLACE、JSON_REMOVE、JSON_MERGE_*修改JSON文档的函数新手最容易混淆的是JSON_SET、JSON_INSERT和JSON_REPLACE。我用一句话区分它们JSON_SET存在就覆盖不存在就添加。相当于“写了就生效”。JSON_INSERT只添加不存在的键已存在的键保持原值。JSON_REPLACE只覆盖已存在的键不存在的键忽略。JSON_REMOVE按路径删除键路径不存在则忽略。-- 原值{a: 1, b: 2} -- 结果{a: 9, b: 2, c: 3} SELECT JSON_SET(json_col, $.a, 9, $.c, 3); -- 结果{a: 1, b: 2, c: 3} SELECT JSON_INSERT(json_col, $.a, 9, $.c, 3); -- 结果{a: 9, b: 2} SELECT JSON_REPLACE(json_col, $.a, 9, $.c, 3); -- 结果{a: 1} SELECT JSON_REMOVE(json_col, $.b);除了这四个还有一对合并函数JSON_MERGE_PATCH和JSON_MERGE_PRESERVE。MySQL 8.0里老的JSON_MERGE被改名成了JSON_MERGE_PRESERVE语义不变会在合并数组时把两个数组的元素合并到一起。而JSON_MERGE_PATCH按照RFC 7396标准合并数组会被后者整体替换。大多数业务场景用PATCH更符合直觉但要注意它和PRESERVE在数组合并上的区别不然数据会莫名其妙对不上。3.5 高级函数JSON_TABLE、JSON_ARRAYAGG、JSON_OBJECTAGG先讲JSON_TABLE我也认为这是MySQL 8.0里最被低估的一个JSON函数。它的作用是把JSON数组“转置”成一张虚拟的关系表从而可以跟普通表做JOIN或者直接参与聚合运算。这在处理嵌套JSON细节数据时是杀手级功能。假设我们订单表里有一个items字段存的是商品明细数组[ {name: 鼠标, price: 99, count: 2}, {name: 键盘, price: 199, count: 1} ]过去想统计每个订单的商品总数只能把整条JSON取到应用里循环解析。用JSON_TABLE就不一样了SELECT t.order_id, d.name, d.price FROM orders t JOIN JSON_TABLE(t.items, $[*] COLUMNS ( name VARCHAR(50) PATH $.name, price DECIMAL(10,2) PATH $.price, count INT PATH $.count ) ) AS d;JSON_TABLE的第二个参数指定遍历路径$[*]表示遍历数组每个元素。COLUMNS子句里为每个要提取的字段定义类型和路径。这个函数把JSON查询和关系查询打通了非常适合在报表里直接展开存储的明细数据。另外两个聚合函数JSON_ARRAYAGG把多行数据聚合成JSON数组JSON_OBJECTAGG把两列分别聚合成JSON对象的键和值。它们配合GROUP BY使用效果很好比在应用层拼JSON数组要高效得多。3.6 格式化与检查JSON_PRETTY、JSON_VALID、JSON_TYPEJSON_PRETTY单纯把JSON文本格式化输出成带缩进的可读形式适合在控制台排查或导出报表时用。JSON_VALID判断一个字符串是不是合法的JSON返回1或0。这个函数在清洗历史脏数据时特别有用——可以把TEXT列里的内容过一遍JSON_VALID快速找出哪些行是坏数据这在数据迁移场景里是大杀器。JSON_TYPE返回JSON值的类型范围包括OBJECT、ARRAY、STRING、INTEGER、BOOLEAN、NULL等。调试路径表达式时可以先调JSON_TYPE确认某个路径到底取到的是什么类型再决定后续操作。比如路径写错时它可能返回NULL而路径取到数字时返回INTEGER从而帮你快速定位“是路径不对还是数据本身类型不对”。4. JSON索引策略与性能优化4.1 为什么JSON列不能直接加索引很多人的第一反应是“我经常按JSON里的某个字段过滤给它加个索引不就行了”。但MySQL不允许对JSON列直接创建索引原因是JSON是半结构化数据行的内容千差万别传统B树索引根本没法直接建立在可变结构的字段上。这其实是合理的如果JSON列可以直接加索引那索引的可选择性反而会非常差。那实际业务里要怎么加速对JSON字段的查询答案是把JSON里的关键字段“提取”出来变成普通的关系型列再对这个列建索引。提取有两种方式虚拟列生成列和MySQL 8.0的函数索引。两者思路一致只是实现细节不同。4.2 方案一虚拟列生成列加索引生成列Generated Column的原理是在定义表结构时用一个表达式从其他列计算出一个新列存储引擎在插入和更新时自动维护这个值。MySQL 5.7开始JSON的提取表达式可以直接用于生成列。这相当于在JSON文档之上创建了一个“影子列”把频繁查询的某个键的值提速到索引可用的水平。ALTER TABLE t ADD COLUMN user_name VARCHAR(64) GENERATED ALWAYS AS (json_col - $.name) STORED, ADD INDEX idx_user_name (user_name);这里有两点实操经验生成列分为VIRTUAL虚拟不占磁盘存储空间只在使用时计算和STORED存储实际持久化。虚拟列也能建索引而且不占额外空间在8.0里一般优先用虚拟列。但查询时虚拟列的值会现场计算如果计算开销大性能会打折STORED适用于读取频率极高的场景。生成列表达式里用到的JSON提取路径必须是非随机的、能被MySQL判定为“确定性”的。路径本身固定就行不用担心。4.3 方案二MySQL 8.0函数索引MySQL 8.0.13以后可以直接对表达式建索引不再需要显式声明生成列。语法上就是在CREATE INDEX里包一个表达式CREATE INDEX idx_user_name ON t ((CAST(json_col - $.name AS CHAR(32))));注意表达式必须被两组圆括号包起来。因为JSON提取结果的类型不确定通常要包一层CAST指定类型让优化器可以准确估算索引的选择性。函数索引的好处是表结构不用动建索引时不额外增加列定义局限性是书写上更复杂而且不同版本对表达式语法的支持细节略有差异团队协作时最好约定一种统一写法。4.4 性能实践与优化建议我在一个大约500万行的日志表上做过对比测试需求是“按LOG_LEVEL字段统计某天的错误日志数量”。不加任何索引用JSON_CONTAINS直接查单次查询耗时在2秒左右加上虚拟列索引后查询时间压到了20毫秒以内差别接近100倍。这个量级的提升充分说明了“能用索引就绝不全表扫”的重要性。但JSON查询的性能问题并不只是索引能解决的。有几个额外的优化点控制JSON文档大小JSON字段别一股脑地把所有关联数据塞进去。设计时要清楚这个文档的核心用途是什么大对象放最小必要字段超大文档提取和更新都慢对存储和网络也有压力。避免在WHERE中直接对JSON_EXTRACT结果做函数运算比如WHERE YEAR(JSON_EXTRACT(...)) 2024这种写法索引基本失效。更好的做法是提取到生成列然后对生成列做范围查询。警惕隐式类型转换JSON_EXTRACT返回的是JSON字符串格式和数字比较时MySQL会做类型转换类型转换可能会导致无法正确利用索引。实践时尽量用CAST明确类型同时确保查询参数类型和生成列类型一致。5. 常见问题与排查技巧实录5.1 高频报错与解决办法把我在群里被问到最多的几个问题做成速查表每一个都是真实踩过的坑线上/开发中的异常现象根本原因解决方案插入时提示“Invalid JSON text”JSON字符串里存在引号不配对或多余逗号用JSON_VALID在应用层提前校验或用JSON_PRETTY格式化肉眼排查JSON_CONTAINS判断总是返回0候选值没带JSON引号如写成JSON_CONTAINS(col, a)写成a候选字符串必须加双引号或改用JSON_CONTAINS_PATH用-查到值带引号字符串比较不一致-返回的是JSON格式值外层有引号WHERE条件改用-需要JSON值做函数判断时保留-对JSON列加索引报错JSON类型的列本身不允许直接建索引生成列或函数索引从JSON里提取字段再建JSON文档里中文字符变问号连接字符集不是utf8mb4SET NAMES utf8mb4JDBC连接串加characterEncodingutf8mb48.0用了JSON_MERGE报错这个函数在8.0改名了改用JSON_MERGE_PRESERVE或JSON_MERGE_PATCH5.2 MySQL 5.7到8.0的兼容性差异这两个版本在JSON功能上的差异很大生产环境如果涉及升级这些问题很实用。最主要的差异包括8.0新增JSON_TABLE函数支持把JSON数组直接转为虚拟表5.7做不到。8.0中JSON_MERGE被废弃并改名旧脚本直接迁移会报错。-操作符在5.7.13才加入更早的版本只能用JSON_UNQUOTE(JSON_EXTRACT(...))。8.0的函数索引可以做JSON表达式的索引5.7只能通过生成列实现同样的效果。8.0支持多值索引在JSON数组字段上建索引成为可能对查询JSON数组中的元素有很大帮助。我在这边做数据库升级测试时最先排查的就是所有和JSON相关的存储过程、视图和定时任务脚本。有几个存储过程在5.7里跑得好好的升级到8.0重启后直接编译失败排查下来全部和JSON_MERGE改名有关。所以版本变更前先搜一遍代码库里所有JSON_MERGE等旧函数的调用是个成本很低、收益不小的习惯。5.3 实战中的避坑经验最后分享几个只会在业务中遇到的隐性经验这些在文档里可不会写得那么细第一个批量更新JSON列里的子字段不要一条条UPDATE。假设你有1000条记录每条都要改内部的status字段逐条UPDATE会带来极其严重的事务日志膨胀和锁竞争。更好的办法是先把记录的主键和JSON修改参数导入临时表再用JOIN UPDATE一次性更新更新的量级能小很多。第二个处理JSON字段时注意结合事务使用。JSON_SET这类修改操作在MySQL里并不是原子的它内部是先读出旧值、修改、再写回。如果并发两个事务同时更新同一个JSON字段可能出现后写覆盖先写的丢失更新问题。关键业务字段的并发修改要么使用SELECT ... FOR UPDATE加锁要么进行版本控制。第三个别对JSON里的数据做复杂聚合尤其是嵌套数组的聚合。JSON_TABLE打开后确实是虚拟表但性能上没有真正的物理表那么高效。如果某条SQL要批量展开数十万行记录里的JSON数组还做多表JOIN性能很可能不够乐观。这种需求在设计期就要考虑要不要把高频使用的明细数据冗余成独立的子表而不是依赖JSON展开。6. 我应该什么时候用JSON什么时候不用6.1 适合用JSON的场景JSON类型最合适的地方是字段结构不确定、需求变化频繁、且对单字段的查询不是极高频的场景。比如配置信息、用户扩展属性、电商订单的快照数据、埋点日志的详情字段。这些数据的共同点是“重写入、轻查询”或者“结构经常变不适合硬编码成表结构”。我自己最常用的一个例子是元数据表。系统里保存了一批API接口的定义每个接口的请求参数、返回字段、校验规则都不一样但又要统一存到一张表里方便管理。这种场景用JSON列简直太合适了——校验规则是嵌套结构请求参数是数组返回字段又是对象。改一个字段只动JSON内部不需要频繁加列。6.2 不适合用JSON的场景JSON类型的隐患在于“过度使用”。如果你发现代码里超过一半的查询都在用JSON_EXTRACT、JSON_CONTAINS做条件过滤而且数据量已经到千万级那就该考虑把它拆成真正的列了。理由很简单JSON里的字段毕竟没有强约束、没有全局唯一性也没有SQL级别的外键大量依赖JSON字段查询会让SQL的可读性显著下降优化器也很难用好索引。另外强一致性和强关联的数据一定要避开JSON。比如订单金额、库存数量这种需要做事务并发控制、需要各种约束的敏感数据放进JSON里看起来省事实则后患无穷。你根本无法在JSON字段上定义CHECK约束去校验金额必须大于0也无法直接建立外键引用。这类数据还是要放在正常的列上让数据库帮你守住底线。6.3 混合使用也是一种选择在实践中多数项目往往会采用“关系型列JSON扩展字段”的混合模式。核心基础字段用传统列定义保证查询性能和约束不确定的扩展信息放进一个JSON列保持灵活性。这种折中方案是我最推荐的做法。它既有结构化数据的稳定性又有半结构化数据的灵活度而且演进成本低万一某些JSON字段后来变成了稳定需求完全可以通过生成列慢慢把它“提升”成正式列而不需要大规模迁移数据。我就用这种方案将一个原本基于EAV表的老模块重构成了“基础列JSON扩展”的新结构。改造后SQL数量减少了一半以上查询速度大幅提升扩展新属性时也不用再动表结构了。每次有业务方过来提“能不能再加一个属性”的需求我只需要更新JSON里的键其余什么都不用做。JSON数据类型是个典型的“双刃剑”。用对了它能帮你把动态业务快速落地省掉大量冗余表设计用错了它也能让你的数据库变成一个谁都不敢动的结构化黑洞。我的建议是把它当成一种“补充手段”而不是“万能容器”在动手设计表之前先想清楚这条数据的生命周期里哪些字段是稳定的、哪些是可变的再决定让JSON承担哪一部分职责。数据库设计里没有银弹好的方案永远是结合场景反复权衡出来的。