不使用unaccent扩展如何在PostgreSQL中实现变音符号不敏感查询

方案1：原生translate()手动映射（最通用） #

PostgreSQL 9.3原生支持translate()函数，可通过手动维护变音字符到无变音字符的映射规则实现去重音，再搭配lower()函数实现同时忽略大小写。

步骤1：定义自定义去变音函数 #

CREATE OR REPLACE FUNCTION custom_unaccent(text) 
RETURNS text AS $$
SELECT translate(
    $1,
    -- 此处为需要映射的变音字符列表，可根据业务使用的语言自行扩充
    'ÀÁÂÃÄÅàáâãäåÒÓÔÕÖØòóôõöøÈÉÊËèéêëÇçÌÍÎÏìíîïÙÚÛÜùúûüÿÑñ',
    -- 对应位置为无变音的目标字符
    'AAAAAAaaaaaaOOOOOOooooooEEEEeeeeCcIIIIiiiiUUUUuuuuyNn'
);
$$ LANGUAGE sql STABLE;

如果需要处理其他语种的特殊变音字符（如德语ß、波兰语ł等），只需在第二个参数字符串末尾添加对应字符，第三个参数字符串末尾添加对应的映射结果即可。

步骤2：查询使用示例 #

-- 等值匹配示例
SELECT * FROM 你的表名
WHERE lower(custom_unaccent(字段名)) = lower(custom_unaccent('你的查询字符串'));

-- 模糊匹配示例
SELECT * FROM 你的表名
WHERE lower(custom_unaccent(字段名)) LIKE lower(custom_unaccent('%查询关键字%'));

性能优化 #

如果查询频率较高，可创建函数索引避免全表扫描：

CREATE INDEX idx_你的表名_字段名_unaccent_lower ON 你的表名 (lower(custom_unaccent(字段名)));

该方案无需任何扩展安装权限，适配所有PostgreSQL 9.3及以上版本，仅需根据业务覆盖的字符范围补充完善translate函数的映射列表即可。

方案2：citext类型简化忽略大小写逻辑（可选） #

如果你的环境允许启用citext内置类型，可将目标字段改为citext类型，省略查询时的lower()调用：

-- 修改字段类型（仅需执行一次）
ALTER TABLE 你的表名 ALTER COLUMN 字段名 TYPE citext;

-- 查询时无需额外调用lower()
SELECT * FROM 你的表名
WHERE custom_unaccent(字段名) = custom_unaccent('你的查询字符串');

如果无权限启用citext，直接使用方案1的lower()实现即可。

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