老朋友们应该都有过类似的经历业务跑得正欢产品突然丢过来一个需求——“用户扩展资料里昵称带‘张’的用户给我捞出来”。你打开表结构一看昵称好几个字段全塞在一个JSON列里心里咯噔一下完了这又得跟MySQL的JSON查询打法交道了。我最初也以为就是写个LIKE糊弄过去结果SQL一跑一条数据都查不出来甚至还有查出来一堆莫名其妙匹配到“键名”上的脏数据。这篇东西不打算念那些官方文档我想把这大半年在MySQL JSON模糊查询上踩过的坑、试过的写法、最后沉淀下来能直接抄作业的方案一次性掰开揉碎讲清楚。文章会用真实建表语句和SQL覆盖JSON对象、JSON数组、多层嵌套三类最常见结构后面再补上性能优化和数据模型该不该拆的建议。不管你是后端开发还是被临时拉去写SQL的运维看完应该都能少走不少弯路。1. 先弄明白JSON字段的模糊查询为什么这么难搞1.1 JSON列和普通字符串列存储逻辑完全不同很多人在JSON字段上栽跟头根子在于把JSON类型当成了VARCHAR来用。MySQL从5.7开始引入原生JSON类型8.0又是一波增强但这个类型的存储逻辑跟普通字符串完全不是一回事。普通VARCHAR就是存一串字符LIKE直接扫就是了JSON类型内部是经过解析、优化后的二进制格式插入时会做合法性校验MySQL会把它拆成一棵文档树来管理。这带来一个直接后果你没法对JSON列本身建普通索引也没法拿它像普通字符串那样随便LIKE。如果直接对JSON列写WHERE user_extra LIKE %张%MySQL确实也能执行——它会先把JSON序列化成文本再匹配可这时候问题就来了。一是整列转换导致索引完全失效数据量稍微上去就是全表扫描二是JSON序列化之后键值顺序、空格、转义都是数据库内部决定的你按自己脑补的格式去LIKE经常匹配出鬼东西三是最要命的它会连键名一起匹配比如nickname:张三里的nickname本身就可能撞上你的搜索词。所以对JSON字段做模糊查询第一步就是放弃“整个字段一把梭”的思维改成“先定位到JSON里的某个字段再对这个字段的值做模糊匹配”。1.2 模糊查询的三个“隐形陷阱”基本大家都踩过第一个陷阱是LIKE到了键名上。这个上面提过直接对JSON列LIKE键和值都会被扫你搜“nick”结果匹配到“nickname”这个键数据量一大你都不知道自己查出来的到底是啥。第二个陷阱是JSON_EXTRACT提取出来的值带引号。很多人知道要先提取字段再匹配于是写了JSON_EXTRACT(user_extra, $.nickname) LIKE %张%结果有时候查得到有时候查不到。原因是JSON_EXTRACT返回的是JSON格式的字符串字符串值会带着双引号而且内部字符可能有转义。比如存储的昵称是“张三”名字里带引号提取出来就变成张\三你按%张%匹配可能没问题但等值匹配或者带特殊字符的搜索就会翻车。第三个陷阱是大小写和排序规则。JSON提取出来的值按MySQL的规则比较对大小写和二进制内容敏感很多时候你以为能查出来的“abc”跟“ABC”结果一条都匹配不上。这一点等会儿用真实SQL演示的时候细说。搞清楚这些坑后面写查询心里就有谱了先按路径提取再做类型转换最后才轮到LIKE或正则。2. 五种JSON模糊查询方案从最暴力到最优雅2.1 直接LIKE整个JSON列能用但请慎用先看一个最直白的写法SELECT * FROM user_profile WHERE user_extra LIKE %nickname:张%%;如果JSON列里存的内容是规规矩矩、没有多余空格和乱序的情况这个写法在某些场景确实能查出数据。但我强烈不建议在生产环境用理由上面已经说了一部分这里再补一个更坑的MySQL的JSON序列化不保证键值顺序稳定同一份数据可能因为插入方式不同序列化结果就不一样。你在测试环境看着没问题一上生产就抽风。而且这个写法里还有个隐性BUG就是如果JSON里某个其他字段的值恰好也包含这段文本比如用户的自定义签名里写了一句“nickname:张某某”一样会被匹配出来。模糊查询本来误差就大这种全列扫描会把误差放到最大排查问题的时候非常痛苦。2.2 JSON_EXTRACT提取字段后再匹配最常见的进阶写法既然不能整个列LIKE那先用JSON函数把目标路径取出来。MySQL里最基础的两个API是JSON_EXTRACT函数和-操作符二者等价-- 写法一函数 SELECT * FROM user_profile WHERE JSON_EXTRACT(user_extra, $.nickname) LIKE %张%; -- 写法二操作符 SELECT * FROM user_profile WHERE user_extra-$.nickname LIKE %张%;这里就要重点说一下1.2里提到的坑了。JSON_EXTRACT(user_extra, $.nickname)的返回值类型是JSON如果nickname是一个字符串那么返回结果在逻辑上是带双引号的JSON字符串。比如你存的是“张三”提取出来其实是张三。你LIKE%张%还能蒙对但要是做等值匹配 张三绝对查不出来因为实际值带着引号。更麻烦的是如果字符串里存了反斜杠、引号这类需要JSON转义的字符提取结果会跟你眼睛看到的不一样模糊匹配就更容易出幺蛾子。所以JSON_EXTRACT适合用来提取数字、布尔值这类不带引号的JSON类型但如果目标是字符串建议配合JSON_UNQUOTE使用或者直接用下文要讲的-操作符。2.3-才是真正的解药但注意返回类型是字符串MySQL从5.7.13开始提供-操作符它等价于JSON_UNQUOTE(JSON_EXTRACT(...))一步到位把JSON字符串值两边的引号去掉SELECT * FROM user_profile WHERE user_extra-$.nickname LIKE %张%;这是我日常用得最多的写法没有之一。它把“提取字段”和“去掉JSON包装”两个动作合并了返回的是纯字符串LIKE行为跟普通VARCHAR几乎一致语义清楚也好维护。不过有个细节必须提醒如果JSON字段里的值是数字-提取出来的是字符串形式的数字比如存储年龄18提取出来是18这个字符串。你拿它跟或做数值比较MySQL会做隐式类型转换有索引也用不上还可能出现精度问题。真要做数值范围查询要么用JSON_EXTRACT提取原始JSON数字要么在比较时显式CAST(... AS UNSIGNED)再比较。2.4 JSON_CONTAINS数组精确匹配的利器但不是模糊查询如果你的JSON结构里有数组比如用户身上挂了好几个标签{nickname: 张三, tags: [vip, 老用户, 高消费]}想查出所有包含“vip”标签的用户有些人会本能地想用LIKE但更合适的方式是JSON_CONTAINSSELECT * FROM user_profile WHERE JSON_CONTAINS(user_extra, vip, $.tags);注意第二个参数是JSON格式的候选值字符串必须自带引号。嫌手写引号难看可以用JSON_QUOTE函数包一层SELECT * FROM user_profile WHERE JSON_CONTAINS(user_extra, JSON_QUOTE(vip), $.tags);JSON_CONTAINS做的是包含关系判断语义是“这个JSON片段是不是在目标路径下”它不解决模糊匹配问题。你想查“包含‘vi’开头的标签”JSON_CONTAINS完全帮不上忙。但它在数组精确匹配上性能比LIKE好得多而且MySQL 8.0.17以上还能配合多值索引后面优化章节我会展开讲。2.5 JSON_SEARCH数组和深层嵌套的模糊查询神器这里要说一个被很多人忽略的函数——JSON_SEARCH。它的设计目标就是在一个JSON文档里“找字符串”支持LIKE风格的通配符简直是为模糊查询量身定做的SELECT * FROM user_profile WHERE JSON_SEARCH(user_extra, all, %张%, NULL, $.tags) IS NOT NULL;参数拆解一下第一个参数是要搜索的JSON文档第二个参数是匹配模式one表示找到第一个就返回all表示返回所有匹配路径第三个参数是你想找的字符串可以直接用%和_通配符第四个参数是转义字符不需要就传NULL第五个及以后的参数是可选的路径范围可以指定只搜哪一部分。JSON_SEARCH最实用的场景是搜索数组元素和深层嵌套值。你不需要知道目标值在数组的哪个位置甚至不需要知道整个文档结构只要告诉它“在tags里找包含‘张’的字符串”就行。返回值是匹配到的JSON路径比如$.tags[1]所以判断标准就是IS NOT NULL。但这个函数有个性能隐患它是遍历式搜索几乎没有索引可以利用。数据量小的时候无所谓数据量一大就算你指定了路径范围该慢还是慢。所以它适合作为“临时应急方案”或“低频后台查询”不适合放在用户高频请求的主链路上。3. 三个真实业务场景的完整落地案例3.1 场景一用户扩展信息表按昵称模糊搜索假设我们有一张用户资料表用户ID和基础信息在主表一堆扩展属性塞在JSON里CREATE TABLE user_profile ( id INT PRIMARY KEY AUTO_INCREMENT, user_id INT NOT NULL, user_extra JSON, created_at DATETIME DEFAULT CURRENT_TIMESTAMP ) ENGINEInnoDB DEFAULT CHARSETutf8mb4; INSERT INTO user_profile (user_id, user_extra) VALUES (1001, JSON_OBJECT(nickname, 张三丰, phone, 13800001111, level, 5)), (1002, JSON_OBJECT(nickname, 李四, phone, 13900002222, level, 3)), (1003, JSON_OBJECT(nickname, 张小雅, phone, 13700003333, level, 4));需求把昵称里带“张”的用户捞出来。最直接的写法是SELECT user_id, user_extra-$.nickname AS nickname FROM user_profile WHERE user_extra-$.nickname LIKE %张%;这里我特意用-而不是JSON_EXTRACT就是因为要等会儿在结果里直接显示昵称不带引号更干净。如果还需要按手机号模糊搜索可以继续用OR拼接但要注意-提取出来的字段已经是普通字符串LIKE走正常规则不会再误伤键名。实际开发中我还会顺手把-提取出来的别名用在SELECT里省得在Java/PHP侧再写一串JSON解析逻辑查出来直接就是字符串。3.2 场景二埋点日志表在JSON参数里搜关键信息日志场景是JSON模糊查询的重灾区。埋点表结构一般是这样的CREATE TABLE event_log ( id BIGINT PRIMARY KEY AUTO_INCREMENT, event_name VARCHAR(64) NOT NULL, params JSON, created_at DATETIME DEFAULT CURRENT_TIMESTAMP ) ENGINEInnoDB DEFAULT CHARSETutf8mb4; INSERT INTO event_log (event_name, params) VALUES (click, JSON_OBJECT(page, home, source, app_home_banner, item_id, 12345)), (click, JSON_OBJECT(page, detail, source, search_result, item_id, 67890)), (share, JSON_OBJECT(page, detail, source, app_home, target, wechat));需求找出source字段里包含“app”的所有记录。SQL可以这样写SELECT id, event_name, params-$.source AS source FROM event_log WHERE params-$.source LIKE %app%;这个场景有个特点日志表通常数据量很大如果每天几百万条上面的LIKE语句即便写法正确跑一次全表扫描也够喝一壶的。后面优化章节我会给出完整方案这里想先强调的是日志表这种场景能走索引就走索引不能走索引就考虑把结果预聚合到统计表别让线上数据库扛这种查询。另外日志表的JSON路径最好提前理清楚。如果埋点上报的字段路径不统一有的叫source有的叫source_type那模糊查询的分支会越来越复杂这时候就该推动后端统一结构比在SQL层面硬扛靠谱得多。3.3 场景三商品规格表多层嵌套JSON的模糊查询最头疼的情况是JSON里套JSON数组里套对象。比如电商SPU表规格属性存得很随意CREATE TABLE spu_info ( id INT PRIMARY KEY AUTO_INCREMENT, spu_name VARCHAR(128) NOT NULL, sku_attrs JSON, updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ) ENGINEInnoDB DEFAULT CHARSETutf8mb4; INSERT INTO spu_info (spu_name, sku_attrs) VALUES (手机A, JSON_OBJECT( colors, JSON_ARRAY(黑色, 白色), memory, JSON_ARRAY(128G, 256G), network, 5G )), (手机B, JSON_OBJECT( colors, JSON_ARRAY(红色, 蓝色), memory, JSON_ARRAY(64G, 128G), network, 4G ));需求找到所有可选颜色包含“红”的商品。这时候JSON_SEARCH就派上用场了SELECT id, spu_name FROM spu_info WHERE JSON_SEARCH(sku_attrs, all, %红%, NULL, $.colors) IS NOT NULL;这种写法能直接钻到colors数组里对每个元素做模糊匹配。如果路径可以省略JSON_SEARCH会遍历整个文档比如搜“128G”这种可能出现在多个路径下的值SELECT id, spu_name FROM spu_info WHERE JSON_SEARCH(sku_attrs, all, %128G%) IS NOT NULL;注意JSON_SEARCH只搜索字符串值不搜key、不搜数字和布尔值。如果规格里存的是数字比如重量150g不要用搜索函数去找“150”除非存的就是文本。这是很多人忽略的边界条件。多层嵌套场景下SQL会变得比较长建议把常用搜索路径抽成视图比如建一个视图把sku_attrs-$.network提升为network列后续查询就清爽很多也方便加索引。4. JSON模糊查询的性能优化别再让全表扫描背锅4.1 虚拟列加索引5.7时代最稳妥的标准答案MySQL 5.7开始支持生成列Generated Column可以在建表或改表时把JSON里某个路径的值提取出来作为一列虚拟列或存储列然后在这列上正常建索引。这是解决JSON查询性能最通用的手段。先看DML级别的操作。假设user_profile表已经建好想给nickname加索引ALTER TABLE user_profile ADD COLUMN nickname VARCHAR(64) GENERATED ALWAYS AS (user_extra-$.nickname) STORED, ADD INDEX idx_nickname (nickname);把-表达式塞进生成列等于在插入数据时就把nickname算好存起来STORED查询直接拿普通索引。之后想按昵称模糊查询用nickname LIKE %张%该走索引走索引该回表回表行为跟普通VARCHAR列完全一致。这里有个细节生成列用STORED还是VIRTUALSTORED占磁盘空间但查询时不需要实时计算VIRTUAL不占空间但查询时MySQL要现场算索引要建立在虚拟列上MySQL会在索引里存计算结果。我的经验是如果这列被查得频繁用STORED更稳如果只是偶尔用VIRTUAL加索引也能跟STORED表现差不多。具体选哪个建议压测后决定。还有一个大坑生成列表达式里的-返回的是TEXT字符串指定VARCHAR(64)的时候如果不带字符集默认继承表字符集和排序规则。所以中文排序和LIKE的匹配规则跟你表里其他普通列一致这反而成了优势。4.2 MySQL 8.0的函数索引省掉虚拟列的麻烦MySQL 8.0.13开始支持函数索引可以不用额外加虚拟列直接在表达式上建索引CREATE INDEX idx_nickname ON user_profile ((CAST(user_extra-$.nickname AS CHAR(64))));注意这里的语法表达式外面包了两层括号这是函数索引的固定写法。CAST不能省略因为-返回的结果类型是长TEXT直接做索引键太长必须指定长度。函数索引的好处是省掉了一个虚拟列表结构更干净。另外函数索引要求表达式是确定性的deterministicJSON函数本身是符合的可以直接用。如果你的JSON里存的是数组还想给数组元素建索引MySQL 8.0.17以上可以用多值索引Multi-Valued IndexCREATE INDEX idx_tags ON user_profile ((CAST(user_extra-$.tags AS CHAR(64) ARRAY)));多值索引专门用来加速JSON数组的查询配合MEMBER OF或JSON_CONTAINS使用SELECT * FROM user_profile WHERE vip MEMBER OF (user_extra-$.tags);注意MEMBER OF 是精确匹配多值索引不能直接加速%模糊匹配。但如果你高频场景是“判断某个用户有没有某个精确标签”多值索引 MEMBER OF 就是这个场景的顶配方案性能远好于全表JSON_SEARCH。4.3 索引为什么没生效可能是隐式类型转换在捣乱有一种让人很抓狂的情况索引建好了EXPLAIN一看还是全表扫描。十有八九是类型对不上。举个例子生成列nickname是VARCHAR(64)查询时你写WHERE nickname 12345数字12345会被转成字符串12345MySQL可能觉得这不安全干脆不走路。另一个常见问题是排序规则不一致表是utf8mb4_general_ci但JSON表达式用-提取的值比较时用的可能是二进制排序规则一旦索引列和查询条件的排序规则不一致索引就废了。排查方法很简单EXPLAIN一下看看key字段是不是空。如果索引建了但没走优先检查查询条件的类型和排序规则跟索引列是否一致。别迷信“我加了索引”EXPLAIN才是硬道理。4.4 数据模型层面高频查询字段该拆就得拆最后说点很多DBA不爱听但特别实在的JSON虽然灵活但不能当万能膏药。如果一个字段频繁出现在WHERE、ORDER BY、GROUP BY里说明它已经不是“扩展字段”了而是核心业务字段。这时候最彻底的优化不是研究怎么给JSON加索引而是直接拆列。拆列的原则很简单查询频率高、长度可控、值的类型稳定的字段从JSON里拿出来单独建列加索引低频、结构变化快、只是偶尔存一下的继续留在JSON里。比如用户昵称、商品价格、订单状态这种没有任何理由放在JSON里供查询用——真想用就得接受全表扫描或者多一层虚拟列的开销。我之前接手的项目里有一张表把订单号都塞JSON里了结果售后模块天天按订单号查整个表才几十万行一个LIKE跑出去要两三秒。后来把订单号拆出来建了索引查询直接毫秒级。模型改对了比什么优化技巧都管用。5. 高频坑点整理我自己踩过的那些雷5.1 JSON_EXTRACT返回值带引号等值匹配永远对不上这是几乎每个JSON查询新手都会踩的坑。我有一个实际案例同事想统计“手机号等于13800001111”的用户SQL写成SELECT * FROM user_profile WHERE JSON_EXTRACT(user_extra, $.phone) 13800001111;查了半天一条都没有。他一度以为是数据问题结果单独把JSON_EXTRACT查出来看屏幕上明晃晃显示13800001111——多了对双引号。等值匹配当然对不上。解决办法就两个一是用-提取SELECT * FROM user_profile WHERE user_extra-$.phone 13800001111;二是用JSON_UNQUOTE包一层SELECT * FROM user_profile WHERE JSON_UNQUOTE(JSON_EXTRACT(user_extra, $.phone)) 13800001111;两种都行但第一种写法更短我推荐统一用-。同样的坑也出现在模糊查询的场景里只不过LIKE的通配符自动容忍了引号有时候能蒙对反而更迷惑人。5.2 搜索词里带引号或反斜杠结果直接失控JSON字符串在存储时会对特殊字符做转义比如昵称是“张三”存进JSON后实际是张\三。你用-提取后得到的是原始值“张三”又变回了一个普通字符串内部其实带着反斜杠。这时候你用LIKE%张%还能查到但用LIKE%\三%就得小心转义问题了。我的建议是凡是用户输入的搜索词进入SQL之前一定做转义或者用参数化查询。模糊查询和特殊字符天生不对付特别是反斜杠和百分号一个不留神就把整个查询语义搞坏了。5.3 搜索目标包含中文、大小写、首尾空格时的规则JSON里的字符串比较规则和普通VARCHAR不太一样大小写敏感性是由collation决定的。用-提取后一般认为它会继承连接字符集或默认排序规则但不同版本、不同表达式可能会有差异。如果你需要不区分大小写的模糊匹配建议在查询条件里显式处理WHERE LOWER(user_extra-$.nickname) LIKE LOWER(%zhang%)中文场景下通常不需要考虑大小写但要注意全角半角空格。比如用户存的是“张 三”中间带空格你搜“张三”就匹配不上。如果产品要求忽略空格可以在提取后套一层REPLACEWHERE REPLACE(user_extra-$.nickname, , ) LIKE CONCAT(%, REPLACE(张三, , ), %)这种方式能容忍用户输入的前后空格和中间空格但SQL会变得丑一些性能也会受影响。实际业务里我更推荐在写入时就把空格规范化而不是在查询时绞尽脑汁。5.4 数组找不到、路径写错JSON_SEARCH返回NULL让人摸不着头脑JSON_SEARCH搜不到数据第一反应别急着怀疑数据先检查路径。JSON路径的语法有点自己的脾气键名是普通字母数字用$.name键名里带空格、连字符、中文或特殊符号必须用双引号包起来比如$.user-name数组下标从0开始$.tags[0]是第一个元素想搜所有层级的某个键可以用递归路径$**.name。我见过最无语的一次排查线上线下数据完全一样本地JSON_SEARCH能查到生产环境就是查不到。后来发现线上表里JSON路径不是“name”而是“Name”大小写差一个字母全查不出来。JSON路径的键名匹配是区分大小写的这个细节一定要刻在脑子里。还有JSON_SEARCH搜索的是字符串值如果目标值是数字或者布尔值要提前转成字符串再查或者直接用CAST(... AS CHAR)处理。用错了就等着返回空吧。5.5 一条SQL查不到不一定错先用SELECT把提取值看看最后分享一个我自己的排查习惯。遇到JSON模糊查询查不出数据我不会直接改SQL而是先跑一条“裸奔”查询把JSON提取结果原样拿出来看SELECT id, user_extra-$.nickname AS nickname FROM user_profile LIMIT 20;这一步能解决80%的迷惑问题看看实际值有没有引号、有没有空格、路径对不对、大小写是不是一致。很多时候你以为是SQL写错了其实是数据本身长得跟你想的不一样。先把值看清楚了再动SQL效率高得多。另外在排查JSON查询问题时建议在测试环境构造几条边界数据比如空字符串、NULL、特殊字符、嵌套层级深的数据分别跑一遍SQL。JSON查询的边界行为真的很多别指望生产环境帮你发现问题。最后再分享一点体会复盘下来MySQL JSON模糊查询本身并不复杂复杂的是数据和场景的多样性。JSON给了我们业务上的灵活性但代价是查询的代价更高、路径更弯。如果你正在选型我建议遵循这么个优先顺序能用传统列加索引解决的需求不要用JSON不得不存JSON的查询时优先走虚拟列或函数索引至于LIKE和JSON_SEARCH这种全表扫描的写法只适合后台低频率查询别放进用户请求的主链路里。我自己现在处理JSON查询前都会先花十分钟想清楚数据会怎么变化、查询频率有多高、能不能接受全表扫描再做方案。很多时候稍微调整一下数据模型比任何SQL技巧都管用。希望这篇文章能帮你把那些JSON模糊查询的弯弯绕绕一次理清少踩几个我当年踩过的坑。