全平台大小写不敏感排序方案——PostgreSQL无需修改collation实现

以下是几种无需修改数据库默认排序规则（collation）的可行方案，可根据应用场景选择：

1. 直接在排序时统一字段大小写 #

这是最直接的实现方式，无需修改表结构，只需在所有涉及排序的SQL语句中，将排序字段转换为小写（或大写）后排序：

SELECT * FROM your_table ORDER BY LOWER(sort_column);

• 优势：零表结构改动，快速验证效果
• 劣势：需要全应用排查并修改所有带排序的SQL，工作量取决于应用规模；如果字段没有对应索引，大表排序会有性能损耗

2. 创建函数索引优化排序性能 #

针对需要频繁排序的字段，创建基于LOWER()或UPPER()的函数索引，配合上面的排序语句使用，能大幅提升大表的排序效率：

CREATE INDEX idx_yourtable_sortcol_lower ON your_table(LOWER(sort_column));

• 适用场景：核心业务表、数据量较大且排序操作频繁的场景
• 注意：索引会占用额外存储空间，且原字段更新时索引会自动同步维护

3. 添加存储型计算字段 #

在表中新增一个自动同步原字段大小写转换结果的存储型计算字段，后续直接基于该字段排序：

ALTER TABLE your_table ADD COLUMN sort_column_lower TEXT GENERATED ALWAYS AS (LOWER(sort_column)) STORED;

之后排序语句可简化为：

SELECT * FROM your_table ORDER BY sort_column_lower;

• 优势：排序语句更简洁，性能和普通字段排序一致，无需每次调用函数转换
• 劣势：需要修改表结构，新增字段会占用存储空间；但STORED类型会自动跟随原字段变化，无需手动维护

4. 利用PostgreSQL的citext类型 #

如果是字符串字段，可以考虑将字段类型改为citext（大小写不敏感文本类型），该类型在排序、比较时会自动忽略大小写：

-- 先安装citext扩展
CREATE EXTENSION IF NOT EXISTS citext;
-- 修改字段类型
ALTER TABLE your_table ALTER COLUMN sort_column TYPE citext;

之后直接使用原字段排序即可实现大小写不敏感：

SELECT * FROM your_table ORDER BY sort_column;

• 优势：无需修改排序语句，全应用自动生效
• 劣势：需要修改字段类型，需评估对现有业务的影响；部分ORM框架可能需要适配该类型

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