排序规则

功能描述

Vastbase在SQL Server兼容模式下支持字符集排序规则chinese_prc_ci_ai，从而达到在查询数据时忽略大小写的效果。

chinese_prc_ci_ai是SQL Server的一种字符排序规则，它会影响字符串比较时的判断逻辑，进而影响字符串的排序。如下为可指定chinese_prc_ci_ai排序规则的场景：

chinese_prc_ci_ai中chinese_prc表示中文；ci表示忽略大小写（即'A'='a'）；ai表示忽略重音（即'a'= 'á'）。因此在本字符集排序规则下，'A'、'a'、'á'都是相等的，即忽略大小写和忽略重音字符。

在创建和变更schema时指定COLLATE chinese_prc_ci_ai，指定排序规则后，此schema下新增的表和列数据的排序规则将使用chinese_prc_ci_ai；而在修改前已存在的schema的表和列，排序规则不会发生变化。
通过 CREATE TABLE 指定表排序规则或单独指定列的排序规则COLLATE chinese_prc_ci_ai。
通过 ALTER TABLE 修改表排序规则COLLATE chinese_prc_ci_ai。指定排序规则后，此table下新增的列将使用chinese_prc_ci_ai；而在修改前已存在的列，排序规则不会发生变化。
通过 ALTER TABLE ADD COLUMN 指定新增列的排序规则COLLATE chinese_prc_ci_ai。
通过 ALTER TABLE ALTER COLUMN SET 修改列的排序规则COLLATE chinese_prc_ci_ai。
在 CREATE TABLE 的 WITH 子句中指定表的字符集排序规则chinese_prc_ci_ai，但需要提前在PG_COLLATION查询chinese_prc_ci_ai字符集的OID值。

注意事项

仅在数据库兼容模式为SQL Server时支持（即数据库实例初始化时指定DBCOMPATIBILITY='MSSQL'）。
当显式指定了schema、table、column的排序规则时，其排序规则生效优先级为：column > table > schema 。
当前仅支持设置行存表的排序规则为chinese_prc_ci_ai，且chinese_prc_ci_ai字符集排序规则对应的数据库字符集仅为UTF-8。

语法格式

创建schema时指定字符集排序规则chinese_prc_ci_ai：
```
CREATE SCHEMA schema_name COLLATE chinese_prc_ci_ai;
```
修改schema的字符集排序规则为chinese_prc_ci_ai：

在更新之前创建在 schema下的表及其表列的字符集排序规则不会发生改变。
```
ALTER SCHEMA table_name(col_name1 data_type,col_name2 data_type...) COLLATE chinese_prc_ci_ai;
```

创建表时指定表的字符集排序规则chinese_prc_ci_ai：

CREATE TABLE table_name(col_name1 data_type,col_name2 data_type...) COLLATE chinese_prc_ci_ai;

创建表时指定列的字符集排序规则chinese_prc_ci_ai：

CREATE TABLE table_name(col_name1 data_type,col_name2 data_type... COLLATE chinese_prc_ci_ai) ;

修改表的字符集排序规则chinese_prc_ci_ai：

在更新之前创建在TABLE中列的字符集排序规则不会发生改变。
```
ALTER TABLE table_name COLLATE chinese_prc_ci_ai;
```
修改表时增加列，同时指定列的字符集排序规则chinese_prc_ci_ai：
```
ALTER TABLE table_name ADD COLUMN col_name data_type  COLLATE chinese_prc_ci_ai;
```

修改指定列的字符集排序规则chinese_prc_ci_ai：

ALTER TABLE table_name ALTER COLUMN col_name  SET DATA TYPE data_type  COLLATE chinese_prc_ci_ai;

在 CREATE TABLE 的 WITH 子句中指定表的字符集排序规则chinese_prc_ci_ai，但需要提前在PG_COLLATION查询chinese_prc_ci_ai字符集的OID值。

若同时指定了COLLATE和WITH中的COLLATE，则优先考虑WITH中的COLLATE。
```
CREATE TABLE table_name(col_name data_type...) WITH(COLLATE=oid);
```

