金仓数据库KingbaseES本地化支持(4. 排序规则)

排序规则功能允许指定每一列甚至每一个操作的数据的排序顺序和字符分类行为。该功能取消了数据库的 LC_COLLATE 和 LC_CTYPE 在创建以后不能更改的限制。

• 排序规则概念

• 排序规则管理

4.1. 排序规则概念 ¶

在概念上，一种可排序数据类型的每一种表达式都有一个排序规则（内置的可排序数据类型是 text 、 varchar 和 char 。用户定义的基础类型也可以被标记为可排序的，并且在一种可排序数据类型上的域也是可排序的）。如果该表达式是一个列引用，该表达式的排序规则就是列所定义的排序规则。如果该表达式是一个常量，排序规则是该常量数据类型的默认排序规则。更复杂表达式根据其输入的排序规则来排序，如下所述：

一个表达式的排序规则可以是根据数据库的区域设置“默认”的排序规则，也可能是不确定的。在排序规则不确定的情况下，排序操作和其他需要知道排序规则的操作会失败。

当您在使用例如 ORDER BY 子句，函数或操作符调用（如 < ）时，数据库系统会执行一次排序或者字符分类。 ORDER BY 子句根据排序键的排序规则来排序，函数调用和操作符根据他们的参数或者操作对象的排序规则来排序，具体如下文所述。除比较操作符之外，在大小写字母之间转换的函数会使用排序规则，例如 lower 、 upper 和 initcap 。模式匹配操作符和 to_char 及相关函数也会使用排序规则。

对于一个函数的调用以及操作符的运行，如果返回的结果是一种可排序的数据类型，其排序规则是执行时指定的参数的排序规则。如果有外围表达式，在解析函数或操作符表达式的结果时使用的排序规则也会被用作函数或操作符表达式的排序规则。

一个表达式的排序规则派生可以是显式或隐式。它们会影响在同一个表达式中如何组合多个不同的排序规则。当使用一个 COLLATE 子句时，将会显式派生排序规则。所有其他排序规则的派生都是隐式的。当多个排序规则混合使用时（例如在一个函数调用中），组合后的排序规则如下：

1. 如果任何一个输入表达式具有一个显式排序规则，在输入表达式之间的所有显式派生的排序规则必须相同，否则将产生一个错误。如果存在任何一个显式的派生排序规则，它就是排序规则组合的结果。

2. 所有输入表达式必须具有相同的隐式排序规则或默认排序规则。如果任何一个非默认排序规则存在，它就是排序规则组合的结果。否则，结果是默认排序规则。

3. 如果在输入表达式之间存在冲突的非默认隐式排序规则，则组合被认为是具有不确定排序规则。这并非一种错误情况（除非被调用的特定函数要求提供排序规则的依据）。如果它确实这样做，运行时将发生一个错误。

例如，考虑这个表定义：

CREATE TABLE test1 (
    a text COLLATE "de_DE",
    b text COLLATE "es_ES",
    ...
);

然后在下方示例中， < 比较被根据 de_DE 规则执行，因为表达式组合了一个隐式派生的排序规则和默认排序规则。

SELECT a < 'foo' FROM test1;

但是在下方示例中，比较被使用 fr_FR 规则执行，因为显式排序规则派生重载了隐式排序规则。 .. code:

SELECT a < ('foo' COLLATE "fr_FR") FROM test1;

更进一步，给定解析器不能确定要应用哪个排序规则，因为 a 列和 b 列具有冲突的隐式排序规则。

SELECT a < b FROM test1;

由于 < 操作符不需要知道到底使用哪一个排序规则，这将会导致一个错误。该错误可以通过在一个输入表达式上附加一个显式排序规则说明符来解决，因此：

SELECT a < b COLLATE "de_DE" FROM test1;

或者等效的

SELECT a COLLATE "de_DE" < b FROM test1;

在另一方面，结构相似的情况

SELECT a || b FROM test1;

不会导致一个错误，因为 || 操作符不关心排序规则：不管排序规则怎样它的结果都相同。

如果一个函数或操作符发送一个具有可排序数据类型的结果，分配给该函数或操作符的组合输入表达式的排序规则也被应用到函数或操作符的结果。因此，在

SELECT * FROM test1 ORDER BY a || 'foo';

中排序将根据 de_DE 规则完成。但这个查询：

