如何在PostgreSQL中实现不区分重音搜索且区分重音排序？

核心思路 #

通过分离搜索和排序所使用的排序规则，实现：

• 搜索时忽略重音符号，匹配带变音的目标值
• 排序时遵循波兰语字母规则，保持lubuskie→łódzkie→małopolskie的正确顺序

具体实现步骤 #

1. 配置表列的默认排序规则 #

确保country_region表的symbol列默认使用波兰语区分重音的排序规则，这样排序时会自动遵循波兰语字母顺序：

-- 创建表时指定（若表未创建）
CREATE TABLE country_region (
    symbol TEXT COLLATE "pl_PL.utf8"
);

-- 若表已存在，修改列的默认排序规则
ALTER TABLE country_region ALTER COLUMN symbol TYPE TEXT COLLATE "pl_PL.utf8";

2. 创建不区分重音的搜索专用排序规则 #

创建一个非确定性ICU排序规则，用于忽略重音符号的匹配：

CREATE COLLATION accent_insensitive (
    provider = icu,
    locale = 'und-u-ks-level1-kc-true',
    deterministic = FALSE
);

该规则基于通用Unicode区域，ks-level1表示忽略重音和变音符号，deterministic = FALSE确保非确定性匹配（支持不区分重音的等值比较）。

3. 创建优化搜索性能的索引 #

为避免全表扫描，创建基于上述排序规则的索引：

CREATE INDEX idx_country_region_symbol_accent_insensitive 
ON country_region (symbol COLLATE accent_insensitive);

4. 执行目标查询 #

• 不区分重音搜索：在WHERE子句中指定使用accent_insensitive规则，即可匹配带变音的łódzkie：

SELECT * FROM country_region 
WHERE symbol COLLATE accent_insensitive = 'lodzkie';

• 区分重音排序：直接使用默认排序规则，结果自动遵循波兰语字母顺序：

SELECT * FROM country_region ORDER BY symbol;

原尝试方案的问题说明 #

• 使用und-u-ks-level1-kc-true时，排序会遵循通用Unicode顺序而非波兰语规则，导致łódzkie的位置错误
• 使用pl-u-ks-level1-kc-true时，波兰语ICU规则在level1强度下仍会区分Ł和L，无法实现不区分重音的匹配

替代方案：使用unaccent扩展 #

若不想依赖ICU排序规则，可通过unaccent函数实现需求：

1. 启用扩展：

CREATE EXTENSION IF NOT EXISTS unaccent;

2. 创建索引优化性能：

CREATE INDEX idx_country_region_symbol_unaccent 
ON country_region (unaccent(symbol));

3. 执行搜索：

SELECT * FROM country_region 
WHERE unaccent(symbol) = unaccent('lodzkie');

排序仍使用默认波兰语规则，结果符合预期。

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