PostgreSQL WHERE查询中%与ILIKE的区别是什么？

好问题！这两个查询乍一看好像都是找和'w4'相关的邮编，但底层逻辑、匹配规则和适用场景其实有不小的差异，我给你逐一拆解：

1. 核心匹配逻辑完全不同 #

• postcode % 'w4%'（pg_trgm的相似度匹配）
  这里的%是pg_trgm模块提供的相似度比较操作符，右边的'w4%'是一个完整的字符串（注意：这里的%不是通配符！它就是你输入的模式的一部分）。pg_trgm会把字段值和输入模式都拆分成「三元组」（比如'w4%'会被处理成' w4'、'w4%'这样的字符组合），然后计算两者的相似度，只要相似度超过默认阈值（0.3）就会返回结果。简单说，这是找和'w4%'这个字符串本身相似的记录，而不是找以'w4'开头的记录。

• postcode ILIKE 'w4%'（内置前缀匹配）
  这是PostgreSQL原生的不区分大小写模式匹配，这里的%是标准SQL通配符，代表「任意长度的任意字符（包括空）」。它的逻辑很直接：只返回那些邮编以'w4'开头（不区分大小写，比如'W4'、'w4XYZ'都符合）的记录，是严格的前缀匹配，没有“相似度”的概念。

2. 性能与索引支持的差异 #

• 对于pg_trgm的%操作符：
  你可以创建GIN或GIST索引来加速查询，比如：

  CREATE INDEX idx_trgm_postcode ON tbl USING GIN (postcode gin_trgm_ops);
  

  这种索引适合处理各种模糊匹配场景，哪怕是中间包含、后缀匹配，只要相似度够就能高效检索。

• 对于ILIKE 'w4%'：
  因为是前缀匹配（模式开头没有通配符），你可以创建带text_pattern_ops的btree索引来优化性能：

  CREATE INDEX idx_postcode_like ON tbl (postcode text_pattern_ops);
  

  但如果你的模式是'%w4'（后缀匹配）或者'%w4%'（包含匹配），btree索引就派不上用场了，这时候pg_trgm的索引反而更靠谱。

3. 返回结果的实际差异 #

举个具体例子，假设你的表中有这些邮编：'W41'、'W4A'、'W5'、'XW4'

• ILIKE 'w4%'会返回：'W41'、'W4A'（只有以'w4'开头的记录）
• postcode % 'w4%'可能返回：'W41'、'W4A'、'XW4'（因为'XW4'和'w4%'的三元组相似度足够高），但不会返回'W5'（相似度不够）

4. 适用场景建议 #

• 如果你需要严格的前缀/后缀/精确包含匹配，逻辑明确，用ILIKE（区分大小写的话用LIKE）更合适，索引优化得当的话性能也很好。
• 如果你需要模糊相似度匹配（比如用户输入了拼写错误的邮编，或者想找相似的字符串），用pg_trgm的%操作符更合适，它的容错性更高，能基于字符组合的相似性返回结果。

内容的提问来源于stack exchange，提问作者rex