前几个月接手了一个老项目的重构表结构里有一堆预留字段全部是varchar(255)里面塞的是各种格式的 JSON 字符串。查询的时候要么LIKE硬匹配要么把数据捞到应用层用代码解析慢得让人抓狂后来我干脆把所有这类字段统一改成了 MySQL 的JSON数据类型配合官方提供的 JSON functions 做查询和更新整个系统的维护成本瞬间降了一个量级。这篇内容我会把 MySQL JSON 数据类型和 functions 从基础用法到性能优化到实战避坑完整拆一遍适合正在考虑“要不要用 JSON 字段”的开发者也适合已经用了但经常踩坑的同学拿去当参考。1. 为什么我最终选择了 MySQL 的 JSON 类型先说结论不是所有场景都适合 JSON但一旦适合收益非常明显。我在这个重构项目里遇到的情况是业务上有一批“属性不固定”的数据比如商品的扩展参数不同类目的商品字段完全不同有的需要“材质”有的需要“功率”用传统的关系型建模要搞一堆稀疏列或者 EAV 表实体-属性-值查询时各种 JOIN写起来费劲跑起来更费劲。JSON 类型就是为了这类“结构灵活但又有一定查询需求”的数据设计的。1.1 什么场景真的需要 JSON 字段我的判断标准很简单字段结构会经常变化或者字段集合在不同记录间差异特别大同时又需要针对里面的某个子字段做条件查询或聚合统计这时候 JSON 就是最优解。举个例子我们做内容平台时每个作者可以配置不同的个人主页模块有人开了“作品集”有人开了“留言板”有人两者都开。这个“开关配置”如果建表就是show_works、show_guestbook、show_about这样的布尔列每加一个模块就要 ALTER TABLE 加一列。后来我们直接改成profile_config JSON新模块上线只改代码数据库完全不用动。这类场景就是 JSON 的典型应用场景schema-less、稀疏、变化频繁。但是注意如果一个字段在绝大多数记录里都存在且查询条件非常固定那就别用 JSON。比如用户手机号你会拿它做 WHERE 条件还建了唯一索引这种数据放在 JSON 里是自找麻烦。JSON 不是银弹它是对关系模型的一种补充用来处理那些“关系模型不擅长”的灵活性需求。1.2 为什么不用 TEXT 字段存 JSON很多人说“我用 TEXT 也能存 JSON 啊还不用学新语法”这个说法坑了很多人。MySQL 官方把 JSON 做成独立类型肯定不是让你继续用 TEXT 的。JSON 类型相比 TEXT 有几个硬核区别写入时会做严格格式校验格式不合法的字符串根本插不进去存储时会做二进制序列化解析和访问的速度比 TEXT 里硬塞字符串快得多查询时可以直接用 JSON path 语法定位到任意子节点而不是把整个字符串读出来再解析。我自己踩过最痛的一个坑是字符集和排序规则问题。TEXT 字段存中文 JSON 串如果表的默认排序规则是utf8mb4_general_ci某些情况下字符串比较会忽略大小写导致你解析 JSON key 的时候出现诡异问题。换成 JSON 类型后MySQL 会按照 JSON 规范去处理key 的重复、大小写、空白字符的规范化全都交给引擎完成省心不是一点半点。1.3 JSON 类型相比 TEXT 的核心优势用一个简单的对比表来说明能力TEXT 存 JSONJSON 数据类型格式校验无脏数据也能写入写入时强制校验非法 JSON 直接报错存储格式原始文本冗余空格二进制格式自动去除键间空白和重复 key 前的旧值子节点访问必须取出整串后应用层解析支持$路径表达式直接定位字段更新必须重写整个字符串JSON_SET等函数局部更新索引支持只能全字段前缀索引支持虚拟列索引、多值索引内存效率解析一次 IO 和 CPU 开销大引擎内部有优化频繁访问子节点更快看到这你可能已经理解了JSON 不是简单地把文本换了个类型而是一整套存储和查询的解决方案。2. 先把 JSON 数据的写入和修改搞明白用 JSON 类型的第一步不是查询而是先搞清楚怎么把数据正确地存进去、改过来。MySQL 8.0 的 JSON functions 覆盖了构造、校验、查询、修改、聚合等方方面面我按使用频率从高到低逐个说。2.1 JSON 字段的自动校验与格式化建表语句里直接写JSON类型即可CREATE TABLE product ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(100), attrs JSON );插入数据时你可以直接插入一个 JSON 字符串字面量MySQL 会自动校验格式INSERT INTO product (name, attrs) VALUES (智能手机, {brand: X, screen_size: 6.1, tags: [5G, OLED]});这里有个细节字段值必须是合法的 JSON包括对象、数组、标量数字、字符串、布尔、null。如果你写{name: foo,}这种带尾逗号的MySQL 会直接报Invalid JSON text错误。字符串里的需要转义比如{desc: It\s good}。另外JSON 列在存储时会自动做规范化。举个例子你插入{a: 1, b: 2, a: 3}注意这里a出现了两次MySQL 会保留最后一个值存成{a: 3, b: 2}。键的顺序也可能调整这是引擎自定义的你不用纠结只要记住“insert 进去之后再 select 出来未必和原来一模一样”就行。2.2 构造 JSON 数据JSON_ARRAY、JSON_OBJECT、JSON_QUOTE有些场景你需要动态拼接 JSON。比如从一张旧的 EAV 表里把数据迁移过来想在 SQL 里直接生成 JSON 写入新表。这时候可以用构造器函数JSON_ARRAY(v1, v2, ...)返回一个 JSON 数组例如JSON_ARRAY(1, a, TRUE)结果[1, a, true]JSON_OBJECT(key, value, ...)返回 JSON 对象例如JSON_OBJECT(name, Tom, age, 20)结果{name: Tom, age: 20}JSON_QUOTE(str)把普通字符串转成合法的 JSON 字符串加上引号并处理转义实际迁移时我最常用的是JSON_OBJECT比如把订单明细里多个商品的属性拼进去UPDATE orders o JOIN order_items oi ON oi.order_id o.id SET o.items_json JSON_OBJECT( product_name, oi.name, qty, oi.qty, price, oi.price ) WHERE o.id 123;注意一个坑JSON_OBJECT的 key 如果重复和手写 JSON 一样后面的值覆盖前面的值。JSON_QUOTE很多人用不上但做日志、导入导出时很关键它可以避免拼接字符串时把引号搞乱强烈建议写 SQL 拼 JSON 时用它包裹动态值。2.3 修改数据JSON_SET、JSON_INSERT、JSON_REPLACE、JSON_REMOVE这组函数是 JSON 修改的核心但也是新手最容易搞混的。我一个个说JSON_SET(json_doc, path, val[, path, val]...)设置指定路径的值如果路径不存在则新增。这是最常用的一个相当于“有则改无则加”。JSON_INSERT(json_doc, path, val[, ...])只在路径不存在时新增如果路径已存在不修改原值。JSON_REPLACE(json_doc, path, val[, ...])与JSON_INSERT相反只在路径存在时替换不存在时忽略。JSON_REMOVE(json_doc, path[, path]...)删除指定路径的键值对或数组元素。光说可能没感觉直接看例子。假设原始数据是SET doc {name: Tom, age: 20, tags: [student, male]};执行以下语句SELECT JSON_SET(doc, $.age, 21, $.city, Beijing); -- 结果{name: Tom, age: 21, tags: [student, male], city: Beijing} SELECT JSON_INSERT(doc, $.age, 99, $.city, Beijing); -- 结果{name: Tom, age: 20, tags: [student, male], city: Beijing} SELECT JSON_REPLACE(doc, $.age, 99, $.city, Beijing); -- 结果{name: Tom, age: 99, tags: [student, male]} SELECT JSON_REMOVE(doc, $.age, $.tags[0]); -- 结果{name: Tom, tags: [male]}实际项目中我常用JSON_SET做“增量更新”比如用户修改了脱离列表中的一项配置不需要把整个 JSON 读出来再写回直接一条 UPDATE 搞定。这里有个性能层面的优势JSON 类型支持部分更新只要使用JSON_SET且目标列是 JSON 类型InnoDB 可以做到只更新变化的部分而不是整行重写在高频更新场景下收益明显。2.4 JSON_MERGE_PATCH 和 JSON_MERGE_PRESERVE 的区别这两个函数在 MySQL 8.0 里都有很多人搞不清。简单说它们都是合并两个或多个 JSON 文档但冲突处理策略完全不同。JSON_MERGE_PRESERVE是“保留式”合并两个对象有相同 key 时两个值会合并成一个数组。而JSON_MERGE_PATCH是“覆盖式”合并相同 key 时后面的值覆盖前面的而且null值表示删除该 key。直接看例子SELECT JSON_MERGE_PRESERVE({a: 1, b: 2}, {b: 3, c: 4}); -- 结果{a: 1, b: [2, 3], c: 4} SELECT JSON_MERGE_PATCH({a: 1, b: 2}, {b: 3, c: 4}); -- 结果{a: 1, b: 3, c: 4} SELECT JSON_MERGE_PATCH({a: 1, b: 2}, {b: null}); -- 结果{a: 1}所以如果你要实现“默认配置 用户自定义配置”合并且用户配置要覆盖默认配置JSON_MERGE_PATCH是首选。如果要保留全部分支历史用JSON_MERGE_PRESERVE。3. 查询 JSON 的常用函数与操作符JSON 数据存进去了怎么高效查出来这是大部分开发者最关心的部分。MySQL JSON functions 提供了非常强大的路径查询能力但前提是你得先理解 JSON Path 语法。3.1 路径表达式基础$、.、[]JSON Path 是一个类似 XPath 的定位语法用来指向 JSON 文档中的某个节点。基础语法$表示整个文档$.key表示对象中 key 对应的值$.arr[i]表示数组第 i 个元素下标从 0 开始$.arr[*]表示数组所有元素$.**表示递归匹配所有后代较少用但某些场景很管用比如文档{ name: Tom, address: { city: Beijing, district: Haidian }, tags: [student, male], scores: [90, 85, 92] }那么$.name返回Tom$.address.city返回Beijing$.tags[1]返回male$.scores[*]返回[90, 85, 92]路径表达式可以带通配符和条件吗可以。比如$.scores[*]就能匹配数组所有元素结合 EXISTS 或者 8.0.17 的JSON_VALUE可以做一些高级过滤。但绝大多数业务查询用最基本的$.key就够了先把基础打磨熟。3.2 JSON_EXTRACT 以及 - 和 - 的差异JSON_EXTRACT(json_doc, path)是官方最核心的提取函数返回的是 JSON 类型。比如SELECT JSON_EXTRACT(attrs, $.brand) FROM product WHERE id 1; -- 结果X注意返回结果自带引号如果你想拿到一个“纯字符串”而不是 JSON 字符串字面量就得用JSON_UNQUOTE或者直接用-操作符。MySQL 提供了两个语法糖操作符-等价于JSON_EXTRACT-等价于JSON_UNQUOTE(JSON_EXTRACT(...))所以上面这句可以简写成SELECT attrs-$.brand FROM product WHERE id 1; -- 结果X这是个高频操作我几乎所有要用 JSON 字段值的查询都写成-配合 JOIN 条件、WHERE 过滤都特别方便。但有个坑-拿到的永远是字符串类型如果你要参与数字比较得注意类型转换。比如SELECT * FROM product WHERE attrs-$.price 100;这里attrs-$.price返回的是字符串1999MySQL 会做隐式转换一般情况下没问题但一旦遇到类似9 100这种字符串比较就会出错。稳妥做法是显式写CAST(attrs-$.price AS DECIMAL(10,2)) 100。这个细节很容易让刚上手的人翻车。3.3 搜索类函数JSON_CONTAINS、JSON_OVERLAPS、JSON_SEARCH业务里最常见的是“某个 JSON 数组里是否包含某个值”这类查询我以前用LIKE %value%匹配 JSON 串容易误匹配而且在某些场景下性能极差。换成官方函数后逻辑清晰还支持索引。JSON_CONTAINS(target, candidate[, path])判断 target 是否包含 candidate。举个例子SELECT * FROM product WHERE JSON_CONTAINS(attrs-$.tags, 5G); -- 注意第二个参数是 JSON 字符串必须自带引号如果 tags 是[5G, OLED]返回 true。第二个参数如果写5G是不对的它要求是合法的 JSON 值严格来说应该是5G。这里有个进阶技巧如果要判断数组里是否包含多个值可以构造一个数组作为 candidateSELECT JSON_CONTAINS(attrs-$.tags, [5G, OLED]);表示同时包含5G和OLED才返回 true。JSON_OVERLAPS(json_doc1, json_doc2)判断两个 JSON 是否有交集只要有一个相同元素就返回 true。它和JSON_CONTAINS的区别是“存在任意一个即可”适用于模糊匹配场景。JSON_SEARCH(json_doc, one_or_all, search_str)在 JSON 文档里搜索字符串并返回路径。这个函数有个限制search_str 是普通字符串不是正则它只能匹配字符串类型的值不能匹配数字、布尔。返回的路径可以配合其他函数使用比如拿到路径后再去更新。说实话JSON_SEARCH的使用频率不高但需要做类似“查出包含某个字符串的所有记录”时它就是最直接的答案。3.4 其他常用函数JSON_KEYS、JSON_LENGTH、JSON_TYPE 等这几个函数不属于高频主查询但写存储过程、做数据校验时会用到JSON_KEYS(json_doc[, path])返回对象的所有 key 组成的 JSON 数组。JSON_LENGTH(json_doc[, path])返回对象的键数量或数组的元素数量。JSON_TYPE(json_val)返回 JSON 值的类型比如OBJECT、ARRAY、STRING、INTEGER、BOOLEAN、NULL。JSON_VALID(str)判断一个字符串是否是合法 JSON返回 1/0。JSON_PRETTY(json_doc)格式化输出 JSON方便手动查看。举个例子校验某列是否都是合法 JSONSELECT id, name FROM product WHERE JSON_VALID(attrs) 0;我在做数据迁移时经常用JSON_TYPE检查字段类型是否和我预期一致避免上线后才发现数据类型不匹配导致程序报错。这些函数不常出现在业务代码里但关键时刻能救命建议至少眼熟。4. 性能优化让 JSON 字段也能走索引“JSON 字段没办法建索引”是流传很广的误解。准确说法是不能直接对 JSON 列建传统索引但完全可以对 JSON 内部的具体字段建索引。MySQL 官方提供了两条路虚拟列索引5.7 起和多值索引8.0.17 起。4.1 虚拟列 索引最稳妥的方案这个方案思路很简单在表上创建一个虚拟列从 JSON 字段里提取某个值然后对这个虚拟列建索引。因为虚拟列本身不占额外存储除非声明STORED又是普通列所以能走常规 BTree 索引。创建方式ALTER TABLE product ADD COLUMN brand VARCHAR(50) GENERATED ALWAYS AS (attrs-$.brand) STORED, ADD INDEX idx_brand (brand);这里我用的是STORED虚拟列原因是查询性能更好适合查询频率高且几乎不更新的情况。如果希望省存储、写入快可以省掉STORED用默认的VIRTUAL10万行以内的数据量两者差别不大。使用注意点虚拟列表达式必须满足确定性也就是说attrs-$.brand这个表达式每次对同一行数据结果一致虚拟列上建的索引查询时一定要用同样的表达式否则无法命中索引。在业务代码里我一直严格要求团队“凡是 JSON 里有高频查询字段必须同步建虚拟列索引”这是 JSON 性能的基础保障。4.2 多值索引MySQL 8.0 处理数组查询的利器8.0 之前如果你想用索引查 JSON 数组里包含某个值的记录基本没戏。从 8.0.17 开始MySQL 支持多值索引专门用来给 JSON 数组建索引。创建语法CREATE INDEX idx_tags ON product ( (CAST(attrs-$.tags AS CHAR(20) ARRAY)) );注意这里的语法非常特殊在索引定义处把 JSON 中的数组字段 CAST 成 SQL 标准数组类型然后由引擎自动建立多值索引。查询时要用JSON_CONTAINS、MEMBER OF或JSON_OVERLAPS才能命中SELECT * FROM product WHERE 5G MEMBER OF (attrs-$.tags); SELECT * FROM product WHERE JSON_CONTAINS(attrs-$.tags, 5G); SELECT * FROM product WHERE JSON_OVERLAPS(attrs-$.tags, [5G]);多值索引的适用场景就是“数组型数据的高频查询”。我把它用在标签、分类、权限列表这类数据上效果立竿见影。但要注意多值索引的创建和维护成本不低不要对超大 JSON 列或者更新极频繁的列滥用。4.3 全表扫描以外的性能建议除了索引JSON 数据本身的存储结构也会影响性能。这里有几个实战建议避免 JSON 字段过大单条 JSON 文档过大几十 KB 以上即使走索引回表读取的数据量也会拖慢查询。建议 JSON 只存“核心灵活数据”大文本拆到单独的表。用JSON_STORAGE_SIZE观察实际占用这个函数返回 JSON 字段的字节数帮你判断是否因为格式问题造成存储膨胀。使用生成列做聚合如果你经常要按 JSON 里的某个数值做 SUM、AVG直接聚合 JSON 字段会比较麻烦不如提前用虚拟列提取成普通列再建普通索引。注意部分更新特性使用JSON_SET、JSON_REMOVE等函数修改 JSON 字段时InnoDB 可以做到部分更新但要求列是 JSON 类型且更新前后长度变化不大否则会退化为整行重写。这个特性天然适合“少量修改”场景但不适合把大 JSON 当文件反复覆盖。5. 真实项目里踩过的坑和实战技巧这部分是重点中的重点都是我在实际项目中真正踩过的坑很多坑在官方文档里根本不会写到。5.1 坑一JSON 的排序和比较结果和你想的不一样JSON 类型在排序时比较的是“序列化后的文本”而不是某个子字段的数值。所以如果你直接ORDER BY attrs结果大概率不是你想要的。正确的做法是提取目标字段再排序SELECT * FROM product ORDER BY CAST(attrs-$.price AS DECIMAL(10,2)) DESC;记住-返回字符串必须转成数值类型再比较。我还见过有人直接ORDER BY attrs-$.price结果排序完全按字符串排100 排在 20 前面查了半天才发现问题。5.2 坑二字符集、转义和大小写问题JSON 字符串中如果包含单引号、反斜杠等特殊字符手动拼 SQL 时很容易出错。我强烈建议所有拼接 JSON 字符串的工作都交给程序里的 JSON 序列化库不要手写字符串。如果非要在 SQL 里拼记得用JSON_QUOTE包一层。另外注意JSON 是大小写敏感的。$.Name和$.name是两个不同的路径。我之前迁移数据时因为源系统里 key 的大小写不统一导致查询结果时有时无。解决办法是在数据入口处做规范化或者干脆约定所有 JSON key 一律用小写加下划线团队内部好沟通程序也好处理。5.3 坑三JSON 数据里的 NULL 到底怎么处理JSON 里的null和数据库里的NULL是两回事。JSON 的null是一个合法的 JSON 值字符串是null。而 SQL 的NULL表示“未知”。如果用JSON_EXTRACT(attrs, $.key)当 key 不存在时返回 SQLNULL当 key 存在但值为 JSON null 时返回 JSON 的 null也就是JSON类型的null打印出来也是一个字符串null。这个差异写条件判断时很容易翻车。比如SELECT * FROM product WHERE attrs-$.brand IS NULL;这行代码会漏掉 brand 为 JSON null 的记录因为 JSON null 转成字符串是null不是 SQL NULL。正确处理方式WHERE attrs-$.brand IS NULL OR attrs-$.brand null或者用JSON_TYPE(attrs-$.brand) NULL专门判断。这个细节在数据清洗、去重时特别重要。5.4 实战经验JSON 字段设计的第一性原则最后分享一个我坚持了很久的原则能录入时就规范不要等查询时再来清洗。JSON 字段的灵活性是把双刃剑如果业务上线后没有一套统一的写入规范半年后你就会看到各种乱七八糟的 key、千奇百怪的嵌套结构、大小写混乱的字段名查询函数写得再好也白搭。我的做法是每个 JSON 字段必须在设计文档里写清楚所有允许出现的 key以及各自的类型和含义。写入端统一走服务端代码用强类型 DTO 序列化为 JSON禁止直接从前端透传到数据库。对高频查询字段建表时就同步建好虚拟列索引不要等慢查询报警再补。定期跑一遍数据质量检查 SQL比如JSON_TYPE判断类型、JSON_KEYS检查 key 集合确保没有脏数据。这样做的好处是JSON 字段在数据库层是“灵活格式”但在业务层是“半强类型”两者结合才是 JSON 数据类型的正确打开方式。最后再分享一个小技巧如果你不确定当前 MySQL 版本支持哪些 JSON functions可以直接执行SELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE FUNCTION AND ROUTINE_NAME LIKE JSON\_%;把自己环境里的函数清单拉出来比对着文档看一遍五分钟就能对整个能力边界有个准确认知。JSON 数据类型这套东西用好了是灵活的瑞士军刀用不好就是一把容易伤到自己的双刃剑希望这篇整理能帮你少走几条弯路。