排序规则

本节介绍排序规则的概念和配置方式。

基本概念

通常，每一种可排序的数据类型都拥有一个排序规则。本节介绍的排序规则（Collation）特指字符的排序规则。排序规则会影响：

ORDER BY 子句的排序。
比较操作符（如 >、<、= 等）。

排序规则依赖于字符集，不同的字符集支持的排序规则不同。可以通过系统表 PG_COLLATION 查询 VexDB 支持的排序规则。

在初始化数据库时，LC_COLLATE 默认会根据操作系统的 locale 设置进行选择，也可以通过 --LC-COLLATE 参数指定。指定 LC_COLLATE 时，需要确保该设置与字符集能够匹配。

使用方式

VexDB 数据库允许在查询中通过 COLLATE 子句指定查询使用的排序规则。

SELECT expr COLLATE collation;

不指定 COLLATE 时，使用对象的排序规则。
```
SELECT col < 'expr' FROM tbl;
```
在括号中指定 COLLATE 时，使用显式指定的排序规则。
```
SELECT col < ('expr' COLLATE 'zh_CN.utf8') FROM tbl;
```
说明
通过 COLLATE 指定的排序规则必须为当前 server_encoding 字符集支持的排序规则。如上述语句在 server_encoding 为 zh_CN.gbk 时，则会报错：ERROR: collation "zh_CN.utf8" for encoding "GBK" does not exist。

若解析器无法确定使用哪种排序规则，则会报错。

CREATE TABLE tbl(col1 text COLLATE 'zh_CN',col2 text COLLATE 'en_US');
INSERT INTO tbl VALUES ('vexdb','vexdb');
SELECT col1 < col2 from tbl;

报错为：

ERROR:  could not determine which collation to use for string comparison

对于上述情况，可以通过显式指定 COLLATE 关键字规避。

SELECT col1 < col2 COLLATE 'zh_CN' from tbl;

对于不涉及排序的函数或操作符，即使表达式前后的排序规则不同，也不会产生影响。

CREATE TABLE tbl(col1 text COLLATE 'zh_CN',col2 text COLLATE 'en_US');
INSERT INTO tbl VALUES ('vexdb','vexdb');
SELECT col1 || col2 from tbl;

配置方式

允许配置排序规则的对象

VexDB 允许为数据库、列级别设置排序规则。其中优先级为：

显式指定 > 列 > 数据库

为索引指定排序规则时，仅显式指定排序规则时，查询计划才会使用该索引。有关该行为的详信息，参见示例1：为索引指定排序规则。

初始化排序规则

在初始化数据库时，LC_COLLATE 默认会使用操作系统 locale.conf 中的 LC_COLLATE 配置，用户可以在初始化数据库时通过 --lc-collate 参数指定。指定 --lc-collate 时，需要确保该设置与字符集能够匹配。

方式一：

修改 locale.conf，将 LANG 设置为需要使用的语言设置。例如 "zh_CN.gbk"。
```
LANG = zh_CN.GBK
```
重启操作系统，使配置生效。
```
locale | grep 'LANG\|LC_COLLATE\|LC_CTYPE'
```
上述命令返回结果如下，表示操作系统支持 zh_CN.gbk 排序规则：
```
LANG=zh_CN.gbk
LC_CTYPE="zh_CN.gbk"
LC_COLLATE="zh_CN.gbk"
```
使用 vb_initdb 命令行工具初始化数据库。
```
vb_initdb -D $GAUSSHOME --nodename=vb 
```

方式二：

使用 vb_initdb 命令行工具显式指定排序规则。

vb_initdb -D $GAUSSHOME \
         --nodename=vb \
         --encoding=GBK \
         --lc-collate=C \
         --lc-ctype=C

数据库级排序规则

VexDB 数据库支持 CREATE DATABASE 语句在创建数据库时使用 LC_COLLATE 指定排序规则。LC_COLLATE 在数据库创建后不允许修改。

CREATE DATABASE database_name
             ENCODING [=] encoding
             LC_COLLATE [=] lc_collate
             LC_CTYPE [=] lc_ctype;

当新建数据库 Encoding、LC_Collate 或 LC_Ctype 与模板数据库（SQL_ASCII）不匹配（为 GBK、UTF8 或 LATIN1 等）时，必须指定 template = template0。

参数说明

database_name
要创建的数据库名称。
encoding
指定数据库使用的字符编码，可以是字符串（如'SQL_ASCII'）、整数编号。
不指定时，默认使用模版数据库的编码。模板数据库 template0 和 template1 的编码默认与操作系统环境相关。template1 不允许修改字符编码，因此若要变更编码，请使用 template0 创建数据库。
指定新的数据库字符集编码必须与所选择的本地环境中（LC_COLLATE 和 LC_CTYPE）的设置兼容。
lc_collate
指定新数据库使用的字符集。例如，通过lc_collate = 'zh_CN.gbk'设定该参数。
该参数的使用会影响到对字符串的排序顺序（如使用 ORDER BY 执行，以及在文本列上使用索引的顺序）。默认是使用模板数据库的排序顺序。
取值范围：操作系统支持的字符集。
lc_ctype
指定新数据库使用的字符分类。例如，通过 lc_ctype = 'zh_CN.gbk' 设定该参数。该参数的使用会影响到字符的分类，如大写、小写和数字。默认是使用模板数据库的字符分类。
取值范围：操作系统支持的字符分类。
说明
对于 lc_collate 和 lc_ctype 参数的取值范围，取决于本地环境支持的字符集。例如：在 Linux 操作系统上，可通过 locale -a 命令获取操作系统支持的字符集列表，在应用 lc_collate 和 lc_ctype 参数时可从中选择用户需要的字符集和字符分类。