SELECT * FROM test1 ORDER BY a || b;

会导致一个错误，因为即使 || 操作符不需要知道排序规则，但 ORDER BY 子句需要。按照以前，冲突可以通过使用一个显式排序规则说明符来解决：

SELECT * FROM test1 ORDER BY a || b COLLATE "fr_FR";

4.2. 排序规则管理 ¶

排序规则是SQL模式对象，它将SQL名称映射到操作系统中安装的库提供的区域设置。 排序规则定义中有一个 提供程序 ， 它指定哪个库提供语言环境数据。一个标准的提供者名称是 libc ， 它使用操作系统C库提供的语言环境。这些是操作系统提供的大多数工具使用的语言环境, 不同操作系统的 libc 版本不同可能会导致排序顺序略有不同。 另一个提供者是 icu ，它使用外部ICU库。 只有在构建KingbaseES时配置了对ICU的支持，才能使用ICU区域设置。

libc 提供的排序规则对象映射到 setlocale() 系统调用接受的 LC_COLLATE 和 LC_CTYPE 设置的组合。 （正如其名字，一个排序规则的主要目的是设置 LC_COLLATE ，它控制排序顺序。 但是在实际中，很少有将 LC_COLLATE 设置成与 LC_CTYPE 不同，因此在同一个概念下收集这些设置比创建另一个基础结构来设置每一个表达式的 ``LC_CTYPE``更加方便）。 此外，一个 libc 排序规则是和一个字符集编码（见 字符集 ）绑定在一起的。 相同的排序规则名字可能存在于不同的编码中。

icu 提供的排序规则对象映射到ICU库提供的命名排序器。 因为ICU不支持单独设置“collate”和“ctype”，所以它们总是相同的。 此外，ICU排序规则与编码无关，因此在数据库中只有一个给定名称的ICU排序规则。

KingbaseES数据库将从以下方面管理排序规则：

• 标准的排序规则

• 预定义的排序规则

• 创建新的排序规则对象

• 非确定性排序规则

4.2.1. 标准的排序规则 ¶

在所有的平台上，名为 default 、 C 和 POSIX 的排序规则都可用。附加的排序规则是否可用取决于操作系统是否支持。 default 排序规则选择在数据库创建时指定的 LC_COLLATE 和 LC_CTYPE 值。 C 和 POSIX 排序规则都指定为“传统的C”行为，在其中只有ASCII字母“ A ”到“ Z ”被视为字母，并且排序严格地按照字符编码的字节值完成。

此外，SQL标准排序规则名称 ucs_basic 可用于编码 UTF8 。 它相当于 C ，并按Unicode编码排序。

4.2.2. 预定义的排序规则 ¶

如果操作系统支持在一个程序中使用多个区域（ newlocale 和相关函数），或者如果支持并配置了ICU，那么在初始化数据集簇时， initdb 根据它当时在操作系统中找到的所有区域设置，用排序规则填充系统目录 sys_collation 。

检查当前可用的区域设置，请在ksql中使用查询 SELECT * FROM sys_collation 或命令 \dOS + 。

• libc排序规则

• ICU排序规则

4.2.2.1. libc排序规则 ¶

如果操作系统提供 de_DE.utf8 的区域设置， initdb 会创建一个名为 de_DE.utf8 用于编码的排序规则，同时将 LC_COLLATE 和 LC_CTYPE 设置为 de_DE.utf8 ， 还将创建一个去掉前缀名为 .utf8 的排序规则，因此您也可以使用 de_DE 名下的排序规则，这样写起来就没有那么麻烦，并且使排序规则的名称不那么依赖编码。 然而请注意，最初的排序规则名称集是依赖于平台的。

libc 提供的默认排序规则集直接映射到操作系统中安装的语言环境，可以使用命令 locale -a 列出这些语言环境。 如果所需的 libc 排序规则与 LC_COLLATE 和 LC_CTYPE 的值不同， 或者在数据库系统初始化之后，操作系统中安装了新的区域设置，您可以使用 CREATE COLLATION 命令创建新的排序规则。 还可以使用sys_import_system_collations()函数整体导入新的操作系统区域设置。

