MS SQL全文搜索忽略特殊字符的最优实现方案咨询

简化数据库结构 #

• [places]表
  • name NVARCHAR(255)
  • description TEXT（通常包含大量文本）
  • region_id INT（外键）
• [regions]表
  • id INT（主键）
  • name NVARCHAR(255)
• [regions_translations]表
  • lang_code NVARCHAR(5)（外键）
  • label NVARCHAR(255)
  • region_id INT（外键）

注：实际数据库中[places]表还有更多可搜索字段，另有与[regions]结构类似的[countries]表。

搜索需求 #

1. 基于name、description及region label进行搜索，逻辑等价于name LIKE '%text%' OR description LIKE '%text%' OR regions_translations.label LIKE '%text%'；
2. 忽略所有特殊字符（如Ą、Ć、Ó、Š、Ö、Ü等）：例如搜索PO ZVAIGZDEM时能返回名称为PO ŽVAIGŽDĖM的地点，同时用带重音的正确字符搜索也能返回该记录；
3. 搜索速度较快。

已尝试的方案 #

1. 创建新列searchable_content，对文本进行规范化（将Ą替换为A、Ö替换为O等），执行SELECT ... FROM places WHERE searchable_content LIKE '%text%'，但速度较慢；
2. 为places和regions_translations表添加全文搜索索引，速度较快，但之前找不到忽略特殊字符的方法（涉及多种语言，指定索引语言无效）；
3. 按方案1创建新列，仅对该列添加全文索引，速度比方案1快（可能因为无需关联表），且可手动规范化内容，但感觉并非最佳方案。

问题 #

哪种方案是最优选择？我的首要需求是忽略特殊字符。

编辑补充：ALTER FULLTEXT CATALOG [catalog_name] REBUILD WITH ACCENT_SENSITIVITY = OFF可能是解决特殊字符问题的方案（需进一步测试）——此前因查询过快，索引未重建导致无结果返回。

最优方案推荐 #

优先选择方案2结合全文目录的重音不敏感设置，理由如下：

1. 性能最优：全文索引本身就是为大文本搜索场景设计的，比LIKE '%...%'的模糊查询效率高几个量级，尤其是当places表数据量较大、description字段内容较多时，优势会更明显；
2. 满足重音忽略需求：执行ALTER FULLTEXT CATALOG [catalog_name] REBUILD WITH ACCENT_SENSITIVITY = OFF后，全文索引会自动忽略重音和特殊字符的差异，不管搜索时输入带重音还是不带重音的字符，都能匹配到目标记录。之前的无结果问题确实大概率是修改设置后未重建索引导致的，执行重建操作后即可生效；
3. 无需冗余字段：相比方案1和3，不需要额外维护searchable_content这类冗余列，避免了数据同步的麻烦（比如更新places.name或regions_translations.label时，还要同步更新规范化列，增加开发和维护成本）。

如果测试后发现多语言场景下仍有个别字符匹配问题，可以考虑结合全文索引的自定义同义词库，或者在创建全文索引时选择支持多语言的Neutral（中性）语言，进一步优化匹配效果。

对比其他方案的劣势：

• 方案1：LIKE '%...%'无法利用普通索引，全表扫描的速度会随着数据量增长急剧下降，完全不适合大数据量场景；
• 方案3：虽然比方案1快，但冗余列的维护成本不可忽视，而且手动规范化字符需要覆盖所有可能的特殊字符，容易遗漏，不如全文索引的重音不敏感设置来得全面。

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