企业官网建设流程全解析

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任何一个为 NULL，LIKE 的结果不是 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'; -- true

ILIKE本质上相当于把两边都转成小写再做等价比较，你可以理解为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 collation），LIKE 的前缀优化可能不可用，因为非确定性 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 '1'='1之类的恶意内容，把整个查询语义改掉。这已经不是 LIKE 的问题，而是最经典的 SQL 注入漏洞。

正确做法永远是参数化查询。PostgreSQL 的$1占位符配合预处理语句可以安全地处理输入的搜索词。我在 Python 的 psycopg2 里通常这样写：

cur.execute( "SELECT * FROM users WHERE name ILIKE '%' || %s || '%'", (keyword,) )

Java JDBC 里用?占位符同理。ORM 框架中，MyBatis 可以写成：

<select id="searchUsers" resultType="User"> SELECT * FROM users WHERE name LIKE CONCAT('%', #{keyword, jdbcType=VARCHAR}, '%') </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 二者在常见场景下的等价写法

正则在单个字符层级上更灵活。比如查找所有以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 里看似人畜无害、实际最容易在性能和准确性上阴人的语法，花半小时吃透它，后面省的排查时间远远不止半小时。