在任何特定的数据库中，只有使用该数据库编码的排序规则才是有意义的。 sys_collation 中的其它项将被忽略。 因此，一个剥离的排序规则名称如 de_DE 在一个给定的数据库中可以被认为是唯一的，即使它在全局上不是唯一的。 我们推荐使用被剥离的排序规则名，因为如果您决定要更改到另一个数据库编码时需要做的修改更少。但是请注意无论数据库编码如何，都可以使用默认的 C 和 POSIX 排序规则。

KingbaseES认为不同的排序规则对象是不兼容的，即使他们有相同的属性，例如：

SELECT a COLLATE "C" < b COLLATE "POSIX" FROM test1;

将会得到一个错误，即使 C 和 POSIX 排序规则具有相同的行为。因此，我们不推荐混合使用剥离的和非被剥离的排序规则名。

4.2.2.2. ICU排序规则 ¶

对于ICU，列举所有可能的区域名称是不明智的。 ICU对区域设置使用特定的命名系统，但命名区域设置的方法比实际不同的区域设置多得多。 initdb 使用ICU API提取一组不同的区域设置，以填充初始排序规则集。 ICU提供的排序规则是在SQL环境创建的，名称采用BCP 47语言标记格式， 并附有一个专用扩展名 -x-icu ，以区别于libc语言环境。

以下是可能创建的一些排序规则的示例：

de-x-icu

德语排序规则，默认变体

de-AT-x-icu

奥地利的德语排序规则，默认变体

(也就是说 de-DE-x-icu 或 de-CH-x-icu ，但是这种写法，相当于 de-x-icu 。)

und-x-icu (for “undefined”)

ICU “根” 排序规则。 使用此选项可以获得合理的语言无关的排序顺序

ICU不支持一些使用频率较低的编码。当数据库编码是其中之一时， sys_collation 中的ICU排序规则项将被忽略。 试图使用其中一个将会抛出一个类似“collation "de-x-icu" for encoding "WIN874" does not exist”的错误。

4.2.3. 创建新的排序规则对象 ¶

如果标准和预定义的排序规则不够用，您可以使用SQL命令 CREATE COLLATION 创建自己的排序规则对象。

与所有预定于的对象一样，标准和预定义的排序规则在模式``sys_catalog``中。用户定义的排序规则应该在用户模式中创建。这也确保它们被 sys_dump 保存。

• libc排序规则

• ICU排序规则

• 复制排序规则

4.2.3.1. libc排序规则 ¶

您可以像这样创建新的libc排序规则：

CREATE COLLATION german (provider = libc, locale = 'de_DE');

该命令中 locale 子句可接受的确切值取决于操作系统。 在类Unix系统上，命令 locale -a 可以显示所有可以设置的区域设置。

由于预定义的libc排序规则已经包含了初始化数据库实例时在操作系统中定义的所有排序规则， 因此通常不需要手动创建新排序规则。原因可能是需要不同的命名系统（在这种情况下，请参见:ref: 复制排序规则 ）， 或者操作系统已经升级以提供新的区域设置定义（在这种情况下，另请参阅 FUNCTIONS-ADMIN-COLLATION 中 sys_import_system_collations() ）。

4.2.3.2. ICU排序规则 ¶

ICU允许在initdb预加载的基本语言+国家/地区集的排序规则。我们鼓励用户定义自己的排序对象， 这些对象利用这些功能使排序行为符合他们的要求。请参阅 http://userguide.icu-project.org/locale 和 http://userguide.icu-project.org/collation/api 获取有关ICU区域设置命名的信息。可接受的名称和属性集取决于特定的ICU版本。

示例：

CREATE COLLATION "de-u-co-phonebk-x-icu" (provider = icu, locale = 'de-u-co-phonebk');
CREATE COLLATION "de-u-co-phonebk-x-icu" (provider = icu, locale = 'de@collation=phonebook');

带有电话簿排序类型的德语排序

第一个例子根据 BCP 47使用“语言标记”选择ICU区域设置。 第二个示例使用传统的ICU特定区域设置语法。第一种风格是首选，但较旧的ICU版本不支持它。

请注意，您可以在SQL环境中任意指定排序规则对象的名称。 在这个例子中，我们遵循预定义排序规则使用的命名风格， 而这种风格又遵循BCP 47，但这对于用户定义的排序规则不是必需的。

CREATE COLLATION "und-u-co-emoji-x-icu" (provider = icu, locale = 'und-u-co-emoji');
CREATE COLLATION "und-u-co-emoji-x-icu" (provider = icu, locale = '@collation=emoji');

