# 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' | 遵循法语排序习惯，特定字符按语言规则排列。 |
       
    ![](https://support.huaweicloud.com/bestpractice-rds-pg/public_sys-resources/notice_3.0-zh-cn.png)
    数据库创建后，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 代表数据库默认规则。
  
 
#### 常见问题
#### 创建新数据库时为什么必须使用template0作为模板库？
template1默认包含众多依赖当前排序规则的系统对象与索引。如果新数据库的LC_COLLATE与模板不一致，继承的索引将失效。template0是纯净的初始化模板，允许自由指定兼容的编码与区域设置。