如何让PostgreSQL 9.6的pg_trgm匹配更宽松？

嘿，我明白你在pg_trgm字符串匹配上遇到的痛点了——默认的三元组匹配没法覆盖任意中间缺字母、重音+重复字符的情况，刚好在PostgreSQL 9.6里有办法解决，我给你一步步拆解方案：

1. 先做字符串归一化：处理重音与重复字符 #

首先要把输入和目标字符串统一成“干净”的格式，解决sofáa→sofa这类问题：

• 去掉重音：用PostgreSQL的unaccent扩展，它能把带重音的字符转成普通字符（比如á→a）
• 合并重复字符：用正则表达式把连续重复的字符只保留一个（比如aa→a）

先安装必要的扩展：

-- 安装unaccent扩展（处理重音）
CREATE EXTENSION IF NOT EXISTS unaccent;
-- 安装fuzzystrmatch扩展（后面用编辑距离）
CREATE EXTENSION IF NOT EXISTS fuzzystrmatch;

可以封装一个自定义归一化函数方便复用：

CREATE OR REPLACE FUNCTION normalize_string(input_str text)
RETURNS text AS $$
BEGIN
  RETURN regexp_replace(unaccent(input_str), '(.)\1+', '\1', 'g');
END;
$$ LANGUAGE plpgsql IMMUTABLE;

这个函数会把sofáa转成sofa，把poduto保持为poduto，完美处理重音和重复字符问题。

2. 结合pg_trgm与编辑距离，覆盖任意缺/多字母场景 #

pg_trgm的三元组匹配擅长处理相似的连续字符，但对中间缺字母的情况（比如poduto→produtos）表现力弱。这时候结合Levenshtein编辑距离就刚好——它能计算两个字符串之间最少需要多少次插入、删除、替换操作才能匹配，完全覆盖你说的缺字母、多字母的错误类型。

示例查询 #

假设你的表是products，目标字段是name，查询语句可以这么写：

SELECT name,
       similarity(normalize_string('poduto'), normalize_string(name)) AS trgm_similarity,
       levenshtein(normalize_string('poduto'), normalize_string(name)) AS edit_distance
FROM products
WHERE 
  -- 同时满足相似性和编辑距离，平衡准确率和召回率
  similarity(normalize_string('poduto'), normalize_string(name)) > 0.2
  OR levenshtein(normalize_string('poduto'), normalize_string(name)) <= 2
ORDER BY 
  -- 按相似度和编辑距离排序，最匹配的结果放前面
  trgm_similarity DESC,
  edit_distance ASC;

• similarity > 0.2：你可以根据实际数据调整这个阈值，数值越低召回的结果越多，越高则匹配越精准
• levenshtein <=2：允许最多2次编辑操作，完全覆盖你提到的“缺一个字母”（比如poduto→produtos编辑距离1）、“多一个字母”（sofáa→sofa编辑距离1）的场景

3. 优化查询性能 #

如果你的表数据量较大，直接用函数计算会很慢，建议给归一化后的字段创建GIN索引（pg_trgm推荐用GIN索引）：

CREATE INDEX idx_normalized_name_trgm ON products 
USING GIN (normalize_string(name) gin_trgm_ops);

这个索引会大幅加速similarity和%操作符的匹配，让查询更快。

4. 参数调整小技巧 #

• 如果漏匹配太多：可以把相似度阈值调低（比如0.15），或者把编辑距离上限调高（比如3）
• 如果误匹配太多：可以提高相似度阈值（比如0.25），或者降低编辑距离上限（比如1）
• 测试时可以单独查看levenshtein的结果，确认哪些匹配是符合预期的

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