最近又有同事跑过来问我说业务里把一堆扩展属性塞进了 MySQL 的 JSON 字段现在要按里面的某个值做模糊查询怎么写都感觉不对劲要么查不出来要么慢得要命。说实话这个“JSON 字段里做模糊查询”的需求我在项目里已经踩过好几轮坑了。它看起来就是一句 LIKE但真正写起来牵扯到 JSON 提取、类型转换、索引设计、中文字符集一堆问题。尤其数据量一上来全表扫一遍能把接口拖到超时这时候才知道光是“能查出来”远远不够还得“查得快”。这篇文章我把 MySQL 里 JSON 数据模糊查询的完整套路整理一遍从最基础的取值操作符、五种常用写法到性能优化和常见坑全部用实际能跑的 SQL 说话。不管你是刚接触 JSON 字段的新手还是已经写过几条 JSON 查询但总感觉别扭的老手都能在这篇里找到能直接抄作业的方案。1. 先分清三个操作符-、- 和 JSON_EXTRACT很多人在 JSON 模糊查询上报错根子不在 LIKE 怎么写而是压根没搞明白怎么从 JSON 字段里把目标值“取”出来。这个前提不解决后面全是空中楼阁。1.1 场景准备一张带 JSON 字段的演示表我们先搞一张商品表结构很简单核心就是meta字段里存了一堆 JSON 数据这种情况在真实业务里特别常见——产品经理今天要加个颜色属性明天要加个产地属性都往 JSON 里塞省得天天改表结构。CREATE TABLE product ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(100) NOT NULL, meta JSON ) ENGINEInnoDB DEFAULT CHARSETutf8mb4; INSERT INTO product (name, meta) VALUES (手机A, JSON_OBJECT( brand, 苹果, price, 5999, tags, JSON_ARRAY(旗舰, 5G, 轻薄) )), (手机B, JSON_OBJECT( brand, 华为, price, 4999, tags, JSON_ARRAY(旗舰, 4G) )), (手机C, JSON_OBJECT( brand, 小米, price, 2999, tags, JSON_ARRAY(性价比, 5G) )), (平板A, JSON_OBJECT( brand, 苹果, price, 3999, tags, JSON_ARRAY(平板, 影音) ));这里我用了JSON_OBJECT函数来构造测试数据比手写 JSON 字符串更不容易出错类型也会被 MySQL 正确识别。实际业务中你可能是通过UPDATE product SET meta {brand:华为,price:4999}这种形式写入的效果一样。1.2 JSON_EXTRACT()最原始的取值函数JSON_EXTRACT(json_doc, path)是 MySQL 提供的原生 JSON 提取函数返回的是 JSON 类型的数据。比如说SELECT JSON_EXTRACT(meta, $.brand) FROM product WHERE id 1;结果是苹果注意这段输出带双引号因为 JSON 里的字符串值本身就是带引号存储的。这个细节非常关键很多新手在这里翻车——拿这个结果去和苹果做精确匹配永远匹配不上因为实际是被引号包裹的苹果。它的路径表达式$.brand意思是从 JSON 文档根节点开始取 key 为brand的值。要是取数组元素用$.tags[0]取嵌套对象用$.a.b这种连续点号路径。1.3 - 和 -两个更常用的简写-和-是JSON_EXTRACT的语法糖区别就在一层meta - $.brand等价于JSON_EXTRACT(meta, $.brand)返回的是带引号的 JSON 字符串。meta - $.brand等价于JSON_UNQUOTE(JSON_EXTRACT(meta, $.brand))返回的是纯字符串不带引号。实践里做模糊查询我几乎只用-因为LIKE操作符本身处理的是普通字符串拿-的结果去 LIKE 也能匹配对但逻辑上特别容易出岔子而且如果 JSON 里的值本身带特殊字符引号会干扰匹配结果。SELECT meta - $.brand AS brand_with_arrow2, meta - $.brand AS brand_with_arrow FROM product WHERE id 1;执行结果第一条是苹果第二条是苹果。肉眼看着差不多但在 SQL 里它们就是两个完全不同的值一个和你查询条件里的苹果相等一个不相等。1.4 路径表达式的几个隐蔽坑JSON 路径的语法看起来简单实际用起来有几个容易忽略的地方。第一$.开头的 key 如果包含特殊字符比如点号、空格需要用双引号包起来$.a.b。这种情况在业务字段命名不规范时很常见别以为所有 key 都能直接点号取。第二$.*代表所有子元素$.tags[*]代表数组所有元素。如果你不确定 JSON 结构先跑一条SELECT JSON_KEYS(meta) FROM product看看有哪些 key再写路径能省不少调试时间。第三路径取不到值时返回 SQL NULL而不是报错。比如meta - $.stock如果 JSON 里没有 stock 这个 key结果就是 NULL。一些人的查询条件写成WHERE meta - $.brand ! 苹果结果把 brand 为空的行也筛出来了因为 SQL 的三值逻辑里 NULL 和 苹果 比较结果是 UNKNOWN不等于判断自然不会包含它。2. 五种模糊查询写法从直觉到高效取值操作符搞清楚之后才是真正的主角模糊查询。这里我梳理了五种常见写法从最直觉的LIKE到原生的JSON_SEARCH再到适合复杂结构的JSON_TABLE每种都说说它们的适用场景和注意点。2.1 LIKE -最直接也最容易上手模糊查询的第一反应必然是 LIKE。结合我们上面的结论正确姿势是SELECT * FROM product WHERE meta - $.brand LIKE %苹果%;这条 SQL 能查出手机A和平板A因为它们的 brand 都是“苹果”。这应该算是最直观的写法了代码可读性好团队成员接手也容易看懂。但是有几个细节要注意如果你的 JSON 值是数字类型比如price-出来的是字符串和数字做比较时 MySQL 会做隐式转换这个转换不走索引数据量大时是个隐患。LIKE %关键字%这种双百分号的写法BTree 索引完全帮不上忙只能全表扫描。这在第 3 节会重点讲。如果 JSON 值本身包含引号、反斜杠这些转义字符LIKE 匹配时可能误伤建议数据写入时做好清洗。2.2 LOCATE()想要更快一点的话可以试试LOCATE(substr, str)函数返回子串在字符串中第一次出现的位置找不到时返回 0。所以模糊查询可以写成SELECT * FROM product WHERE LOCATE(苹果, meta - $.brand) 0;功能上和LIKE %苹果%基本等价但有一个微妙的差异LIKE 支持通配符如果搜索文本里包含%或_会被当通配符解析而 LOCATE 是纯字面量匹配搜索包含特殊字符的内容更安全。比如你要查品牌名里带下划线的数据LIKE %_%会把所有品牌都匹配出来因为_在这里代表任意单个字符而LOCATE(_, brand) 0只会匹配真正包含下划线的值。这个差异在搜索用户输入的关键词时非常重要。不过从性能角度讲LOCATE 和LIKE %xx%一样都是无法利用索引的全表扫描谈不上质的飞跃只是写法上更稳一点。2.3 REGEXP / REGEXP_LIKE复杂模式匹配的利器如果你的模糊查询不是简单的“包含某个词”而是需要支持正则表达式那就要上 REGEXP 了。MySQL 8.0 里推荐使用REGEXP_LIKE(expr, pat)函数老版本也可以用expr REGEXP pat。SELECT * FROM product WHERE meta - $.brand REGEXP 苹果|华为;这条 SQL 能查出 brand 为“苹果”或“华为”的商品。正则的能力在这里完全放开比如^[0-9]$匹配纯数字、苹果.*Pro匹配包含特定模式的字符串都是 LIKE 做不到的。但正则最大的问题是性能它是这三种字符串匹配里最慢的数据量一大基本告别生产环境。所以我的建议是正则只用于数据量小比如千条以内或者后台管理系统的过滤场景核心业务接口尽量别用。2.4 JSON_SEARCH()MySQL 原生的 JSON 搜索函数JSON_SEARCH()是 MySQL 5.7 开始提供的 JSON 专用搜索函数它能直接在一个 JSON 文档里搜索指定的字符串值返回匹配到的路径。语法是这样的JSON_SEARCH(json_doc, one | all, search_str[, escape_char[, path] ...])第二个参数决定返回单个匹配路径还是所有匹配路径第三个参数就是搜索字符串支持%和_通配符。SELECT * FROM product WHERE JSON_SEARCH(meta, one, %苹果%) IS NOT NULL;这条 SQL 会搜索 meta 里的所有层级的字符串值不只是 brand连 tags 数组里的内容也会被搜到。我把手机A 的 tags 里加了“轻薄”如果你在 meta 里搜索“轻薄”它也能查出来。这个特性既是优点也是缺点。优点是你不用关心 JSON 的具体结构一个函数搜全部缺点是无法指定只搜某个 key如果 JSON 里存在无关字段包含相同关键词会产生误匹配。要指定路径也可以第四个参数可以传SELECT * FROM product WHERE JSON_SEARCH(meta, one, %苹果%, NULL, $.brand) IS NOT NULL;注意这里的第五个参数是路径第四个参数是转义字符不用就填 NULL。JSON_SEARCH 在功能上是最贴合“JSON 模糊查询”本意的但同样无法走索引而且性能比LIKE-更差一些因为它需要把整个 JSON 文档解析一遍再逐层搜索适合数据量小且不确定 JSON 结构的场景。2.5 JSON_TABLE()把 JSON 变成关系表再查这套组合拳是我面对复杂 JSON 结构比如嵌套数组时的首选。JSON_TABLE是 MySQL 8.0 提供的强大函数它能把 JSON 数据转换成一张虚拟的关系表你就可以用标准的 SQL 去查询和过滤了。举个例子如果我想查 tags 数组里包含“5G”的商品SELECT DISTINCT p.* FROM product p, JSON_TABLE(p.meta, $ COLUMNS ( brand VARCHAR(50) PATH $.brand, price INT PATH $.price, tags VARCHAR(50) PATH $.tags[*] )) jt WHERE jt.tags LIKE %5G%;这里JSON_TABLE把每一行的 meta 展开成一张虚拟表tags用$.tags[*]路径把数组里的每个元素都展开成一行然后就可以用最普通的LIKE去过滤了。这种写法的优势非常明显一是过滤条件可以组合比如brand LIKE 苹% AND tags LIKE %5G%也能轻松实现二是可以配合其他表做 JOIN三是阅读起来就像在查一张普通表理解成本低。缺点就是语法略长第一次看的人可能会觉得复杂。而且JSON_TABLE在 WHERE 子句之前就会被执行如果外层表数据量大虚拟表的膨胀可能很可观要注意性能。我有一个经验JSON 数组展开后虚拟表的行数会呈几何倍数增长先用其他条件缩小范围再展开会好很多。五种写法的对比我整理成了一张表方便参考写法MySQL 版本是否走索引适用场景LIKE -5.7否%xx%简单包含匹配可读性好LOCATE()5.7否匹配特殊字符避免通配符干扰REGEXP5.7否复杂模式匹配数据量小场景JSON_SEARCH()5.7否搜索整个 JSON不关心具体结构JSON_TABLE()8.0取决于外层复杂嵌套结构需组合多个条件3. 性能优化从全表扫描到毫秒级响应功能实现后问题往往还没结束。我之前在一个订单明细表上做过一次 JSON 模糊查询那张表有 200 多万行直接LIKE %关键词%扫了快 10 秒这个时间在生产环境是完全不能接受的。所以性能优化是 JSON 模糊查询永远绕不开的话题。3.1 为什么慢JSON 解析 无法走索引的双重打击先理解慢的根本原因。第一JSON 字段在 InnoDB 中是以二进制形式存储的bson格式查询时需要对每一行做 JSON 解析这个 CPU 开销比普通字段的字符串比较要高出一个数量级。第二LIKE %关键词%这种中间匹配即使字段上建了普通 BTree 索引也用不上因为索引是按照完整值的字典序组织的索引里无法快速定位“包含某个单词”的值只能全部遍历。两个因素叠加全表扫描 逐行解析 JSON速度可想而知。3.2 生成列 普通索引最实用的一招既然无法直接对 JSON 字段建索引那就把要查询的值“抽”出来变成普通字段再对这个字段建索引。MySQL 的生成列Generated Column正好能完成这件事。生成列有两种类型VIRTUAL不占用物理存储查询时实时计算。适合计算成本低、查询频率不特别高的场景。STORED占用物理存储写入时计算好。适合查询频繁、需要建索引的场景。建一个 STORED 生成列来提取 brand 字段ALTER TABLE product ADD COLUMN brand VARCHAR(50) GENERATED ALWAYS AS (meta - $.brand) STORED; ALTER TABLE product ADD INDEX idx_brand (brand);执行完这两条 SQL 之后这张表就多了一个叫brand的普通列它的值自动从 meta 里提取。然后你查询时就能像查普通字段一样SELECT * FROM product WHERE brand LIKE 苹果%;注意这里我把%苹果%换成了苹果%这是为了走索引。BTree 索引对前缀匹配LIKE abc%是有效的对中间匹配LIKE %abc%无效。如果你既想走索引又想中间匹配有两个思路第一个思路看业务能不能改成前缀匹配查询。比如搜索“苹果手机”时拆成brand LIKE 苹果%和name LIKE 手机%两个条件用 OR 或者 UNION 拼起来。第二个思路如果中间那个关键词必须搜那就考虑 MySQL 8.0 的全文索引下面会说。先看个走索引前后的 EXPLAIN 对比。索引前type: ALL rows: 2000000 Extra: Using where索引后type: range key: idx_brand rows: 4000 Extra: Using index condition数据量 200 万索引后扫描行数从 200 万降到了几千速度从秒级降到毫秒级这个提升是实打实的。3.3 函数索引不用动表结构的懒人方案MySQL 8.0.13 开始直接支持在表达式上建索引这意味着你可以不加生成列直接对 JSON 提取表达式建索引CREATE INDEX idx_meta_brand ON product ((meta - $.brand));这条 SQL 在功能上基本等同于生成列 索引的组合但少了一步 ALTER TABLE 加列的环节对不想改表结构的场景特别友好。不过要注意函数索引的表达式必须和查询语句里的表达式完全一致MySQL 优化器才可能使用这个索引。你建索引时写的是meta - $.brand查询时也得写meta - $.brand少一个索引就废了。这个一致性要求在实际团队协作里很容易被忽略所以我还是更推荐显式生成列方案查询语句更直观也不容易踩这个坑。3.4 全文索引处理“包含任意位置关键词”的场景如果你的业务确实需要“搜索某个关键词出现在一段文本的任何位置”又对性能有要求那就得动用全文索引了。MySQL 全文索引对中文的支持在 8.0 版本里通过内置的 ngram 分词器有了很大的改善。你可以在生成列基础上建全文索引也可以直接对 JSON 提取表达式建-- 在生成列上建全文索引 ALTER TABLE product ADD FULLTEXT INDEX ft_brand (brand) WITH PARSER ngram; -- 然后使用 MATCH...AGAINST 语法 SELECT * FROM product WHERE MATCH(brand) AGAINST(苹果 IN NATURAL LANGUAGE MODE);全文索引的底层是一个倒排索引它能直接定位包含某个词的行不需要全表扫描。对于“包含词”查询它的速度比LIKE %词%快一到两个数量级。但是也有代价全文索引占用额外存储空间建索引时间也不短大数据量表上建全文索引要挑业务低峰期。ngram 分词器的分词粒度会影响结果默认 ngram_token_size 是 2意味着单字搜索查不出来最少两个字。全文索引的匹配逻辑有自己的评分规则MATCH...AGAINST的结果排序和普通查询不一样可能和你预想的不一致。3.5 多值索引针对 JSON 数组的模糊/精确查询如果业务里经常用 JSON 数组存储标签、分类这些数据比如标签为[“旗舰”, “5G”, “轻薄”]MySQL 8.0.17 开始支持的多值索引Multi-Valued Index就是为你量身定做的。它的原理是把一个 JSON 数组里的每个元素都作为索引的“值”存起来这样查某个元素是否存在就能直接命中索引。CREATE INDEX idx_meta_tags ON product ((CAST(meta - $.tags AS CHAR(50) ARRAY)));查询时配合MEMBER OF操作符SELECT * FROM product WHERE 5G MEMBER OF (meta - $.tags);或者用JSON_CONTAINSSELECT * FROM product WHERE JSON_CONTAINS(meta, 5G, $.tags);多值索引对这种“数组里是否包含某元素”的查询优化效果非常明显EXPLAIN 里能看到它从全表扫描变成了 range/ref 类型。但如果你的需求是“数组里包含某个子串”比如搜“5”能匹配出“5G”和“5G版”MEMBER OF 和 JSON_CONTAINS 都做不到这俩都是等值匹配子串匹配还是回到前面说的生成列 LIKE 方案。3.6 一条完整的优化流程实录为了让你更直观地看到整套流程我按实际生产的操作顺序走一遍。假设线上已经出现了慢查询问题 SQL 是SELECT * FROM orders WHERE meta - $.status LIKE %已发货%;第一步先确认慢在哪。用 EXPLAIN 看执行计划正常情况下会看到type: ALLrows 很大Extra 里有Using where这就说明是全表扫描了。第二步决定加生成列。因为 status 字段是关键的查询条件而且经常要配合其他条件筛选我选择 STORED 生成列ALTER TABLE orders ADD COLUMN status VARCHAR(20) GENERATED ALWAYS AS (meta - $.status) STORED;第三步建索引。如果业务大多数场景是“查某个固定状态”前缀匹配就够了建普通 BTree 索引如果确实需要任意位置匹配考虑全文索引。我这边经过沟通发现其实都是查“已发货”这种前缀场景所以ALTER TABLE orders ADD INDEX idx_status (status);第四步改写业务 SQL 并验证SELECT * FROM orders WHERE status LIKE 已发货%;再 EXPLAIN能看到type: ref或者rangerows 从几百万降到几千慢查询日志里这条 SQL 彻底消失。如果历史数据已经有几百万行ALTER TABLE 加 STORED 生成列会锁表重建建议用 pt-online-schema-change 这个工具或者选择 VIRTUAL 生成列避免物理存储开销。VIRTUAL 列也可以建索引只是每次查询要计算一次提取值对复杂 JSON 提取表达式性能影响明显。我的原则是查询条件简单就 VIRTUAL复杂就 STORED。4. 高频问题与排查技巧JSON 模糊查询的坑密度之高在 SQL 开发里算很突出的。这一节我把这几年来的高频问题和排查思路整理成清单你可以当成一张速查表用。4.1 查出来是空结果先检查引号和 NULL最常见的“为啥查不到”场景多半是下面两种之一。第一种用了-而不是-查询条件里写的是LIKE %苹果%这当然匹配不到你心里的“苹果”。排查方法是先跑一条不带条件的 SELECT直接用SELECT meta - $.brand看看返回值的真实样子是带引号还是不带引号。第二种JSON 里根本没有这个 key返回 NULL。NULL 和空字符串、字符串null都是不同的值。建议查询时带上WHERE meta - $.brand IS NOT NULL先确认数据存在再谈匹配。4.2 JSON null 和 SQL NULL两个不同的东西JSON 里的null没有引号和 SQL 的 NULL 是两码事。JSON 解析后一个值为 JSON null 的字段用IS NULL判断是查不出来的它是 false。-- 假设 meta {brand: null} SELECT meta - $.brand null AS is_json_null_string, meta - $.brand IS NULL AS is_sql_null;第一条结果可能是 1取决于 JSON 里写的是null还是null第二条结果一定是 0。这个细节很容易坑到写判断语句的人。想判断 JSON 里是否存在某 key正确的姿势是用JSON_CONTAINS_PATH(meta, one, $.brand)。4.3 中文查不出来检查字符集和排序规则很多人在本地开发环境查中文 JSON 数据一切正常上了生产就查不出来大概率是生产库的字符集设置问题。MySQL 8.0 默认utf8mb4能存能查中文但老库可能还是utf8mb3对某些特殊字符emoji、生僻字就不够用了。建表时明确指定CREATE TABLE product ( ... ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_unicode_ci;utf8mb4_unicode_ci对中文的排序和比较更符合直觉。另外注意如果你用了 LIKE 查询排序规则会影响匹配行为比如大小写是否敏感_ci结尾的是大小写不敏感。4.4 JSON_TABLE 展开后数据翻倍记得先去重JSON_TABLE 把数组展开后一行会变多行再用 JOIN 或者其他操作时很容易产生数据膨胀。一个商品有 3 个标签展开后就是 3 行如果你 JOIN 了一张订单表结果可能变成 3 倍的订单记录。解决方法是两层嵌套查询先展开过滤再去重最后再 JOINSELECT DISTINCT p.* FROM product p JOIN JSON_TABLE(p.meta, $ COLUMNS ( tag VARCHAR(50) PATH $.tags[*] )) jt WHERE jt.tag 5G;很多人在这一步忘记 DISTINCT然后就会对查询结果的条数产生怀疑。4.5 隐式类型转换数字和字符串的错位JSON 里存了数字5999但-提取出来的是字符串5999。当你拿它和数字5999比较时MySQL 会尝试把字符串转为数字再比较这个过程中的索引失效问题在4.2.2 生成列方案里格外突出。假设生成列定义为price_display VARCHAR(20) GENERATED ALWAYS AS (meta - $.price) STORED然后你在查询里写WHERE price_display 5999即使 price_display 上有索引MySQL 也会对索引列做函数转换索引照样失效。正确做法是把生成列定义成数字类型price_display INT GENERATED ALWAYS AS (meta - $.price) STORED这就是为什么生成列定义时要尽量贴合实际的数据类型而不是统一用 VARCHAR 一把梭。4.6 问题速查表现象可能原因解决方案查询结果为空SQL 不报错-返回带引号值导致匹配不上改用-查询结果为空JSON 里没有目标 key返回 NULL用JSON_CONTAINS_PATH先判断LIKE 匹配出意外结果搜索词含 % 或 _ 被当通配符用LOCATE或转义数据量一大就慢LIKE %xx%不走索引生成列 前缀匹配 / 全文索引生成列建了索引还是慢隐式类型转换导致索引失效生成列类型改为目标字段的原始类型JSON_TABLE 结果翻倍数组展开后未去重加 DISTINCT中文搜不到字符集或排序规则问题统一 utf8mb4JSON null 判断异常把 JSON null 当 SQL NULL用JSON_TYPE判断4.7 排查套路三步定位问题最后分享一个排查 JSON 查询问题的通用思路。第一步先拆。把复杂的 JSON 查询拆成“提取值”和“匹配条件”两步单独跑提取值这一步确认取到的值长什么样。这一步能解决一半以上的问题因为大多数坑都出在取值的类型和格式上。第二步用小数据量试。插入一条人工构造的、已知结果的测试数据拿它能测出你的查询逻辑是否自洽。第三步上 EXPLAIN。它的key字段如果显示NULL说明索引没用上去检查表达式一致性、隐式转换和%位置。这一步能解决剩下的性能问题。我在实际处理中超过八成的问题在前两步就能定位真正需要深挖索引优化的场景反而没有想象中多。5. 写在后面关于 JSON 字段的使用边界把 JSON 模糊查询这件事聊透之后我反而想说点“反 JSON”的话。JSON 字段确实是 MySQL 5.7 以来的好功能它让“半结构化数据”不再需要单独建一张扩展属性表写起来方便读起来也直观。但我见过太多项目因为“方便”就把理应是普通字段的数据全塞进 JSON结果业务增长后查询越来越慢代码里到处是 JSON_EXTRACT 和 - 的组合维护成本直线上升。我个人的经验是如果一个属性需要参与 WHERE 过滤、JOIN 关联或者排序那它就是“结构化数据”该建独立字段就建独立字段别懒。JSON 字段真正适合的是三类场景一是确实变化频繁的扩展属性一周加一个 key 都不嫌多二是低频读取的完整配置快照三是归档数据里的原始信息备份不承担查询职责。如果 JSON 字段已经成了核心查询条件那用生成列 索引把它“结构化”回来是最务实的补救方案。这也恰好是本文花了大篇幅讲生成列的原因——它不是让你把 JSON 用得更花哨而是让你在享受 JSON 灵活性的同时不把数据库性能拖垮。希望这套方法对你能有帮助。如果你在项目里还遇见过其他 JSON 查询的怪问题欢迎在评论区聊聊我也在学习这些边缘场景的处理经验。
