PostgreSQL - LIKE 子句

PostgreSQL - 使用 LIKE、ILIKE 和正则表达式进行现代模式匹配

PostgreSQL 提供了强大的运算符用于文本模式匹配。最常见的是 LIKE 运算符，它使用通配符进行简单的模式匹配。对于更高级的需求，PostgreSQL 还提供了不区分大小写的 ILIKE 运算符和完整的正则表达式支持。

LIKE 运算符

LIKE 运算符在字符串匹配指定模式时返回 true。它区分大小写，并使用两个标准通配符：

• 百分号 (%)：代表零个、一个或多个字符。
• 下划线 (_)：代表单个字符。

语法

SELECT column_name(s)
FROM table_name
WHERE column_name LIKE pattern;

示例

考虑 COMPANY 表：

id | name  | age | address     | salary
----+-------+-----+-------------+--------
  1 | Paul  |  32 | California  |  20000
  2 | Allen |  25 | Texas       |  15000
  3 | Teddy |  23 | Norway      |  20000
  4 | Mark  |  25 | Rich-Mond   |  65000

以下查询查找所有名字以 ‘P’ 开头的员工：

SELECT * FROM COMPANY WHERE name LIKE 'P%';

结果：

id | name | age | address    | salary
----+------+-----+------------+--------
  1 | Paul |  32 | California |  20000

ILIKE 运算符（不区分大小写）

ILIKE 是 PostgreSQL 特有的扩展，其行为与 LIKE 完全相同，但匹配不区分大小写。这在应用程序中非常有用，并且通常是首选。

-- 这将匹配 'Paul'、'paul'、'PAul' 等。
SELECT * FROM COMPANY WHERE name ILIKE 'p%';

高级模式匹配

转义通配符

如果您需要搜索字面量 ’%’ 或 ’_’ 字符，您必须使用 ESCAPE 子句来指定转义字符。

-- 查找名称中包含 '50%' 的产品
SELECT * FROM products WHERE product_name LIKE '%50\%%' ESCAPE '\';

正则表达式

对于比 LIKE 能处理的更复杂的模式，PostgreSQL 支持使用 ~ 运算符的 POSIX 正则表达式。

• ~ : 区分大小写的正则表达式匹配。
• ~* : 不区分大小写的正则表达式匹配。
• !~ : 区分大小写的正则表达式不匹配。
• !~* : 不区分大小写的正则表达式不匹配。

-- 查找以 'en' 或 'dy' 结尾的名称
SELECT * FROM COMPANY WHERE name ~* '(en|dy)$';

这将匹配 ‘Allen’ 和 ‘Teddy’。

最佳实践与性能

处理非文本类型

模式匹配运算符作用于文本类型。如果您的列是数字或其他类型，您必须显式地将其转换为 TEXT 类型。

SELECT * FROM COMPANY WHERE salary::text LIKE '2%';

性能考量

警告：以通配符（% 或 _）开头的模式会阻止 PostgreSQL 使用该列上的标准 B-tree 索引，这可能导致全表扫描，从而在大表上性能下降。

对于需要快速全文搜索或高效通配符搜索的应用程序，请考虑以下解决方案：

• pg_trgm 扩展：安装 pg_trgm 扩展并创建 GIN 或 GiST 索引。这使得 PostgreSQL 能够非常快速地索引和搜索子字符串以及 LIKE/ILIKE 模式，即使通配符在开头。
• 全文搜索：对于在自然语言文档中搜索单词和短语，请使用 PostgreSQL 内置的全文搜索（Full-Text Search）功能，它比 LIKE 更强大且可配置。

-- 创建 trigram 索引以加速 LIKE/ILIKE 搜索的示例
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX idx_company_name_trgm ON COMPANY USING gin (name gin_trgm_ops);