PostgreSQL / 系统课时

模糊匹配与正则表达式

模糊匹配与正则表达式

本课解决“只知道部分文本时怎样查找记录”的问题。完成后,你能使用 LIKE、ILIKE 和 PostgreSQL 正则运算符完成前缀、包含和格式匹配。

学习目标

  • 掌握 LIKE 中百分号和下划线的含义
  • 能使用 ILIKE 进行不区分大小写匹配
  • 能用正则运算符处理更复杂的文本规则

LIKE 与通配符

LIKE 使用模式字符串匹配文本。百分号 % 表示任意长度字符,包括零个字符;下划线 _ 表示恰好一个字符。

例如,name LIKE 'Post%' 匹配以 Post 开头的文本,name LIKE '%SQL%' 匹配任意位置包含 SQL 的文本。

ILIKE 与正则运算符

LIKE 默认区分大小写,ILIKE 是 PostgreSQL 提供的不区分大小写匹配。正则运算符 ~ 表示区分大小写匹配,~* 表示不区分大小写匹配;对应的否定形式是 !~ 和 !~*。

下面的完整示例同时演示 ILIKE 和正则表达式,查询名称中包含 sql 且编码符合“两个大写字母加三位数字”的课程:

sql
CREATE TEMP TABLE courses (
    course_id integer,
    course_name text,
    course_code text
);

INSERT INTO courses VALUES
    (1, 'PostgreSQL Basics', 'DB101'),
    (2, 'Advanced SQL', 'DB205'),
    (3, 'Python Web', 'PY301'),
    (4, 'sql Practice', 'LAB01');

SELECT course_id, course_name, course_code
FROM courses
WHERE course_name ILIKE '%sql%'
  AND course_code ~ '^[A-Z]{2}[0-9]{3}$'
ORDER BY course_id;

预期输出

text
CREATE TABLE
INSERT 0 4
 course_id |    course_name    | course_code
-----------+-------------------+-------------
         1 | PostgreSQL Basics | DB101
         2 | Advanced SQL      | DB205
(2 rows)

LAB01 不符合正则,因为它包含三个字母和两位数字。PY301 虽然编码符合格式,但课程名不包含 sql,因此也不会返回。

转义通配符

当要匹配文本中的真实百分号或下划线时,不能把它们直接当普通字符。可以使用 ESCAPE 指定转义字符,例如:

sql
SELECT '折扣_10%' LIKE '折扣!_10!%' ESCAPE '!' AS matched;

预期输出

text
 matched
---------
 t
(1 row)

这里的 !_ 和 !% 分别表示真实下划线和百分号,而不是通配符。

性能边界

前缀匹配如 name LIKE 'Post%' 更有机会使用适合的索引;以百分号开头的包含匹配如 LIKE '%sql%' 通常无法直接利用普通 B-tree 索引。

大量文本模糊搜索可考虑 pg_trgm 扩展和 GIN/GiST 索引,但应先确认查询规模和性能瓶颈,再增加索引复杂度。

按自己的节奏,确认真正理解。

目录与阅读设置

阅读设置

17

记录一个想法

模糊匹配与正则表达式

0 / 10000 · 笔记保存到账号。未提交的修改会在关闭、离开或刷新后丢失。

登录哪都通