MySQL通配符与LIKE查询：从基础语法到性能优化实战

1. 通配符到底是什么先搞清楚这两个符号1.1 为什么需要通配符做开发这些年我接到过不少类似的需求查一下所有姓张的用户找出所有邮箱是QQ的订单统计所有手机号以139开头的人。如果你的第一反应是写一堆WHERE name 张三这种精确匹配那遇到这种模糊条件就会非常痛苦。因为你根本不知道用户到底叫张三还是张四也不可能把每个可能的值都穷举出来。这时候就需要模糊匹配而实现模糊匹配最基础的工具就是通配符。MySQL里的通配符主要指%和_两个配合LIKE关键字一起使用。理解这两者的区别基本就掌握了MySQL模糊查询的核心。我把通配符理解成占位符或者填空规则。%代表这里可以有任意多个字符包括零个_代表这里必须有且只有一个字符。1.2 % 和 _ 的底层逻辑先看一段最简单的示例。假设我们有一张用户表users里面存了一些姓名数据idnamephone1张三138123400012张三丰139123400023张伟137123400034李四158123400045王张13612340005执行下面两条SQL结果完全不同SELECT * FROM users WHERE name LIKE 张%; -- 结果张三、张三丰、张伟 SELECT * FROM users WHERE name LIKE 张_; -- 结果张三、张伟第一条查到的是所有以张开头的名字后面不管还有几个字都能匹配上第二条只查到张后面有且只有一个字的名字张三丰因为张后有两个字就不符合_的规则。生活化的类比%就像你去百度搜索时输入的关键词百度会返回包含这个词的所有页面前后可能有各种内容_则更像是填字游戏里一个确定的格子只能填一个字。这两个符号还可以组合使用。_张%匹配第二个字是张的所有字符串比如王张、一张纸%张_匹配倒数第二个字是张的字符串比如张伟就不符合因为张是第一个字伟是最后一个字%张_要求张前面可以有任意内容但张后面必须还有一个字。记住一个核心规则%能匹配零个字符_必须匹配一个字符。这个区别在实际工作中经常导致数据查不到或者多查出来。2. LIKE语句的完整用法与实战细节2.1 四种基本匹配形态LIKE配合通配符可以组合出几种最常见的查询场景。我来逐一拆解这些在实际工作中用到频率非常高后缀匹配查询以指定内容结尾的数据SELECT * FROM users WHERE email LIKE %qq.com;这个查询会找出所有QQ邮箱。注意%放在前面表示qq.com前面可以是任意内容。前缀匹配查询以指定内容开头的数据SELECT * FROM users WHERE phone LIKE 139%;找出所有139号段的手机号。%放在最后表示139后面可以是任意数字。包含匹配查询包含指定内容的数据SELECT * FROM users WHERE name LIKE %张%;找出所有名字中带张的用户不管张在名字的哪个位置。精确位数匹配查询固定长度的数据SELECT * FROM users WHERE phone LIKE 138________; -- 8个下划线这是一条比较妙但容易踩坑的用法。手机号是11位138后面正好还有8位数字用8个_加起来正好是11位。这种写法可以匹配所有以138开头的11位手机号从效果上看和LIKE 138%一致但逻辑完全不同——前者对长度有强约束后者对长度没有约束。如果真的想校验位数需要用CHAR_LENGTH(phone) 11配合使用单独靠_去数位数很容易数错。2.2 通配符与ESCAPE转义特殊字符查询怎么破这是很多新手栽跟头的地方。如果你想在数据库里查一个真的含有%或_字符的数据比如产品名称是折扣50%直接写LIKE %50%%MySQL会怎么理解它会把第一个和最后一个%当通配符中间的%也是通配符结果就是匹配所有包含50且50后面可以有任意内容的数据根本不是你想要的。解决办法是使用ESCAPE关键字指定转义符。MySQL默认用反斜杠\作为转义符但也可以显式指定其他字符-- 查询包含50%的产品默认反斜杠转义 SELECT * FROM products WHERE name LIKE %50\%%; -- 也可以用ESCAPE显式指定转义符 SELECT * FROM products WHERE name LIKE %50!%% ESCAPE !;第二条SQL的关键在于ESCAPE !它告诉MySQL!后面的字符不再当作通配符处理。这里的第一个%和最后一个%依然是通配符中间的!%才表示一个字面量的%。我一直建议团队里统一用ESCAPE显式指定转义符而不是依赖默认的反斜杠。原因很现实在不同系统、不同客户端里反斜杠本身还牵扯到字符串解析容易出幺蛾子。显式定义一个符号逻辑最清晰。2.3 大小写、排序规则与通配符匹配的坑MySQL的通配符匹配是否区分大小写完全取决于表的排序规则collation。这是我在实际工作中排查过的一个经典问题。在MySQL 8.0中默认排序规则是utf8mb4_0900_ai_ci这个排序规则不区分大小写。也就是说SELECT * FROM users WHERE name LIKE abc%;会把ABC、Abc、aBc都查出来。如果你的业务要求区分大小写需要在建表时指定utf8mb4_bin排序规则或者在建字段级别指定CREATE TABLE users ( name VARCHAR(50) COLLATE utf8mb4_bin );utf8mb4_bin会按二进制比较abc和ABC就是完全不同的两个字符串。还有一个容易忽略的细节如果字段本身有COLLATE指定但查询条件里用了另一个排序规则MySQL会报Illegal mix of collations的错误。这个时候就需要在查询里显式加COLLATESELECT * FROM users WHERE name LIKE ABC% COLLATE utf8mb4_bin;实际项目中这个错误不算高频但一旦遇到不熟悉的人容易被错误信息吓住。其实原理很简单MySQL要求比较的双方排序规则必须兼容。2.4 通配符与大小写、空格、引号的协同问题处理用户输入时查询条件里常常带着首尾空格。我见过不少同事直接写SELECT * FROM users WHERE name LIKE CONCAT(%, 张三 , %);这样大概率什么都查不到。因为LIKE不会自动去掉字符串两边的空格空格会被当作匹配内容的一部分。稳妥的做法是先用TRIM处理输入再拼接到LIKE条件里SELECT * FROM users WHERE name LIKE CONCAT(%, TRIM( 张三 ), %);还有一个关于引号的问题。在SQL里字符串用单引号包裹。如果搜索内容本身就包含单引号比如一个人名是 OBrien直接拼接会有问题-- 错误的写法 SELECT * FROM users WHERE name LIKE %OBrien%; -- 正确的写法单引号翻倍转义 SELECT * FROM users WHERE name LIKE %OBrien%;这个细节在拼接动态SQL时尤其重要处理不当不仅查不到数据还可能导致SQL语法错误。3. 通配符的性能陷阱为什么你的查询越来越慢3.1 前缀通配符与索引失效的真相做MySQL性能调优时我几乎每次都会强调一个观点通配符不是不能用但要用得聪明。最经典的问题就是%放在开头导致索引失效。MySQL的B树索引是按顺序排列的。你可以把索引想象成一本按拼音排序的字典。你想查找所有以张开头的词直接翻到张那一页就行这就是前缀匹配可以走索引的原因对应LIKE 张%。但如果你要查所有包含张的词比如LIKE %张%字典按拼音排序的优势就完全用不上了。你只能把整本字典从头到尾翻一遍看看每一页有没有张字。在MySQL里这就是全表扫描。用EXPLAIN看最直观EXPLAIN SELECT * FROM users WHERE name LIKE 张%; -- type: range说明使用了索引范围扫描 EXPLAIN SELECT * FROM users WHERE name LIKE %张%; -- type: ALL说明是全表扫描如果表里有几百万条数据%张%这种查询的耗时可能会从前缀匹配的几十毫秒暴涨到几秒甚至几十秒。3.2 通配符查询的量级评估与调优思路什么时候该关注通配符性能我个人的经验是当单表数据量超过100万行且模糊查询是核心业务路径时必须认真对待。有几个调优方向可以组合使用第一尽可能使用前缀匹配。如果业务允许把%关键词%改成关键词%。比如搜索用户时优先按用户名开头匹配而不是包含匹配。第二避免多个前导通配符的组合。LIKE %张%三%这种写法比LIKE %张三%慢得多因为需要匹配的位置更多。第三用覆盖索引减少回表。如果查询只需要返回name和id可以在name上建索引并且只查这两个字段。这样即使LIKE 张%走了索引也无需回表读取整行数据性能会好很多。第四考虑全文索引FULLTEXT。真正需要做包含匹配的场景比如搜索文章内容、商品描述MySQL内置的全文索引比LIKE %关键词%快得多。全文索引是倒排索引的实现专门为文本里包含某个词这种查询设计的。MySQL 8.0支持中文全文索引用ngram分词器ALTER TABLE articles ADD FULLTEXT INDEX ft_title_content (title, content) WITH PARSER ngram; SELECT * FROM articles WHERE MATCH(title, content) AGAINST(数据库 IN NATURAL LANGUAGE MODE);不过全文索引也有自己的限制比如对短词、停用词的处理以及对分词器的依赖。它适合的是全文搜索场景不适合用户名模糊匹配这种短字段场景。3.3 当通配符不够用REGEXP正则的补充通配符的匹配能力其实很有限它只能表达有任意字符有固定一个字符表达不了更多复杂的规则。如果你要匹配 手机号是以138或139开头 这种多选一的条件通配符写起来很别扭SELECT * FROM users WHERE phone LIKE 138% OR phone LIKE 139%;用正则表达式就简洁得多SELECT * FROM users WHERE phone REGEXP ^13[89];MySQL的REGEXP支持完整的正则语法。字符类[89]、量词{m,n}、分组()都能用。相比LIKEREGEXP表达力强了不止一个量级。但要注意两个问题。第一REGEXP通常是无法使用索引的性能比LIKE 前缀%差适合数据量不大或者低频查询的场景。第二正则表达式的写法一定要提前测试我见过不少人把^\d{11}$写错成\d{11}结果匹配出一堆乱七八糟的数据。如果需要做一个既能表达复杂规则又能兼顾性能的方案实际项目中更常见的做法是用LIKE 前缀%缩小范围走索引再用REGEXP做精确过滤。两个条件用AND连接MySQL优化器通常会先执行能走索引的条件。4. 通配符实战场景从简单到复杂的完整案例4.1 用户搜索场景以一个典型的后台用户管理页面为例搜索框需要支持按姓名模糊搜索。Java或PHP后端代码里通常这样拼接SQLSELECT id, name, phone, email, created_at FROM users WHERE name LIKE CONCAT(%, ?, %) ORDER BY created_at DESC LIMIT 20;参数化查询配合?占位符可以避免SQL注入。这里的LIKE CONCAT(%, ?, %)是一种规范的写法比提前在业务代码里拼好% keyword %更安全。但我在实际优化中会建议改成这样SELECT id, name, phone, email, created_at FROM users WHERE name LIKE CONCAT(?, %) ORDER BY created_at DESC LIMIT 20;前提是产品可以接受只按姓名开头搜索。如果产品经理坚持要做包含搜索也可以加一个折中方案优先返回前缀匹配的结果再返回包含匹配的结果用ORDER BY控制顺序SELECT id, name, phone, email FROM users WHERE name LIKE CONCAT(%, ?, %) ORDER BY CASE WHEN name LIKE CONCAT(?, %) THEN 0 ELSE 1 END, created_at DESC LIMIT 20;这个写法的思路是把以关键词开头的记录排在前面其余包含关键词的排后面。兼顾了搜索体验和排序逻辑。4.2 数据清洗与批量匹配场景通配符在数据清洗中也有非常实际的应用。比如有一张订单表order_no字段格式混乱有的带前缀OD-有的不带。你想找出所有看起来像订单号的记录SELECT * FROM orders WHERE order_no LIKE OD-% OR order_no LIKE OD\_% ESCAPE \ OR order_no REGEXP ^OD-?[0-9]{6,}$;这个例子混合了LIKE、ESCAPE和REGEXP三种语法实际场景中很常见。但要注意一个问题多个OR条件连接时MySQL可能无法有效利用索引即使每个条件本身都能走索引。这时可以用UNION改写SELECT * FROM orders WHERE order_no LIKE OD-% UNION SELECT * FROM orders WHERE order_no REGEXP ^OD-?[0-9]{6,}$;UNION会去重性能一般比多个OR稳定。但如果数据量不大直接用OR写法更简洁不必过度优化。4.3 通配符与排序、分页的协同通配符查询和ORDER BY、LIMIT组合时有一个性能陷阱值得注意。当LIKE %关键词%匹配出大量数据又要按某个字段排序并分页时MySQL必须先对所有匹配结果排序再取需要的页。这个排序过程如果发生在全表扫描之后性能会很差。常见的优化方案是把分页查询改成基于游标的分页。也就是用上次查询返回的最后一条记录的id作为下一次查询的起点-- 第一页 SELECT id, name FROM users WHERE name LIKE %张% ORDER BY id LIMIT 20; -- 第二页传入上一页最后一条记录的id SELECT id, name FROM users WHERE name LIKE %张% AND id 上一页最大id ORDER BY id LIMIT 20;这种做法的好处是id ?条件可以使用主键索引配合ORDER BY id LIMIT 20非常高效。缺点是它要求排序字段是唯一的、递增的如果用户需要按其他字段排序就不好办了。如果必须按created_at这类非唯一字段排序同时保持分页稳定就要在ORDER BY里加一个唯一字段作为第二排序键ORDER BY created_at DESC, id DESC。否则会出现翻页时数据重复或丢失的问题。5. 通配符常见问题与排查技巧实录5.1 通配符匹配中文查不到数据很多人遇到过LIKE %张%查不到张三丰的情况。排查思路依次是检查排序规则。如果字段是utf8mb4_bin中文匹配是区分大小写其实中文不存在大小写但_bin排序规则要求完全一致的字节序列。张和張繁体在这种排序规则下是不同的。检查字段是否有隐藏字符。从外部导入的数据可能带有不可见的字符比如UTF-8 BOM头、零宽空格。用下面的SQL把字段转成十六进制看看SELECT id, HEX(name) FROM users WHERE id 1;张三丰的HEX应该是E5BCA0E4B889E4B8B0这种格式。如果中间冒出E2808B零宽空格之类的字节序列就说明数据本身不干净用REPLACE清洗掉即可。检查字符集配置。连接字符串里的character_set_connection和表的字符集不一致时LIKE比较可能产生乱码。在连接数据库后执行SET NAMES utf8mb4;这是排查中文匹配问题最常用的第一步能解决八成以上的显示乱码和匹配失败问题。5.2 通配符与NULL、空字符串的边界情况很多人在写条件时忽略了NULL和空字符串的区别。LIKE %能匹配所有非NULL的字符串但匹配不了NULL。也就是说-- 这条SQL不会返回 name 为 NULL 的记录 SELECT * FROM users WHERE name LIKE %; -- 但会返回 name 为 的记录这个行为让不少人困惑。如果你的业务逻辑是筛选出所有填了姓名的用户应该写SELECT * FROM users WHERE name IS NOT NULL AND name ! ;如果只是想统计有值的姓名更高效的写法是直接WHERE name IS NOT NULL不要用LIKE %因为在某些版本和配置下LIKE %可能会带来额外的性能开销。5.3 通配符出现在数据本身里我之前负责过一个问卷系统用户在填空题里可以输入任意内容包括填写率100%这种文本。后来做统计时用LIKE 填写率100%查对应记录发现查出了所有问卷因为%被当成了通配符。这种问题的核心是用户输入的数据里的特殊字符在拼接到SQL之前就要做好转义。在业务代码里需要写一个转义函数把用户输入中的%、_、\统一替换为带转义符的形式。比如在PHP里$keyword str_replace([\\, %, _], [\\\\, \\%, \\_], $keyword);然后在SQL里用LIKE CONCAT(%, ?, %)匹配时才能保证用户输入的%被当作字面量处理而不是通配符。这里有一个容易忽略的细节转义符\在PHP双引号和单引号字符串里以及在MySQL解析层里都各自有一层转义逻辑。顺序搞错了转义就失效。我建议所有人在代码里写这种转义逻辑时先输出一下转换后的字符串看看确认没问题再拼SQL。6. 通配符使用速查表把我这些年用到过的通配符场景整理成一个速查表方便日常参考场景写法说明任意前缀LIKE 关键词%走索引性能好任意后缀LIKE %关键词全表扫描任意包含LIKE %关键词%全表扫描性能最差固定单个字符LIKE 张_匹配张加任意一个字多个任意字符LIKE 张%丰匹配张开头丰结尾的任意长度字面量百分号LIKE %50\%%用反斜杠转义字面量下划线LIKE %\_%用反斜杠转义自定义转义符LIKE %50!%% ESCAPE !显式指定转义符多选一前缀REGEXP ^13[89]正则表达式表达力更强全文包含搜索MATCH(...) AGAINST(...)全文索引适合大数据量文本这张表在面试和日常开发里都挺实用。很多面试官喜欢问通配符优化的问题本质上就是想考察你是否理解索引失效的原理。把这几点说清楚基本就能过关。我个人在实际操作中的体会是通配符是个看似简单、实则细节极多的功能。很多人用了几年MySQL遇到%和_还是分不清优先级遇到特殊字符转义还是一脸懵。这并不丢人因为这个东西确实只有在真实业务里踩过坑才会真正重视起来。建议你把自己手头的表拿出来按照上面的案例挨个跑一遍尤其是EXPLAIN看索引的部分。亲眼看到type: ALL和type: range的区别比看十篇文章都管用。