做过几年业务系统处理过不少歪七扭八的数据结构我对 MySQL JSON 类型的感情比较复杂。刚接触时觉得它好用复杂查询一来又觉得它鸡肋用顺了以后才明白问题不在 JSON 本身而在用的人是否清楚它的边界。今天想把这几年积累下来的相关经验完整整理一遍包括存储原理、常用函数、索引设计还有迁移和踩坑的那些事。如果你正打算把一些扩展字段、配置快照、三方回调日志或动态表单数据存进 MySQL这篇文章能帮你直接建立一套可落地的判断标准和操作套路。如果你是那种习惯把所有数据都拆成范式表的“关系型原教旨主义者”也建议看看因为现实中总有一些场景老老实实建表反而是在过度设计。1. JSON 类型能干成什么事先分清“友好区”和“危险区”1.1 一个经典例子扩展字段到底该不该单独建表我见过最多的 JSON 应用场景是“给主表加一堆可选属性”。假设你做一个商品系统普通商品有名称、价格、库存但电子产品需要电压、保修期服装需要尺码、材质食品需要保质期、产地。最标准的关系型做法是建一张product_attribute表主表存公共字段属性表用 product_id 关联自定义字段。但实际跑一段时间你就会发现痛点属性表频繁 JOIN哪怕数据量不大每查一次商品详情都要拼一次聚合属性表没有强约束同一个 key 在不同商品里可能叫“保修期”也可能叫“质保期”更难受的是属性表里的 value 只能统一存字符串类型信息丢失了。换成 JSON 以后做法就变成这样CREATE TABLE product ( id BIGINT PRIMARY KEY, name VARCHAR(100), price DECIMAL(10,2), attrs JSON ); INSERT INTO product VALUES (1, 智能手机, 3999.00, JSON_OBJECT( voltage, 5V/2A, warranty_years, 2, weight_g, 180 ));这样改完商品详情一次查询就能出全量数据不需要 JOIN扩展属性不再污染主表 schema而且 JSON 里可以同时存字符串、数字、布尔值类型不会丢。这是 JSON 类型最舒服的使用姿势属性不稳定、查询频率远低于写入频率、需要“整体存取”多于“按内部元素过滤”。1.2 适合 JSON 与不适合 JSON 的场景判断从我接触的项目看适合用 JSON 的典型场景集中在下面几类配置快照比如优惠规则、模板内容、用户偏好设置每次更新整体覆盖查询时整段取出。三方回调原始报文支付回调、物流回传、开放平台事件先原样落库后续异步解析。动态表单/问卷结果字段由运营在后台配置答案结构不定存 JSON 比反复加列务实。埋点与日志明细属性多、单条数据量大、基本不做跨记录的字段关联。网关转发参数A 服务转给 B 服务的参数透传中间层只需要能存能取。不适合的场景也要心里有数需要频繁对内部字段做范围查询、排序、聚合的场景比如“查保修期大于 3 年的商品”JSON 的方案在性能上始终拼不过独立列。跨文档关联JSON 里存了另一个业务对象的 ID但你没办法建外键只能在应用层保证一致性。高频更新的场景JSON 在 8.0 之后支持部分更新但如果一把梭JSON_SET整个文档写放大问题依然存在。对字段完整性要求极高的核心交易数据虽然 MySQL 会校验 JSON 语法但它不会校验“必填字段是否存在”这种业务约束还是应该放在独立的强类型列上。一句话总结JSON 适合当一个“结构灵活的容器”不适合当一个“需要被数据库频繁拆解的查询主体”。2. JSON 不是 TEXT存储格式、校验规则和二进制结构2.1 二进制格式带来的收益和限制很多人以为 JSON 列就是“带格式校验的 VARCHAR”这是最大的误解。MySQL 从 5.7 开始引入了json_binary内部格式插入数据时会把 JSON 文本解析成二进制结构而不是原样存字符串。二进制格式最大的收益是读取效率。当你用JSON_EXTRACT提取某个路径时MySQL 不需要把整个文档当普通文本从头扫描匹配引号和括号而是按内部的键值表快速定位。文档越大这个优势越明显。同时二进制格式会在键名上做去重处理源文本里如果重复了会按 JSON 规范保留最后一个值读取时不会出现重复键。但也有代价。二进制格式通常会比原始文本略大因为内部要存储键的元数据、值类型的标记和长度信息。对于三五百字节的小文档这个差距可以忽略但如果一个 JSON 文档有几百 KB存储空间的增长感受会比较明显。实际项目中如果对空间敏感评估时不能按源文本大小估算需要按实际插入后的列长度来看。2.2 插入校验JSON_VALID 与那些“诡异报错”JSON 列在插入和更新时都会自动做合法性校验语法不对会直接报Invalid JSON text。这里有个容易踩的坑JSON 标准里要求对象键、字符串值必须用双引号不能用单引号。很多从 Python 或 JavaScript 侧拼 JSON 字符串的同事习惯性把单引号传进来结果插入失败。-- 错误示范 INSERT INTO product (id, name, attrs) VALUES (2, 测试, {name: test}); -- 正确做法交给 JSON 函数构造 INSERT INTO product (id, name, attrs) VALUES (2, 测试, JSON_OBJECT(name, test)); -- 或者确保源字符串本身就是合法 JSON SET s {name: test}; SELECT JSON_VALID(s); -- 返回 1校验规则的几个细节值得记住JSON 文档不能有末尾逗号{a: 1,}是非法格式。整数、小数、true、false、null都可以作为合法值。日期时间字符串只要符合 JSON 字符串要求就能存进去但 MySQL 不会自动识别它是不是合法日期那是应用层的责任。深层嵌套默认没有硬上限但max_allowed_packet会限制单个 JSON 文档的最大尺寸8.0 默认配置通常是 64MB 或更大实际项目里要配合业务文档大小来调整。JSON_VALID函数除了用来验证数据也可以在表的 CHECK 约束里用防止应用层绕过校验写入脏数据CREATE TABLE log ( id BIGINT PRIMARY KEY, payload JSON, CONSTRAINT chk_payload_valid CHECK (JSON_VALID(payload)) );2.3 字符集和排序规则的影响JSON 列本身采用utf8mb4字符集也就是说它可以存中文、Emoji 和其他 Unicode 字符这一点比早期utf8mb3时代好太多。但要注意JSON 内部键的排序规则和普通 VARCHAR 列的排序规则是独立的一套MySQL 会按二进制格式存储键而不是按你建表时指定的 collation 来比较 JSON 内部文本。这意味着如果你希望JSON_EXTRACT出来再去比较字符串时按照某个规则走可能需要在提取后显式CAST成目标字符集和排序规则。例如SELECT * FROM product WHERE CAST(attrs-$.name AS CHAR CHARACTER SET utf8mb4) 手机;如果不做这些设置默认情况下按二进制比较结果可能和你在应用层的预期不同特别是涉及大小写和重音时。3. 高频函数实操读、写、改、查的细节和坑3.1 提取数据JSON_EXTRACT、- 运算符、- 运算符怎么选JSON_EXTRACT(json_doc, path)是最基础的提取函数返回的是JSON类型。注意“返回的还是一个 JSON 值”所以如果你提取的路径指向一个字符串手机函数返回的内容会包含双引号直接跟数据库里的字符串比较可能对不上。为此 MySQL 提供了两个等价写法json_col - $.path等价于JSON_EXTRACT(json_col, $.path)。json_col - $.path等价于JSON_UNQUOTE(JSON_EXTRACT(json_col, $.path))会把 JSON 字符串两侧的引号去掉返回真正意义上的文本。实际使用中我推荐在应用层需要字符串的场景一律用-需要保持 JSON 类型或者想继续对提取出的对象做函数操作时才用-。SELECT name, attrs - $.warranty_years AS warranty_json, attrs - $.warranty_years AS warranty_text, attrs - $.voltage AS voltage_json FROM product WHERE id 1;path路径的语法也不复杂但经常有人写错$表示整个文档。$.name表示顶层键 name。$.a.b表示嵌套键。$[0]表示数组第一个元素。$.items[2].price表示对象属性 items 数组第三个元素的 price。$**.price表示递归搜索所有层级里的 price 键。递归搜索很方便但性能一般适合小文档和低频查询不要把它当成常规查询手段。3.2 构造和更新JSON_OBJECT、JSON_ARRAY、JSON_SET 系列构造 JSON 最简单的方式是直接写字符串但隐患多。我更推荐用函数构造尤其是值来自动态参数时SELECT JSON_OBJECT( name, 手机, tags, JSON_ARRAY(数码, 新品), info, JSON_OBJECT(brand, 某品牌) );JSON_ARRAY和JSON_OBJECT可以嵌套灵活度很高。实际应用里后端语言如 Python 的json.dumps生成字符串再插入 MySQL 也是最常见方式完全没问题只要保证是合法 JSON 就能插。更新操作有四个容易混淆的函数JSON_SET(json_doc, path, val, ...)存在则覆盖不存在则新增。JSON_INSERT(json_doc, path, val, ...)只插入新路径已存在的路径不会覆盖。JSON_REPLACE(json_doc, path, val, ...)只替换已存在的路径不存在的路径不会新增。JSON_REMOVE(json_doc, path, ...)删除指定路径。一个实用场景给商品扩展属性加一个“上架时间”的字段。如果之前文档里没有这个键JSON_REPLACE不会生效必须用JSON_SETUPDATE product SET attrs JSON_SET( attrs, $.shelve_time, 2026-03-01 10:00:00, $.status, active ) WHERE id 1;如果只希望首次加入时生效后续不改动用户已设置的值就用JSON_INSERTUPDATE product SET attrs JSON_INSERT(attrs, $.default_address, 默认仓库) WHERE id 1;8.0 版本对JSON_SET、JSON_REMOVE等操作做了部分更新优化事务内只要路径明确底层可以只更新变更的二进制节点不需要重写整个文档。但要注意这个优化依赖 binlog 格式和事务隔离级别如果使用了binlog_formatSTATEMENT部分更新能力可能受限。实践中建议保持ROW格式这也是 8.0 的默认值。3.3 条件判断JSON_CONTAINS、JSON_OVERLAPS、JSON_CONTAINS_PATH查询 JSON 内部数据时有三个常用函数功能容易混淆。JSON_CONTAINS(target, candidate[, path])判断的是“target 是否包含 candidate”。很多人会把两个参数写反。第一个参数是目标文档第二个是你要查找的内容而不是反过来。-- 判断 attrs 里是否包含一个对象其中 brand 为 某品牌 SELECT * FROM product WHERE JSON_CONTAINS(attrs, JSON_OBJECT(brand, 某品牌));判断数组里是否包含某个元素JSON_CONTAINS也适用SELECT * FROM product WHERE JSON_CONTAINS(attrs-$.tags, JSON_ARRAY(新品));JSON_OVERLAPS(doc1, doc2)从 8.0.17 开始提供判断两个 JSON 是否有交集适合数组重叠查询比JSON_CONTAINS在语义上更直观SELECT * FROM product WHERE JSON_OVERLAPS(attrs-$.tags, JSON_ARRAY(数码, 清仓));JSON_CONTAINS_PATH(doc, one|all, path, ...)的作用是判断路径是否存在而不是判断值。这个函数对“接口回调里有没有传某个字段”这种场景特别顺手SELECT id FROM callback_log WHERE JSON_CONTAINS_PATH(payload, one, $.transaction_id);第二个参数用one表示任意一个路径存在就返回 1用all表示所有路径都必须存在。实际使用中经常碰到的问题是路径存在但值是nullJSON_CONTAINS_PATH也会返回 1因为它的判断标准是路径本身不管值是不是null。如果业务上要排除null还得再加上一层JSON_EXTRACT(...) IS NOT NULL的条件。3.4 JSON_TABLE把 JSON 拉平成关系表做 JOINJSON_TABLE是 8.0 里非常实用但容易被忽略的函数它能把 JSON 数组按行展开成一张虚拟表让你可以用普通 SQL 去 JOIN、聚合、过滤。SELECT p.id, t.tag FROM product p, JSON_TABLE(p.attrs-$.tags, $[*] COLUMNS ( tag VARCHAR(20) PATH $ )) AS t WHERE p.id 1;这个函数让 JSON 不再是一个“只能整体存取的黑盒”。比如商品 tags 数组里有 5 个标签过去想在报表里统计每个标签的商品数得在应用层循环现在直接用JSON_TABLE展开后GROUP BY就行。不过要说明白JSON_TABLE展开的临时表是一次性计算的列名和类型都需要在定义时写清楚。如果 JSON 内部结构混乱比如数组元素有的是对象有的是字符串展开时要注意用PATH配合ON EMPTY兜底。JSON_TABLE(doc, $[*] COLUMNS ( id INT PATH $.id DEFAULT 0 ON EMPTY, name VARCHAR(50) PATH $.name DEFAULT ON EMPTY )) AS t这个兜底很关键因为实际数据里经常存在“大部分元素都有 name偶尔有个元素漏了”没有ON EMPTY的话整条查询会报错。4. 让 JSON 数据也能走索引生成列与多值索引4.1 为什么不能直接给 JSON 列建普通索引MySQL 不允许直接对 JSON 列建普通二级索引因为 JSON 内部是二进制结构不是定长可比较的普通列。但实际业务又总会碰到“按 JSON 里的某个字段查记录”的需求单纯全表扫描迟早出事。解决办法是利用“生成列”。你可以把 JSON 里的关键字段“提取”成一个虚拟列或存储列然后对这个生成列建索引。生成列在 MySQL 5.7 就支持了语法也不复杂。ALTER TABLE product ADD COLUMN attrs_brand VARCHAR(50) GENERATED ALWAYS AS (attrs - $.brand) STORED; CREATE INDEX idx_attrs_brand ON product(attrs_brand);这样查询时直接WHERE attrs_brand 某品牌就能走普通索引。这里有一个选择生成列用VIRTUAL还是STORED。VIRTUAL列不占用实际存储空间值在读取时实时计算二级索引可以建立在虚拟列上InnoDB 支持。STORED列会真实落盘读起来更快但增加存储占用写入时也有额外成本。我的建议是绝大多数场景优先用VIRTUAL列建索引空间省、维护成本低查询时 InnoDB 会自动引用索引里的值不会反复计算。只有在虚拟列参与了复杂表达式计算、或者你需要在生成列上再建普通索引且读多写少的极端情况下才考虑STORED。4.2 多值索引解决数组元素匹配的硬需求普通生成列索引适合“JSON 对象里的单个字段”但遇到数组场景就麻烦了。比如商品 tags 是[数码, 新品, 5G]你想查所有包含“新品”标签的商品传统生成列只能把整个数组提取成一个字符串无法逐元素索引。MySQL 8.0.17 开始支持多值索引专门解决 JSON 数组的匹配问题。它的核心语法是在生成列上定义一个ARRAY类型ALTER TABLE product ADD INDEX idx_attrs_tags ((CAST(attrs-$.tags AS CHAR(20) ARRAY)));建索引之后下面的查询可以走索引SELECT * FROM product WHERE JSON_OVERLAPS(attrs-$.tags, CAST([新品] AS JSON));或者用MEMBER OF这个运算符也是 8.0.17 引入的SELECT * FROM product WHERE 新品 MEMBER OF (attrs-$.tags);多值索引的限制也不少实际使用前一定要确认必须使用CAST(... AS type ARRAY)表达式。索引只能用于等值匹配和部分重叠匹配范围查询、排序、GROUP BY基本指望不上。CAST的目标类型不能是BINARY、JSON这种复杂类型常用的是UNSIGNED、DECIMAL、CHAR、DATE等固定类型。一个表只能有一个多值索引。如果 JSON 数组里元素类型不统一比如混着数字和字符串建索引时大概率会把所有值都转成CHAR再查询时也要保持类型一致性。4.3 哪些 JSON 查询模式注定走不了索引不管生成列还是多值索引能覆盖的查询模式都是有限的。下面这些场景我实测过基本都会退化成全表扫描路径中用$**递归匹配因为匹配的键层级不确定无法预先计算生成列。对 JSON 函数结果做计算后再比较比如CAST(attrs-$.sale_count AS UNSIGNED) 100如果多次通过函数包裹优化器无法把它绑定到简单的生成列索引上。直接在 WHERE 条件里写JSON_EXTRACT(attrs, $.brand)即使你建了生成列索引如果查询没有直接引用生成列名优化器也不一定能自动重写。“字段是否包含某个键”这种查询比如JSON_CONTAINS_PATH如果没有额外生成一个“是否存在”的标记列索引帮不上忙。所以在设计表结构时就要想清楚哪些 JSON 字段会长期成为查询条件。是的话宁可在建表时就规划好生成列也不要等数据量大了再痛苦迁移。5. 真实项目中的避坑经验版本差异、迁移路径与一致性5.1 MySQL 5.7 与 8.0 在 JSON 能力上的关键差别虽然 5.7 就有 JSON 类型了但很多高级用法都是 8.0 才补齐的业务上线前一定要确认版本能力匹配。一个最容易踩的差异是JSON_OVERLAPS和MEMBER OF它们只在 8.0.17 之后存在5.7 里查函数表都看不到。多值索引也是 8.0.17 才有的能力5.7 只能靠生成列解决对象字段数组匹配还是要靠应用层。JSON_TABLE是 8.0 新增5.7 完全不支持。如果项目还锁死在 5.7想拉平 JSON 数组就只能先查出结果再在代码里循环性能和代码复杂度都会上升。另一个细节是 JSON 部分更新8.0 对JSON_SET等操作做了优化可以在 binlog 里只记录变更部分减少从库复制压力。5.7 里同样的操作基本上等于整行更新。如果主从复制延迟敏感这个差异会直接影响生产架构。我现在的建议是新项目一律用 8.0 及以上最好选用官方长期支持的版本。5.7 已经进入生命周期末期为省一个升级成本在 JSON 功能上处处受限实在不划算。5.2 从 TEXT 列或关联表迁移到 JSON 的实操路径很多遗留系统里JSON 数据最初是存在 TEXT 或 LONGTEXT 里的迁移起来看着简单其实坑很多。第一步是数据清洗。TEXT 列里可能混着空字符串、非法 JSON、数组和对象共存等脏数据。直接 ALTER 成 JSON 类型会导致整表迁移失败所以必须先跑一遍校验SELECT COUNT(*) FROM legacy_table WHERE JSON_VALID(payload) 0;如果查到非法数据要么在应用层修复要么先拉出来分析再决定是丢弃还是补默认值。第二步是把合法数据搬进新列。我习惯分阶段做而不是直接 ALTERALTER TABLE legacy_table ADD COLUMN payload_json JSON; UPDATE legacy_table SET payload_json CAST(payload AS JSON) WHERE payload IS NOT NULL AND payload ! ;全部更新完后检查行数和数据长度是否对齐确认无误再停应用做一次原子切换最后删掉旧列。这里提醒一句CAST(payload AS JSON)在 8.0 里很稳定但如果 payload 里有非 UTF-8 字符转换会报错最好在转换前先做字符集清洗。从规范化关联表迁移到 JSON 的思路也类似。比如之前product_attribute表一行一个属性迁移时用动态 SQL 或者应用层把各路属性聚合好再拼成 JSON 写入新列。这里更重要的是提前确认查询侧改造原来 JOIN 属性表的接口、报表脚本全都要同步改成 JSON 提取或 JSON_TABLE不能只改存储不动读取。5.3 JSON 的 null 和 SQL 的 NULL 不是一回事这个坑够隐蔽但影响很大。在 JSON 文档里键的值可以是字面量null它是一个合法的 JSON 值。而这个值通过-提取出来在 SQL 里是一个字符串null不是数据库的NULL。SELECT doc - $.a AS val, ISNULL(doc - $.a) AS is_null_1, doc - $.a IS NULL AS is_null_2 FROM t;当路径不存在时-返回 SQLNULL当路径存在但值是 JSON null 时-返回字符串 “null”。这个差异会导致应用层出现灵异问题明明存了 null查询判断IS NULL却不成立。我个人建议在写入端就约定业务中“不存在”和“值为空”最好统一语义。如果不需要区分两者就不要把 JSON null 写进文档如果一定要有应用层读取时必须先判断是不是字符串 “null”。这是个低调但能把人折磨半天的细节。5.4 导入导出、备份和监控的注意点mysqldump 默认对 JSON 列的处理是把它当作普通文本导出在 CREATE TABLE 语句里用 JSON 类型恢复整体问题不大。但要注意 old 参数和兼容性选项比如--compatibleoracle这类选项是否会影响 JSON 类型的导出我没有深入验证但实测中曾遇到 5.7 的 dump 文件在 8.0 里恢复时日期时间格式不一致的案例所以迁移后一定要做抽查比对。备份策略上JSON 大字段很容易拖大 binlog特别是频繁整体更新文档的场景。建议监控每个实例的 binlog 体积增长趋势及时拆库或者改成“只记录变更字段”的应用层策略。慢查询日志也要重点关注带 JSON 函数的语句很多隐式类型转换和路径递归会在日志里暴露出来是排查性能隐患的第一手材料。监控指标方面至少要看这几项JSON 列平均宽度、单行最大宽度、JSON 相关查询的响应时间分位数、临时磁盘表出现频率。特别是当JSON_TABLE展开大量数组时内存临时表可能会溢出到磁盘导致查询慢上十倍这类问题在监控里会体现为临时表数量突增。写到最后想说的JSON 类型在 MySQL 里从来不是银弹它的存在是为了应对“结构不稳定”这个现实问题。用的时候守住一条底线让 JSON 成为一个存放灵活结构的容器把需要关联、过滤、约束的字段提前用生成列捞出来不要在 JSON 里塞需要频繁参与复杂计算的核心数据更不要因为“省事”把整个业务表全改成两个字段加一个 JSON 大字段。按这个原则走它会是很好的工具反着来它会变成你的性能噩梦。