数据库的排序规则存储在 PG_DATABASE 系统表中。可以使用 vsql 的 -l 选项或 \l 命令列出这些排序规则。

vsql -l

列级排序规则

VexDB 数据库支持在 CREATE TABLE 语句为列指定排序规则。支持通过 ALTER TABLE 修改列的排序规则。

CREATE TABLE table_name ( column_name data_type COLLATE collation 
                         [, ...] );

参数说明

table_name
要创建的表名。
column_name
要创建的表名。
data_type
字段的数据类型。

collation
指定列的排序规则。指定排序规则的列必须是可排序的数据类型。
如果没有指定，则使用默认的排序规则。排序规则可以使用 select * from pg_collation; 命令从 pg_collation 系统表中查询，默认的排序规则为查询结果中以 default 开始的行。

说明

列的排序规则存储在PG_ATTRIBUTE系统表中。可以通过以下 SQL 查询 tbl 表上各列的排序规则。

WITH foo AS (SELECT co.oid,co.collname FROM pg_collation co
           JOIN pg_namespace nco ON co.collnamespace = nco.oid  
           WHERE nco.nspname <> 'pg_catalog'::name 
               OR co.collname <> 'default'::name) 
   SELECT tbl.relname AS table_name,
       col.attname AS column_name,
       foo.collname::varchar(100) AS "collation"
   FROM pg_attribute col 
   JOIN pg_class tbl ON col.attrelid = tbl.oid
   LEFT JOIN foo ON col.attcollation = foo.oid
   WHERE col.attnum > 0 
   AND col.attisdropped = false 
  AND relname = 'tbl';

示例

示例1：为索引指定排序规则

创建测试表，其中 c2 列排序规则指定为 C。
```
CREATE TABLE coll_ind(c1 int,c2 text COLLATE 'C');
```
在测试表上创建索引，指定排序规则为 zh_CN。
```
CREATE INDEX coll_idx ON coll_ind(c2 COLLATE 'zh_CN');
```

不显示指定排序规则，查看执行计划。

EXPLAIN (costs off,verbose) SELECT * FROM coll_ind WHERE c2 = 'a';

返回结果如下：

            QUERY PLAN
-------------------------------------
Seq Scan on public.coll_ind
Output: c1, c2
Filter: (coll_ind.c2 = 'a'::text)
(3 rows)

显式指定排序规则，查看执行计划。

EXPLAIN (costs off,verbose) SELECT * FROM coll_ind WHERE c2 = 'a' COLLATE 'zh_CN';

返回结果如下，表示执行计划使用了索引：

                        QUERY PLAN
---------------------------------------------------------------
Bitmap Heap Scan on public.coll_ind
Output: c1, c2
Recheck Cond: (coll_ind.c2 = 'a'::text COLLATE "zh_CN")
->  Bitmap Index Scan on coll_idx
        Index Cond: (coll_ind.c2 = 'a'::text COLLATE "zh_CN")
(5 rows)

清理测试环境。
```
DROP TABLE coll_ind;
```

示例2：指定数据库的排序规则

在默认字符集为 utf8 的数据库实例中，创建排序规则为 zh_CN.gbk 的数据库。
```
CREATE DATABASE gbk LC_COLLATE 'zh_CN.gbk';
```
返回结果如下，报错 utf8 字符集与 zh_CN.gbk 排序规则不匹配：
```
ERROR:  encoding "UTF8" does not match locale "zh_CN.gbk"
```
在创建数据库命令中增加 ENCODING 选项。
```
CREATE DATABASE gbk LC_COLLATE 'zh_CN.gbk' ENCODING 'gbk';
```
返回结果如下，报错 GBK 字符集与区域 en_US.UTF8 不匹配：
```
ERROR:  encoding "GBK" does not match locale "en_US.UTF-8"
```

在创建数据库命令中增加 LC_CTYPE 选项。

CREATE DATABASE gbk LC_COLLATE 'zh_CN.gbk' ENCODING 'gbk' LC_CTYPE 'zh_CN.gbk';

返回结果如下，创建数据库成功。

CREATE DATABASE

清理测试环境。
```
DROP DATABASE gbk;
```

示例3：指定列的排序规则

创建测试表，并插入数据。

CREATE TABLE coll_tbl(c1 text COLLATE 'en_US',c2 text COLLATE 'C');
INSERT INTO coll_tbl VALUES ('vexdb','vexdb');

查询 c1 < c2。
```
SELECT c1 < c2 FROM coll_tbl;
```
返回结果如下，由于执行器无法选择使用哪一种排序规则，返回报错。
```
ERROR:  could not determine which collation to use for string comparison
```

在查询中显式指定排序规则。

SELECT c1 < c2 collate 'en_US' from coll_tbl;

返回结果如下：

?column?
----------
f
(1 row)

清理测试环境。
```
DROP TABLE coll_tbl;
```

排序规则

需要帮助？