平时写业务 SQL最绕不开的条件就是模糊查询。搜索框里输入关键字、按订单号前缀筛选、根据手机号中间几位捞数据这些场景全靠LIKE撑起来。很多初学者一开始就是WHERE name LIKE %关键字%一把梭查出来没问题就完事结果数据量一上去慢查询日志刷屏才发现这个最基础的查询其实藏着不少门道。这偏文章我就把LIKE查询掰开揉碎了讲清楚包括匹配规则、索引利用、转义陷阱、动态拼接防注入、大数据量下的替代方案以及我这些年实际踩过的坑。不管你是刚接触 MySQL 的新手还是写了好几年业务的开发只要你还在用LIKE这篇内容都值得仔细看看。1. 先从最常用的 LIKE 用法说起LIKE本身不是函数它是 SQL 里的一个条件运算符专门用来做字符串的模式匹配。理解它的核心在于两个通配符百分号%和下划线_。这两个符号组合起来能覆盖几乎所有的模糊匹配需求。1.1 基本语法与匹配规则看语法其实特别简单SELECT * FROM table_name WHERE column_name LIKE pattern;这里的pattern就是匹配模式%代表任意数量的字符包括零个字符_代表恰好一个字符。我用一个例子演示就清楚了。假设有一张用户表里面有这些名字张三、张三丰、张无忌、李四、张小三。-- 以张开头的所有记录匹配到张三、张三丰、张无忌、张小三 SELECT * FROM user WHERE name LIKE 张%; -- 以三结尾的所有记录匹配到张三、张小三 SELECT * FROM user WHERE name LIKE %三; -- 名字里包含三的所有记录匹配到张三、张三丰、张小三 SELECT * FROM user WHERE name LIKE %三%; -- 名字恰好是张加一个字符匹配到张三不匹配张三丰、张无忌 SELECT * FROM user WHERE name LIKE 张_; -- 名字恰好是张加两个字符匹配到张三丰、张无忌 SELECT * FROM user WHERE name LIKE 张__;这里有一个新手特别容易忽略的点%匹配零个或多个字符。所以LIKE 张%也能匹配到只有一个字的名字张如果表里有的话。而_是必须占一个字符位的张_无法匹配单独的张。另外要注意LIKE是作用于整个字符串的模式匹配不是那种整体相等判断。WHERE name 张和WHERE name LIKE 张结果一样但LIKE 张这种写法毫无意义既然要完全匹配不如直接用效率还更高。1.2 实际业务里的典型场景LIKE最常见的应用就是搜索框。电商后台按商品名搜索、用户管理按姓名搜索、订单系统按订单号搜索基本上都长这样SELECT order_id, user_name, amount, status FROM orders WHERE order_no LIKE CONCAT(%, #{keyword}, %) ORDER BY create_time DESC LIMIT 20;还有一类场景是字典值或编码的前缀匹配。比如按地区编码筛选某个省下面的所有城市编码规则是省代码(2位) 市代码(2位) 区代码(2位)那查某个省所有城市就是SELECT * FROM region WHERE code LIKE 44%;这种写法在配置类数据里特别常见因为它利用了编码本身的层级规律比逐条查 IN 列表省事得多。还有一个很有意思的场景是手机号脱敏后的模糊搜索。运营同事经常拿着138****1234这种半截号码来捞用户因为完整手机号涉及隐私不能全量展示SELECT user_id, phone, nickname FROM user WHERE phone LIKE 138%1234;这种查询本质上是一种折中方案但要注意它一定走不了索引后文我会详细说为什么以及怎么优化。2. LIKE 的执行计划与性能瓶颈很多开发写模糊查询能跑通就满足了直到线上出现慢查询才回头分析。搞清楚LIKE为什么慢、什么时候快什么时候慢这是进阶的必经之路。2.1 为什么 LIKE 查询会慢直接给结论前面带%的模糊查询几乎百分百走不了索引只能全表扫描。原因要从 B 树索引的结构说起。MySQL 的索引底层是 B 树数据按照索引列的值有序排列。查询条件LIKE abc%时MySQL 可以定位到abc这个前缀所在的叶子节点位置然后顺序扫描从这个节点开始往后的一段区间这就是索引的范围扫描效率很高。但一旦模式变成%abc%或者%abc查询条件就不知道从哪个节点开始扫描了。因为以%开头意味着目标字符串可能在任意位置出现B 树的有序性完全帮不上忙优化器只能选择全表扫描逐行匹配那性能就只能靠数据量说话。我举个直观的数字例子。一张 500 万行的订单表如果走主键索引查单条记录响应时间在毫秒级。但同样的表执行WHERE order_no LIKE %20250315%全表扫一遍可能要 3 到 8 秒具体取决于行宽度和服务器磁盘性能。这个差距就是索引失效的代价。EXPLAIN看得更清楚EXPLAIN SELECT * FROM orders WHERE order_no LIKE 2025%; -- type: range, key: idx_order_no走了索引 EXPLAIN SELECT * FROM orders WHERE order_no LIKE %2025%; -- type: ALL, key: NULL全表扫描type从range变成ALLkey从索引变成NULL慢就慢在这里。2.2 LIKE 与索引的正确配合姿势既然前导通配符会导致索引失效那想让LIKE走索引唯一的办法就是让模式以确定前缀开头。但业务需求经常是%关键字%这种包含查询根本没法固定前缀怎么办第一个思路是覆盖索引 只查必要列。比如订单表经常要按订单号模糊搜但只需要返回订单号和状态那就建一个联合索引(order_no, status)。虽然%关键字%依然走不了索引定位但 MySQL 可以在二级索引的叶子节点上完成扫描不需要回表效率比全表扫描高一个量级。第二个思路是后缀匹配的变通。如果业务场景比较多的是查某个结尾比如按身份证号后几位找人可以额外冗余一列id_card_reverse存反转后的字符串然后查询LIKE CONCAT(REVERSE(后几位), %)让模糊搜索重新变成前缀匹配。这种做法在特定业务里非常有效付出的代价是多维护一列。第三个思路更粗暴但也更通用全文索引或外部搜索引擎这个我会在后面第 5 节展开。总而言之LIKE在百万行以下的数据量里随便用问题不大一旦上了千万行性能敏感的场景就要认真思考替代方案了。3. 转义、大小写、字符集等易踩的坑模糊查询除了性能还有一批隐蔽的逻辑坑。很多线上 bug 查到最后发现问题出在LIKE的转义和排序规则上。3.1 特殊字符转义% _ \%和_是通配符但当用户搜索的关键字里本身就包含这两个字符时问题就来了。假设业务里有商品的折扣叫5%折扣执行SELECT * FROM product WHERE name LIKE %5%%;预期是找出包含5%的商品结果会把所有包含数字5的商品全捞出来。因为%5%%被解析成了任意前缀 5 任意字符 任意后缀这里的第二个%被当成通配符了。解决方案是用ESCAPE关键字指定转义字符SELECT * FROM product WHERE name LIKE %5\%% ESCAPE \\;这个写法指定了\为转义符\%就代表字面量的百分号。更常见的用法是#做转义符避免在 Java 或其它语言里还要额外处理\SELECT * FROM product WHERE name LIKE %5#%% ESCAPE #;同理下划线_也需要转义。搜索用户名的场景用户输入下划线时LIKE %_%会把所有至少含一个任意字符的记录全部匹配出来逻辑直接炸裂。处理方式和%一样SELECT * FROM user WHERE nickname LIKE %\_% ESCAPE \\;如果你在代码里是用字符串拼接的方式去构造 SQL还要额外注意转义符本身在编程语言里的转义比如 Java 字符串里写\\才能真正传一个\给 MySQL。这块弄错的话SQL 层的转义设置就完全失效了。3.2 大小写与排序规则MySQL 的字符串比较行为由**排序规则collation**决定。使用最广泛的utf8mb4_general_ci和utf8mb4_unicode_ci都是大小写不敏感的_ci就是 case-insensitive 的意思所以在默认配置下SELECT * FROM user WHERE name LIKE zhang%; -- 同样会匹配 Zhang、ZHANG、zhang这在大部分业务里反而符合预期但如果你做的是账号密码相关的匹配、或要求严格区分大小写的编号匹配就需要手动指定排序规则SELECT * FROM user WHERE name LIKE Zhang% COLLATE utf8mb4_bin;不过说实话绝大多数场景我建议不要依赖排序规则来实现大小写敏感因为一旦字段本身定义了_ci排序规则你在条件里写COLLATE会让索引再次失效。更稳妥的做法是需要大小写敏感的字段直接在表定义时就用utf8mb4_bin或者在业务代码里统一处理好大小写再查询。还有一个容易忽略的点排序规则也影响模糊匹配的行为。同样是LIKE a%在_ci下匹配到A开头在_bin下就匹配不到。3.3 NULL 与空字符串的模糊查询陷阱NULL和空字符串在LIKE面前是两码事。执行WHERE email LIKE %%不会匹配到email IS NULL的行即使你以为模糊应该扫到一切。同样地LIKE 也不会匹配 NULL。实际开发里我见过不止一次因为这种语义踩坑的场景。比如统计有多少用户填了邮箱用WHERE email LIKE %%去统计结果漏掉了某些邮件服务商后缀情况或者把填了NULL和填了空串的混在一起处理。更隐蔽的坑在NOT LIKE上。如果写WHERE email NOT LIKE %%那么email IS NULL的行也会被排除。因为NULL参与比较运算时结果不是TRUE而是NULLWHERE条件只保留结果为TRUE的行NOT LIKE作用于NULL还是NULL行照样被过滤掉。如果你确实想统计没有有效邮箱的用户需要显式加条件SELECT * FROM user WHERE email IS NULL OR email NOT LIKE %%;类似的在COUNT聚合里也要小心NULL的隐形影响不要想当然认为NOT LIKE的互补集合就是把LIKE的结果取反。4. 复杂模糊查询场景的实战写法前面讲的是单个LIKE的基础知识和注意事项真实业务的复杂程度要高得多。搜索框往往涉及多字段、多条件还要处理分页排序和防注入。4.1 多字段模糊查询的几种写法最常见的需求是输入一个关键字同时匹配名称、编码、手机号等多个字段。新手喜欢用多个OR拼SELECT * FROM user WHERE name LIKE %keyword% OR phone LIKE %keyword% OR email LIKE %keyword%;这种写法功能没问题但一旦其中一个字段是NULL这一行就会被整体排除。更麻烦的是如果条件拼得不好OR和AND的优先级会导致结果集合和预期完全不一致。另一种常见需求是关键字可能包含空格需要拆成多个词分别匹配。比如搜索张 三期望匹配到姓张名三的人。可以先把关键字按空格拆分成多个 token然后动态拼ANDSELECT * FROM user WHERE (name LIKE %张% OR phone LIKE %张% OR email LIKE %张%) AND (name LIKE %三% OR phone LIKE %三% OR email LIKE %三%);这样做的问题是 SQL 会越来越长而且多个OR并列会严重拖慢性能。数据量不大的时候无所谓量大了建议用CONCAT把多个字段合并成一个虚拟字段再匹配SELECT * FROM user WHERE CONCAT(IFNULL(name, ), IFNULL(phone, ), IFNULL(email, )) LIKE %keyword%;这种写法牺牲了索引潜力但逻辑清晰尤其适合后台管理系统的低频搜索场景几百毫秒的查询时间完全能接受。4.2 动态拼接 LIKE 条件的防注入实践业务系统里搜索关键字几乎都是从请求参数里拿来的。直接用字符串拼进 SQL 简直是给黑客送人头// 错误示例直接把参数拼进SQL String sql SELECT * FROM user WHERE name LIKE % keyword %;一旦用户输入% OR 11SQL 就变成了SELECT * FROM user WHERE name LIKE %% OR 11%;整张表直接裸奔。正确的做法是使用PreparedStatement 参数化查询String sql SELECT * FROM user WHERE name LIKE CONCAT(%, ?, %); PreparedStatement ps conn.prepareStatement(sql); ps.setString(1, keyword);或者使用 MyBatis 的#{}占位符select idsearchUser resultTypeUser SELECT * FROM user WHERE name LIKE CONCAT(%, #{keyword}, %) /select这里特意用CONCAT(%, #{keyword}, %)而不是%${keyword}%。因为${}是直接字符串替换同样存在注入风险而#{}是预编译参数关键字里的%和_会被当作普通字符处理不会影响匹配语义——除非你确实想让用户输入通配符那就要回到第 3 节说的ESCAPE转义方案了。4.3 模糊查询 分页 排序的常见问题模糊查询结果集往往很大分页是标配。最常用的LIMIT offset, size写法在小数据量没问题但偏移量大了以后性能急剧下降。比如LIMIT 100000, 20MySQL 要扫描前 100020 行再丢弃前 10 万行全是无效功。优化思路有两种先用主键索引定位起始位置再取数据SELECT * FROM user WHERE name LIKE %keyword% AND id (上一页最后一条记录的id) ORDER BY id LIMIT 20;这种游标分页或叫 keyset pagination不依赖偏移量翻页再深也稳。不过这要求排序字段和游标字段一致且唯一通常用主键id最合适。还有一点模糊查询结合ORDER BY时尽量让排序放到有索引的字段上。如果排序字段也没索引排序就会为全表扫描增加filesort的开销慢上加慢。5. 大数据量下 LIKE 的替代方案数据量到了一定程度LIKE再怎么优化也顶不住。这时候就要考虑换赛道了。5.1 MySQL 全文索引FULLTEXTMySQL 自带的全文索引主要用于大文本的快速检索和LIKE最大的区别在于全文索引不是基于字符串匹配的而是分词 倒排索引。在英文场景默认分词器按空格和标点切词中文场景则要依赖ngram分词器把一句话切成连续的 N 个字组合。创建全文索引很简单ALTER TABLE article ADD FULLTEXT INDEX ft_title_content (title, content) WITH PARSER ngram;查询用MATCH ... AGAINSTSELECT id, title FROM article WHERE MATCH(title, content) AGAINST (分布式缓存 IN NATURAL LANGUAGE MODE);全文索引对大数据量的文本检索性能远好于LIKE %关键词%尤其是命中词频很高、结果集很大的搜索业务。它支持相关性排序是搜索框场景的天然选择。但全文索引也有明显局限对短字符串比如手机号、订单号没意义对业务规则性很强的代码前缀匹配无效分词器对特殊字符和数字的处理需要调优。所以它适合的是文本搜索不是模式匹配。5.2 什么时候该上外部搜索引擎当数据达到千万甚至亿级全文索引也不够用了常见的选择是 ElasticsearchES。ES 基于 Lucene 倒排索引擅长复杂全文检索、聚合分析、多条件组合查询分布式架构天然扩展是很多互联网公司做搜索功能的标准方案。不过引入 ES 意味着系统复杂度大幅上升数据同步、分词配置、索引生命周期管理、集群运维每一个都是成本。我的建议是有一条明显的分界线数据量在几百万行以内LIKE 合理索引能扛住别折腾数据量到千万级但查询模式固定比如就是订单号前缀或者单字段包含可以先试覆盖索引和缓存优化查询模式多变、需要相关性排序、多字段联合搜索或日均搜索量高到 MySQL 磁盘 IO 吃不消果断上 ES 或其它搜索引擎。还有一条更轻量的路如果只是后台管理页面低频搜索数据量大但是查询频率低可以用只读从库来分摊主库压力从库上随便 LIKE 也不影响线上主业务。6. 常见问题与排查技巧实录最后这部分聊一点实战中容易遇到的现象和排查方法都是自己趟过的坑拿出来分享希望能帮读者少走弯路。6.1 线上 LIKE 慢查询的排查思路慢查询日志里看到带%...%的 SQL第一反应别急着改成 ES先按这个顺序排查第一步EXPLAIN看执行计划。重点看type是不是ALL、rows估算扫描了多少行、key用的是哪个索引。EXPLAIN SELECT * FROM orders WHERE user_name LIKE %张% AND status 1;如果rows有几十万上百万走全表扫描那慢是必然的。第二步看能不能改造成前缀匹配。业务上搜索用户用户输入张你完全可以name LIKE 张%而不是%张%——如果产品允许的话。很多搜索需求其实用前缀匹配就能满足是开发想当然用了双百分号。第三步检查返回列。SELECT *在模糊查询里尤其致命回表代价巨大。改成只查需要的列或者建覆盖索引效果立竿见影。第四步实在优化不了评估查询频率和数据量决定要不要上全文索引或外部搜索引擎。别一上来就上重武器。6.2 几个容易忽略的实战细节数值类型字段别直接用 LIKE。WHERE order_id LIKE %2025%不会报错但 MySQL 会做隐式类型转换导致索引失效甚至结果集异常。数值字段要先确认业务语义比如本身就是编码型但建表时误用了 int那建议改字段类型或者查询时转字符串但接受全表扫描的代价。用户输入的通配符要先处理。做后台搜索时用户可能真的会输入%或_。如果你不加处理搜索结果会意外地匹配到大量无关数据。我的习惯是先把用户输入里的%、_、\转义掉再拼LIKE模式除非产品明确告知用户支持通配符。LIKE 与 IN 的混用要小心中文逗号。有些业务会在一个字段里存多个值用逗号分隔虽然我强烈建议拆表但老系统里确实不少。模糊查这种字段时注意用户输入的中文逗号和英文逗号不一致导致匹配不到。编码统一问题。如果表和连接字符集不一致比如表是utf8连接是gbk模糊查询中文时会出现乱码或者匹配不上的诡异现象。排查时先看SHOW VARIABLES LIKE character_set_connection和表定义是否一致。最后一条LIKE和REGEXP别混着用。MySQL 也有REGEXP支持正则表达式功能比LIKE强太多但性能更差而且对索引毫无建树。除非遇到LIKE实在表达不了的模式否则别去碰。写 SQL 这么多年我的体会是越基础的语法越容易被低估。LIKE看起来简单真要优雅高效地用好牵扯到索引原理、排序规则、转义、架构选型一堆知识。每次在代码评审里看到有人把LIKE写成三四个%大串联我都会多问一句这个查询频率高不高、数据量多大。大多数时候这一问就能问出一段本可以避免的慢查询。