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 索引,但应先确认查询规模和性能瓶颈,再增加索引复杂度。
按自己的节奏,确认真正理解。