1. 内容整体设计与思路拆解1.1 从扔掉json_decode语法说起我第一次在业务里用上MySQL JSON数据类型是因为一个蛮头疼的场景用户下单时商家会附带各种小纸条、配送备注、发票抬头甚至是一堆前端弹窗勾选的附加服务。这些字段不同商家完全不同用传统关系型列根本没法建模要么搞个extra大字段存文本要么拆一张又宽又稀松的子表不管是哪个查询和修改都能把人磨死。后来我决定把这类结构不固定、但又有查询需求的数据统一丢进JSON列里配合MySQL 5.7开始的JSON类型和函数问题一下子清爽了很多。JSON datatype和functions本质上解决的是三个痛点不需要为了几个可选字段频繁ALTER TABLE加列节省迭代成本。可以直接在SQL里解析JSON内容不用把整段文本取出来放业务层处理。数据会自动校验合法性不规范、格式错的JSON根本存不进去从源头上堵住了脏数据。这篇文章适合谁如果你在做电商、内容系统、爬虫采集、配置中心或者任何表结构天生活泛、字段经常变的项目那你迟早会遇到JSON列这玩意儿。我会从数据类型本身、常用函数、索引优化、坑点排查四个维度用一个真实项目里的实操过程串起来讲尽量讲得直接一点。1.2 官方文档看了就困但底层逻辑就三件事MySQL官方文档关于JSON的部分其实写得很细但阅读体验确实不好因为函数太多动不动就列十几个每行还有参数说明看着就头大。我把这些铺开之后底层逻辑其实就是三件事第一存储格式。从MySQL 5.7开始JSON数据落盘不再是普通TEXT而是用内部二进制格式存储。读出来的时候会自动剥离外层空格、调整键的顺序按长度和字符集排序规则来排同时对文档做合法性校验。第二路径定位。你想操作JSON里某个字段就得用路径表达式JSON Path官方叫法是$.a.b[0].c这种。这是JSON函数的基础凡是JSON_EXTRACT、JSON_SET、JSON_REMOVE全部围绕这套路径规则运转。第三一行拆多行。JSON本身是文档型数据MySQL 8.0提供了JSON_TABLE函数可以把JSON数组展开成类似一张表的结构去关联查询这是数据库场景下最有价值的能力之一。理解了这三件事后面每一个函数你都能找到归类不是在造数据、拆数据、改数据就是在把JSON变成关系表。本文的所有示例我统一以MySQL 8.0.28为基准来写函数和语法部分与8.0及以上版本兼容。2. 核心函数的功能拆解与实操要点2.1 构造与校验JSON_ARRAY / JSON_OBJECT / JSON_VALID先聊构造。实际项目里我们很少人手动敲JSON字符串但构造函数在写存储过程、初始化默认值时非常有用。比如-- 生成一个JSON数组 SELECT JSON_ARRAY(1, abc, NULL, TRUE, NOW()); -- 生成一个JSON对象 SELECT JSON_OBJECT(name, 张三, age, 28, tags, JSON_ARRAY(vip, 老客户));输出类似[1, abc, null, true, 2026-01-11 10:30:00.000000] {name: 张三, age: 28, tags: [vip, 老客户]}注意两个细节JSON_ARRAY和JSON_OBJECT里的NULL最终会显示为null小写而不是SQL里的NULL类型。生成出来的JSON如果是null它属于合法JSON值但和字段不存在有本质区别。构造函数嵌套着用比直接拼字符串安全得多。以前不少人用CONCAT({name:, $name, })来拼JSON一旦name里有英文双引号或反斜杠直接产出非法JSON。用JSON_OBJECT让数据库自己处理转义这是第一道防线。JSON_VALID函数则是用来预检字符串能不能转成JSON。我在做数据迁移时写过这样一段判断SELECT id, raw_json, JSON_VALID(raw_json) AS is_valid FROM legacy_logs WHERE raw_json IS NOT NULL LIMIT 100;把不合法的筛选出来先清洗再导入避免一次性导入把线上表污染了。2.2 查询与提取JSON_EXTRACT / - / - / JSON_CONTAINS这一组是使用频率最高的。JSON_EXTRACT(json_doc, path)就是按路径取值返回值是JSON格式。为了写SQL方便MySQL又给了两个语法糖-相当于JSON_EXTRACT。-相当于JSON_UNQUOTE(JSON_EXTRACT(...))直接去掉了JSON字符串两侧的双引号。举个电商订单表的例子CREATE TABLE order_info ( id bigint NOT NULL AUTO_INCREMENT, order_no varchar(32) NOT NULL, extra json DEFAULT NULL, PRIMARY KEY (id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;插入一条带JSON的数据INSERT INTO order_info (order_no, extra) VALUES ( ORD202601110001, JSON_OBJECT( source, app, tags, JSON_ARRAY(flash_sale, new_user), address, JSON_OBJECT(city, 杭州, district, 西湖区) ) );然后查询SELECT order_no, extra-$.source AS source, extra-$.source AS source_text, extra-$.address.city AS city, extra-$.tags[0] AS first_tag FROM order_info WHERE order_no ORD202601110001;结果差异很典型表达式返回结果extra-$.sourceapp带双引号JSON字符串extra-$.sourceapp纯文本不带引号extra-$.address.city杭州extra-$.tags[0]flash_sale做WHERE条件的时候也要注意如果想按JSON里的字段过滤-- 正确且能用到生成列索引后面讲 SELECT * FROM order_info WHERE extra-$.source app; -- 这样也行但维护性稍差 SELECT * FROM order_info WHERE JSON_EXTRACT(extra, $.source) app;这里有个最常见的坑JSON_EXTRACT返回的是JSON类型你用 app去比它认为JSON字符串app带引号和你传的SQL字符串app不是一个东西。所以要么用-要么在等式右边自己也带双引号写app。建议统一用-做等值比较可读性好得多。JSON_CONTAINS则适合判断JSON里是否包含某个值。比如一个用户的标签数组里有没有某个标签SELECT * FROM user_tag WHERE JSON_CONTAINS(tags, vip, $);注意第二个参数必须是合法的JSON字符串vip而不是vip这是我每次都要啰嗦一遍的点。2.3 修改与合并JSON_SET / JSON_INSERT / JSON_REPLACE / JSON_REMOVE / JSON_MERGE_PATCH改JSON里的内容我用得最多的就是JSON_SET。它和另外两个兄弟函数的区别主要体现在键不存在时怎么办函数键不存在时键存在时JSON_SET追加新键值覆盖旧值JSON_INSERT追加新键值保留旧值不覆盖JSON_REPLACE不做任何事覆盖旧值JSON_REMOVE无操作删除指定键举一个实际场景用户修改了配送地址原来字段里是city: 杭州现在要切成city: 上海同时新增一个delivery_note字段UPDATE order_info SET extra JSON_SET( extra, $.city, 上海, $.delivery_note, 工作日晚上7点后配送 ) WHERE order_no ORD202601110001;JSON_REMOVE的用法就更好理解了比如删掉数组里的指定下标UPDATE order_info SET extra JSON_REMOVE(extra, $.tags[0]) WHERE order_no ORD202601110001;这里提醒一点JSON_SET一次可以传多组path-value对在你需要同时改好几个字段的时候别写成多次UPDATE一个JSON_SET搞定性能也好。合并函数JSON_MERGE_PATCH比较抽象我通常用在合并两个JSON配置这个场景。比如默认配置和用户个性化配置合并用户配置优先SELECT JSON_MERGE_PATCH( {theme: light, locale: zh_CN, pagination: 20}, {locale: en_US, pagination: 50} );结果是{theme: light, locale: en_US, pagination: 50}注意它是后者覆盖前者的语义和JSON_MERGE_PRESERVE保留重复键前者优先的语义正好相反。MySQL 8.0.3以后JSON_MERGE改名为JSON_MERGE_PRESERVE老SQL里如果还写着JSON_MERGE在8.0.28里会直接报错我就在一次升级里踩过这个雷。3. 索引优化生成列与多值索引落地实录3.1 不加索引的JSON查询有多慢JSON列本身没法直接建普通索引。你写ALTER TABLE order_info ADD INDEX idx_source ((extra-$.source))在标准MySQL里是不允许的会报语法错误——至少不能像普通列那样直接写。所以初用JSON的人第一个困扰就是查询虽然能写但一旦数据量到几十万行以上没索引就是全表扫描速度感人。我实测过一个20万行的订单表直接按extra-$.source过滤跑下来大约在700到900毫秒左右。看起来还行但那是因为source这个值区分度不够如果查询条件精度更高、表更大响应时间会线性恶化。生产环境一旦上了两三千万行这种查询直接拖垮监控告警。解决方法就是生成列Generated Column加索引。3.2 生成列把JSON字段变成虚拟索引列生成列分为STORED和VIRTUAL两种。在查询JSON场景我习惯用VIRTUAL它不额外占磁盘InnoDB在建立二级索引时会把这个虚拟列物化到索引结构里查询时可以直接走索引。实操步骤如下-- 1. 添加虚拟列表达式一定要和查询里的表达式保持一致 ALTER TABLE order_info ADD COLUMN source_v VARCHAR(32) GENERATED ALWAYS AS (extra-$.source) VIRTUAL; -- 2. 给虚拟列建索引 ALTER TABLE order_info ADD INDEX idx_source_v (source_v);插完列以后查询里这么写SELECT order_no, extra FROM order_info WHERE source_v app;执行计划里会出现idx_source_v速度一下子从几百毫秒降到几毫秒。关键在于表达式一致性。你如果查询时写extra-$.source但虚拟列定义也是extra-$.sourceMySQL才能自动匹配索引如果一边用JSON_EXTRACT(extra, $.source)一边用-MySQL大概率匹配不上。再补充一个细节虚拟列的数据类型要和你实际值的类型对得上。如果JSON里存的是数字虚拟列建立成VARCHAR会出现隐式转换索引失效。我自己习惯按语义声明成INT、DECIMAL(10,2)或DATETIME。3.3 数组搜索神器多值索引MySQL 8.0.17引入了多值索引Multi-Valued Index专门给JSON数组里的元素建索引。比如你要快速找出标签里包含flash_sale的所有订单以前只能JSON_CONTAINS(extra, flash_sale, $.tags)全表扫现在可以ALTER TABLE order_info ADD INDEX idx_tags ((CAST(extra-$.tags AS UNSIGNED ARRAY)));等等标签是字符串不能CAST成UNSIGNED数组。字符串数组的多值索引在MySQL 8.0.17之后是支持的但要写成ALTER TABLE order_info ADD INDEX idx_tags ((CAST(extra-$.tags AS CHAR(32) ARRAY)));然后查询时用MEMBER OF或JSON_OVERLAPSSELECT order_no FROM order_info WHERE flash_sale MEMBER OF (extra-$.tags); -- 或者查是否有交集 SELECT order_no FROM order_info WHERE JSON_OVERLAPS(extra-$.tags, [flash_sale, new_user]);多值索引有个限制它只能建在VIRTUAL生成列上不能建在存储列上而且索引表达式类型必须是ARRAY。这个索引我第一次用的时候死活建不上后来翻版本手册才意识到字符串数组的CAST类型要指定长度直接写CHAR ARRAY不合法。这些细节官方文档埋得很深不踩一次根本记不住。3.4 索引失效的三大典型场景生成列、多值索引用起来确实爽但有三类写法会让索引直接失效场景一查询条件套函数但不匹配虚拟列表达式。-- 虚拟列定义是 extra-$.source查询却这么写 WHERE JSON_UNQUOTE(JSON_EXTRACT(extra, $.source)) app表达式结构不同MySQL不会把它识别为同样的虚拟列索引不会生效。场景二路径里带JSON数组通配符。WHERE extra-$.tags[*] flash_sale这种通配路径匹配代价很高生成列也无法稳定命中。场景三字符集或排序规则不一致。如果虚拟列定义时用了utf8mb4但查询里常量是latin1或者反过来索引也会被放弃。MySQL连接参数里统一设置SET NAMES utf8mb4能规避大多数问题。4. 常见问题与排查技巧实录4.1 数据类型陷阱为什么JSON里的数字变成了字符串这是新人最常踩的坑。比如JSON对象里写的是price: 19.99从JSON_EXTRACT取出来返回JSON值是19.99看起来像字符串。如果你要按价格排序SELECT id, extra-$.price AS price FROM product ORDER BY price DESC;结果会按字符串字典序排9.99会排在19.99后面。解决办法是CAST转数值SELECT id, CAST(extra-$.price AS DECIMAL(10,2)) AS price FROM product ORDER BY price DESC;这类问题在聚合统计时尤其致命。之前有个报表月度销售额统计从JSON里取amount后直接SUM结果因为JSON里存的是字符串出现了自动类型转换匹配到部分极小值排查半天才意识到是类型问题。4.2 报错信息Invalid JSON text背后90%是字符问题当你INSERT或者UPDATE一个不合法的JSON字符串时MySQL会报Invalid JSON text in argument 1 to function json_set: Invalid value. at position 1.排查思路我总结了一个顺序先用JSON_VALID验证原始字符串确定非法位置。把字符串用CONVERT(... USING utf8mb4)强制转码后再存。有时来源系统用的GBK直接拷贝过来变成了乱码JSON解析必然失败。注意控制字面量里的特殊字符比如反斜杠、单引号。在SQL里写JSON字符串时外层用单引号里面所有双引号都别转义反斜杠要写双份。很多非法JSON问题就是看着对实际转义不对。4.3 路径表达式中的$和.号JSON Path里的$代表整个JSON文档本身。它既可以指代整个对象也可以取属性$.a.b。但如果你要取的键名本身包含点号比如{user.name: zhangsan}路径要多一层写法SELECT extra-$.user.name FROM order_info;注意键名要加双引号包裹否则MySQL会把点号解析成层级分隔符取出来是NULL。这种问题不报错查出的结果永远是空非常迷惑。我在解析第三方开放平台的回调参数时就遇到过对方字段命名带点号用普通路径取老取不到后来才想起来转义。4.4 JSON函数与存储过程的组合拳我最近在做的一个自动清理任务就需要在存储过程里操作JSON。核心逻辑是这样的CREATE PROCEDURE sp_archive_expired_coupons() BEGIN DECLARE v_batch_json JSON; DECLARE v_id BIGINT; DECLARE v_expired_ids JSON; DECLARE done INT DEFAULT 0; DECLARE cur CURSOR FOR SELECT id FROM coupon WHERE JSON_UNQUOTE(JSON_EXTRACT(extra, $.expire_time)) NOW() LIMIT 1000; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done 1; SET v_expired_ids JSON_ARRAY(); OPEN cur; read_loop: LOOP FETCH cur INTO v_id; IF done 1 THEN LEAVE read_loop; END IF; SET v_expired_ids JSON_ARRAY_APPEND(v_expired_ids, $, v_id); END LOOP; CLOSE cur; IF JSON_LENGTH(v_expired_ids) 0 THEN UPDATE coupon SET status archived WHERE JSON_CONTAINS(v_expired_ids, CAST(id AS CHAR)); END IF; END$$整体思路是把ID先聚成JSON数组再配合JSON_CONTAINS批量更新。这个写法避免了逐条UPDATE的性能问题也减少了多次打开游标的开销。实际跑下来一次1万条过期的优惠券归档从几十秒降到两秒左右。当然如果你要处理的是大表还是建议用临时表关联替代JSON数组这里只是提供一个JSON函数的典型组合用法。4.5 日志型JSON表用JSON_TABLE把文档拉回关系型边界当你需要把JSON数组里的多个元素拆开跟其他表做JOIN时JSON_TABLE就是神器。比如订单extra里存了商品明细items数组SELECT o.order_no, item_table.item_id, item_table.qty, item_table.price FROM order_info o, JSON_TABLE( o.extra, $.items[*] COLUMNS ( item_id VARCHAR(32) PATH $.item_id, qty INT PATH $.qty, price DECIMAL(10,2) PATH $.price ) ) AS item_table WHERE o.order_no ORD202601110001;这个等值JOIN的写法和普通表一摸一样JSON_TABLE在8.0里可以当成派生表来用。性能上如果外层已经通过order_no过滤到单行拆行开销非常小。但如果全表大范围展开JSON数组性能消耗还是很明显的要控制好外层过滤条件。4.6 快速定位问题的小工具JSON_PRETTY排查JSON结构问题时我偶尔会用JSON_PRETTY来格式化输出效果和在线JSON格式化工具一样但直接在SQL里跑SELECT JSON_PRETTY(extra) FROM order_info WHERE id 1;输出会带缩进直观很多。这在调试三层嵌套结构时特别有用不用把原字符串复制出去格式化再回来。缺点是数据量大的时候格式化非常吃CPU建议只在单行调试时使用。5. 设计选型什么时候该用JSON什么时候该老老实实建表5.1 关系型建模和JSON型建模的边界不是所有灵活场景都适合JSON。我自己有一个判断标准如果这个字段只是存起来偶尔展示或者只有少数几条查询逻辑会用到JSON合适。如果这个字段要被大量WHERE过滤、JOIN关联、聚合统计那就应该拆成正式列或者子表。如果这个字段的查询频率中等但字段本身经常增减比如动态表单、自定义属性用生成列JSON组合最合适。我用JSON用得最舒服的场景是配置类数据。比如一个营销活动不同渠道的活动配置完全不同CREATE TABLE campaign_config ( id bigint NOT NULL AUTO_INCREMENT, campaign_code varchar(64) NOT NULL, config json DEFAULT NULL, PRIMARY KEY (id) );配置里可能有规则集、奖品列表、限购信息结构差异极大。传统关系建模要么提前把所有列都想全要么每次需求变更就加列而JSON配置列配合少量生成列索引改动成本基本为零。5.2 JSON列会拖慢写入吗会但没那么夸张。JSON列需要在写入时做合法性和序列化处理实测比普通的VARCHAR列慢5%到10%左右对于绝大多数业务场景可以接受。但一定要注意不要频繁UPDATE JSON列。因为每改一次MySQL都要重新序列化整个JSON对象而不是只改其中一段。如果JSON里有几十个字段你只改其中一个代价也是整个文档重新写入。所以我把JSON列当写少读多的数据来对待需要频繁改的字段比如订单状态独立成普通列不太变的聚合信息比如地址快照、商品快照才放JSON列。5.3 JSON排序的注意点MySQL对JSON列的ORDER BY如果没有显式指定路径其实是对内部二进制表示排序结果几乎没意义。所以你排序必须写路径表达式而且最好加上CAST。举个例子-- 不推荐结果不可控 SELECT * FROM product ORDER BY extra; -- 推荐按某个具体字段排序 SELECT * FROM product ORDER BY CAST(extra-$.price AS DECIMAL(10,2)) DESC;如果只是按字符串排序可以不加CAST但数字语义一定要加否则字符串字典序会错乱。这个坑我在前面数字变字符串里已经提过排序和比较是重灾区。6. 实操总结与性能调优心得6.1 我建JSON表时固定的四件套经历过几个项目后我形成了一套固定的建表风格。每次涉及JSON字段我都会同时考虑四件事字段是否真的需要JSON类型如果结构确定、改动概率低优先选TEXTVARCHAR。查询入口是否固定固定的话直接设计生成列索引不固定考虑全文检索或多值索引。写入频率写入频繁的JSON文档尽量控制文档体积别把大段文本塞进去。备份和迁移成本JSON列虽然存储灵活但在做数据迁移、订阅同步时解析成本比普通列高。如果有离线数仓消费同一份数据必须提前约定好JSON里面的字典结构不然下游解析脚本要跟着改。四件事想清楚之后再建表几乎没出过大问题。6.2 从一次线上故障看JSON列的性能上限有一回线上订单表就因为在JSON里放了很大一段埋点数据单条JSON文档到了40KB然后业务又频繁更新其中一个字段。结果MySQL的写放大非常严重binlog和undo log都涨得飞快从库延迟一度飙到五分钟以上。后来我们做的调整有两个一是把大JSON拆出去只保留高频查询的小JSON字段大埋点数据放到独立的OSS日志通道。二是把频繁更新的字段改成普通列JSON列干脆做成不可变快照。这次故障给我的教训很直接JSON列的存储灵活是用写放大和不可变更新成本换来的你不能既要求它高频改又要求它零成本。相比设计阶段多花点心思线上故障的代价大多了。6.3 最后分享一个小技巧如果你在一条SQL里既要JSON函数取字段又要走生成列索引最稳的写法就是虚拟列表达式完全复用。项目里统一规范所有虚拟列命名加_v后缀一眼能看出来。所有查询统一用虚拟列筛选。除非为了动态查询不允许直接在WHERE里写JSON_EXTRACT。这个规范帮我们在Code Review时省了大量时间也避免了隐式类型转换带来的潜在性能问题。如果你还没有自己的JSON列使用规范建议尽早把这条纳入团队约定能少踩很多坑。