导读

ICU 提供了强大的排序和比较功能，这对全球化应用非常重要。比如，各种语言中的字符排序规则可能各不相同，ICU 可以确保在排序和比较字符串时遵循正确的规则。

最近阅读了 Peter Eisentraut 的关于《ICU排序规则设置》的两篇文章，写了这篇ICU排序实践分享给大家。

根据原作者的环境，我选择了PG15.3和ICU72.1，操作系统是银河麒麟高级服务器操作系统V10（SP2）（也可以使用CentOS8）。

注意：

ICU72.1源码遵循的是C++11标准，所以编译时要使用完全支持C++11的GCC工具集（GCC4.9以上均可）；

编译安装或测试使用Centos7也可以，但Centos7的glibc版本是2.17，而麒麟v10SP2的glibc版本是2.28，这两者的差异导致libc排序不同。这里使用银河麒麟V10SP2，便于对比libc和icu排序规则。

Here we go!!! >>>

一、环境搭建

麒麟V10SP2通过YUM源安装的版本是ICU 62.1（注：CentOS7.9的是ICU 50.2），我试过这样编译安装的 PostgreSQL 虽然启用了 ICU 功能，但是结果可能与那两篇文章不一致。我们要按照 Peter 的环境要求去安装ICU 72.1。

由于ICU 72.1使用的是C++11标准，我们需要完全支持C++11标准的GCC工具集。麒麟V10SP2自带的是GCC7.3.0，是可以满足要求的（CentOS7.9 的GCC版本 4.8.5编译时报错，需要升级。具体升级步骤参照CSDN上的一篇文章：《CentOS7.9 上 GCC4.8.5 升级到10.4.0实践》https://blog.csdn.net/qiuchenjun/article/details/131346391）。

1.1 安装 ICU72.1

首先从Github上克隆一个72.1版本的源码：

  
  
  
   
   
   git clone --branch release-72-1 https://github.com/unicode-org/icu.git icu72.1

进入icu4c源码目录：

  
  
  
   
   
   cd /root/icu72.1/icu4c/source

编译配置：

  
  
  
   
   
   ./configure --prefix=/usr/local

编译安装：

  
  
  
   
   
   CXXFLAGS="-std=c++11 -Wall" make -j $(nproc) && make install

验证安装：

  
  
  
   
   
   PKG_CONFIG_PATH=/usr/local/lib/pkgconfig pkg-config --modversion icu-uc icu-io

输出：

72.1

由此可见安装成功了。

1.2 编译安装 PG15.3

完整安装参考我在CSDN上的一篇文章《PostgreSQL 编译安装通用步骤》（https://blog.csdn.net/qiuchenjun/article/details/131356450）。

编译配置需要这样操作：

  
  
  
   
   
   PKG_CONFIG_PATH=/usr/local/lib/pkgconfig \
   
   
   ./configure --prefix=$PGIPATH --with-libxml --with-ssl=openssl --with-python --with-perl --with-icu

用户（如：postgres）的环境变量：

  
  
  
   
   
   export LD_LIBRARY_PATH=/opt/pgsql15.3/lib:/usr/local/lib:\$LD_LIBRARY_PATH

把ICU库文件的路径添加到LD_LIBRARY_PATH上，以保证PG能对外部ICU的依赖不出问题。

二、指定排序规则

指定ICU排序规则一般是在SELECT时或CREATE TABLE时指定，从PG15开始可以分别在initdb和CREATE DATABASE时指定。

2.1 SELECT 指定

在SELECT语句中指定ICU排序规则前，要创建一个排序规则，如：

  
  
  
   
   
   CREATE COLLATION zh_pinyin (provider = icu, locale = 'zh@collation=pinyin');

然后在查询时指定这个定义的规则：

  
  
  
   
   
   select * from (
   
   
       values ('1','秦浪',''),('2','魏东来',''),('4','徐大嫂',''),('3','张大大',''),('5','张老师',''),('7','周芷诺',''),
   
   
       ('6',' A孙剑',''),('8','董大鹏',''),('9','陈强',''),('10','','这是一个<空串>'),('11',' ','这是一个<空格>'),
   
   
       ('13','a',''),('12','b',''),('14','A',''),('15','#',''),('16','!',''),('18','/',''),('19','9',''),('17','@',''),
   
   
       ('20','Z',''),('21','_',''),('22','~',''),('23','z',''),('25','{',''),('24',NULL,'这是一个空值<NULL>')
   
   
   ) _(id,name,remark) order by name collate zh_pinyin;

