PG中的排序规则

0    863    0

Tags:

👉 本文共约12231个字,系统预计阅读时间或需46分钟。

排序规则支持

排序规则特性允许指定每一列甚至每一个操作的数据的排序顺序和字符分类行为。这放松了数据库的LC_COLLATELC_CTYPE设置自创建以后就不能更改这一限制。

1. 概念

在概念上,一种可排序数据类型的每一种表达式都有一个排序规则(内建的可排序数据类型是textvarcharchar。用户定义的基础类型也可以被标记为可排序的,并且在一种可排序数据类型上的域也是可排序的)。如果该表达式是一个列引用,该表达式的排序规则就是列所定义的排序规则。如果该表达式是一个常量,排序规则就是该常量数据类型的默认排序规则。更复杂表达式的排序规则根据其输入的排序规则得来,如下所述:

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

当数据库系统必须要执行一次排序或者字符分类时,它使用输入表达式的排序规则。这会在使用例如ORDER BY子句以及函数或操作符调用(如<)时发生。应用于ORDER BY子句的排序规则就是排序键的排序规则。应用于函数或操作符调用的排序规则从它们的参数得来,具体如下文所述。除比较操作符之外,在大小写字母之间转换的函数会考虑排序规则,例如lowerupperinitcap。模式匹配操作符和to_char及相关函数也会考虑排序规则。

对于一个函数或操作符调用,其排序规则通过检查在执行指定操作时参数的排序规则来获得。如果该函数或操作符调用的结果是一种可排序的数据类型,万一有外围表达式要求函数或操作符表达式的排序规则,在解析时结果的排序规则也会被用作函数或操作符表达式的排序规则。

一个表达式的排序规则派生可以是显式或隐式。该区别会影响多个不同的排序规则出现在同一个表达式中时如何组合它们。当使用一个COLLATE子句时,将发生显式排序规则派生。所有其他排序规则派生都是隐式的。当多个排序规则需要被组合时(例如在一个函数调用中),将使用下面的规则:

  1. 如果任何一个输入表达式具有一个显式排序规则派生,则在输入表达式之间的所有显式派生的排序规则必须相同,否则将产生一个错误。如果任何一个显式派生的排序规则存在,它就是排序规则组合的结果。
  2. 否则,所有输入表达式必须具有相同的隐式排序规则派生或默认排序规则。如果任何一个非默认排序规则存在,它就是排序规则组合的结果。否则,结果是默认排序规则。
  3. 如果在输入表达式之间存在冲突的非默认隐式排序规则,则组合被认为是具有不确定排序规则。这并非一种错误情况,除非被调用的特定函数要求提供排序规则的知识。如果它确实这样做,运行时将发生一个错误。

例如,考虑这个表定义:

然后在

中,<比较被根据de_DE规则执行,因为表达式组合了一个隐式派生的排序规则和默认排序规则。但是在

中,比较被使用fr_FR规则执行,因为显式排序规则派生重载了隐式排序规则。更进一步,给定

解析器不能确定要应用哪个排序规则,因为a列和b列具有冲突的隐式排序规则。由于<操作符不需要知道到底使用哪一个排序规则,这将会导致一个错误。该错误可以通过在一个输入表达式上附加一个显式排序规则说明符来解决,因此:

或者等效的

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

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

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

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

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

2. 管理排序规则

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

libc提供的一个排序规则对象映射到LC_COLLATELC_CTYPE设置的组合, 如setlocale()系统库调用所接受的。 (正如其名字所说的,一个排序规则的主要目的是设置LC_COLLATE, 它控制排序顺序。但是在实际中LC_CTYPE设置与LC_COLLATE 不同是很少有必要的,因此通过一个概念来收集这些信息比为了设置每一个表达式的 LC_CTYPE而创建另一种架构要更加方便)。此外, 一个libc排序规则是和一个字符集编码(见第 23.3 节) 绑定在一起的。相同的排序规则名字可能存在于不同的编码中。

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

2.1. 标准的排序规则

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

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

2.2. 预定义的排序规则

如果操作系统支持在一个程序中使用多个区域(newlocale和相关函数), 或者配置了ICU支持,那么在一个数据集簇被初始化时,initdb 将以它在操作系统中能找到的所有区域为基础在系统目录pg_collation 中填充排序规则。

要检查当前可用的语言环境,请在psql中使用查询 SELECT * FROM pg_collation或命令\dOS +

2.2.1. libc 排序规则

例如,操作系统可能会提供一个名为de_DE.utf8的区域。initdb则会创建一个用于编码UTF8的名为de_DE.utf8的排序规则,在其中LC_COLLATELC_CTYPE都被设置为de_DE.utf8。它也会创建一个具有去掉名称的.utf8标签的排序规则。这样你也可以使用名字de_DE来使用该排序规则,这写起来更简单并且使得名字更加独立于编码。不过要注意,最初的排序规则名称的集合是平台依赖的。

libc提供的默认排序规则直接映射到操作系统中安装的语言环境, 可以使用命令locale -a列出。如果所需的libc 排序规则与LC_COLLATELC_CTYPE的值不同, 或者在数据库系统初始化之后, 操作系统中安装了新的语言环境,可以使用CREATE COLLATION 命令创建新的排序规则。新的操作系统语言环境也可以使用 pg_import_system_collations() 函数集中导入。

在任何特定的数据库中,只有使用数据库编码的排序规则是令人感兴趣的。其他pg_collation中的项会被忽略。因此,一个如de_DE的被剥离的排序规则名在一个给定数据库中可以被认为是唯一的,即使它在全局上并不唯一。我们推荐使用被剥离的排序规则名,因为在你决定要更改到另一个数据库编码时需要做的事情更少。但是要注意defaultCPOSIX排序规则在使用时可以不考虑数据库编码。

PostgreSQL在碰到具有相同属性的不同排序规则对象时会认为它们是不兼容的。因此对于例子:

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

本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务,私聊QQ646634621或微信db_bao,谢谢!
PG中的排序规则后续精彩内容已被小麦苗无情隐藏,请输入验证码解锁本站所有文章!
验证码:
请先关注本站微信公众号,然后回复“验证码”,获取验证码。在微信里搜索“DB宝”或者“www_xmmup_com”或者微信扫描右侧二维码都可以关注本站微信公众号。

标签:

Avatar photo

小麦苗

学习或考证,均可联系麦老师,请加微信db_bao或QQ646634621

您可能还喜欢...

发表回复

嘿,我是小麦,需要帮助随时找我哦。
  • 18509239930
  • 个人微信

  • DB宝
  • 个人邮箱
  • 点击加入QQ群
  • 个人微店

  • 回到顶部