有一回做投诉工单报表要从一张几万行的表里筛出“产品名称中带 5% 字样”的记录。我顺手写了WHERE product_name LIKE %5%%执行完当场愣住——全表几乎全中。第一反应是数据脏排查了大半天最后才发现锅根本不在数据而是这条 SQL 里的%太“自作聪明”了。像“字段中含有转义符”这类坑凡是写过模糊匹配查询的人基本都会踩一次区别只是有人半小时就绕出来了有人会为此怀疑人生。这篇就专门聊透这个事LIKE 里的%和_到底在干嘛、转义符是怎么回事、不同数据库怎么处理、工程代码里怎么防御以及这类查询的性能和索引陷阱。内容以 MySQL 为主线兼顾 Oracle、SQL Server、PostgreSQL最后附上踩坑记录和排查速查表。适合正在做数据查询、报表统计、接口开发的朋友尤其是被模糊查询结果弄到一头雾水的那几位。1. 模糊匹配为什么会“栽”在转义符上1.1 LIKE 里 % 和 _ 的真实身份先明确一个基本概念。LIKE做模糊匹配时模式串里有两个特殊字符是通配符%匹配任意长度的任意字符包括空字符串。_匹配且仅匹配任意单个字符。生活化类比%相当于你搜索文件时输入的*只要文件名里有一段对得上整串都能匹配_则更像下棋时的“一个空格”必须刚好有一个字符占住这个位置多一个少一个都不行。问题在于如果业务字段的数据本身包含%或_比如“洗面奶 100% 正品”或者编码规范里常见的KPI_2024_考核表那么这些字符在 LIKE 模式里会被当成通配符来解释查出来的结果自然不是字面含义。这是“字段中含有转义符”的核心矛盾数据里的特殊字符和查询语法的特殊字符撞车了。解决思路只有一个——告诉数据库这里出现的%或_不是通配符是普通字符。这个动作就叫“转义”。1.2 一个典型的误匹配现场用数据说话。假设有一张投诉记录表结构很简单CREATE TABLE complaint_log ( id INT PRIMARY KEY, product_name VARCHAR(100), deal_rate VARCHAR(20) ); INSERT INTO complaint_log VALUES (1, 洗面奶促销装100%正品, 15%), (2, 洗发水500ml, 5%), (3, KPI_2024_考核表, NULL), (4, KPX2024考核表, NULL);业务需求查出所有商品名称里带KPI_2024的记录。直觉写法SELECT * FROM complaint_log WHERE product_name LIKE %KPI_2024%;这条 SQL 不会报错但结果里第 3 条和第 4 条都会出来。第 4 条KPX2024考核表并没有_但因为_被当成“任意单个字符”KPI_2024中的_恰好匹配了X整条就命中了。再看%的例子。需求是查“包含 5% 这个字面值”的记录SELECT * FROM complaint_log WHERE product_name LIKE %5%%;这个写法更离谱第二个%是通配符等价于%5%于是只要字段里出现过数字 5就会全部命中。表里第 1、2 条都会查出来甚至某条记录里带个“5 元优惠券”也会被捞上来。这两段代码看起来人畜无害实际跑起来全是坑。根本原因就是没做转义。1.3 不同数据库的默认转义行为这里有个特别重要的点不同数据库对 LIKE 中转义符的默认处理是不一样的。我整理了一个对照表方便你排查时对号入座。数据库默认转义字符需要显式 ESCAPE 吗补充说明MySQL\是默认转义符可以不写但受NO_BACKSLASH_ESCAPES模式影响模式不统一时容易出隐性 bugSQL Server无默认转义符必须写ESCAPE否则%和_永远是被通配还支持[]做单字符匹配容易混Oracle无默认转义符必须写ESCAPE字符串字面量里的\处理也容易混乱PostgreSQL\是默认转义符建议显式写ESCAPE受standard_conforming_strings影响字面量写法易混淆所以“为什么我在 MySQL 里写了%\_%有效在 SQL Server 里却什么都不匹配”这类问题答案往往就在这个表里。SQL Server 不认反斜杠转义你必须显式给出ESCAPE比如WHERE product_name LIKE %KPI\_2024\_% ESCAPE \;MySQL 里默认能直接用反斜杠但如果数据库处于NO_BACKSLASH_ESCAPES模式反斜杠也会失效。因此最稳妥的做法是不要依赖数据库的默认行为每条 LIKE 都显式声明转义符。2. 正确姿势ESCAPE 子句与等价替代方案2.1 ESCAPE 子句的完整写法SQL 标准提供了一套通用的解决方案在 LIKE 模式末尾加ESCAPE子句自定义一个转义字符。语法格式WHERE 字段 LIKE 模式 ESCAPE 转义字符;转义字符的作用是当它出现在%或_前面时后面的通配符就被“降级”为普通字符。用前面的5%场景举例SELECT * FROM complaint_log WHERE product_name LIKE %5/%% ESCAPE /;拆开看这段模式第一个%是通配符中间的/是自定义转义符/%表示“字面意义上的百分号”最后的%又是通配符。整条表达的意思是字段里只要包含字符串5%无论前后有没有其他内容都算命中。匹配下划线的写法同理SELECT * FROM complaint_log WHERE product_name LIKE %KPI/_2024/_% ESCAPE /;这里要注意三点。第一转义符与后面的%或_之间不能有空格必须连写。因为空格本身是普通字符一旦插入空格转义逻辑就断了。第二转义符只对它紧跟着的一个字符生效。如果要匹配两个连续的特殊字符比如字符串100%%模式就要写成%100/%%/%一个字符一个转义位。第三如果字段本身包含反斜杠比如 Windows 路径C:\Users\test推荐用/这类非歧义字符做转义符避免和字符串字面量级别的反斜杠转义叠加否则写两层转义很容易数错斜杠。2.2 用字符串函数代替 LIKE绕开通配符问题如果不需要用%做真正的模糊匹配只需要“判断字段中是否包含某个固定文本”那更干净的做法是用字符串定位函数。这些函数搜索的是普通字符串不存在通配符语义也就不需要转义。四个主流数据库的写法-- MySQL SELECT * FROM complaint_log WHERE LOCATE(5%, product_name) 0; -- Oracle SELECT * FROM complaint_log WHERE INSTR(product_name, 5%) 0; -- SQL Server SELECT * FROM complaint_log WHERE CHARINDEX(5%, product_name) 0; -- PostgreSQL SELECT * FROM complaint_log WHERE POSITION(5% IN product_name) 0;这套方案的优点非常直观不用管%、_、\搜索什么就是什么。适合“完全匹配子串但不关心前后缀”的场景比如在黑名单词表里查包含违禁%的数据。缺点也有不像LIKE那样能表达复杂的模糊模式比如“以 A 开头、中间包含 B、以 C 结尾”这种组合用函数就不好写还得回到 LIKE 加正则那套。2.3 正则表达式方案适合复杂场景的兜底既然 LIKE 的通配符和转义符容易出错另一个思路是换成正则表达式。在正则的世界里%和_是普通字符没有通配符的语义所以不需要对它们转义。以 MySQL 8 和 Oracle 的REGEXP_LIKE为例想查包含字面100%正品的记录SELECT * FROM complaint_log WHERE REGEXP_LIKE(product_name, 100%正品);这里%在正则里就是百分号本身不需要像 LIKE 那样加转义。但别高兴得太早正则有另一套自成体系的元字符.、*、[]、()、|等它们才是需要转义的对象。比如要搜KPI_2024_考核表这个需求用正则是正常查但若要搜KPI.2024中的字面点号正则里就得写成KPI\.2024。简单总结只搜固定子串优先用字面函数LOCATE/INSTR/CHARINDEX。需要复杂模式、且字段里%、_出现频繁时用正则表达式反而省事。LIKE 加ESCAPE则适合需求简单但就是想用 LIKE、团队习惯统一的场景。3. 工程代码里如何稳妥处理“转义符”查询3.1 参数化查询不等于自动转义很多开发者在代码里用参数化查询以为把用户输入原样塞进%...%就万事大吉。这是个很致命的误解。参数化查询解决的是 SQL 注入问题它确保用户输入被当成“值”而不是“SQL 片段”来解析。但在LIKE内部%和_依然会被数据库解释为通配符。举个 Python 操作 MySQL 的例子keyword 5% sql SELECT * FROM complaint_log WHERE product_name LIKE %s params (f%{keyword}%,) # 错误的做法% 还是通配符这样做执行后5%中的%依然通配一切结果还是会把所有含 5 的记录全部捞出来。正确做法是在拼接 LIKE 模式之前先对关键词做一次转义处理def escape_like(keyword: str) - str: # 转义顺序有讲究先反斜杠再 %再 _ return keyword.replace(\\, \\\\).replace(%, \\%).replace(_, \\_) keyword 5% escaped escape_like(keyword) # 得到 5\% sql SELECT * FROM complaint_log WHERE product_name LIKE %s ESCAPE \\\\ params (f%{escaped}%,)这里ESCAPE \\\\对应数据库里的反斜杠转义符代码里写多少个斜杠经常把人绕晕。所以我在工程里更推荐自定义一个不容易出错的转义符def escape_like(keyword: str) - str: return keyword.replace(/, //).replace(%, /%).replace(_, /_) sql SELECT * FROM complaint_log WHERE product_name LIKE %s ESCAPE / params (f%{escape_like(keyword)}%,)这个方案一眼就能看明白用户输入里原来的/被写成//%写成/%_写成/_。查询时使用ESCAPE /可读性比数反斜杠好太多。3.2 ORM 框架里同样要预先处理ORM 并不负责帮你转义 LIKE 的特殊字符。比如 Django 的 ORM# 看起来没问题实际生成 SQL 是 LIKE %5%%照样全表捞 ComplaintLog.objects.filter(product_name__contains5%)__contains只是帮你拼了个%...%内部的%该通配还通配。正确的做法是用extra或RawSQL把转义逻辑显式写进 SQL 片段from django.db.models.expressions import RawSQL escaped escape_like(5%) qs ComplaintLog.objects.extra( where[product_name LIKE %s ESCAPE /], params[f%{escaped}%], )MyBatis 场景要区分#{}和${}。#{}是参数绑定安全但不能自动处理 LIKE 通配符${}是文本拼接有注入风险不适合直接拼用户输入。稳妥的写法是在 XML 中用CONCAT并且提前在 Java 服务层完成转义select idsearch resultType... SELECT * FROM complaint_log WHERE product_name LIKE CONCAT(%, #{escapedKeyword}, %) ESCAPE / /selectescapedKeyword在 Service 层已经调用escape_like()处理过。这么做既避免注入又防住了通配符穿透。JPA 的Query同理LIKE :keyword里的keyword必须携带转移后的模式。3.3 动态拼 SQL 时的防守底线如果团队里还有人习惯用字符串拼接的方式生成查询 SQL一定要立几条规矩所有进入 LIKE 模式的用户输入必须先走统一的转义函数。转义函数里必须包含对转义符本身、%、_三者的处理缺一不可。拼接时LIKE必须显式带ESCAPE不允许依赖数据库默认行为。尽量限制输入长度比如 50 个字符以内防止有人把超长正则或通配符变体塞进来。另外一个时常被忽略的坑是字段名本身带特殊字符或数据库保留字。比如热词里提到的“mysql 表中字段为关键字”如果字段命名是order、group、desc这类保留字查询时必须用反引号包裹SELECT * FROM complaint_log WHERE order 1;这类问题和转义符属于同一大类的“特殊字符处理”在开发规范里值得专门加一条字段命名尽量避开保留字和特殊符号实在避不开要统一标识符的引用方式。4. 含有转义符的模糊查询性能和索引怎么兼顾4.1 前导通配符会让索引失效聊完正确性必须说说性能。很多人在排查转义符问题时往往忽略了 LIKE 查询本身就存在索引陷阱。WHERE product_name LIKE %xxx%这种前后都带通配符的写法数据库无法利用普通 B 树索引只能走全表扫描。原因很简单优化器没法确定匹配的起点不知道应该从索引树的哪个位置开始扫描。即使你加了ESCAPE把特殊字符转义成了字面量这个性能问题也不会消失。ESCAPE解决的是匹配结果的正确性索引失效的根源是前导%本身。如果你的查询模式是固定的前缀比如LIKE abc%那普通索引还能用上。但“字段中间含有特殊字符”这类需求往往逃不掉全表扫描。这时就需要靠下面两种手段兜底。4.2 用生成列或函数索引优化MySQL 8.0 以上支持生成列。可以提前把字段中的特殊字符剥离出来存成独立列再对这个列建索引查询时直接走索引ALTER TABLE complaint_log ADD COLUMN product_name_clean VARCHAR(100) GENERATED ALWAYS AS (REPLACE(REPLACE(product_name, %, ), _, )) STORED; ALTER TABLE complaint_log ADD INDEX idx_clean (product_name_clean);这样业务代码里查“包含 5% 的记录”时可以先按干净列过滤或者直接对干净列做常规查询效率和正确性都得到了保障缺点是占存储空间。Oracle 支持函数索引可以针对INSTR这类函数建索引CREATE INDEX idx_instr_rate ON complaint_log (INSTR(deal_rate, %));查询时用WHERE INSTR(deal_rate, %) 0优化器就有机会走函数索引。PostgreSQL 的表达式索引类似直接在查询列上建立表达式CREATE INDEX idx_instr ON complaint_log ((POSITION(% IN deal_rate)));4.3 视图解决不了性能问题热词里有个高频问题“视图可以加快查询速度吗”这个误解在模糊查询场景里特别常见。有人把WHERE product_name LIKE %5%%封装成一个视图指望查询视图能变快。可以直接给你结论视图只是把一段 SQL 保存成了一个命名对象它不存储数据也没有自己的索引。查询视图等价于执行视图中嵌套的那条 SQL扫描成本和直接写 LIKE 没有任何区别。真正能提速的是合理的前缀匹配、覆盖索引、全文索引或者干脆把特殊字符剥离后建生成列。想靠视图解决模糊查询性能问题方向就错了。5. 常见问题与实战排查小抄5.1 快速对照表为了让你遇到问题时能快速定位我整理了下面这张表。问题现象可能原因处理办法查询结果明显过多字段里的%被当成通配符使用ESCAPE转义为字面量明明字段含_条件就是匹配不上_被当成单字符占位符对_转义或改用LOCATE等函数MySQL 里写了\%无效数据库处于NO_BACKSLASH_ESCAPES模式改用显式ESCAPE /SQL Server 里\%无效SQL Server 无默认转义符必须写ESCAPE \或用[]反斜杠路径字段查询不到字符串字面量转义与 LIKE 转义叠加换用非\的转义符或参数绑定用户输入%导致全表匹配参数化查询未处理 LIKE 通配符入参前统一调用escape_like模糊查询很慢前导%导致索引失效生成列/函数索引/全文索引把模糊查询封装成视图想提速视图不存储数据不能加速改为对索引列做前缀查询5.2 三个真实踩坑现场第一个坑业务编码里大量使用下划线。商品编码规范是SZ_2024_001这类格式需求是“查询 2024 年所有深圳商品”。同事写的是LIKE %SZ_2024%结果把SZ02024、SZ12024都捞出来了因为这些_都可以被当成任意单字符。排查时如果不先用一个小数据集验证 LIKE 语义很容易误判为数据质量问题。第二个坑JSON 字段里做模糊匹配。有人把整个 JSON 字符串存到字段里查询时直接LIKE %type:A%。虽然 JSON 里的双引号和冒号不是通配符但如果 JSON 数据里恰好含%或_一样中招。更麻烦的是 JSON 字符串中的反斜杠转义会叠加。这种情况不要用 LIKE直接用数据库的 JSON 函数比如 MySQL 的JSON_EXTRACT既安全又高效。第三个坑SQL Server 的方括号。SQL Server 的 LIKE 支持[]做字符集匹配比如LIKE sales[_]2024可以匹配sales_2024。但如果你想匹配字面方括号就又得引入一层转义。类似这种“一个数据库一套规则”的细节跨数据库移植时最容易翻车。建议所有涉及LIKE的 SQL 都写清楚ESCAPE不要裸奔。5.3 特殊字段名和 IN 查询的补充注意虽然本文主角是转义符但“特殊字段处理”还有两个经常一起出现的坑值得多说两句。一是热词里提到的字段名是数据库保留字。MySQL 用反引号Oracle 和 PostgreSQL 用双引号SQL Server 用方括号。各数据库的标识符引用规则不一样迁移 SQL 时很容易忽略。最省心的办法是建表时就避免使用保留字命名。二是IN查询报错或结果异常。常见原因包括列表里混入了 NULL导致结果缺少记录字段类型和列表元素类型不一致列表过长超过数据库限制。排查顺序应该是先打印实际执行的 SQL确认列表内容有没有特殊字符再用COALESCE或IFNULL把 NULL 处理掉最后检查字符集和排序规则是否统一。最后说点实际的我在实际项目里被这种问题折腾过好几回之后总结出一条经验凡是写 LIKE 相关的需求第一件事就是问清楚字段内容里会不会出现%、_、\这些特殊字符。会就统一在代码里做一次escape_like()数据库层一律显式ESCAPE /绝不依赖默认行为。这个习惯养成了后面能少踩很多坑。再分享一个调试小技巧遇到“模糊查询结果不对”时别急着改 SQL 来回试。先用一条SELECT把转义后的模式串打出来看一眼往往能立刻发现%和_的位置错了。比如你原本以为模式是%5/%%打印出来发现成了%5%%/%那问题就一目了然了。这种问题最耗时间的环节从来不是写修复语句而是确认数据里的特殊字符到底是什么。把排查顺序理顺效率能提高不少。