这里我们选用了几个汉字，还有ASCII字符，便于区分理解排序。

2.2 初始化时指定

PG15版本开始可以在初始化（initdb）时指定ICU规则，如：

  
  
  
   
   
   initdb -A md5 -D $PGDATA -E 'UTF8' --pwfile=<(printf "%s" "666666") \
   
   
          --locale-provider=icu --icu-locale=zh@collation=pinyin

直接执行SELECT命令不指定collate则会默认使用pinyin排序规则：

  
  
  
   
   
   select * from (
   
   
       values ('1','秦浪',''),('2','魏东来',''),('4','徐大嫂',''),('3','张大大',''),('5','张老师',''),('7','周芷诺',''),
   
   
       ('6',' A孙剑',''),('8','董大鹏',''),('9','陈强',''),('10','','这是一个<空串>'),('11',' ','这是一个<空格>'),
   
   
       ('13','a',''),('12','b',''),('14','A',''),('15','#',''),('16','!',''),('18','/',''),('19','9',''),('17','@',''),
   
   
       ('20','Z',''),('21','_',''),('22','~',''),('23','z',''),('25','{',''),('24',NULL,'这是一个空值<NULL>')
   
   
   ) _(id,name,remark) order by name;

查看数据库信息：

  
  
  
   
   
   postgres=# \l+
   
   
                                                                                           List of databases
   
   
      Name    |  Owner   | Encoding |   Collate   |    Ctype    |     ICU Locale      | Locale Provider | ...   
   
   
   -----------+----------+----------+-------------+-------------+---------------------+-----------------+-----
   
   
    postgres  | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | zh@collation=pinyin | icu             | ...
   
   
    template0 | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | zh@collation=pinyin | icu             | ...
   
   
    template1 | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | zh@collation=pinyin | icu             | ...
   
   
   (3 rows)
   
   
   # 如果初始化时未指定ICU，输出：
   
   
      Name    |  Owner   | Encoding |   Collate   |    Ctype    |     ICU Locale      | Locale Provider | ...   
   
   
   -----------+----------+----------+-------------+-------------+---------------------+-----------------+-----
   
   
    postgres  | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 |                     | libc            | ...
   
   
    template0 | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 |                     | libc            | ...
   
   
    template1 | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 |                     | libc            | ...
   
   
   (3 rows)

在这两个列表中，PostgreSQL 数据库的某些属性被列出。特别是：

Encoding：此数据库的字符编码，这决定了可以在数据库中存储哪些字符。
Collate：此数据库的排序规则。这决定了在数据库中比较和排序文本数据时如何处理字符。
Ctype：此数据库的字符分类规则。这决定了在数据库中如何处理字符的类型和类别，例如大写、小写、字母、数字等。
ICU Locale：此数据库的 ICU 排序规则，如果在初始化时指定了 ICU，则此字段会显示对应的规则。ICU 是一种提供强大本地化支持的库，包括复杂的排序规则和文本处理功能。
Locale Provider：这个字段指明是哪个库提供了 Collate 和 Ctype 的本地化设置，可能是 libc（标准的 C 库）或 ICU。

第一个是在初始化时指定了 ICU，而第二个没有指定。当你在初始化时指定 ICU 时，新建的数据库默认会使用 ICU 提供的本地化设置。如果没有指定，那么新建的数据库会使用 libc 提供的本地化设置。在这两种情况下，都可以在后期创建数据库或表时指定其他的本地化设置。

2.3 创建数据库时指定

同样是PG15开始支持建库（CREATE DATABASE）时指定排序规则：

  
  
  
   
   
   CREATE DATABASE mydb 
   
   
   WITH ENCODING 'utf8'
   
   
        icu_locale = 'zh@collation=pinyin'
   
   
        locale_provider = 'icu'
   
   
        TEMPLATE template0;

在这个CREATE DATABASE命令中，你正在创建一个新的 PostgreSQL 数据库，该数据库有一些特定的设置。

