这段时间一直在调一个会员运营系统的查询模块被一个看似不起眼的SQL字符匹配问题折腾得不轻。表面上是数据查不出来实际上是把匹配规则、通配符语义、排序规则这些老底全翻了一遍。借着这次踩坑我把SQL字符匹配从头到尾重新梳理了一遍这篇文章就是完整的复盘从通配符原理到转义处理从正则表达到性能优化该踩的坑、该避的雷都写在里面了。如果你平时写SQL主要靠,碰到模糊查询就直接拼like %关键词%那这篇文章值得花十分钟看完。字符匹配看起来简单但真正决定查询结果正确性的往往是你最不留意的那几个细节。1. 一次误伤查询引发的思考字符匹配为什么那么容易出错1.1 排查现场问题从哪里开始暴露事情是这样的。运营同事提了一个很普通的查询需求在会员表里筛出所有昵称中包含VIP标识的用户准备做一轮定向运营。负责这块的A同学自认为很熟练直接写了一条看似正确的语句SELECT user_id, nickname, level FROM member_info WHERE level LIKE VIP%;查出来的结果却让运营炸了锅——名单里混进了一批等级叫VVIPVIP的用户不说还有一个昵称里带VIP字样的用户被漏掉了。表面问题是查多了和查漏了同时出现但真正让人头疼的是这条SQL看起来符合业务直觉为什么结果和预期对不上带着疑问往下翻数据发现level字段里实际存储的值五花八门有的叫VIP有的叫VIP-1还有的叫超级VIP。LIKE VIP%只能匹配以VIP开头的字符串超级VIP天然匹配不上而VVIP和VIP能匹配上是因为它们都以VIP开头。换句话说这条SQL的匹配语义和业务语义根本不是一回事。1.2 把问题拆开看匹配需求其实有几层字符匹配的困境本质上是因为匹配这个词在业务里有好几层含义而SQL里对应着不同的实现方式。第一层是最简单的精确匹配两个字符串完全相等。比如查用户ID、查订单号用就够了这个场景最简单不容易出错。第二层是前缀或后缀匹配典型场景是查某个编号规则下的所有记录比如找出所有以2024开头的订单号或者所有以.bak结尾的文件名。这类需求用LIKE 2024%和LIKE %.bak就能解决但要注意匹配方向。第三层是包含匹配也就是字符串中任意位置出现关键词。比如在文章表里搜所有包含数据库的标题在会员表里搜昵称含VIP的用户。这层需求用LIKE %数据库%但它的性能代价和误匹配风险都比前两层高出不少。第四层是模式匹配需要根据某种结构特征去找数据比如找出所有手机号格式合法的记录、找出所有邮箱地址、找出所有连续重复的数字。这层需求就得请出正则表达式了。很多人栽跟头就是因为没搞清楚自己面对的是哪一层需求随手用LIKE解决所有问题。我后来总结了一句特别糙但特别管用的话用LIKE的时候脑子里要时刻想着它是在做模式匹配不是在做语义判断。1.3 三种实现方式与选择逻辑SQL里字符匹配的实现手段可以粗略分成三类LIKE运算符、正则表达式函数、全文检索。它们不是彼此替代的关系而是各自有各自的适用场景。匹配方式典型语法核心能力主要限制精确匹配col VIP全等判断不支持模糊LIKE匹配col LIKE VIP%通配符模式无法表达复杂结构正则匹配col REGEXP ^VIP复杂模式写法门槛高、性能开销大全文检索MATCH(col) AGAINST(VIP)大规模文本检索依赖索引配置这里的核心选择逻辑其实很简单能用就绝不上LIKE能用前缀匹配就尽量别用包含匹配非要用包含匹配就考虑全文检索。很多人一上来就写LIKE %xxx%是对性能和后患都缺乏感知的表现。下面我逐个展开讲。2. LIKE与通配符最常用的匹配手段也是风险最高的入口2.1 通配符%和_的匹配规则和你的直觉哪里不一样LIKE是SQL里最常用的字符匹配运算符它靠两个通配符撑起核心能力%代表任意长度的字符串包括空字符串_代表恰好一个字符。这个规则背得再熟实际用起来还是会踩坑因为人脑对通配符的判断往往是差不多就行而SQL是严格的模式匹配。先看%。LIKE A%B能匹配AB、AAB、A123B也能匹配A中间随便什么都行B但它不能匹配BA因为模式要求必须以A开头、以B结尾。这个大家基本都能理解。问题出在_上面。LIKE A_B匹配的恰好是三个字符的字符串第一位是A第三位是B中间任意一个字符。注意这里是恰好一个字符不是至少一个字符。所以AB不匹配A_B匹配AXXB也不匹配。很多人在写中间有任意一个字符这种需求时会误以为_表示任意内容结果匹配结果和预期南辕北辙。我见过一个真实事故某系统做用户搜索前端传过来一个用户名张_伟后端直接拼成LIKE 张_伟结果把所有三字用户名比如张伟强张阿伟全捞出来了。为什么因为_匹配任意单个字符它根本不关心这个字符是不是真的下划线。另一个容易出问题的是%放在模式开头。LIKE %VIP和LIKE VIP%虽然都叫模糊查询但前者无法利用索引后者在特定条件下还能走索引。这个问题放到第五节细说这里先记住一个原则能用后缀不用前缀能用前缀不用包含。2.2 数据里真的有%或_时怎么精确搜索这些字符这是LIKE匹配里最经典的坑你要搜的数据里本身就包含了%或_怎么办举个例子某个课程系统的章节编号格式是第1章_进阶篇用户想搜所有包含下划线的章节。如果直接写SELECT * FROM chapter WHERE title LIKE %_%;结果会把所有章节都查出来因为_在这里被解释成了任意单个字符而任何章节标题都至少包含一个字符。这显然是错的。正确做法是使用转义。SQL标准提供了ESCAPE子句来指定转义符告诉数据库引擎跟在转义符后面的字符不再当作通配符处理。常见写法是这样SELECT * FROM chapter WHERE title LIKE %\_% ESCAPE \;这里指定反斜杠\为转义符\_表示一个字面意义的下划线%照旧是通配符。如果数据里既有普通下划线又有作为分隔符的下划线要区分就得把需求想清楚再设计对应的模式。还有更隐蔽的情况搜索条件本身的来源不可控。比如用户在前端输入了一个%或_你直接就拿去拼SQL那用户等于是在帮你构造通配符。一个输入了100%的用户本意是搜100%推荐的文章如果直接拼成LIKE %100%%%那匹配结果会完全失控。这种场景下的正确姿势是把字符串里的通配符先转义再用ESCAPE。具体做法用编程语言先处理一遍比如先对用户输入做替换先把用户输入里的每个 \ 替换成 \\ 再把每个 % 替换成 \% 再把每个 _ 替换成 \_ 最后拼成 LIKE % 处理后的输入 % ESCAPE \这个过程我用一个简单例子验证过。假设用户输入50%_off处理后变成50\%\_off再放进LIKE %50\%\_off% ESCAPE \匹配到的就是真正包含这段文字的字符串%和_都被当作普通字符处理了。2.3 大小写、中文与排序规则第三变量往往是隐形元凶很多人在本机测试时一切正常部署到服务器后同样的SQL查出来的结果不一样或者换个环境就出错这时候极大概率是排序规则Collation在作怪。排序规则决定了字符比较时的规则包括大小写是否敏感、重音是否敏感、中文按什么顺序排列等。MySQL里常见的utf8mb4_general_ci中_ci就表示case insensitive也就是大小写不敏感。在这种规则下LIKE vip%和LIKE VIP%的结果完全一样。但如果你在另一张表或另一台服务器上用的是utf8mb4_bin或者utf8mb4_0900_as_cs字符采用二进制比较大小写就敏感了。同样一条SQL在这个库能查到数据在那个库就查不到根子就在排序规则。中文匹配也有类似影响。有些排序规则下中文是按拼音顺序排列的有些是按Unicode码点排列的这会影响以中文开头的模糊匹配结果尤其在做范围查询LIKE 张%时不同排序规则返回的用户集合可能不一样。最让我头疼的一次是在一个历史项目里两张表分别用了不同的字符集做JOIN时关联字段是字符串结果出现了一堆本该匹配却匹配不上的记录。查了半天发现是字符集不同导致比较时隐式转换出了问题。从那以后我养成了两个习惯一是所有表的字符串字段尽量统一种字符集和排序规则二是在执行查询前先用SHOW CREATE TABLE或等价命令检查字段属性不要凭记忆假定环境。提示排查字符匹配结果异常时第一步不是看SQL而是查字段的字符集和排序规则。这个变量很多人一辈子都碰不到一次碰到就是大坑。3. 精确匹配、模糊匹配与正则按需求选对武器3.1 最朴素的和IN别在能用它们的场景里硬用LIKE查询需求里其实有相当大一部分是精确匹配查用户ID、查订单状态码、查地区编码。这类需求和IN就是最优解但我在Code Review里经常看到类似这样的写法SELECT * FROM orders WHERE status LIKE paid;LIKE paid和paid在结果上没区别但它引入了无谓的模糊匹配语义也让读代码的人困惑这里是想做精确匹配吧用不是更清楚吗更值得说的是LIKE精确值时某些数据库优化器不一定能把查询优化成等值比较执行计划可能走全表扫描性能差距在数据量大时会非常明显。我实测过一个百万行级别的表WHERE status paid走索引只要几十毫秒改成WHERE status LIKE paid后优化器放弃索引耗时涨了几十倍。IN同理。WHERE city IN (上海, 北京, 广州)表达的是城市属于这个集合语义清晰且索引友好。别把简单问题复杂化这本身就是一种能力。3.2 正则表达式复杂模式的无死角方案LIKE的通配符表达能力有限它处理不了开头是数字结尾是字母不包含某些字符必须满足某种格式这类复杂结构。这时候就要请出正则表达式。不同数据库的正则函数不一样。MySQL里是REGEXP或REGEXP_LIKEPostgreSQL里是~或~*SQL Server需要用到LEN、REPLACE这些函数拐几个弯来模拟。先看一个MySQL的实用例子找出所有手机号格式合法的记录SELECT user_id, phone FROM user_profile WHERE phone REGEXP ^1[0-9]{10}$;这里^表示开头[0-9]表示任意数字{10}表示重复10次$表示结尾。合起来就是1开头后面跟10位数字整个字符串必须完全匹配。这种模式LIKE完全做不了。再比如找出昵称中含连续重复字母的用户SELECT user_id, nickname FROM user_profile WHERE nickname REGEXP (.)\\1;这个例子里的\\1是反向引用表示匹配前面括号里捕获到的同一个字符可以用来识别aa、ll这类连续重复。LIKE面对这种需求完全没有办法。正则虽强但有两个使用前提必须清楚。第一性能开销比LIKE高一个量级正则匹配没法走普通索引如果表很大每次查询都是全表扫描加逐行计算数据量大到一定程度会慢到无法接受。第二写法有门槛且不同数据库实现有差异同一条正则换数据库可能要改写。所以我的建议是能用LIKE解决的就别上正则复杂结构匹配才考虑正则并且尽量把正则条件限制在已有过滤之后的子集上。3.3 三种匹配方式的适用场景对照把三种方式放在一张表里看适合选型参考需求类型典型SQL索引友好性易错点完全相等col VIP高无前缀匹配col LIKE VIP%较高通配符位置后缀匹配col LIKE %VIP低性能开销中间包含col LIKE %VIP%低误匹配性能精确包含某字符col LIKE %\_% ESCAPE \低转义遗漏格式校验col REGEXP ^1[0-9]{10}$低正则写法大规模文本搜索MATCH(col) AGAINST(VIP)高建索引这个对照表我贴在了团队的Wiki上每次有人写模糊查询前先看一遍能省掉不少低级问题。4. 深挖一个真实案例下划线查询引发的连锁故障4.1 故障表象与迅速排查前面提到的章节搜索事故当时的表现是用户在前台输入进阶篇搜不到任何结果但是数据表里明明有很多标题里带进阶篇的记录。开发同事一开始以为是索引问题把LIKE改成LOCATE、INSTR各种函数试了一圈结果都一样。我介入后第一件事不是改SQL而是复现问题。拿用户输入的进阶篇在数据表里手动跑了一遍发现一个规律搜第1章能搜到很多记录搜进阶篇零结果。这说明问题可能出在数据本身而不是查询本身。接着看数据发现章节标题的格式大多是第1章_进阶篇、第2章_基础篇这种结构。看到这里我心里大概有数了——这个下划线十有八九就是元凶。4.2 真正的原因LIKE %进阶篇%的匹配语义被改变了团队同事写的SQL是SELECT * FROM chapter WHERE title LIKE %进阶篇%;乍一看没问题但实际执行时如果标题里还有其他下划线这个模式其实是在匹配任意内容进阶篇任意内容。问题不在这条SQL本身而在另一个入口管理后台的模糊搜索功能。后台的SQL是SELECT * FROM chapter WHERE title LIKE % 进阶篇 %;这样拼起来也没问题。但运营后台还有一个高级筛选功能允许用户按章节类型筛选筛选条件是进阶篇。这个条件被拼成了WHERE type LIKE %进阶篇%字段type的值是进阶篇_A、进阶篇_B这种带下划线的编码。到这里问题就清晰了LIKE %进阶篇%中的%可以匹配下划线所以进阶篇_A应该能被匹配上才对那为什么查不到我拉出来一看发现数据里存的不是下划线而是中文全角下划线或者是一个看起来像下划线的特殊字符。用户输入的是半角下划线或普通文字模式匹配时%匹配不上那个特殊字符结果就全偏了。更讽刺的是这个故障的根子不只是通配符而是数据录入时就混入了一批长得像下划线但不是下划线的字符。字符匹配精度的敌人从来不只是通配符还有数据本身的脏乱差。4.3 修复方案与验证修复分两步走。第一步是把脏数据洗干净统一把全角下划线及各种特殊空格替换成半角下划线UPDATE chapter SET title REPLACE(title, , _) WHERE title LIKE %%;第二步是修正应用层的SQL拼装逻辑对所有用户输入先做通配符转义再放进LIKE模式SELECT * FROM chapter WHERE title LIKE %\_进阶篇\_% ESCAPE \;验证时我特意构造了几类输入带下划线的、带百分号的、带反斜杠的、带全角空格的全部跑了一遍。确认只有真正包含目标文本的记录才会被命中误伤率为零。4.4 同类故障的预防机制这个教训让我意识到字符匹配的问题不能总靠事后救火。团队后来定了几条硬规矩第一所有用户输入的搜索关键词进入SQL之前必须做通配符转义禁止直接拼接。这个靠代码Review卡死。第二模糊匹配尽量用白名单字符。比如章节编号、状态码这类有固定格式的字段先用正则或格式校验确认输入合法再决定是否允许模糊匹配。第三数据写入时对关键字段做字符规范化。把全角数字、全角字母、特殊空格统一转半角能省掉后续无数匹配烦恼。第四每次字符匹配结果异常时先怀疑通配符再怀疑排序规则然后怀疑数据本身按这个顺序排查效率最高。5. 性能视角字符匹配慢在哪里怎么优化5.1 LIKE %xx%对索引的杀伤力如果说准确性是字符匹配的底线那性能就是字符匹配的天花板。一条LIKE %xxx%在大表上能把查询拖到几秒钟这是很多人第一次意识到问题严重性的场景。原因在于B树索引的存储结构。索引是按顺序组织的只有前缀是固定的查询才能利用索引快速定位。LIKE VIP%有固定前缀优化器可以走索引范围扫描LIKE %VIP%的前缀是不确定的优化器只能放弃索引退化为全表扫描。我在一张500万行的订单表上做过一次测试。WHERE remark LIKE 已发货%走了索引平均耗时80毫秒WHERE remark LIKE %已发货%直接全表扫描平均耗时3.8秒。差了两个数量级。所以性能优化最重要的原则就一条**把通配符尽量放在匹配模式的末尾别放在开头。**如果业务上确实需要包含匹配那就得考虑其他方案不能硬扛。5.2 前模糊与后模糊写SQL时就该想好索引策略很多人写SQL时没意识到模糊查询的索引策略和通配符位置强相关。总结下来就三种场景前缀匹配LIKE abc%有机会走索引条件是字段上有普通索引并且查询条件里没有对索引列做函数运算。后缀匹配LIKE %abc没法走索引因为前缀未知。包含匹配LIKE %abc%也没法走普通索引但某些数据库支持倒排索引或全文索引来应对。如果业务中高频出现后缀匹配或包含匹配有两个优化方向供参考第一个方向是存储冗余字段。比如需要频繁按文章标题后缀搜索可以在表里加一列title_reverse存储标题的反转字符串然后对title_reverse建前缀索引后缀匹配LIKE %abc就变成了LIKE cba%索引就生效了。代价是写入时要多算一个字段属于典型的以空间换时间。第二个方向是引入全文检索。MySQL的FULLTEXT索引、PostgreSQL的tsvector、ES这类搜索引擎都内置了分词和倒排索引专门解决字符串里包含某词这类查询。现在很多业务系统已经在用这里不展开。5.3 全文检索与LIKE的边界很多开发者对全文检索有误解以为它就是更快的LIKE。其实两者处理的问题不一样。LIKE做的是子串匹配它对连续字符做模式匹配不关心词的分界全文检索做的是词条匹配先把文本切成词再为每个词建立倒排索引查询时直接按词条定位。举个直观的例子。搜数据库设计LIKE%数据库%能命中数据库设计与优化和分布式数据库详解全文检索的分词器可能把数据库和设计分成两个词数据库作为独立词条能命中数据库设计与优化但分布式数据库详解里数据库是分布式数据库这个词的一部分不一定能被命中要看分词器的粒度。判断用LIKE还是全文检索我自己的标准是数据量在十万行以内、查询频次不高、不需要分词语义用LIKE加ESCAPE就够数据量大、查询频次高、需要按词搜索的直接上全文检索。别在中间地带反复横跳那是最耗时间的。6. 字符匹配的边界条件与常见陷阱清单6.1 空字符串、NULL与匹配的隐性语义字符匹配里最容易被忽略的边界是NULL。任何包含NULL的比较结果都是未知LIKE也不例外。WHERE nickname NOT LIKE %指定%不会返回nickname为NULL的行因为NULL不满足条件。这在做排除类查询时特别容易踩坑统计结果比预期少掉一批。另一个坑是空字符串。在MySQL里空字符串与NULL是不同的值。LIKE 能匹配到空字符串但匹配不到NULL。如果你在应用层传了个空串进来恰好数据里又有NULL的字段结果集就会漏数据。稳妥的做法是查询前先明确字段的默认值策略或者在条件里显式加上IS NULL判断。6.2 大小写敏感与不敏感不同数据库的默认差异同样的LIKE语句在不同数据库里的默认行为不一样。MySQL的默认排序规则一般是不区分大小写的所以LIKE vip%能匹配到VIP666。PostgreSQL的默认排序规则按语言环境来通常也是大小写不敏感的比较但LIKE运算符本身走的是模式和文本的逐字符比较在某些配置下又是大小写敏感的。SQL Server的默认排序规则通常是大小写不敏感但排序规则设置不同结果就不同。这就带来一个现实问题你精心调试好的SQL换个数据库环境结果可能完全变化。我的应对方式是涉及大小写语义的查询一律显式指定排序规则或使用大小写无关的函数不依赖数据库默认行为。在MySQL里可以用COLLATE utf8mb4_bin强制二进制比较在PostgreSQL里可以用ILIKE做大小写不敏感的匹配。6.3 从一次全角字符事故看统一编码的重要性字符集不一致是字符匹配的又一个大坑。最典型的是全角与半角字符混存。同样是字母A半角是AU0041全角是UFF21。在大多数排序规则下这两个字符不相等存储时没统一查询时就必然出问题。我当时处理的那个系统历史数据里一部分用户昵称是半角字符一部分是全角字符。运营按VIP搜索时全角昵称的用户永远搜不出来。修起来也很麻烦因为不能简单地把所有全角字符替换成半角昵称里可能有用户故意保留的全角标点。最后只能写脚本做自动化规范化同时从录入环节把全角字符转换做掉。教训很简单字符匹配系统的稳定高度依赖于字符编码和字符规范化的一致性。录入不规范查询两行泪。6.4 参数化查询与通配符处理最后说一个很多人忽视的问题参数化查询和通配符处理并不冲突。很多人以为用了PreparedStatement或者ORM框架SQL注入就完全免疫了然后放心大胆地拼接LIKE % keyword %。实际上参数化只能防止SQL注入它不会替你处理通配符语义。你在参数里传一个%进去数据库依然会把它当作通配符解释。正确做法是参数化照用但传入参数之前先完成转义处理。两层都做了才叫安全的模糊查询。注意参数化是防火墙通配符转义是安检门。防火墙挡住了恶意代码安检门挡住了误伤数据。两道门各管各的缺一不可。7. 从SQL字符匹配到搜索体系一些进阶思考7.1 匹配正确性优先还是性能优先字符匹配方案设计时正确性和性能有时会冲突。比如为了性能把包含匹配换成全文检索但全文检索的分词逻辑可能让某些子串命不中为了正确性把所有字符做规范化处理但规范化本身又是有损的比如把全角字符转半角后搜索全角内容反而查不到。我现在的原则是核心业务场景正确性优先非核心场景性能优先。用户登录、权限判断这类场景错误是不可接受的多耗点性能无妨日志检索、运营筛选这类场景偶尔漏数据可以接受但要快速返回结果。方案选型前先把业务场景对错误率的容忍度问清楚不然很容易做出一个技术上很漂亮但业务上完全不适用的东西。7.2 输入规范化与查询规范化的双向奔赴要想让字符匹配长期稳定不能只改查询端输入端的规范化同样重要。我在多个项目里推过一套规则所有用户输入的文本都先经过一遍透明转换把全角字母数字转半角、把特殊空格统一为普通空格、把大小写做归一化存放。这样查询端只需要针对规范化后的数据做匹配两边对齐后匹配成功的概率会高很多。这套思路在大多数场景下都成立唯一要注意的是别把规范化做成不可逆的。比如用户昵称里故意用全角字母拼出花活你直接全部转半角用户会投诉。规范的粒度要按业务场景设计不能一刀切。7.3 日志里的匹配性能异常也能反向暴露数据质量字符匹配性能差的根因不一定是SQL写得差也可能是数据分布出了问题。比如某字段的重复率极高、某些值特别长、某些值包含大量特殊字符这些都会让LIKE匹配变慢。我在排查慢查询时经常会额外看一眼数据分布最长的值有多长、最多的重复值占了多大比例、有没有异常字符。这些数据质量问题和SQL本身的问题同样需要处理。我印象很深的一次一条LIKE %xxx%的查询在大表上执行了8秒排查后发现是有一批记录在文本字段里存了很长的HTML片段导致全表扫描时每行都要做大量字符比较。把那些记录清洗掉之后同样SQL的耗时降到了600毫秒。所以说字符匹配的性能问题有时候是SQL的问题有时候是数据的问题要一起看。写在最后这次字符匹配问题的排查让我重拾了一个很基本的观念越是看起来简单的基础操作越值得把它的边界条件搞明白。LIKE、通配符、转义、排序规则、字符集这些概念每个单独拎出来都不难理解但当它们叠加在一起时产生的组合效应远比想象中复杂。个人体会是字符匹配没有一劳永逸的万能写法它的正确性来自对业务需求的清晰判断、对数据库行为差异的充分了解、以及对数据的持续治理。如果你也在做类似的功能建议先从这几件事做起检查字段的排序规则、规范用户输入、统一通配符转义逻辑、给核心查询加性能监控。做好了这四件事字符匹配基本不会给你惹大麻烦。