根据Unicode技术标准＃51，使用表情符号排序规则类型的根排序规则

观察在传统的ICU区域设置命名系统中，根区域设置是如何由空字符串选择的。

CREATE COLLATION digitslast (provider = icu, locale = 'en-u-kr-latn-digit');
CREATE COLLATION digitslast (provider = icu, locale = 'en@colReorder=latn-digit');

在拉丁字母后面排列数字。（默认是字母前的数字。）

CREATE COLLATION upperfirst (provider = icu, locale = 'en-u-kf-upper');
CREATE COLLATION upperfirst (provider = icu, locale = 'en@colCaseFirst=upper');i766

在小写字母前面排列大写字母。（默认是小写字母在前面。）

CREATE COLLATION special (provider = icu, locale = 'en-u-kf-upper-kr-latn-digit');
CREATE COLLATION special (provider = icu, locale = 'en@colCaseFirst=upper;colReorder=latn-digit');

结合上述两个选项。

CREATE COLLATION numeric (provider = icu, locale = 'en-u-kn-true');
CREATE COLLATION numeric (provider = icu, locale = 'en@colNumeric=yes');

数字排序，按数字值排序数字序列，例如： A-21 < A-123 （也称为自然排序）。

参阅 Unicode 技术标准 35 和 BCP 47 获取详细信息。 可能的排序规则类型（ co 子标签）列表可以在 CLDR 仓库 中找到。

请注意，虽然系统允许创建“忽略大小写”或“忽略重音符” 或类似（使用 ks 键）的排序规则，但这样的排序规则可以真正不区分大小写或者重音。 它们还需要在 CREATE COLLATION 中声明为非确定性 ； 参考 非确定性排序规则 。 否则，根据排序规则比较相等，但按照字节不相等的任何字符串将根据其字节值进行排序。

注意

根据设计，ICU几乎可以接受任何字符串作为区域名称， 并使用其文档中描述的后备程序，将其与最接近的区域设置相匹配。因此，如果您使用给定ICU安装实际上不支持的功能组合排序规范，则不会有直接反馈。因此建议创建应用程序级别的测试用例，以检查排序规则定义是否满足需求。

4.2.3.3. 复制排序规则 ¶

您可使用命令 CREATE COLLATION 从现有的排序规则创建新的排序规则，这对于能够在应用程序中使用独立于操作系统的排序规则名称、创建兼容性名称或使用ICU提供的更易读的排序规则名称很有帮助。 例如：

CREATE COLLATION german FROM "de_DE";
CREATE COLLATION french FROM "fr-x-icu";

4.2.4. 非确定性排序规则 ¶

排序规则要么是 确定性的 ，要么是 非确定性的 。 确定性排序规则使用确定性比较，这意味着仅当字符串由相同的字节序列组成时才认为它们是相等的。 即使字符串由不同的字节组成，非确定性比较也可能会确定字符串相等。 典型的情况包括不区分大小写的比较、不区分上下文的比较，以及不同Unicode标准格式的字符串比较。 由排序规则提供程序来实际实现这种不敏感的比较。确定性标志仅决定是否使用字节数比较来进行关联。 有关非确定性排序规则的更多信息，请参考 Unicode Technical Standard 10 。

您可以通过 CREATE COLLATION 指定属性 deterministic = false ，来创建非确定性排序规则，例如：

CREATE COLLATION ndcoll (provider = icu, locale = 'und', deterministic = false);

本例将以一种不确定的方式使用标准的Unicode排序规则。特别是，这将允许正确地比较不同形式的字符串。

更有趣的例子是使用ICU定制规则。 示例：

CREATE COLLATION case_insensitive (provider = icu, locale = 'und-u-ks-level2', deterministic = false);
CREATE COLLATION ignore_accents (provider = icu, locale = 'und-u-ks-level1-kc-true', deterministic = false);

所有标准和预定义的排序都是确定性的，默认情况下所有用户定义的排序都是确定性的。 虽然不确定性排序提供了更“正确的”行为，但考虑到Unicode的强大功能及其许多特殊情况时，它们也有一些缺点。 首先，使用它们会导致性能损失。 此外，对于非确定性排序，某些操作是不可能的，例如模式匹配操作。 因此，它们应该只在特别需要的情况下使用。