ENCODING 'utf8'：这指定了数据库的字符编码为’utf8’，这是一种普遍的编码格式，它能够处理任何Unicode字符，包括所有的拉丁字符、亚洲字符、和其他语言的字符。
icu_locale = 'zh@collation=pinyin'：这一行指定了ICU本地化设置。ICU是一种强大的库，用于处理本地化和国际化的问题。在这个情况下，你正在指定使用”pinyin”作为排序和比较文本数据的规则，这对于处理中文文本非常有用。
locale_provider = 'icu'：这是一个新选项，用于指定哪个库（libc或ICU）应该提供本地化设置。在这个例子中，你选择了ICU。
TEMPLATE template0：当创建新的数据库时，PostgreSQL会从一个模板数据库中复制所有的设置和系统对象。template0是一个特殊的只读模板，它包含了最小的系统对象集，而且总是被PostgreSQL保持为未改变的状态。使用template0作为模板可以保证新数据库在本地化设置上有最大程度的自由度。

这里创建一个新的数据库mydb，使用UTF-8字符编码，并且使用ICU库提供的本地化设置，特别是使用”pinyin”规则来进行排序和比较。这个数据库会是一个template0的复制品，但是将会有你指定的设置。

查看数据库信息：

  
  
  
   
   
   mydb=# \l+
   
   
                                                                                           List of databases
   
   
      Name    |  Owner   | Encoding |   Collate   |    Ctype    |     ICU Locale      | Locale Provider |  ...   
   
   
   -----------+----------+----------+-------------+-------------+---------------------+-----------------+--------
   
   
    mydb      | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | zh@collation=pinyin | icu             |  ...
   
   
    postgres  | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 |                     | libc            |  ...
   
   
    template0 | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 |                     | libc            |  ...
   
   
    template1 | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 |                     | libc            |  ...
   
   
   (4 rows)

可以看出数据库mydb的Locale Provider是icu，不同于其他库是libc。

2.4 创建表时指定

创建（CREATE TABLE）或修改表（ALTER TABLE）可以通过collate指定排序规则。

同样是需要首先创建一个ICU排序规则：

  
  
  
   
   
   CREATE COLLATION zh_pinyin (provider = icu, locale = 'zh@collation=pinyin');

创建一个表mytable指定排序规则为上面的zh_pinyin：

  
  
  
   
   
   CREATE TABLE mytable (
   
   
       id varchar(10) ,
   
   
       name varchar(10) collate zh_pinyin,
   
   
       remark text
   
   
   );

插入测试数据

  
  
  
   
   
   insert into mytable 
   
   
   select * from (
   
   
       values ('1','秦浪',''),('2','魏东来',''),('4','徐大嫂',''),('3','张大大',''),('5','张老师',''),('7','周芷诺',''),
   
   
       ('6',' A孙剑',''),('8','董大鹏',''),('9','陈强',''),('10','','这是一个<空串>'),('11',' ','这是一个<空格>'),
   
   
       ('13','a',''),('12','b',''),('14','A',''),('15','#',''),('16','!',''),('18','/',''),('19','9',''),('17','@',''),
   
   
       ('20','Z',''),('21','_',''),('22','~',''),('23','z',''),('25','{',''),('24',NULL,'这是一个空值<NULL>')
   
   
   ) _(id,name,remark);

查看默认排序结果：

  
  
  
   
   
   select * from mytable order by name;

2.5 创建索引时指定

在创建索引（CREATE INDEX）时可以指定使用索引时的默认排序规则。

先创建一张表：

  
  
  
   
   
   CREATE TABLE mytable (
   
   
       id varchar(10) ,
   
   
       name varchar(10),
   
   
       remark text
   
   
   );

创建一个按照数字排序的排序规则：

  
  
  
   
   
   CREATE COLLATION zh_pinyin_knt (provider = icu, locale = 'zh-u-co-pinyin-kn-true');

创建索引，指定排序规则：

  
  
  
   
   
   CREATE INDEX idx_mytable_id ON mytable ( id collate zh_pinyin_knt);

三、排序场景

不同的排序规则可能会导致同一组汉字的排序结果不同，因此选择哪种排序规则应当根据具体的使用场景和需求来决定。

创建一个表mytable并插入测试数据：

  
  
  
   
   
   DROP TABLE mytable;
   
   
   CREATE TABLE mytable (
   
   
       id varchar(10) ,
   
   
       name varchar(10) ,
   
   
       remark text
   
   
   );
   
   
   insert into mytable 
   
   
   select * from (
   
   
       values ('1','秦浪',''),('2','魏东来',''),('4','徐大嫂',''),('3','张大大',''),('5','张老师',''),('7','周芷诺',''),
   
   
       ('6',' A孙剑',''),('8','董大鹏',''),('9','陈强',''),('10','','这是一个<空串>'),('11',' ','这是一个<空格>'),
   
   
       ('13','a',''),('12','b',''),('14','A',''),('15','#',''),('16','!',''),('18','/',''),('19','9',''),('17','@',''),
   
   
       ('20','Z',''),('21','_',''),('22','~',''),('23','z',''),('25','{',''),('24',NULL,'这是一个空值<NULL>')
   
   
   ) _(id,name,remark);

