PostgreSQL LIKE 子句
PostgreSQL LIKE 子句完全指南
LIKE是文本模式匹配的利器，配合ILIKE、~、~*可实现 模糊搜索、忽略大小写、正则匹配 等高级功能。
本文涵盖 语法、元字符、性能优化、索引支持、常见陷阱、JSON/数组应用、替代方案 等全部内容。
一、LIKE 与 ILIKE 基本语法
WHERE column LIKE pattern
WHERE column ILIKE pattern| 运算符 | 说明 |
|---|---|
LIKE | 大小写敏感，支持 % 和 _ |
ILIKE | 忽略大小写（PostgreSQL 独有），功能同 LIKE |
二、元字符（通配符）
| 字符 | 含义 | 示例 |
|---|---|---|
% | 匹配 0 或多个字符 | 'A%' → Alice, ABC |
_ | 匹配单个字符 | 'A_ce' → Alice |
\% | 转义 % | '100\%' → 匹配 100% |
\_ | 转义 _ | 'A\_B' → 匹配 A_B |
三、准备测试数据
CREATE TABLE products (
id SERIAL PRIMARY KEY,
name TEXT,
code VARCHAR(20),
tags TEXT[],
metadata JSONB
);
INSERT INTO products (name, code, tags, metadata) VALUES
('iPhone 15 Pro', 'IPH-15-PRO', '{"mobile","apple"}', '{"color": "black", "storage": "256GB"}'),
('Samsung Galaxy S24', 'SAM-S24-ULT', '{"mobile","android"}', '{"color": "silver", "storage": "512GB"}'),
('MacBook Air M2', 'MAC-AIR-M2', '{"laptop","apple"}', '{"color": "space gray"}'),
('Dell XPS 13', 'DEL-XPS-13', '{"laptop","windows"}', '{"color": "platinum"}'),
('AirPods Pro 2', 'AIR-PRO-2', '{"audio","apple"}', '{"color": "white"}'),
('Sony WH-1000XM5', 'SONY-XM5', '{"audio"}', '{"color": "black"}');四、基础 LIKE 用法
| 需求 | SQL |
|---|---|
| 以 ‘iPhone’ 开头 | WHERE name LIKE 'iPhone%' |
| 包含 ‘Pro’ | WHERE name LIKE '%Pro%' |
| 以 ‘2’ 结尾 | WHERE name LIKE '%2' |
| 第1个字符任意，第2个是 ‘P’，共5个字符 | WHERE code LIKE '_P___' |
包含百分号 % | WHERE name LIKE '100\%' ESCAPE '\' |
-- 查找所有 Pro 系列
SELECT name FROM products WHERE name LIKE '%Pro%';
-- 结果: iPhone 15 Pro, AirPods Pro 2五、ILIKE：忽略大小写（PostgreSQL 专属）
-- 查找包含 "mac"（不区分大小写）
SELECT name FROM products WHERE name ILIKE '%mac%';
-- 结果: MacBook Air M2注意：
LIKE是大小写敏感的：'MacBook' LIKE '%mac%' → false 'MacBook' ILIKE '%mac%' → true
六、转义特殊字符
-- 查找 code 包含下划线 '_'
SELECT code FROM products WHERE code LIKE '%\_%' ESCAPE '\';
-- 结果: 所有 code（因为都含 -）
-- 查找 name 包含百分号
INSERT INTO products (name) VALUES ('Discount 50%');
SELECT name FROM products WHERE name LIKE '%50\%%' ESCAPE '\';七、性能优化：索引支持
问题：LIKE '%abc%' 无法使用普通 B 树索引
| 模式 | 是否可用索引 |
|---|---|
'abc%' | Yes（左前缀） |
'%abc' | No |
'%abc%' | No |
'_abc' | No |
解决方案
方案 1：前缀匹配建索引
CREATE INDEX idx_prod_name_prefix ON products(name);
-- 加速 'iPhone%'
SELECT * FROM products WHERE name LIKE 'iPhone%';方案 2：表达式索引 + ILIKE
-- 加速忽略大小写搜索
CREATE INDEX idx_prod_name_lower ON products(LOWER(name));
-- 查询
SELECT * FROM products WHERE LOWER(name) ILIKE '%mac%';方案 3：全文搜索（推荐复杂搜索）
-- 添加 tsvector 列
ALTER TABLE products ADD COLUMN search_text TSVECTOR;
UPDATE products SET search_text = to_tsvector('english', name || ' ' || array_to_string(tags, ' '));
CREATE INDEX idx_prod_fts ON products USING GIN(search_text);
-- 搜索
SELECT * FROM products
WHERE search_text @@ plainto_tsquery('macbook OR pro');方案 4：pg_trgm 扩展（模糊匹配）
CREATE EXTENSION IF NOT EXISTS pg_trgm;
-- 创建 trigram 索引
CREATE INDEX idx_prod_name_trgm ON products USING GIN (name gin_trgm_ops);
-- 加速 %abc%
SELECT * FROM products WHERE name ILIKE '%pro%';八、LIKE 在 JSON 和数组中的应用
1. JSON 文本搜索
-- 查找 metadata.color 包含 "black"
SELECT name, metadata->>'color' FROM products
WHERE metadata->>'color' ILIKE '%black%';2. 数组元素匹配
-- 查找 tags 包含 "apple"
SELECT name, tags FROM products
WHERE 'apple' = ANY(tags);
-- 错误！不能直接用 LIKE
-- WHERE tags LIKE '%apple%' → 错误正确方式：
-- 转换数组为字符串
SELECT name FROM products
WHERE array_to_string(tags, ',') ILIKE '%apple%';九、正则表达式替代（~ 系列）
| 运算符 | 说明 |
|---|---|
~ | 正则匹配（区分大小写） |
~* | 正则匹配（忽略大小写） |
!~ | 正则不匹配 |
!~* | 正则不匹配（忽略大小写） |
-- 匹配以数字结尾
SELECT name FROM products WHERE name ~ '\d$';
-- 匹配包含 "Pro" 或 "XM"
SELECT name FROM products WHERE name ~* '(Pro|XM)';性能：正则比
LIKE慢，慎用
十、常见陷阱与解决方案
| 陷阱 | 说明 | 解决方案 |
|---|---|---|
LIKE '%abc%' 无索引 | 全表扫描 | 用 pg_trgm 或全文搜索 |
LIKE 大小写敏感 | 'Apple' LIKE '%apple%' → false | 用 ILIKE |
| 转义字符忘记 | LIKE '%50%%' → 语法错误 | 用 ESCAPE |
NULL 值不匹配 | NULL LIKE '%' → UNKNOWN | 用 IS NOT NULL |
-- 安全写法
SELECT * FROM products
WHERE name IS NOT NULL
AND name ILIKE '%pro%';十一、最佳实践 Checklist
| 项目 | 建议 |
|---|---|
优先用 ILIKE | 忽略大小写 |
| 前缀匹配 | 'abc%' + B 树索引 |
| 复杂搜索 | 用 pg_trgm 或全文搜索 |
避免 %abc% | 除非建 gin_trgm_ops 索引 |
| NULL 安全 | col IS NOT NULL AND col ILIKE ... |
| 转义 | 用 ESCAPE '\' |
十二、一键搜索系统（推荐架构）
-- 1. 启用扩展
CREATE EXTENSION IF NOT EXISTS pg_trgm;
-- 2. 创建组合搜索字段
ALTER TABLE products ADD COLUMN search_text TEXT
GENERATED ALWAYS AS (name || ' ' || array_to_string(tags, ' ')) STORED;
-- 3. 建索引
CREATE INDEX idx_prod_search_trgm ON products USING GIN (search_text gin_trgm_ops);
-- 4. 查询
SELECT name, tags FROM products
WHERE search_text ILIKE '%pro%';十三、快速实战：5 分钟掌握 LIKE
-- 1. 基础
SELECT name FROM products WHERE name LIKE 'iPhone%';
-- 2. 忽略大小写
SELECT name FROM products WHERE name ILIKE '%mac%';
-- 3. 包含下划线
SELECT code FROM products WHERE code LIKE '%\_%' ESCAPE '\';
-- 4. 建索引加速
CREATE INDEX idx_prod_name_trgm ON products USING GIN (name gin_trgm_ops);
SELECT * FROM products WHERE name ILIKE '%pro%';现在就动手：
在 products 表中：
- 查找
name以S开头且包含24的产品 - 查找
code包含下划线_的记录 - 查找
metadata.color包含black的产品 - 启用
pg_trgm，建索引，测试%pro%搜索性能
十四、性能对比（EXPLAIN）
-- 慢查询（无索引）
EXPLAIN ANALYZE SELECT * FROM products WHERE name ILIKE '%pro%';
-- 快查询（有 trgm 索引）
CREATE INDEX idx_prod_name_trgm ON products USING GIN (name gin_trgm_ops);
EXPLAIN ANALYZE SELECT * FROM products WHERE name ILIKE '%pro%';需要我生成：
- 1000 条测试数据 + 模糊搜索压力测试？回复
测试数据 - 全文搜索 vs LIKE 性能对比？回复
性能对比 - Python 动态 LIKE 查询（防注入）？回复
Python LIKE - 实时搜索自动补全？回复
自动补全
随时告诉我！