PostgreSQL LIKE 操作符

什么是 LIKE？

LIKE 操作符用于 WHERE 子句中，用于搜索列中的指定模式。它是 PostgreSQL 中模式匹配和文本搜索的重要工具。

基本语法

SELECT column1, column2, ...
FROM table_name
WHERE column LIKE pattern;

通配符

PostgreSQL LIKE 支持两个通配符：

通配符	描述	示例
%	匹配零个或多个字符	'a%' 匹配 "a"、"ab"、"abc"
_	匹配恰好一个字符	'a_' 匹配 "ab"、"ac" 但不匹配 "a" 或 "abc"

基本 LIKE 示例

示例 1：以...开头

-- 查找名字以 'John' 开头的客户
SELECT * FROM customers
WHERE customer_name LIKE 'John%';

-- 查找以 'Pro' 开头的产品
SELECT product_name, price
FROM products
WHERE product_name LIKE 'Pro%';

示例 2：以...结尾

-- 查找以 '@gmail.com' 结尾的邮箱
SELECT * FROM users
WHERE email LIKE '%@gmail.com';

-- 查找以 '.pdf' 结尾的文件
SELECT filename FROM documents
WHERE filename LIKE '%.pdf';

示例 3：包含

-- 查找名字中任何位置包含 'son' 的客户
SELECT * FROM customers
WHERE customer_name LIKE '%son%';

-- 查找包含 'phone' 的产品
SELECT * FROM products
WHERE product_name LIKE '%phone%';

示例 4：使用下划线匹配精确长度

-- 查找 3 个字母的代码
SELECT * FROM country_codes
WHERE code LIKE '___';

-- 查找格式为 XXX-XXXX 的电话号码
SELECT * FROM contacts
WHERE phone LIKE '___-____';

组合通配符

-- 以 'A' 开头并以 'n' 结尾
SELECT * FROM customers
WHERE customer_name LIKE 'A%n';

-- 第二个字符是 'a'
SELECT * FROM products
WHERE product_name LIKE '_a%';

-- 包含 'book' 并以 's' 结尾
SELECT * FROM products
WHERE product_name LIKE '%book%s';

大小写敏感性

LIKE（区分大小写）

-- 区分大小写的搜索
SELECT * FROM products
WHERE product_name LIKE 'iPhone%';  -- 匹配 'iPhone 12' 但不匹配 'iphone 12'

ILIKE（不区分大小写）

-- 不区分大小写的搜索
SELECT * FROM products
WHERE product_name ILIKE 'iphone%';  -- 匹配 'iPhone 12'、'iphone 12'、'IPHONE 12'

-- 不区分大小写的邮箱搜索
SELECT * FROM users
WHERE email ILIKE '%@GMAIL.COM';  -- 匹配任何大小写变体

NOT LIKE

查找不匹配模式的行：

-- 查找不是来自美国的客户
SELECT * FROM customers
WHERE country NOT LIKE 'USA';

-- 查找非 PDF 文件
SELECT * FROM documents
WHERE filename NOT LIKE '%.pdf';

-- 排除测试邮箱
SELECT * FROM users
WHERE email NOT LIKE '%test%'
  AND email NOT LIKE '%demo%';

转义特殊字符

当需要搜索字面的 % 或 _ 字符时：

-- 使用 ESCAPE 搜索字面下划线
SELECT * FROM products
WHERE product_name LIKE '%\_off%' ESCAPE '\';

-- 搜索字面百分号
SELECT * FROM discounts
WHERE description LIKE '%50\%%' ESCAPE '\';

-- 替代方案：使用不同的转义字符
SELECT * FROM products
WHERE product_name LIKE '%#_off%' ESCAPE '#';

LIKE 与多个条件

OR 条件

-- 查找来自多个城市的客户
SELECT * FROM customers
WHERE city LIKE 'New%'
   OR city LIKE 'Los%'
   OR city LIKE 'San%';

AND 条件

-- 查找包含 'phone' 且以 'Smart' 开头的产品
SELECT * FROM products
WHERE product_name LIKE 'Smart%'
  AND product_name LIKE '%phone%';

性能优化

1. 前导通配符性能

-- 慢：无法使用索引
SELECT * FROM customers
WHERE customer_name LIKE '%son';

-- 快：可以使用索引
SELECT * FROM customers
WHERE customer_name LIKE 'John%';

2. 使用索引

-- 为 LIKE 查询创建索引
CREATE INDEX idx_customers_name ON customers(customer_name);

-- 对于不区分大小写的搜索，使用表达式索引
CREATE INDEX idx_customers_name_lower ON customers(LOWER(customer_name));

-- 使用索引的查询
SELECT * FROM customers
WHERE LOWER(customer_name) LIKE 'john%';

3. 全文搜索替代方案

对于复杂的文本搜索，考虑使用全文搜索：

-- 创建全文搜索索引
CREATE INDEX idx_products_fts ON products 
USING gin(to_tsvector('english', product_name));

-- 全文搜索（对于复杂模式更快）
SELECT * FROM products
WHERE to_tsvector('english', product_name) @@ to_tsquery('english', 'phone');

实际应用示例

示例 1：邮箱验证

-- 查找有效的邮箱模式
SELECT * FROM users
WHERE email LIKE '%_@__%.__%';

