Menu

PostgreSQL 模糊查询:LIKE、ILIKE 与正则表达式

LIKE 用 % 和 _ 按模式匹配文本,ILIKE 做同样的匹配但忽略大小写,~ 系列操作符匹配 POSIX 正则表达式。

本页包含可运行的编辑器。编辑、运行,立即查看输出。

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 或 ~ 操作符更清楚。

Coddy 编程语言插图

和 Coddy 一起学编程

开始