PostgreSQL存储大小写不敏感唯一邮箱：citext与非确定性排序规则选型

方案优劣对比 #

方案一：citext 扩展 #

• 优势
  • 兼容所有主流 PostgreSQL 版本，无最低版本要求
  • 实现逻辑简单透明，本质是字段比较时自动调用lower()做转换，行为可预期，行业落地时间久，存量案例多
  • 无需额外配置排序规则，适配成本低
• 劣势
  • 需要提前开启citext扩展，部分权限管控严格的数据库实例可能不支持自定义扩展启用
  • 字段为非标准citext类型，部分ORM、数据库迁移工具可能存在适配问题

方案二：非确定性 ICU 排序规则 #

• 优势
  • 基于标准text类型实现，不需要引入额外扩展
  • 属于SQL标准支持的特性，跨数据库迁移的适配成本更低
  • 规则配置灵活，除大小写不敏感外，可自定义重音、全半角等匹配规则，对国际化多语言场景的适配性更好
• 劣势
  • 仅支持 PostgreSQL 12 及以上版本，且要求数据库编译时开启ICU支持
  • 非确定性排序的匹配逻辑和默认text类型存在差异，不熟悉规则的情况下容易踩坑，例如LIKE模糊匹配的行为会和默认规则不一致

业界选型建议 #

如果你的业务使用的PostgreSQL版本低于12，citext是唯一可选的成熟方案，也是过去十年这类需求的首选实现。如果使用PostgreSQL 12及以上版本，两种方案都可以落地，目前新业务中使用ICU非确定性排序规则的占比正在逐步提升，你可以根据你的实例权限、多语言需求来选择。

locale 参数选择说明 #

你提到的两个locale参数的差异是ICU排序强度的区别：

• und-u-ks-level1：强度1级，仅匹配基础字符，不区分大小写、不区分重音、不区分全半角
• und-u-ks-level2：强度2级，仅不区分大小写，会区分重音、全半角差异

邮箱存储场景下，除非你的业务明确需要兼容带重音的国际化邮箱且允许重音不同的邮箱判定为同一个，否则优先选择und-u-ks-level2，该配置可以满足你仅不区分大小写、其余字符严格匹配的需求，是更适配普通业务场景的选择。

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