22.2. 排序规则支持 · PostgreSQL 中文文档 9.3

# 22.2\. 排序规则支持排序规则特性允许为每一列数据指定排序顺序和字符分类行为，或者甚至为每个操作指定。这缓解了`LC_COLLATE`和`LC_CTYPE`在数据库被创建后不能被修改的限制。 ## 22.2.1\. 概念从概念上讲，collatable数据类型的每个表达式都有一个排序规则。（内置collatable数据类型有`text`, `varchar`以及`char`。用户定义的基本类型，也可以标记为collatable，当然一个collatable数据类型的域也是collatable的）。如果表达式是一个列引用，该表达式的排序规则是就这个列的排序规则。如果表达式是一个常数，排序规则是常数数据类型的缺省排序规则。一个更复杂表达式的排序规则从它的输入端排序规则推导，如下所述。一个表达式的排序规则可以是"缺省"排序规则，这意味着为数据库的区域设置。它也可以用于表达式的排序规则是不确定的。在这种情况下，排序操作符以及其他需要知道排序规则的操作符会在执行时失败。当数据库系统必须执行排序或字符分类时，它使用输入表达式的排序规则。这种情况发生，例如，使用`ORDER BY`子句以及函数或运算符调用比如`<`。应用到`ORDER BY`子句中的排序规则直接就是排序关键字的排序规则。应用到函数或操作符调用的排序规则要从参数上派生，如下文所述。除了比较操作符外，大小写字母转换函数，如`lower`, `upper`和 `initcap`；模式匹配运算符号；以及 `to_char`及其相关函数都需要考虑排序规则。对于一个函数或运算符调用，通过检查用来在运行时执行指定操作的参数排序规则派生出该排序规则。如果函数或运算符调用结果是collatable数据类型，并且有需要知道该排序规则的外围表达式，排序规则也可用于在解析时作为函数或运算符表达式的定义的排序规则。表达式的_排序规则推导_可以是隐式或显式的。当多个不同的排序规则出现在表达式中，这种区别会影响排序规则如何组合，当使用`COLLATE`子句的时候，产生显式排序规则推导;所有其他排序规则推导是隐式的。当多个排序规则必须结合时，例如在一个函数调用中，使用下面的规则： 1. 如果任何输入表达式有显式的排序规则推导，那么输入表达式中所有显式派生的排序规则必须是一样的，否则将引发错误。如果有任何显式派生的排序规则，这即是排序规则组合的结果。 2. 否则，所有输入表达式必须具有相同的隐式排序规则推导或者缺省排序规则。如果出现任何非缺省的排序规则，则是这就是排序组合的结果。否则，结果是缺省排序规则。 3. 如果在输入的表达式之间非缺省的隐式排序规则有冲突，那么该组合视为不明确的排序规则。这不是一个错误情况，除非被调用的那个函数需要知道排序规则。如果是这样，在运行时将引发一个错误。比如，考虑这个表定义： ``` CREATE TABLE test1 ( a text COLLATE "de_DE", b text COLLATE "es_ES", ... ); ``` 然后 ``` SELECT a < 'foo' FROM test1; ``` 按照`de_DE`规则，执行`<`比较，因为表达式组合隐式推导排序规则与缺省排序规则，但是 ``` SELECT a < ('foo' COLLATE "fr_FR") FROM test1; ``` 使用`fr_FR`规则执行比较，因为显式排序规则覆盖了隐式的。此外， ``` SELECT a < b FROM test1; ``` 解析器无法确定应该应用哪个排序规则，因为 `a`列和`b`列拥有冲突的隐式排序规则。因为`<`操作符确实需要知道所使用的排序规则，这将导致一个错误。错误可以通过附加一个明确的排序规则说明符给输入表达式得以解决，如下： ``` SELECT a < b COLLATE "de_DE" FROM test1; ``` 或者等效的， ``` SELECT a COLLATE "de_DE" < b FROM test1; ``` 另一方面，结构上类似的情况 ``` SELECT a || b FROM test1; ``` 不会产生错误，因为`||`操作符并不关心排序规则：不论什么排序规则结果都是一样的。如果函数或者操作符的结果还是collatable数据类型, 那么分配给函数或者操作符的组合的输入表达式的排序规则也会应用到函数或者操作符结果上。因此， ``` 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"; ``` ## 22.2.2\. 管理排序规则排序规则是把SQL名称映射到操作系统区域的SQL模式对象。特别是，它映射到`LC_COLLATE`和`LC_CTYPE`的组合。（顾名思义，排序规则的主要目的是设置`LC_COLLATE`，它控制排序顺序。但在实际中很少需要一个和 `LC_COLLATE`不同的`LC_CTYPE`设置，所以将它们合在一起，而不是为每个表达式另外创造一个设置 `LC_CTYPE`的基础设施，更为方便）。另外，排序规则和字符集编码（参见[Section 22.3](#calibre_link-1272)）紧密关联。对不同的编码可能存在同名的排序规则。在所有平台上，名称为`default`, `C`和`POSIX`排序规则都是可用的。其他可用的排序规则取决于操作系统支持。`default`排序规则选择在创建数据库时指定的`LC_COLLATE` 和`LC_CTYPE`值。 `C`和`POSIX`的排序规则都表现为"传统 C"的行为，即只有ASCII字母"`A`"到"`Z`" 被视为字母，并且严格按照字符的编码字节值进行排序。如果操作系统提供了在一个程序中使用多语言环境的支持 (`newlocale`以及相关函数)，那么当一个数据库集群初始化的时候， `initdb`使用基于当时在操作系统上发现的所有语言环境的排序规则来填充系统表`pg_collation`。例如，操作系统可能提供名为`de_DE.utf8`的区域。那么，`initdb`就可能为编码`UTF8`创建命名为 `de_DE.utf8`的排序规则，它的`LC_COLLATE`和 `LC_CTYPE`都被设置为`de_DE.utf8`。它还将创建名称中被剥离了`.utf8`标签的排序规则。所以，你也可以使用`de_DE`名称的排序规则，这方便于编写并且使得名称较少依赖于编码。然而，需要注意的是，排序规则名称的初始设置是平台相关的。在需要有不同的`LC_COLLATE`和`LC_CTYPE` 值的排序规则的情况下，可以使用[CREATE COLLATION](#calibre_link-559) 命令创建新的排序规则。该命令也可以从现有排序规则中创建一个新的，这可能是有用的，以便能够在应用中使用操作系统无关的排序规则名称。在任何特定的数据库中，只关心使用该数据库编码的排序规则。 `pg_collation`中的其他项目会被忽略。因此，剥离了编码名的排序规则名称，如`de_DE`，在一个给定的数据库中也算是独一无二的，即使它不是全局唯一的。建议使用剥离过的排序规则名称，因为如果你以后决定改变到另一个数据库编码，可以少改变一样东西。但是请注意，无论是什么数据库编码，都可以使用`default`, `C`和`POSIX`排序规则。 PostgreSQL认为不同排序规则对象是不兼容的，即使他们有相同的属性。例如： ``` SELECT a COLLATE "C" < b COLLATE "POSIX" FROM test1; ``` 将引起一个错误，即使`C`和`POSIX` 排序规则具有完全相同的行为。因此不推荐混合剥离的和非剥离的排序规则名。