LIKE 把文本和一个模式比较,模式中 % 匹配任意长度的字符,_ 恰好匹配一个字符,所以 email LIKE '%@gmail.com' 能找到 Gmail 邮箱。LIKE 区分大小写。ILIKE 做同样的匹配但忽略大小写,~ 和 ~* 匹配 POSIX 正则表达式。
结果是 Asha 和 Bella:ILIKE 忽略了 Bella 邮箱中的大写字母,而 Chen 的 .co 不符合结尾。
用 % 和 _ 匹配
整个字符串都必须和模式匹配,所以不带 % 的模式就相当于 =。NOT LIKE 和 NOT ILIKE 取反。
A-___ 匹配 A-100,但不匹配 A-2000,因为每个 _ 恰好代表一个字符。小写的 a-300 只能通过 ILIKE 的判断。
搜索字面的 % 或 _
在模式中,反斜杠会转义它后面的字符,所以 '%50\%%' 能找到包含 50% 的文本。如果数据中本身就有反斜杠,可以用 ESCAPE 换一个转义字符:
SAVE\_% 只匹配 SAVE_10。如果不转义,_ 也会匹配 SAVE10 中的 1。
用 ~ 做正则匹配
正则操作符有 ~(匹配)、~*(匹配,忽略大小写)、!~ 和 !~*(不匹配)。除非用 ^ 和 $ 锚定,否则正则可以匹配字符串中的任何位置:
只有 555-0101 符合带短横线的格式。带 g 标志的 regexp_replace 会删除所有非数字字符,所以 (555) 0199 变成 5550199,call me 变成空字符串。
提取和统计匹配
其他正则函数返回匹配到的内容,而不是真或假:
| 函数 | 返回 |
|---|---|
substring(s from 'pattern') | 第一个匹配,或者第一个括号分组 |
regexp_match(s, 'pattern') | 第一个匹配中的各个分组,类型为 text[] |
regexp_matches(s, 'pattern', 'g') | 每个匹配一行 |
regexp_count(s, 'pattern') | 匹配的次数(PostgreSQL 15 起) |
regexp_substr(s, 'pattern') | 第一个匹配(PostgreSQL 15 起) |
regexp_like(s, 'pattern') | 和 ~ 相同,只是写成函数(PostgreSQL 15 起) |
regexp_split_to_table(s, 'pattern') | 匹配之间的每一段各返回一行 |
substring('Order 4521 shipped' from '\d+') 返回 4521。手册的模式匹配章节列出了所有标志和正则语法。
让模式搜索用上索引
当数据库使用 C 排序规则,或者索引是用 text_pattern_ops 建的时,B-tree 索引可以用于锚定在开头的模式,比如 LIKE 'abc%':
CREATE INDEX users_email_prefix ON users (email text_pattern_ops);
以 % 开头的模式、ILIKE 和大多数正则表达式都用不上 B-tree。对于这些情况,pg_trgm 扩展提供了三元组(trigram)索引,可以用于 LIKE、ILIKE、~ 和 ~*:
CREATE EXTENSION pg_trgm;
CREATE INDEX users_email_trgm ON users USING gin (email gin_trgm_ops);
如果只是不区分大小写的相等比较而不是模式匹配,更简单的做法是在 lower(email) 上建索引,并用 lower(email) = 'ben@work.io' 查询。lower、position 和 split_part 见字符串函数页面。
常见问题
PostgreSQL 中怎么判断一个字符串是否包含某个子串?
用 col LIKE '%text%',或者用 col ILIKE '%text%' 忽略大小写。position('text' in col) > 0 和 strpos(col, 'text') > 0 也可以,而且不需要转义 % 或 _。
PostgreSQL 的 LIKE 区分大小写吗?
区分。'Apple' LIKE 'a%' 为假。可以用 ILIKE,或者比较 lower(col) LIKE 'a%',或者使用不区分大小写的正则操作符 ~*。
怎么一次匹配多个模式?
把 LIKE ANY 和数组结合起来:name LIKE ANY (ARRAY['a%', 'b%']) 在任意一个模式匹配时为真,LIKE ALL 则要求全部匹配。正则中的分支写法,比如 name ~ '^(a|b)',效果也一样。
PostgreSQL 中的 SIMILAR TO 是什么?
它是 SQL 标准中 LIKE 和正则表达式的混合体:使用 % 和 _,再加上 |、*、+ 和分组,而且必须匹配整个字符串。通常 LIKE 或 ~ 操作符更清楚。