PostgreSQL模糊匹配避坑指南：那些年我们踩过的转义字符和大小写坑

PostgreSQL模糊匹配实战：从转义陷阱到性能调优的深度解析

模糊匹配是数据库查询中最常见也最让人头疼的需求之一。无论是用户搜索、日志分析还是数据清洗，我们总免不了要和那些不完整的、大小写混乱的或者包含特殊字符的字符串打交道。PostgreSQL在这方面提供了丰富的武器库，但选择不当的武器往往会让开发者陷入性能泥潭。

我见过太多团队在模糊查询上栽跟头——有的因为转义字符处理不当导致安全漏洞，有的因为大小写敏感问题让用户搜索体验糟糕，更常见的是在百万级数据表上执行LIKE '%keyword%'查询时，眼睁睁看着数据库CPU飙升到100%。这篇文章将带你深入PostgreSQL模糊匹配的各个层面，从基础语法到高级优化，分享我在实际项目中积累的经验和教训。

1. 基础操作符：LIKE、ILIKE与SIMILAR TO的实战选择

1.1 LIKE操作符：最熟悉的陌生人

LIKE是SQL标准中的模糊匹配操作符，几乎所有开发者都接触过它。但你真的了解它的所有细节吗？

-- 基本用法：%匹配任意多个字符，_匹配单个字符
SELECT * FROM products WHERE name LIKE 'MacBook%';
SELECT * FROM users WHERE username LIKE 'john_doe';

这里有个常见的误区：很多人以为LIKE只能用于字符串开头或结尾的匹配。实际上，%可以出现在模式的任何位置：

-- 查找包含"pro"的所有产品
SELECT * FROM products WHERE name LIKE '%pro%';

-- 查找以"pro"开头且以"max"结尾的产品
SELECT * FROM products WHERE name LIKE 'pro%max';

转义字符的坑：当我们需要匹配包含%或_的字符串时，问题就来了。假设我们有一个产品表，其中包含名为"50%_discount"的记录：

-- 错误写法：这会匹配所有以"50"开头、任意字符、然后"discount"结尾的记录
SELECT * FROM products WHERE name LIKE '50%_discount';

-- 正确写法：使用反斜杠转义
SELECT * FROM products WHERE name LIKE '50\%\_discount';

-- 或者使用ESCAPE子句指定自定义转义字符
SELECT * FROM products WHERE name LIKE '50@%_discount' ESCAPE '@';

注意：在PostgreSQL中，默认的转义字符是反斜杠（\），但如果你在字符串常量中使用了反斜杠，可能需要双写。更好的做法是使用ESCAPE子句明确指定转义字符，避免混淆。

1.2 ILIKE：忽略大小写的救星

在用户搜索场景中，大小写敏感往往是糟糕体验的根源。用户搜索"macbook"时，我们肯定希望同时返回"MacBook"、"MACBOOK"等所有变体。

-- LIKE区分大小写
SELECT * FROM products WHERE name LIKE 'macbook%'; 
-- 可能返回0条记录，如果表中只有"MacBook"

-- ILIKE不区分大小写
SELECT * FROM products WHERE name ILIKE 'macbook%';
-- 返回所有大小写变体

性能提示：ILIKE虽然方便，但在大数据集上可能比LIKE慢。如果数据量很大且性能是关键考虑因素，可以考虑在存储时统一大小写，或者使用函数索引。

1.3 SIMILAR TO：正则表达式的轻量级入口

SIMILAR TO是SQL:1999标准引入的操作符，它结合了LIKE的简单性和正则表达式的部分能力：

-- 使用SIMILAR TO进行模式匹配
SELECT * FROM products WHERE name SIMILAR TO 'Mac(B|P)ook%';

-- 等价的正则表达式写法
SELECT * FROM products WHERE name ~ 'Mac(B|P)ook.*';

SIMILAR TO支持的正则表达式元字符有限，主要包括：