参数说明

table_name

要创建或修改的表名。
col_name

表中要创建或修改的字段名。
data_type

字段的数据类型。
oid

查询所得的chinese_prc_ci_ai字符集的OID值。查询语句为：
```
SELECT oid ,* FROM PG_COLLATION WHERE collname = 'chinese_prc_ci_ai';
```

示例

1、创建测试表，不指定排序规则。

CREATE TABLE t_1202848(col int,col2 varchar);

2、修改测试表的排序规则为chinese_prc_ci_ai，并为测试表新增两列，分别将排序规则设置为默认和C。

ALTER TABLE t_1202848 COLLATE chinese_prc_ci_ai;
ALTER TABLE t_1202848 ADD COLUMN col3 text;
ALTER TABLE t_1202848 ADD COLUMN col4 text collate "C";

3、向测试表中插入数据。

INSERT INTO t_1202848 VALUES(1,'abc','abc','abc');
INSERT INTO t_1202848 VALUES(2,'ABC','ABC','ABC');
INSERT INTO t_1202848 VALUES(3,'aBc','aBc','aBc');
INSERT INTO t_1202848 VALUES(4,'bbc','bbc','bbc');
INSERT INTO t_1202848 VALUES(5,'A','A','A');
INSERT INTO t_1202848 VALUES(6,'a','a','a');
INSERT INTO t_1202848 VALUES(7,'à','à','à');

4、分别查看col2、col3、col4中对abc的查询结果。由于col2是表修改排序规则前创建的，所以排序规则为默认；col3中abc不区分大小写、重音；col4指定了C排序规则，字段排序由相应的调整。

SELECT * FROM t_1202848 WHERE col2='abc' ORDER BY 2;
SELECT * FROM t_1202848 WHERE col3='abc' ORDER BY 2;
SELECT * FROM t_1202848 WHERE col4='abc' ORDER BY 2;

返回结果为：

 col | col2 | col3 | col4
-----+------+------+------
   1 | abc  | abc  | abc
(1 row)

 col | col2 | col3 | col4
-----+------+------+------
   1 | abc  | abc  | abc
   3 | aBc  | aBc  | aBc
   2 | ABC  | ABC  | ABC
(3 rows)

 col | col2 | col3 | col4
-----+------+------+------
   1 | abc  | abc  | abc
(1 row)

5、查看col2、col3、col4中对à的查询结果。由于col2是表修改排序规则前创建的，所以排序规则为默认；col3中abc不区分重音；col4指定了C排序规则，字段排序由相应的调整。

SELECT * FROM t_1202848 WHERE col2='a' ORDER BY 2;
SELECT * FROM t_1202848 WHERE col3='a' ORDER BY 2;
SELECT * FROM t_1202848 WHERE col4='a' ORDER BY 2;

返回结果为：

 col | col2 | col3 | col4
-----+------+------+------
   6 | a    | a    | a
(1 row)

 col | col2 | col3 | col4
-----+------+------+------
   6 | a    | a    | a
   5 | A    | A    | A
   7 | à    | à    | à
(3 rows)

 col | col2 | col3 | col4
-----+------+------+------
   6 | a    | a    | a
(1 row)

6、查看col2的排序情况。

SELECT * FROM t_1202848 WHERE col2>='abc' ORDER BY 2;
SELECT * FROM t_1202848 WHERE col2<'abc' ORDER BY 2;

返回结果为：

 col | col2 | col3 | col4
-----+------+------+------
   1 | abc  | abc  | abc
   3 | aBc  | aBc  | aBc
   2 | ABC  | ABC  | ABC
   4 | bbc  | bbc  | bbc
(4 rows)

 col | col2 | col3 | col4
-----+------+------+------
   6 | a    | a    | a
   5 | A    | A    | A
   7 | à    | à    | à
(3 rows)

7、删除测试表。

DROP TABLE t_1202848;

VastbaseG100

排序规则

功能描述

注意事项

语法格式

参数说明

示例

弹框头部