1. MySQL的JSON类型从“能存”到“好用”究竟差在哪MySQL的JSON类型我从5.7版本开始就在生产环境里用了前后差不多五年。从刚出来时的兴奋到后来被隐式转换、SQL_MODE和索引限制坑到怀疑人生再到8.0把JSON函数链补齐、把多值索引端上桌整个演进过程我基本一路踩过来。今天这篇就把MySQL里的JSON类型一次说透——怎么建表、怎么写、怎么查、怎么更新、怎么建索引以及哪些场景千万别用JSON全部摊开讲。这篇内容适合几类人准备在项目里引入JSON列但心里没底的新手开发在线上被JSON字段查询慢、走不了索引折腾过的后端或DBA以及在“拆列还是用JSON”之间反复摇摆的项目负责人。我会尽量把每个操作背后的为什么讲清楚而不是只甩一段能跑的SQL。1.1 业务里会冒出JSON需求多数不是炫技我见过最典型的JSON需求来自开放平台回调。对接的渠道A给三个固定字段渠道B给五个字段渠道C今天给两个、明天又多出一个嵌套结构。这种场景如果全建成独立列表会被空值填得很难看而且每次渠道接口升级都要ALTER TABLE。JSON列这时候就像抽屉里的大收纳盒——先塞进去查询时再拆至少表结构不用频繁动。另一种高频场景是配置类数据和埋点数据。配置项少说几十个经常只改其中几个把整个配置串存成JSON业务代码只取自己关心的key就行。埋点数据更是天然带“键动态、值随意”的特点提前定关系模型不现实。MySQL 5.7之后有了JSON类型这类数据至少能在数据库里被校验格式、被部分索引而不是一团只能靠LIKE碰运气的字符串。但必须先泼盆冷水JSON列适合的是“动态属性、低频过滤、结构变化快”的数据。如果某个字段是核心业务字段天天被拿来WHERE、ORDER BY那就老老实实建列。JSON不是用来替代关系模型的它是关系模型的一种灵活补充。这个定位想不清楚后面全是在给自己挖坑。1.2 JSON字段和TEXT存字符串本质差别在哪很多读者问既然都能存长内容直接用TEXT列存JSON字符串行不行我刚上手时也这么干过后来被坑了几次才明白差别在哪核心是三点。第一是格式校验。JSON列在写入时MySQL会调用JSON_VALID做严格校验非法JSON直接拒绝。TEXT列不管你塞一段“hello world”它也收等到程序解析时才炸而且炸在应用层排查链路长一大截。第二是存储格式。JSON列在InnoDB里不是按原始文本存的而是被转成MySQL内部的二进制JSON格式会去掉无关空白、合并重复键并给每个路径建立快速定位的结构。这意味着你读取某个路径的值不需要像TEXT那样每次做完整字符串解析。写入可能稍慢一点但查询时天然吃红利。第三是函数和索引生态。JSON列有完整的JSON函数集比如JSON_EXTRACT、JSON_CONTAINS、JSON_TABLE还能在虚拟列或多值索引上加速查询。TEXT列想做到这些只能靠应用层把整串拉出来解析或者用LIKE碰运气性能完全不在一个量级。单从“能存”看TEXT确实能用从“好用、可查、可控”看JSON列对TEXT是全面碾压。能用JSON类型就别纠结。2. 建表和写入先把JSON列的地基打好2.1 建表语法与容易忽略的约束JSON列建表很简单写法跟普通类型一样CREATE TABLE user_profile ( id INT PRIMARY KEY AUTO_INCREMENT, profile JSON, created_at DATETIME DEFAULT CURRENT_TIMESTAMP );这里有几个细节非常容易被忽略。关于默认值MySQL 8.0.13之前JSON列不允许直接指定默认值8.0.13之后才允许用表达式DEFAULT (expression)作为默认值。如果你还在MySQL 5.7上跑试图给JSON列加DEFAULT会直接报错。这是很多人迁移到JSON类型时撞上的第一堵墙。关于长度限制JSON列存储时受max_allowed_packet限制超出会直接报错。如果设计时知道某个JSON文档会到GB级别那就不适合塞进MySQL的JSON列要么拆字段要么走对象存储。建表后建议先用JSON_TYPE、JSON_VALID、JSON_PRETTY这几个函数自查一下行为。JSON_TYPE({a:1})返回OBJECTJSON_TYPE([1,2])返回ARRAY。这类函数在排查异常数据时非常实用。2.2 写入JSON数据的三个常见坑位写JSON列可以直接插字符串也可以是参数化传入的合法JSON串。最直观的方式INSERT INTO user_profile (profile) VALUES ({name:张三,age:28,tags:[python,mysql],address:{city:上海,district:浦东}}), ({name:李四,age:35,tags:[java,redis],address:{city:北京,district:朝阳}}), ({name:王五,age:24,tags:[mysql,go],address:{city:深圳,district:南山}});这里我一次性插了三条示例数据后面所有查询演示都基于这张表。写入时最常遇到的坑我列三个。第一个坑SQL_MODE和引号。JSON里的字符串值必须带双引号比如{name:张三}如果你在程序端拼字符串时把双引号漏了写成{name:张三}MySQL会立刻抛Invalid JSON text。建议所有JSON写入在应用层先做序列化而不是手拼字符串。第二个坑重复键。{a:1,a:2}这种写法在MySQL 8.0.3之前保留第一个值之后保留最后一个值。我在生产里就遇到过上游系统同时推送两个版本字段导致取到老数据的事。如果业务依赖这个行为建议在写入前用JSON_OBJECT聚合或者干脆在应用层保证键唯一。第三个坑中文和转义。JSON里如果包含中文确认连接字符集是utf8mb4否则写入后读出来就是乱码。另外JSON字符串里的双引号、反斜杠都需要转义最容易出错的场景是URL里带和手动拼JSON时经常把引号搞错。用参数化写法和JSON_OBJECT函数能少踩一半的坑。写完后想快速验证格式没问题可以用SELECT id, JSON_VALID(profile) AS valid_flag FROM user_profile;JSON_VALID返回1表示合法这在排查写入告警数据时非常实用。3. 查询JSON五个高频函数一次吃透JSON真正让开发效率飞起的部分在查询下面这几个函数是我的日常主力。3.1 JSON_EXTRACT和两个箭头运算符最常用的取值手段JSON_EXTRACT是基础函数作用是从JSON文档里按路径取值SELECT JSON_EXTRACT(profile, $.name) AS name FROM user_profile;这个写法返回的是JSON类型结果如果值是字符串会带着双引号。输出里看到的是张三而不是张三很多人第一次用都会愣一下。所以实际项目里我更推荐用 - 运算符它等价于JSON_UNQUOTE(JSON_EXTRACT())。用 - 拿字符串就是不带引号的干净值SELECT profile-$.name AS name, profile-$.address.city AS city FROM user_profile;箭头运算符里- 等价于JSON_EXTRACT- 等价于JSON_UNQUOTE(JSON_EXTRACT())。日常取值我几乎只写 -。下面这条拿数组元素的SQL也非常常用SELECT profile-$.tags[0] AS first_tag FROM user_profile;路径表达式的语法需要单独熟悉一下$表示整个文档$.name取根层键$[name]是等价写法$[0]取数组第一个元素$[0].tag取数组第一个对象的tag键$.*取所有键$**.name递归搜索所有层级的name。掌握了这套路径语法大部分取值场景就够用了。3.2 JSON_CONTAINS、JSON_SEARCH、JSON_TABLE过滤与展开的利器取值只是基础过滤才是JSON查询的重头。JSON_CONTAINS用于判断JSON文档是否包含某个值注意参数顺序是先目标再候选。想看哪些用户带了“mysql”标签正确写法是SELECT id, profile-$.name AS name FROM user_profile WHERE JSON_CONTAINS(profile-$.tags, mysql);这里有个特别容易踩的坑第二个参数必须是合法的JSON值字符串要写成mysql带双引号的JSON字符串而不是mysql。写错或者用单引号拼结果就会一直为空。这个问题我在线上排查过好几次冷静下来发现都是手写字符串时少了JSON的引号。JSON_SEARCH用来定位某个值在文档中的路径返回的是路径字符串SELECT JSON_SEARCH(profile, one, mysql) FROM user_profile;第二个参数one表示只返回第一个匹配路径all表示返回全部。这个函数在判断“某个值是否存在”时不如JSON_CONTAINS直接但在你要修改数组中特定元素时它能帮你先找到位置。JSON_TABLE则是把JSON文档展开成关系表的神器支持把数组元素拆成一行行再和原表做JOIN。想统计每个标签分别有多少用户可以这样写SELECT jt.tag, COUNT(*) AS cnt FROM user_profile t JOIN JSON_TABLE(t.profile, $.tags[*] COLUMNS (tag VARCHAR(20) PATH $)) jt GROUP BY jt.tag;这条SQL会把三条数据里的tags数组全部拍平输出一行行标签然后再聚合。JSON_TABLE在8.0之后是真的把JSON和关系表打通了很多原本要先用应用层解析再拼IN查询的需求直接在SQL里一条完成。如果只想判断路径是否存在用JSON_CONTAINS_PATH配合one或all控制匹配策略。比如判断用户是否填了city和districtSELECT id FROM user_profile WHERE JSON_CONTAINS_PATH(profile, all, $.address.city, $.address.district);查询函数这块基础够用后效率很高的组合是路径表达式取值 JSON_CONTAINS过滤 JSON_TABLE展开。三个搭起来基本覆盖了动态数据的检索诉求。4. 更新JSON数据局部动刀别整行重写更新JSON列最容易犯的错误是一整列拿出来改完再覆盖回去。这个做法在大并发下会引发严重的更新冲突和写放大。MySQL从5.7开始就给了若干个局部更新函数这里展开讲。4.1 JSON_SET / JSON_INSERT / JSON_REPLACE 三兄弟怎么选这三个函数都是“在指定路径写入值”但行为完全不同JSON_SET路径存在就更新不存在就新增。JSON_INSERT路径存在就不动只处理不存在的路径。JSON_REPLACE只处理已经存在的路径不存在就忽略。拿“改年龄”举例想确保age字段一定被更新用JSON_SETUPDATE user_profile SET profile JSON_SET(profile, $.age, 29) WHERE id 1;如果业务语义是“补默认值已有值不动”那用JSON_INSERTUPDATE user_profile SET profile JSON_INSERT(profile, $.level, silver) WHERE id 1;如果只想改已存在的键、不想误新增字段用JSON_REPLACEUPDATE user_profile SET profile JSON_REPLACE(profile, $.name, 张小三) WHERE id 1;这三个函数的路径参数可以一次写多组比如UPDATE user_profile SET profile JSON_SET( profile, $.age, 30, $.level, gold ) WHERE id 1;单条UPDATE里同时维护多个字段能少一次就是一次网络往返。4.2 JSON_REMOVE与数组扩展函数的实操细节要删掉某个键用JSON_REMOVE。UPDATE user_profile SET profile JSON_REMOVE(profile, $.level) WHERE id 1;可以一次删多个路径比如JSON_REMOVE(profile, $.level, $.age)。数组操作有两个容易混淆的函数JSON_ARRAY_APPEND是把元素追加到数组末尾JSON_ARRAY_INSERT是把元素插入到指定下标位置。向后端tags数组加一个“elasticsearch”UPDATE user_profile SET profile JSON_ARRAY_APPEND(profile, $.tags, elasticsearch) WHERE id 1;把新元素插到数组第一位UPDATE user_profile SET profile JSON_ARRAY_INSERT(profile, $.tags[0], elasticsearch) WHERE id 1;注意JSON_ARRAY_APPEND的路径是数组本身$.tags而JSON_ARRAY_INSERT的路径要写到具体下标$.tags[0]。写反之后结果会完全看不懂。合并两个JSON对象时MySQL 8.0里推荐用JSON_MERGE_PATCH它是“后者覆盖前者同名键”的语义。如果要用“不覆盖且保留重复键”的语义用JSON_MERGE_PRESERVE。5.7时代的JSON_MERGE函数在新版本已经废弃我建议直接别用接口语义太容易混淆。更新JSON列时还有个容易被忽视的点JSON_SET这类函数执行后MySQL内部会重建整个二进制JSON值。如果JSON文档非常大局部更新就没那么局部了性能照样退化。所以设计上要控制单个JSON文档的体积别把几千个key全塞进去然后天天UPDATE这种场景就该拆表了。5. JSON性能优化虚拟列与多值索引怎么落地JSON列最大的痛点是直接用它做WHERE条件时走不了索引基本全表扫描。这一节讲怎么救以及什么时候别救。5.1 虚拟列 普通索引被低估的黄金组合虚拟列是MySQL 5.7引入的特性它不实际存储数据查询时按表达式计算。更关键的是可以给虚拟列建二级索引MySQL 8.0对VIRTUAL列上的二级索引支持已经比较完善。这个组合放到JSON场景下就是“把JSON里的常用字段抽出来让数据库把它当作普通列来索引”。比如我们经常按age过滤用户那就抽取一个age虚拟列ALTER TABLE user_profile ADD COLUMN age INT GENERATED ALWAYS AS (profile-$.age) VIRTUAL, ADD INDEX idx_age (age);后续查询写成SELECT id, profile-$.name AS name FROM user_profile WHERE age BETWEEN 20 AND 30;这条查询就能走idx_age索引执行计划从全表扫描秒变索引范围扫描。为什么这里用-而不是-因为-返回的是去掉引号的字符串配合CAST到INT时才不会出现隐式转换问题。虚拟列还有STORED和VIRTUAL两种选择。STORED会把计算结果物理落盘建索引后读取更快但占存储空间并且每次写入都有计算成本VIRTUAL不占存储查询时计算二级索引在8.0里能覆盖大多数场景。我的习惯是过滤高频的用VIRTUAL加索引计算复杂或需要稳定读取性能的再用STORED。实践下来虚拟列方案是把JSON条件查询性能拉回普通列水平的最稳妥办法。5.2 多值索引8.0.17之后的数组查询加速虚拟列能解决“取单个标量值”的加速但碰见JSON数组条件就无能为力了。比如查tags数组里包含“mysql”的用户靠虚拟列没法做一般只能写成JSON_CONTAINS(profile-$.tags, mysql)全表扫。MySQL 8.0.17引入的多值索引专治这种场景。创建多值索引的语法略微特殊必须把JSON数组CAST成一个类型数组CREATE INDEX idx_tags ON user_profile ( (CAST(profile-$.tags AS CHAR(20) ARRAY)) );查询时用JSON_CONTAINS就可以走索引也可以配合MEMBER OF、JSON_OVERLAPS等条件。我用的一个经验是多值索引的查询写法要跟索引定义匹配否则优化器会放弃它。用EXPLAIN看执行计划时key字段如果显示idx_tags说明走索引了否则要检查表达式是否一致。多值索引在MySQL 8.0.17之前是没有的如果你还在5.7或者8.0早期版本数组过滤就只能全表扫性能只能靠控制表体积和缓存来兜底。这也是升级8.0之后能直接感受到的甜头之一。5.3 有些场景再优化都不如拆列优化讲完必须泼一盆更大的冷水有些场景再优化都不如拆列。第一个不该用JSON的场景是核心主数据。订单金额、库存数量、用户手机号这类天天参与事务、强约束、强关联的字段任何情况下都不要塞进JSON。JSON没法建外键没法做精度约束连CHECK约束配合JSON路径也有诸多限制。这类字段建成独立列才能享受类型检查、事务保证、索引优化这些数据库核心能力。第二个不该用JSON的场景是高并发热点更新。JSON文档的重建机制决定了只要更新任何一个内部路径整个二进制JSON结构都要重写。如果一个用户画像JSON有1MB每次修改一个标签都要重写1MB热点用户一多InnoDB的写放大能拖垮整个实例。第三个不该用JSON的场景是超过三层的深度嵌套。嵌套越深路径提取越慢JSON的可维护性越差。我接手过一套埋点系统五层嵌套的正确性维护成本相当高程序崩溃后排查数据格式就要花半个晚上。JSON是糖但糖吃多了会蛀牙。我的原则是能用拆列解决的优先拆列真正动态变化的核心属性才用JSON用了JSON也要给高频查询路径建好虚拟列或索引。6. 常见报错、故障排查与我的独家心得6.1 高频报错对照表这一节我把自己这几年踩过的典型报错整理成一张表遇到可以直接按图索骥。报错信息常见原因解决手段Invalid JSON text in argument 1 to function ...写入内容不是合法JSON常见手拼字符串漏引号应用层用序列化库生成JSON或改用JSON_OBJECT/JSON_ARRAY函数构造The column xxx cannot be a JSON column because it has a default valueMySQL 8.0.13之前JSON列不允许DEFAULT去掉默认值或升级到8.0.13以上并用表达式默认值Cannot create a JSON index ...直接对JSON列建普通索引MySQL不支持改用虚拟列索引或多值索引Incorrect arguments to JSON_CONTAINS参数顺序写反或第二个参数不是合法JSON值确认JSON_CONTAINS(目标, 候选)候选字符串值必须带双引号Data too long for column profile插入的JSON超过了max_allowed_packet调大max_allowed_packet或拆分文档The path expression $... is not valid路径语法写错比如忘记写$核对路径表达式数组下标和键名引用要符合语法Invalid JSON text: The document is empty给JSON列传了空字符串空串不是合法JSON可存NULL或{}这张表之外最容易被忽略的是字符集报错。连接字符集是latin1时写入中文JSON读出来是问号数据库层不报错只在业务数据里表现为乱码。遇到JSON中文乱码先检查character_set_connection和client统一用utf8mb4几乎能根除。6.2 关于JSON类型使用的几条实操心得最后聊几条来自一线的心得希望能帮你少走我走过的弯路。第一关于隐式转换。JSON查询条件里数字和字符串很容易踩隐式转换的坑。profile-$.age返回的是JSON类型和整数28直接比较时MySQL会尝试把JSON转成数字写错会查不到数据或者性能走偏。我的习惯是条件里统一用-先取出来再显式CAST避免一切隐式转换的意外。第二关于JSON_SIZE排查。需要确认JSON实际占用空间时用JSON_STORAGE_SIZE(profile)能看到这个列实际占用的字节数。之前调max_allowed_packet时我靠它精确判断了JSON文档的体积。JSON_PRETTY则能把二进制存储的JSON排版出来调试时非常直观好用。第三关于批量更新。在UPDATE里大量使用JSON_SET时先小范围测试确认执行计划里走的扫描路径别直接对几百万行全表UPDATE。JSON重写成本摆在那里分批次限速执行才能避免对在线业务造成影响。第四关于下游数据消费。很多BI或流式工具对MySQL JSON的解析支持还不完整如果下游要消费这些JSON数据可以在MySQL端用JSON_TABLE先展开成行再导出或者应用层读取后用原生库解析。让下游直接处理复杂JSON串往往会把排错成本转嫁给别人。日常接手JSON字段项目时我的自查清单这段我放在最后是因为它是我评审表的浓缩版。接手一个含JSON字段的项目时我会快速回答几个问题。这个字段是否会被频繁查询或排序如果会有没有对应的虚拟列或索引这个字段是否参与JOIN或外键如果会说明拆列时机到了。单个JSON文档的平均大小是多少如果超过几百KB就要考虑拆表或者是不是选错了存储方案。JSON嵌套层级是否超过三层如果是先和业务确认能不能拍扁。这些问题过一遍方案基本就清晰了。JSON类型在MySQL里已经足够成熟它不是银弹但用对场景确实能省掉一大半“动态属性改表结构”的事情。我的建议是用它但要有节制地用——先拆最确定的列再用JSON承接真正会变的属性最后用索引兜住查询性能。写到最后分享一个小习惯每次建完JSON列我都会立即建好配套的虚拟列和索引并在表注释里写明“哪些字段被抽出来了”。这样几个月后自己回来看这张表也能一眼看出JSON里的核心路径是哪些不至于面对一坨嵌套靠猜。希望这篇能让你在MySQL里用JSON时少踩几个我当时踩过的坑。