PostgreSQL 强大的排序处理规则，你知道吗？-51CTO.COM

介绍

PostgreSQL 的排序规则确定了如何在数据库中执行字符串比较。它会影响排序和相等性检查，这对于涉及文本数据的查询非常重要。了解排序规则，对于确保数据库按预期运行相当重要，尤其是在多语言的应用程序中。

排序规则是指确定如何对数据进行排序和比较的一组规则。在 PostgreSQL 中，可以在数据库、表或列级别定义排序规则。这种灵活性使开发者能够自定义字符串比较的行为，以满足特定的应用程序要求。

示例

查看下测试的实例，这是目前实例上有的数据库对象：

postgres=# \l
                                                        List of databases
   Name    |  Owner   | Encoding | Locale Provider |   Collate   |    Ctype    | ICU Locale  | ICU Rules |   Access privileges  
-----------+----------+----------+-----------------+-------------+-------------+-------------+-----------+-----------------------
 postgres  | postgres | UTF8     | icu             | en_US.UTF-8 | en_US.UTF-8 | en-US-x-icu |           | 
 template0 | postgres | UTF8     | icu             | en_US.UTF-8 | en_US.UTF-8 | en-US-x-icu |           | =c/postgres          +
           |          |          |                 |             |             |             |           | postgres=CTc/postgres
 template1 | postgres | UTF8     | icu             | en_US.UTF-8 | en_US.UTF-8 | en-US-x-icu |           | =c/postgres          +
           |          |          |                 |             |             |             |           | postgres=CTc/postgres
(3 rows)

通过创建一个包含几行的小表，可以很容易地验证，在这样的设置中字符串的排序方式：

CREATE TABLE t ( a text );

INSERT INTO t VALUES ('a'),('A'),('ii'),('II'),('*'),('1.1'),('-');

SELECT * FROM t;
  a
-----
 a
 A
 ii
 II
 *
 1.1
 -
(7 rows)

现在对此进行排序，会得到下面的结果：

SELECT * FROM t ORDER BY 1;
  a
-----
 *
 -
 1.1
 a
 A
 ii
 II
(7 rows)

特殊字符在前，跟着是数字，字符在后面，小写在大写之前。如果我们想用英语的区域设置，来对它们进行排序，会发生什么情况？如文章区域设置和编码所述：如果你想使用 glibc 提供的区域设置，那么需要在操作系统上安装它们：

$ locale -a | grep en_US
en_US.utf8

SELECT * FROM t ORDER BY a COLLATE "en_US.utf8";
  a
-----
 *
 -
 1.1
 a
 A
 ii
 II
(7 rows)

这样给出了完全相同的结果。那么，“C” 或 “POSIX” 呢？

SELECT * FROM t ORDER BY a COLLATE "POSIX";
  a
-----
 *
 -
 1.1
 A
 II
 a
 ii
(7 rows)

现在，大写字符排列在小写字符之前，顺序不再相同。您还可以使用 pg_collation 中列出的 ICU 排序规则：

SELECT * FROM t ORDER BY a COLLATE "zh-Hans-x-icu";
  a
-----
 -
 *
 1.1
 a
 A
 ii
 II
(7 rows)

总结一下：根据您希望如何对字符串进行排序，您需要为此使用正确的排序规则。并非所有语言都遵循相同的规则，如果您想支持多种语言，事情可能会变得更加棘手。