RDS for PostgreSQL排序规则设置指导

什么是排序规则？

RDS for PostgreSQL的排序规则（Collation）定义了字符集在数据库中的字符顺序和分类属性，包括大小写敏感性、重音符号处理以及空格忽略规则等。它直接决定字符串的比较逻辑、排序结果以及部分字符处理函数的行为。PostgreSQL支持在数据库级别、列级别或查询表达式级别指定排序规则。若未显式指定，系统将自动继承所在数据库的默认排序规则。

核心区域参数详解（LC_COLLATE与LC_CTYPE）

在初始化数据库集群（initdb）或创建新数据库（CREATE DATABASE）时，必须配置LC_COLLATE和LC_CTYPE参数。这两个参数源自底层操作系统的区域设置标准（ISO C标准与POSIX规范），是排序规则的底层基础。

LC_COLLATE（字符串排序顺序）

定义：控制字符的字典序与排序规则，决定字符串比较时的大小关系。
影响范围：
- ORDER BY排序结果。
- 字符串比较操作符（>, <, >=, <=, BETWEEN 等）。
- 聚合函数（MIN(), MAX()）的取值逻辑。
- B-Tree索引的物理构建顺序与范围扫描路径。

示例对比：假设有数据 ['a', 'A', 'b', 'B']，不同规则下的升序排列结果如下：

表1 不同规则下的排序结果
排序规则 (LC_COLLATE)	排序结果 (Ascending)	说明
C或POSIX	'A', 'B', 'a', 'b'	基于ASCII字节值排序，大写字母优先。
en_US.UTF-8	'a', 'A', 'b', 'B'	基于英语字典序，通常按逻辑分组排序。
fr_FR.UTF-8	'a', 'b', 'A', 'B'	遵循法语排序习惯，特定字符按语言规则排列。

数据库创建后，LC_COLLATE严禁修改。PostgreSQL的B-Tree索引严格依赖创建时的排序顺序。若强行更改LC_COLLATE，已有索引的物理顺序将与新的逻辑排序规则冲突，导致查询结果异常或数据损坏。

LC_CTYPE（字符分类属性）
- 定义：定义字符的语义分类，决定数据库如何识别字符类型（如字母、数字、标点、空白符等）。
- 影响范围：
  - 大小写转换函数：UPPER(), LOWER(), INITCAP()。
  - 字符分类判断：底层函数如 isalpha(), isdigit(), isspace() 的判定逻辑。
  - 正则表达式匹配：决定POSIX正则表达式中字符类（如 [[:alpha:]], [[:digit:]], \w, \s）的匹配范围。
- 示例对比：
  - 正则匹配：SELECT 'a' ~ '^[[:alpha:]]$'; 的结果取决于LC_CTYPE对“字母”的定义边界。
  - 语言特性：在土耳其语区域设置（tr_TR）中，小写 i 的大写形式是带点的 İ 而非 I，该转换逻辑严格受LC_CTYPE控制。

使用Collation的三个级别

数据库级别
 创建数据库时指定，作为该库内所有新建对象的默认排序规则。
```
CREATE DATABASE mydb LC_COLLATE = 'zh_CN.UTF-8' LC_CTYPE = 'zh_CN.UTF-8' TEMPLATE = template0; 
```
注意：数据库一旦创建，排序规则即固定。如需变更，只能将数据迁移至新库。
列级别（物理存储属性）
在表结构定义时为特定字段指定排序规则，直接影响该列的索引构建与默认排序行为。
```
CREATE TABLE test1 ( id integer, content varchar COLLATE "es_ES" ); 
```
在此列上创建的索引将严格遵循指定规则。执行排序查询且未显式声明COLLATE时，将自动应用此列定义的规则。
表达式级别（动态覆盖）
在SELECT、ORDER BY或条件过滤中动态指定排序规则，用于临时覆盖默认行为。
- 覆盖默认排序规则进行查询。
```
SELECT * FROM test1 ORDER BY content COLLATE "en_US";
```
- 在比较操作中强制使用C排序规则。
```
SELECT * FROM test1 WHERE content = 'ä' COLLATE "C";
```

确定性与非确定性Collation

确定性（Deterministic）排序规则：PostgreSQL 12引入了确定性排序规则的概念，主要应用于ICU提供者。
非确定性（Non-deterministic）排序规则：允许将具有相同基础字符但表现形式不同的字符串视为相等（例如忽略重音符号，或将 ä 与 ae 等同处理）。
创建示例：
```
CREATE COLLATION und_deterministic ( provider = icu, locale = 'und-u-ks-level1', deterministic = false ); 
```
如果未显式声明deterministic参数，系统默认采用确定性模式（deterministic = true）。

常用操作

查看当前Collation配置
 可通过psql的\l命令查看各数据库的Locale设置，或执行以下SQL查询：
```
SELECT datname, datcollate, datctype FROM pg_database WHERE datname NOT IN ('template0', 'template1'); 
```

创建数据库时指定Collation
如需创建与集群默认设置（即template1）不同的新数据库，必须指定template0作为模板库。
```
CREATE DATABASE my_new_db TEMPLATE template0 LC_COLLATE = 'zh_CN.UTF-8' LC_CTYPE = 'zh_CN.UTF-8' ENCODING 'UTF8';
```
创建自定义Collation
当内置排序规则无法满足特定业务需求时，可创建自定义规则。
- 基于libc创建
  适用于底层操作系统已安装目标Locale的环境。
```
CREATE COLLATION german_phonebook ( provider = libc, lc_collate = 'de_DE.utf8', lc_ctype = 'de_DE.utf8' );
```
- 基于ICU创建
  ICU提供更精细的国际化规则，且无需依赖操作系统预先配置。
```
CREATE COLLATION "de-u-co-phonebk-x-icu" ( provider = icu, locale = 'en-US' ); 
```
  优势：单节点即可支持数千种细分排序规则，且行为跨平台一致，不受底层OS升级影响。
查看系统支持的Collation
通过查询系统目录表，可获取当前环境支持的所有排序规则及其底层提供者。
```
SELECT collname, collprovider, collcollate, collctype FROM pg_collation;
```
字段说明：collprovider中，c 代表 libc，i 代表 ICU，d 代表数据库默认规则。