-- 查找特定邮箱域
SELECT * FROM users
WHERE email ILIKE '%@gmail.com'
   OR email ILIKE '%@yahoo.com'
   OR email ILIKE '%@hotmail.com';

示例 2：电话号码模式

-- 查找美国电话号码（XXX-XXX-XXXX）
SELECT * FROM contacts
WHERE phone LIKE '___-___-____';

-- 查找以 +1 开头的国际号码
SELECT * FROM contacts
WHERE phone LIKE '+1%';

示例 3：产品搜索

-- 按关键字搜索产品
SELECT product_id, product_name, price
FROM products
WHERE product_name ILIKE '%laptop%'
   OR product_name ILIKE '%notebook%'
   OR description ILIKE '%laptop%'
ORDER BY price;

示例 4：文件管理

-- 查找图像文件
SELECT * FROM files
WHERE filename ILIKE '%.jpg'
   OR filename ILIKE '%.png'
   OR filename ILIKE '%.gif';

-- 查找特定年份的文档
SELECT * FROM documents
WHERE filename LIKE '%2024%';

LIKE 与其他子句

与 JOIN

-- 查找来自特定城市的客户的订单
SELECT o.order_id, c.customer_name, c.city
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE c.city LIKE 'New%';

与 GROUP BY

-- 按城市前缀统计客户
SELECT 
    CASE 
        WHEN city LIKE 'New%' THEN 'New Cities'
        WHEN city LIKE 'San%' THEN 'San Cities'
        ELSE 'Other'
    END as city_group,
    COUNT(*) as customer_count
FROM customers
GROUP BY city_group;

与子查询

-- 查找匹配模式的类别中的产品
SELECT * FROM products
WHERE category_id IN (
    SELECT category_id
    FROM categories
    WHERE category_name LIKE '%Electronics%'
);

高级模式

模式 1：多词搜索

-- 查找包含多个关键字的产品
SELECT * FROM products
WHERE product_name ILIKE '%wireless%'
  AND product_name ILIKE '%mouse%';

模式 2：排除模式

-- 查找活跃产品，排除测试项目
SELECT * FROM products
WHERE status = 'active'
  AND product_name NOT LIKE '%test%'
  AND product_name NOT LIKE '%sample%'
  AND product_name NOT LIKE '%demo%';

模式 3：复杂邮箱过滤

-- 查找企业邮箱（排除常见免费提供商）
SELECT * FROM users
WHERE email LIKE '%@%.%'
  AND email NOT ILIKE '%@gmail.com'
  AND email NOT ILIKE '%@yahoo.com'
  AND email NOT ILIKE '%@hotmail.com'
  AND email NOT ILIKE '%@outlook.com';

SIMILAR TO（SQL 标准）

PostgreSQL 还支持 SIMILAR TO 用于类似正则表达式的模式：

-- SIMILAR TO 使用 SQL 标准正则表达式
SELECT * FROM products
WHERE product_name SIMILAR TO '%(phone|tablet)%';

-- 等同于
SELECT * FROM products
WHERE product_name LIKE '%phone%'
   OR product_name LIKE '%tablet%';

正则表达式（更强大）

对于复杂模式，使用正则表达式操作符：

-- 使用 ~（正则匹配，区分大小写）
SELECT * FROM products
WHERE product_name ~ '^(iPhone|Samsung)';

-- 使用 ~*（正则匹配，不区分大小写）
SELECT * FROM products
WHERE product_name ~* '^(iphone|samsung)';

-- 使用 !~（正则不匹配）
SELECT * FROM products
WHERE product_name !~ 'test|demo|sample';

常见错误和解决方案

错误 1：忘记通配符

-- 错误：仅精确匹配
SELECT * FROM customers
WHERE customer_name LIKE 'John';

-- 正确：使用通配符
SELECT * FROM customers
WHERE customer_name LIKE '%John%';

错误 2：大小写敏感性问题

-- 由于大小写可能遗漏结果
SELECT * FROM products
WHERE product_name LIKE 'iphone%';

-- 更好：使用 ILIKE
SELECT * FROM products
WHERE product_name ILIKE 'iphone%';

错误 3：前导通配符的性能问题

-- 慢：无法使用索引
SELECT * FROM customers
WHERE customer_name LIKE '%Smith';

-- 考虑使用全文搜索以获得更好的性能
SELECT * FROM customers
WHERE to_tsvector('english', customer_name) @@ to_tsquery('english', 'Smith');

最佳实践

1. 使用 ILIKE 进行不区分大小写搜索：比使用 LOWER() 与 LIKE 更易读
2. 避免前导通配符：它们会阻止索引使用
3. 创建适当的索引：对不区分大小写的搜索使用表达式索引
4. 考虑全文搜索：用于复杂的文本搜索
5. 转义特殊字符：搜索字面 % 或 _ 时
6. 使用特定模式：更具体的模式更快
7. 测试性能：使用 EXPLAIN ANALYZE 分析查询

总结

LIKE 操作符是模式匹配的重要工具：

• 使用 %（零个或多个字符）和 _（恰好一个字符）
• LIKE 区分大小写，ILIKE 不区分大小写
• NOT LIKE 排除匹配的模式
• 前导通配符会阻止索引使用
• 对于复杂模式考虑使用全文搜索
• 使用 ESCAPE 转义字面通配符
• 正则表达式提供更强大的模式匹配

掌握 LIKE 对于在 PostgreSQL 中进行有效的文本搜索和过滤至关重要。