3.1 按拼音首字母

创建ICU规则：

  
  
  
   
   
   DROP COLLATION zh_pinyin;
   
   
   CREATE COLLATION zh_pinyin (provider = icu, locale = 'zh@collation=pinyin');

执行SQL查询：

  
  
  
   
   
   select * from mytable order by name collate zh_pinyin;

结果：

  
  
  
   
   
    id |  name  |       remark       
   
   
   ----+--------+--------------------
   
   
    10 |        | 这是一个<空串>
   
   
    11 |        | 这是一个<空格>
   
   
    6  |  A孙剑  | 
   
   
    21 | _      | 
   
   
    16 | !      | 
   
   
    25 | {      | 
   
   
    17 | @      | 
   
   
    18 | /      | 
   
   
    15 | #      | 
   
   
    22 | ~      | 
   
   
    19 | 9      | 
   
   
    9  | 陈强    | 
   
   
    8  | 董大鹏  | 
   
   
    1  | 秦浪    | 
   
   
    2  | 魏东来  | 
   
   
    4  | 徐大嫂  | 
   
   
    3  | 张大大  | 
   
   
    5  | 张老师  | 
   
   
    7  | 周芷诺  | 
   
   
    13 | a      | 
   
   
    14 | A      | 
   
   
    12 | b      | 
   
   
    23 | z      | 
   
   
    20 | Z      | 
   
   
    24 |        | 这是一个空值<NULL>
   
   
   (25 rows)

3.2 按笔画

创建ICU规则：

  
  
  
   
   
   DROP COLLATION zh_stroke;
   
   
   CREATE COLLATION zh_stroke (provider = icu, locale = 'zh@collation=stroke');

执行SELECT示例命令验证：

  
  
  
   
   
   select * from mytable order by name collate zh_stroke;

结果：

  
  
  
   
   
    id |  name  |       remark       
   
   
   ----+--------+--------------------
   
   
    10 |        | 这是一个<空串>
   
   
    11 |        | 这是一个<空格>
   
   
    6  |  A孙剑  | 
   
   
    21 | _      | 
   
   
    16 | !      | 
   
   
    25 | {      | 
   
   
    17 | @      | 
   
   
    18 | /      | 
   
   
    15 | #       | 
   
   
    22 | ~      | 
   
   
    19 | 9      | 
   
   
    3  | 张大大  | 
   
   
    5  | 张老师  | 
   
   
    7  | 周芷诺  | 
   
   
    9  | 陈强    | 
   
   
    4  | 徐大嫂  | 
   
   
    1  | 秦浪    | 
   
   
    8  | 董大鹏  | 
   
   
    2  | 魏东来  | 
   
   
    13 | a      | 
   
   
    14 | A      | 
   
   
    12 | b      | 
   
   
    23 | z      | 
   
   
    20 | Z      | 
   
   
    24 |        | 这是一个空值<NULL>
   
   
   (25 rows)

3.3 字母大小写顺序

前面我们可以看到相同字母是小写在前，大写在后，如：aAbBcCdD。我们可以在排序规则基础上加上一个属性让大写在前面，规则创建命令如下：

  
  
  
   
   
   DROP COLLATION zh_pinyin_kfu;
   
   
   CREATE COLLATION zh_pinyin_kfu (provider = icu, locale = 'zh@collation=pinyin;colCaseFirst=upper;');

执行查询：

  
  
  
   
   
   select * from mytable order by name collate zh_pinyin_kfu;

结果：

  
  
  
   
   
    id |  name  |       remark       
   
   
   ----+--------+--------------------
   
   
    10 |        | 这是一个<空串>
   
   
    11 |        | 这是一个<空格>
   
   
    6  |  A孙剑  | 
   
   
    21 | _      | 
   
   
    16 | !      | 
   
   
    25 | {      | 
   
   
    17 | @      | 
   
   
    18 | /      | 
   
   
    15 | #      | 
   
   
    22 | ~      | 
   
   
    19 | 9      | 
   
   
    9  | 陈强    | 
   
   
    8  | 董大鹏  | 
   
   
    1  | 秦浪    | 
   
   
    2  | 魏东来  | 
   
   
    4  | 徐大嫂  | 
   
   
    3  | 张大大  | 
   
   
    5  | 张老师  | 
   
   
    7  | 周芷诺  | 
   
   
    14 | A      | 
   
   
    13 | a      | 
   
   
    12 | b      | 
   
   
    20 | Z      | 
   
   
    23 | z      | 
   
   
    24 |        | 这是一个空值<NULL>
   
   
   (25 rows)

可以看出后面的相同字母的大写字母排在小写前面了。

3.4 数字字符排序

一般来说，数字组成的字符串排序时不会按照数字的大小顺序排序，如：

  
  
  
   
   
   select string_agg(id,',' order by id) from mytable;

输出：

1,10,11,12,13,14,15,16,17,18,19,2,20,21,22,23,24,25,3,4,5,6,7,8,9

我们可以利用colNumeric属性来创建排序规则，如：

  
  
  
   
   
   DROP COLLATION zh_pinyin_knt;
   
   
   CREATE COLLATION zh_pinyin_knt (provider = icu, locale = 'zh@collation=pinyin;colNumeric=yes');

执行查询：

  
  
  
   
   
   select string_agg(id,',' order by id COLLATE zh_pinyin_knt) from mytable;

输出：

1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25

这个结果是比较理想的。再如复杂一点的：

  
  
  
   
   
   select * from (
   
   
       values ('1'),('1.1'),('1.10'),('1.11'),('1.12'),('1.13'),('1.14A'),('1.15'),('1.14B'),('1.14a'),('1.2'),('1.3'),
   
   
       ('2'), ('2.1'),('2.1.1'),('2.1.10'),('2.1.11'),('2.1.2'),('2.1.12'),('4'),('4.2'),('4.23-1'),('4.23-3'),('4.23-2'),
   
   
       ('4.23.11'),('4.24')
   
   
   ) _(x) order by x collate zh_pinyin_knt;

输出结果对比一下，哪种结果最理想，一目了然，如：

按数字排序（使用`zh_pinyin_knt`）	按字符排序
1	1
1.1	1.1
1.2	1.10
1.3	1.11
1.10	1.12
1.11	1.13
1.12	1.14a
1.13	1.14A
1.14a	1.14B
1.14A	1.15
1.14B	1.2
1.15	1.3
2	2
2.1	2.1
2.1.1	2.1.1
2.1.2	2.1.10
2.1.10	2.1.11
2.1.11	2.1.12
2.1.12	2.1.2
4	4
4.2	4.2
4.23-1	4.23-1
4.23-2	4.23.11
4.23-3	4.23-2
4.23.11	4.23-3
4.24	4.24

<<< There you go!!!

总结

可以看出 ICU 对于排序还是比较灵活的，最重要的是不依赖操作系统。要想在中文中排序，建议使用ICU。

参考链接：ICU 排序规则设置概述（1）https://pgfans.cn/a/2199、ICU 排序规则设置概述（2）https://pgfans.cn/a/2200

本文分享自微信公众号 - 开源软件联盟PostgreSQL分会（kaiyuanlianmeng）。
如有侵权，请联系 support@oschina.cn 删除。
本文参与“OSC源创计划”，欢迎正在阅读的你也加入，一起分享。

PostgreSQL最佳实践：ICU 排序规则的应用

导读

一、环境搭建

1.1 安装 ICU72.1

1.2 编译安装 PG15.3

二、指定排序规则

2.1 SELECT 指定

2.2 初始化时指定

2.3 创建数据库时指定

2.4 创建表时指定

2.5 创建索引时指定

三、排序场景

3.1 按拼音首字母

3.2 按笔画

3.3 字母大小写顺序

3.4 数字字符排序

总结

关于作者

作者的专辑

作者的其它热门文章

热门资讯

热门软件

OSCHINA 社区

在线工具

攻略

公众号

视频号

问题反馈

PostgreSQL最佳实践：ICU 排序规则的应用

导读

一、环境搭建

1.1 安装 ICU72.1

1.2 编译安装 PG15.3

二、指定排序规则

2.1 SELECT 指定

2.2 初始化时指定

2.3 创建数据库时指定

2.4 创建表时指定

2.5 创建索引时指定

三、排序场景

3.1 按拼音首字母

3.2 按笔画

3.3 字母大小写顺序

3.4 数字字符排序

总结

关于作者

作者的专辑

作者的其它热门文章

热门资讯

推荐关注

热门软件

OSCHINA 社区

在线工具

攻略

公众号

视频号

问题反馈