PostgreSQL 的 LIKE 语句看起来是数据库里最容易写的一行 SQL真正较真起来却到处都是细节。等值查询大家都会一旦产品经理提模糊搜索包含匹配第一反应就是where name like %关键词%但很多人没有意识到这套语法背后的通配符语义、大小写规则、索引利用条件和转义陷阱。我这些年排查过不少线上慢查询和诡异结果一半以上都跟 LIKE 用的不够严谨有关。这篇文章不是把官方文档翻译一遍而是把我在真实项目里积累的 LIKE 使用经验、踩坑记录和优化路径完整拆开适合刚接触 PostgreSQL 的开发者也适合写了几年 SQL 但对 LIKE 的理解还停留在%%包一下就行的同行。1. LIKE 在解决什么问题以及它的执行语义1.1 等号匹配的盲区如果你有一张用户表里面存着张伟张杰王张用户在前端只输入了一个张字期望把所有含张的人搜出来。用name 张永远只能查到名字恰好叫张的那条记录因为等号做的是全值精确比较它不理解包含这个概念。LIKE 的定位就是模糊匹配。它允许你描述一个模式然后引擎逐字符去判断目标字符串是否匹配这个模式。举个不太恰当但很形象的例子等号是拿着完整的暗号对暗号一个字都不能差LIKE 是拿着一个关键词在广播里找人只要广播的内容里包含这个关键词、或者满足你描述的形状就算命中。这种需求在真实业务里到处都是。订单号搜索、商品名搜索、日志关键字过滤、标签匹配甚至很多报表系统里的筛选器底层都是 LIKE。掌握了 LIKE基本就掌握了关系型数据库模糊查询的通用范式PostgreSQL 的语法和 MySQL、Oracle 在通配符层面几乎一致技能可以平移。1.2 表达式的基本结构LIKE 的标准语法是expr LIKE pattern [ESCAPE escape_char]它返回一个布尔值。可以在WHERE、HAVING、JOIN ... ON、CASE WHEN甚至CHECK约束里直接使用。比如SELECT name FROM users WHERE name LIKE 张%;这条 SQL 会匹配所有以张开头的字符串包括张本身、张伟、张伟伟但不匹配王张。很多人会忽略的是 NULL 的传播规则如果expr或pattern任何一个为 NULLLIKE 的结果不是 false而是 NULL。比如name LIKE NULL的结果永远是 NULL而WHERE子句只会保留结果为 true 的行NULL 会被当作不满足条件而过滤掉。所以如果 pattern 是动态拼接进来的一定做一下空值判断否则可能莫名全表不返回。LIKE 还有一对配套写法NOT LIKE。它的语义就是NOT (expr LIKE pattern)但同样遵循 NULL 规则NULL NOT LIKE pattern结果是 NULL不是 true。1.3 大小写敏感到底由谁决定标准 LIKE 本身是大小写敏感的。LIKE abc不会匹配ABC。PostgreSQL 在此基础上提供了ILIKE专门做大小写不敏感的模糊匹配SELECT abc LIKE ABC; -- false SELECT abc ILIKE ABC; -- trueILIKE本质上相当于把两边都转成小写再做等价比较你可以理解为lower(expr) LIKE lower(pattern)但ILIKE在很多场景下能走索引优化而lower(expr)这种写法除非建了表达式索引否则基本是死路。需要强调的是LIKE 是否区分大小写并不完全由 LIKE 自身决定而是受数据库 collation排序规则影响。PostgreSQL 的默认 collation 通常来自初始化时的 locale。在C这种二进制排序规则下LIKE是对字节做比较属于严格大小写敏感。在大多数en_US.UTF-8环境下普通文本比较也是大小写敏感的。如果你的业务需要无论大写小写都能匹配直接用ILIKE是最省心的不要赌 collation 的行为。2. 通配符的真实语义与转义的边界条件2.1 % 和 _ 到底匹配什么LIKE 只认识两个通配符%匹配任意长度包括零长度的任意字符序列。_匹配且仅匹配一个任意字符。举例来说LIKE a%b可以匹配ab、aab、aXYb、a123456b但不能匹配ba因为模式要求以a开头以b结尾。LIKE a_b只能匹配由三个字符组成、首字母是a尾字母是b的字符串比如acb、a1b、a_ b但不能匹配ab缺一个字符也不能匹配a22b中间有两个字符。这里最容易出事的其实是_。因为它只匹配一个字符所以很多新手下意识把它当成普通的下划线字符。比如你要搜的订单号规则是AB_123写成SELECT * FROM orders WHERE order_no LIKE %AB_123%;结果可能把ABX123、AB-123、AB1123全部捞出来因为它们都满足AB 任意一个字符 123的模式。这个坑我在生产环境踩过不止一次后面专门有一节讲排查过程。2.2 百分号的字面量匹配%作为通配符带来的另一个麻烦是当用户真的想搜索含 50% 折扣这种带百分号的内容时直接写LIKE %50%%会匹配所有包含50且后面还有任意字符的内容完全不对。正确思路是用转义把模式中的%变成字面量。PostgreSQL 的 LIKE 默认使用反斜杠作为转义字符所以SELECT * FROM products WHERE name LIKE %50\%%;这条语句中的\%表示一个字面百分号最后一个%是通配符。整体含义是包含 50% 这个连续片段的任意字符串。同理LIKE %\_%匹配的是包含下划线的字符串。2.3 用 ESCAPE 子句避免反斜杠地狱反斜杠作为默认转义符在绝大多数场景下够用但有个隐患PostgreSQL 的字符串常量本身对反斜杠有一套规则。standard_conforming_strings参数开启时PG 9.1 后默认开启普通字符串字面量里的反斜杠就是普通字符不会做任何解释而当你使用E前缀写转义字符串时反斜杠会被字符串解析层先处理一层。举一个很实际的区别。在默认配置下以下两条 SQL 的结果完全相同SELECT a\%b LIKE a\\%b; -- 字符串里有两个反斜杠LIKE 模式里转义了一层 SELECT a\%b LIKE a\%b; -- 字符串里有一个反斜杠LIKE 模式也转义了一层但如果standard_conforming_strings被关掉字符串a\%b在解析阶段就会变成一个a%b到 LIKE 这一步看到的是通配符%语义完全变了。为了避免环境差异导致的诡异问题我建议不要依赖默认转义符而是一旦模式中需要出现%或_就显式地指定ESCAPE子句例如用!或#这种不常用的字符SELECT * FROM products WHERE name LIKE %50!%% ESCAPE !;这种写法完全绕开了反斜杠在字符串层和模式层之间的双重解释问题。项目里统一约定一个转义符比如ESCAPE !会让代码的可读性高很多也少踩很多坑。3. 性能问题LIKE 什么时候会拖垮数据库3.1 前导通配符为什么必然全表扫描一个很常见的性能教训SELECT * FROM users WHERE name LIKE %张%;这条查询如果数据量上百万基本上会走全表扫描。原因很简单普通的 B-tree 索引按字符串的字节顺序组织数据只能支持前缀确定的搜索。当模式以%开头时数据库无法利用排序好的索引来缩小范围因为任意一个字符串都可能在后半段藏着目标子串它只能把每一条记录都拉出来做一遍匹配。所以我看到团队里有人写模糊查询时第一反应就是看模式的前缀是否固定。如果业务允许LIKE 张%的搜索体验和性能都会好一个数量级因为这种以此为开头的查询完全可以在普通 B-tree 索引上做范围扫描。3.2 普通 B-tree 索引能优化什么对于LIKE abc%这种前缀固定、后缀开放的查询PostgreSQL 可以使用普通索引。比如CREATE INDEX idx_users_name ON users (name);然后执行SELECT * FROM users WHERE name LIKE 张%;优化器会在索引上定位到第一个以张开头的键值然后顺序扫描到最后一个以张开头的键值效率极高。这里的前题是索引的排序规则和 LIKE 比较规则一致。如果列定义了特殊 collation或者你在列上加了函数比如lower(name) LIKE abc%普通索引就无法使用必须创建对应的表达式索引。需要额外注意 PostgreSQL 官方文档里的一个提示如果表使用的是非确定性 collation例如某些 ICU collationLIKE 的前缀优化可能不可用因为非确定性 collation 下字符串的等价关系不稳定索引无法可靠地用于范围匹配。生产环境用到了这类 collation 的建议在测试环境跑一下 EXPLAIN 确认有没有走索引。3.3 pg_trgm解决包含匹配的杀手锏如果你的业务无论如何都绕不开%关键词%这种中间匹配PostgreSQL 有一个非常成熟的方案pg_trgm扩展。它把字符串拆成连续的三个字符组trigram然后建立 GIN 索引让包含匹配也能走索引。启用方式CREATE EXTENSION pg_trgm; CREATE INDEX idx_users_name_trgm ON users USING gin (name gin_trgm_ops);有了这个索引LIKE %张%就可能变成 Bitmap Index Scan而不是 Seq Scan。我做过一个简单的压力测试在 100 万行数据里搜索一个隐藏在中间的关键词普通全表扫描需要 300 毫秒以上建立pg_trgmGIN 索引后命中查询耗时降到 1 毫秒左右提升非常明显。不过 pg_trgm 有一个重要边界它依赖至少三个连续的字符才能形成有区分度的 trigram。这意味着对于LIKE %ab%这种只有两个字符的模式pg_trgm 几乎无法提供帮助因为拆不出完整的 trigram优化器通常会回退到全表扫描。中文场景同理搜索单个汉字或双字关键词时pg_trgm 并不理想搜索三个及以上连续汉字时效果才会显现。另外ILIKE在 pg_trgm 版本较新时也能走 GIN 索引但最好在真实数据上跑EXPLAIN验证。索引并不是建了就一定被用优化器会根据代价判断小表反而更倾向全表扫描。3.4 用执行计划判断 LIKE 是否走了索引判断 LIKE 查询是否高效最直接的方式是看执行计划EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM users WHERE name LIKE %张%;重点关注有没有Seq Scan全表扫描如果出现Bitmap Index Scan on idx_users_name_trgm或者Index Range Scan说明索引生效了。如果没有索引又想评估性能加上BUFFERS可以看到真实读了多少数据块这对判断物理 I/O 消耗非常有帮助。我个人的习惯是所有包含 LIKE 的核心 SQL 变更都必须附带一份EXPLAIN (ANALYZE, BUFFERS)截图作为上线依据。数据量不大时全表扫描的感受差异很小但数据一旦涨上去一条没走索引的 LIKE 查询就可能把数据库 CPU 拉满。4. 业务代码里的 LIKE参数绑定、转义顺序与动态查询4.1 直接拼 SQL 等于引狼入室很多项目里的模糊搜索是这样写的-- 危险写法 SELECT * FROM users WHERE name LIKE % || 输入的词 || %;问题是这里的输入的词如果用字符串拼接的方式放进 SQL而不是走参数绑定用户完全可以输入 OR 11之类的恶意内容把整个查询语义改掉。这已经不是 LIKE 的问题而是最经典的 SQL 注入漏洞。正确做法永远是参数化查询。PostgreSQL 的$1占位符配合预处理语句可以安全地处理输入的搜索词。我在 Python 的 psycopg2 里通常这样写cur.execute( SELECT * FROM users WHERE name ILIKE % || %s || %, (keyword,) )Java JDBC 里用?占位符同理。ORM 框架中MyBatis 可以写成select idsearchUsers resultTypeUser SELECT * FROM users WHERE name LIKE CONCAT(%, #{keyword, jdbcTypeVARCHAR}, %) /select参数绑定可以防止语义注入但要注意%s或#{keyword}只会作为参数传入%和_依然会被 LIKE 当作通配符处理如果你不希望用户输入的关键词里自带通配符必须在传入前先做转义。4.2 用户输入中的 % 和 _ 必须单独处理这里有一个经典的二段转换流程。比如用户在前端搜索框输入了50%_off这个关键词你的目标是把它当成普通文本匹配包含这个完整文本的记录。首先对用户输入做一次转义把\、%、_都加反斜杠前缀escaped keyword.replace(\\, \\\\).replace(%, \\%).replace(_, \\_)然后把转义后的内容拼进模式里pattern % escaped % cur.execute( SELECT * FROM products WHERE name LIKE %s ESCAPE \\, (pattern,) )这里有一个很容易忽略的细节replace的顺序不能乱。必须先转义反斜杠再转义%和_否则反斜杠本身会被二次转义最终落到 LIKE 模式里的转义符可能是错的。如果不想在代码层处理这些反斜杠也可以在应用层换一种思路既然%和_是通配符不如直接禁止它们在用户输入中出现或者把搜索语义定为包含文本片段而不是支持通配符。这种情况下可以完全绕开 LIKE 的通配符能力退回到strpos这种纯文本包含判断这反而是最稳妥、最不容易出 bug 的方案。关于这一点我在后面排错部分会展开。4.3 动态查询里常见的三种 LIKE 组合业务中高频出现的 LIKE 查询基本可以归为三类前缀匹配LIKE keyword%适合搜索框自动补全、订单号开头的筛选。这类查询是性能最好的普通 B-tree 索引就能支持。中缀匹配LIKE %keyword%适合通用关键词搜索。数据量大时必须上 pg_trgm 索引。后缀匹配LIKE %keyword适合邮箱域名后缀、文件扩展名这类场景。普通 B-tree 索引无法优化pg_trgm 可以帮上忙。同一个搜索接口可能同时支持多种匹配方式。我的做法是在代码里先判断用户输入是否以%或_开头来识别是否包含通配符再决定拼接哪种模式。但大多数面向普通用户的场景其实不需要把通配符能力暴露出去前缀匹配或中缀匹配二选一固定成产品规则反而容易维护。4.4 空字符串和 NULL 的处理LIKE 还有一个容易忽略的边界LIKE %%实际上会匹配所有非 NULL 的字符串包括空字符串。因为%能匹配零个字符空字符串也满足条件。所以如果你写SELECT * FROM users WHERE name LIKE %%;这等价于name IS NOT NULL对优化器来说没什么帮助。如果业务想判断某个字段是否非空直接用IS NOT NULL更清晰。反过来LIKE %同样能匹配所有非 NULL 字符串。这个特性在某些动态条件拼接中可能意外影响查询结果。比如前端没传关键词时如果你默认拼了LIKE %%所有非空记录都会被选中这往往不是业务真实意图。5. LIKE 和正则表达式什么时候该换挡5.1 PostgreSQL 的正则操作符PostgreSQL 提供了一组基于 POSIX 正则表达式的操作符~匹配正则大小写敏感~*匹配正则大小写不敏感!~不匹配正则大小写敏感!~*不匹配正则大小写不敏感LIKE 只有%和_两个通配符表达能力非常有限。一旦业务需求变成以数字开头包含 3 到 5 位数字手机号中间四位脱敏匹配这种结构化描述LIKE 就会变得很别扭正则却可以一行解决。5.2 二者在常见场景下的等价写法LIKE 写法正则写法说明LIKE abc%~ ^abc以 abc 开头LIKE %abc~ abc$以 abc 结尾LIKE %abc%~ abc包含 abcILIKE %abc%~* abc不区分大小写包含 abcLIKE a_c%~ ^a.ca 开头 c 结尾单字符桥梁正则在单个字符层级上更灵活。比如查找所有以1开头后面跟 3 位数字再加一个横线的手机号前缀码正则写法~ ^1[0-9]{3}-LIKE 完全表达不出来。5.3 正则的代价和转义问题正则虽然强大但代价更高主要体现在三点第一正则引擎处理更复杂表达式一旦写得不好CPU 消耗会明显高于 LIKE。第二正则的错误排查难度大你很难一眼看出一个复杂正则到底匹配什么。第三字符串里的正则元字符非常多.*?()[]{}^$|\都需要转义。比如你想匹配字面意义的www.example.com正则要写成www\.example\.com而 LIKE 只需要www.example.com正常写即可。所以在真实业务里我的选择原则很简单如果用户输入的是普通关键词优先 LIKE如果产品需求描述里出现了数字连续出现的次数字符范围这种词汇直接用正则如果需要大小写不敏感的包含匹配LIKE 的ILIKE通常比正则的~*更简单直观。5.4 正则也能用 pg_trgm 索引pg_trgm 不仅支持 LIKE对部分正则表达式也有一定的加速效果。比如WHERE name ~ abc这种简单包含关系GIN 索引可以参与计划。但正则一旦复杂到无法提取有效的三字符组索引就无力回天优化器会回到全表扫描。所以正则适合写逻辑不适合做高频大表查询。真要在大表上做复杂文本匹配更靠谱的方向是全文检索那是另一个话题了。6. 我踩过的 LIKE 坑三次线上事故复盘与最佳实践6.1 下划线把订单查串了有一次线上工单反馈客户在订单搜索里输入AB_2024系统却返回了大量AB12024、ABX2024之类的订单。一开始所有人都怀疑数据写错了我查了一下 SQL果然写的是WHERE order_no LIKE %AB_2024%这里_被当成了通配符匹配任意单个字符导致所有AB 任意字符 2024的订单全部命中。修复方式很简单把下划线转义掉WHERE order_no LIKE %AB\_2024% ESCAPE \但更根本的教训是代码里所有来自用户的搜索词都不能直接塞给 LIKE 通配符。后来的修复方案我改成了先转义、再拼接同时增补了一批包含%和_的自动化测试用例。这个坑如果不注意藏在业务里非常难发现因为结果不是报错而是多出来一些看似有关联的脏数据。6.2 collation 引发的 ILIKE 失效另一个项目里数据库是别人初始化好的用的是比较老的 ICU collation 配置。业务上线后频繁出现线上告警某条ILIKE查询执行计划显示全表扫描数据量一大就超时。定位后发现这个表的 collation 不是确定性排序规则优化器无法利用 B-tree 索引来做前缀匹配所以ILIKE abc%也只能走 Seq Scan。当时的解决方案是给列新建了pg_trgmGIN 索引让包含匹配也能有索引可用。这件事让我养成了习惯接手任何新库第一件事是把所有涉及 LIKE/ILIKE 的核心查询在测试环境跑一遍EXPLAIN确认是否真的用上了索引而不是看WHERE条件里有没有索引列就盲目乐观。6.3 参数类型不匹配导致的隐式转换错误还有一种不太常见但很容易被忽略的报错在 PL/pgSQL 函数里使用动态 SQL 时出现ERROR: operator does not exist: character varying ~~ unknown原因是LIKE左操作数是varchar而右侧的$1或外部传参没有被推断出明确的文本类型。解决方法很简单给参数显式加类型WHERE name LIKE $1::text这种问题在常见 ORM 里很少遇到因为驱动通常会把参数类型声明清楚。但在手写 SQL、写存储过程、或者某些查询工具里一不留神就会冒出来。遇到这种报错不要慌不是你 LIKE 语法写错了而是 PostgreSQL 对操作符两侧的类型要求比较严显式类型转换能解决大部分问题。6.4 如果只是判断包含不如用 strpos这里必须给一个非常实用的建议如果你的需求只是判断字符串里是否包含某个子串其实根本不需要 LIKE。PostgreSQL 提供strpos(string, substring)和position(substring in string)它们返回子串第一次出现的位置大于 0 就说明包含。最关键的优点是它们把%和_当作普通字符处理完全没有通配符语义天然不需要转义。写出来就是WHERE strpos(name, 张) 0;等价于LIKE %张%但不会出现用户搜索50%时把%当通配符的问题。类似的还有starts_with(string, prefix)函数等价于LIKE prefix%也是前缀匹配的更清晰替代。性能方面strpos和LIKE %...%都是无法直接利用普通 B-tree 索引的但strpos不引入额外的通配符和转义逻辑在代码维护和 bug 概率上反而更优。如果数据量大还是需要靠 pg_trgm 索引但索引本身是建立在列上的跟查询里用 LIKE 还是 strpos 没有必然关系。6.5 最后一点经验把 LIKE 的决策写进团队的 SQL 规范我现在的习惯是在团队内部 SQL 规范里明确约定几条模糊查询必须参数化禁止字符串拼接用户输入一律先转义再进入 LIKE不允许在返回大结果集的查询里裸用LIKE %keyword%必须评估 pg_trgm 或改写为strpos所有涉及 LIKE 的新查询上线前提交执行计划。这些规则让团队少走了很多弯路。LIKE 是 PostgreSQL 里看似人畜无害、实际最容易在性能和准确性上阴人的语法花半小时吃透它后面省的排查时间远远不止半小时。