为什么postgresql中的字符串排序结果会不一样

原创已于 2024-03-04 00:05:37 修改 · 1.2k 阅读

22 ·

CC 4.0 BY-SA版权

文章标签：

#postgresql #数据库

于 2024-02-25 14:24:27 首次发布

转载请注明出处，作者：archimeai

奇怪的排序

在不同的环境上，postgresql对同一个表的排序（order by）结果可能不同。

postgres=# create table t1(s text);
CREATE TABLE
postgres=# insert into t1 values (' b'),('b'),('a'),('c');
INSERT 0 4

postgres=# select * from t1 order by s collate "en_US.utf8";
 s
----
 a
  b
 b
 c
(4 rows)

postgres=# select * from t1 order by s collate "C";
 s
----
  b
 a
 b
 c

可以看到，当使用en_US.utf8 collation时，前面带了一个空格的b排到了a的后面，而当使用 C collation时，两者的顺序又反过来了，这是为什么呢？

不同的排序方法

核心参考资料：https://www.postgresql.org/docs/current/collation.html

从postgresql的文档中可知，字符串的排序是通过collate相关的参数来控制的。在不同的环境上，默认使用的collation可能不同，这就导致在不同的环境上order by的结果不同。

简单来讲，collation C的含义是按照字符编码的字节值进行排序（注意相同的字符在不同的编码方式下可能具有不同的编码值）。这种情况下，空格+b的utf8编码值是 0020 0062 （均为16进制），a的utf8编码为0061，b的utf8编码为0062，c的utf8编码为0063。0020最小，因此排在最前面。

postgres=# select * from t1 order by s collate "C";
 s
----
  b  -- 0020 0062
 a   -- 0061
 b   -- 0062
 c   -- 0063

collation en_US.utf8的含义是按照美国英语的规范对unicode字符进行排序，笔者将在下一节中介绍。

unicode中的排序规则

核心参考资料：

https://www.unicode.org/reports/tr10/
https://unix.stackexchange.com/questions/252419/unexpected-sort-order-in-en-us-utf-8-locale
https://unicode-org.github.io/icu/userguide/collation/concepts.html

Unicode中定义了复杂的字符串排序规则，这一规则可以被不严谨地简化为一个多趟排序的过程。注意只有在前一趟排序中存在无法比较大小的情况时，才需要下一趟排序对无法比较大小的值进行排序。在Unicode提供的all_keys.txt中，定义了所有Unicode字符在每一趟排序中的先后顺序。还是以文章一开头的排序为例子，所涉及的字符的排序顺序定义如下。

// 顺序定义在 https://www.unicode.org/Public/UCA/latest/allkeys.txt
0020  ; [*0209.0020.0002] # SPACE
0061  ; [.20A9.0020.0002] # LATIN SMALL LETTER A
0062  ; [.20C3.0020.0002] # LATIN SMALL LETTER B
0063  ; [.20DD.0020.0002] # LATIN SMALL LETTER C

上述定义中，第一个数字是字符在unicode中的编码值。后面方括号中有三个值，代表unicode排序中的权重（先后顺序，权重越小越靠前）

第一个权重代表在第一趟排序中大小比较的依据，如果以.开头，就代表这个字符在第一趟排序中不能被忽略，这种字符被叫做base character。如果以*开头，就代表这个字符在第一、二、三、四趟排序中的情况都可以动态选择，这种字符被叫做variable character，注意空格就是variable character。在en_US.utf8中，第一、二、三趟排序需要忽略space，也即需要忽略空格。
第二个权重代表在第二趟排序中大小比较的依据
第三个权重代表在第三趟排序中大小比较的依据。
第一趟排序仅对“base character"进行排序，忽略其它字符。

所以，上述order by语句在第一趟排序时假设情况如下：

 a  -- 20A9
 b  -- 20C3
  b -- 20C3  空格被忽略了
 c  -- 20DD

无法区分空格+b 和b的大小，需要进行第二趟排序，只考虑上述两个值。注意因为是第二趟排序，需要使用第二个权重。

 b  -- 0020
  b -- 0020  空格被忽略了

还是无法区分大小，进行第三趟排序

 b  -- 0002
  b -- 0002  空格被忽略了

还是无法区分大小，怎么办呢？这个时候要用上一个特殊规定，即在en_US.utf8中，对于前三趟都区分不出大小的字符串，如果其中插入了一个variable character，则插入这个variable character会使得其排在前面。这样我们终于可以把空格+b排到b前面了。

b
 b

综合上面的情况，就有了postgresql中的排序结果

postgres=# select * from t1 order by s collate "en_US.utf8";
 s
----
 a
  b
 b
 c
(4 rows)

上述解释只是对unicode排序规则的一个非常简单化的解释，忽略了很多重要的细节，想要进一步详细了解的读者请移步阅读本节开头提供的参考资料。

其它参考资料

/usr/share/i18n/locales/en_US
https://linuxconfig.org/how-to-install-generate-and-list-locale-on-linux
https://superuser.com/questions/1551967/what-does-locale-gen-generate-in-linux