如何在PostgreSQL姓名搜索中处理多种撇号变体

临时查询方案（适合偶尔使用） #

直接在查询中用translate函数把所有撇号变体统一转换成标准单引号，再和处理后的搜索词对比：

SELECT last_name
FROM users
WHERE translate(last_name, '`՚′‵', '''') = translate('O''Brien', '`՚′‵', '''');

原理：translate函数会把第二个参数里的每个字符，替换成第三个参数对应位置的字符。这里把所有常见的撇号变体（反引号、亚美尼亚语撇号、prime符号、反向prime）都替换成标准单引号，两边统一后就能匹配所有变体记录。如果还有其他撇号变体，直接加到第二个参数的字符集合里就行。

性能优化方案（适合频繁查询） #

如果经常需要做这种匹配，每次查询都调用translate会有性能损耗，建议创建归一化列并加索引：

1. 新增一个存储归一化后姓氏的生成列：

ALTER TABLE users ADD COLUMN last_name_normalized TEXT GENERATED ALWAYS AS (translate(last_name, '`՚′‵', '''')) STORED;

2. 给归一化列创建索引：

CREATE INDEX idx_users_last_name_normalized ON users(last_name_normalized);

之后查询就可以直接用归一化列，效率更高：

SELECT last_name
FROM users
WHERE last_name_normalized = translate('O''Brien', '`՚′‵', '''');

如果是应用端传入搜索词，也可以在应用层先把输入里的撇号变体转换成标准单引号，再直接匹配last_name_normalized，进一步减少数据库端的计算。

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