UTF-8字符集中文排序方法研究

背景

        某项目在列表展示时，使用达梦数据库需要中文字段按照拼音排序展示。经过尝试发现不同字符集、不同数据库中文字段排序不一样。本文从字符集和字符编码开始对中文排序做解读。

字符集与字符编码

        在测试中文排序时，发现如果数据库不做相关设置，在字符集为UTF-8和GB18030下中文排序顺序是不一样的：UTF-8下中文看不出什么顺序，GB18030则是按照拼音排序。为什么两种字符集下中文排序不一样呢？接下来将从字符集的历史沿革中探索他们的前世今生。

字符集

        字符（Character）是各种文字和符号的总称，包括各国文字、标点符号、图形符号、数字等。字符集（Character set）是多个字符的集合，字符集种类较多，每个字符集包含的字符个数不同，譬如ASCII只支持英文，GB18030支持中文等等。常见字符集名称：ASCII字符集、GB2312字符集、BIG5字符集、 GB18030字符集、Unicode字符集等。

字符编码

        定义字符集中的字符如何编码为特定的二进制数，以便在计算机中存储。不同字符集会有多种不同的编码实现。 例如：Unicode字符集有多种编码 UTF-8（8-bit Unicode Transformation Format）、UTF-16 和 UTF-32。

字符集的前世今生

ASCII（American Standard Code for Information Interchange，美国信息交换标准代码）

        计算机刚开始在美国使用，以8位字节组合出128个字符，共包括33个控制字符和95个可显示字符（标点符号、字母、数字），这一字符集被称为ASCII，于1967年被正式公布。

EASCII（Extended ASCII，延伸美国标准信息交换码）

        随着科技惊人的发展，欧洲国家也开始使用上计算机了。不过128个字符明显不够，于是一些欧洲国家就决定，利用字节中闲置的最高位编入新的符号。这样一来，这些欧洲国家的编码体系，可以表示最多256个字符了。 但是，这里又出现了新的问题。不同的国家有不同的字母，因此，哪怕它们都使用256个符号的编码方式，代表的字母却不一样。 EASCII（Extended ASCII，延伸美国标准信息交换码）由此应运而生。EASCII码比ASCII码扩充出来的符号包括表格符号、计算符号、希腊字母和特殊的拉丁符号。

GB2312（中华人民共和国国家标准简体中文字符集，GB为国标汉语拼音的首字母）

        EASCII码基本可以满足欧洲国家的使用，不久，计算机便来到了中国，要知道汉字是世界上包含符号最多并且也是最难学的文字。 据不完全统计，汉字共包含了古文、现代文字等近10万个文字，就是我们现在日常用的汉字也有几千个，那么对于只包含256个字符的EASCII码也难以满足天朝的需求了。 于是⌈中国国家标准总局⌋（现已更名为⌈国家标准化管理委员会⌋）在1981年，正式制订了中华人民共和国国家标准简体中文字符集，全称《信息交换用汉字编码字符集·基本集》，项目代号为GB 2312 或 GB 2312-80（GB为国标汉语拼音的首字母），此套字符集于1981年的5月1日起正式实施。GB2312存储方式基于EUC,共包含7445个字符，6763个汉字和682个其他字符（拉丁字母、希腊字母、日文平假名及片假名字母、俄语西里尔字母）。

BIG5（支持繁体中文，港澳台使用）

        港澳台同胞使用的是繁体字，而中国大陆制定的GB2312编码并不包含繁体字，于是信息工业策进会在1984年与台湾13家厂商签定计划并开始编写并推出BIG5标准。 之后推出的倚天中文系统则基于BIG5码，并在台湾地区取得了巨大的成功。在BIG5诞生后，大部分的电脑软件都使用了Big5码。此套字符集共收录13,060个汉字及441个符号。

Unicode（一统天下，Unicode 1.1开始支持中文）

        在计算机进入中国大陆的相同时期，计算机也迅速发展进入了世界各个国家。 每个国家都有自己的文字，于是每个国家或地区都像中国大陆这样去制定了自己的编码标准，以便能在计算机上正确显示自己国家的符号。 但带来的结果就是国家之间谁也不懂别人的编码，谁也不支持别人的编码。 于是，世界相关组织意识到了这个问题，并开始尝试制定统一的编码标准，以便能够收纳世界所有国家的文字符号。

        国际标准化组织（ISO）及国际电工委员会（IEC）于1984年联合成立了ISO/IEC小组，主要用于开发统一编码项目。ISO/IEC小组在1984年成立后的第三年（即1987年）开始启动ISO 8859标准的编写，ISO 8859是一系列8位字符集的标准，主要为世界各地的不同语言（除CJK）而单独编写的字符集，一共定义了16个字符集：ISO/IEC 8859-1~ISO/IEC 8859-16。

        而Xerox、Apple等软件制造商则于1988年组成了统一码联盟，用于开发统一码项目。 两个组织都在编写统一字符集，但后来他们发现各自在做相同的工作，同时世界上也不需要两个不兼容的字符集，于是两个组织就此合并了双方的工作成果，并为创立一个单一编码表而协同工作。

        1991年，两个组织共同的工作成果Unicode 1.0正式发布，不过Unicode 1.0并不包含CJK字符（即中日韩）。

ISO/IEC 10646 / UCS（Universal Character Set，通用字符集）

        1993年，ISO/IEC 10646标准第一次发表，ISO/IEC 10646是ISO 646 的扩展，标准中定义的字符集为UCS。该字符集包含最初的ISO 10646-1:1993的编码标准，即Unicode 1.1，收录中国大陆、台湾、日本及韩国通用字符集的汉字共计20,902个。

UTF（Unicode Transformation Format，Unicode 面向网络传输）其中UTF-8使用1~4个字节为每个UCS中的字符编码

        Unicode 诞生，随之而来的计算机网络也发展了起来，Unicode 如何在网络上传输也是一个必须考虑的问题，于是在1992年，面向网络传输的UTF标准出现了。 可以把Unicode看作是一个标准或组织，而UCS就是一个字符集，那么UCS在网络中的传输标准就是UTF了。UTF-8采用可变字节编码，这样可以大大节省带宽，并增加网络传输效率。

GB13000（GB13000.1-93国家编码标准，此标准等同于 ISO/IEC 10646.1:1993和Unicode 1.1）

        至1993年时，包含CJK的Unicode 1.1已经发布了，天朝的政府也意识到了需要一个更大的字符集来走向世界，于是在同一年，中国大陆制定了几乎等同于Unicode1.1的GB13000.1-93国家编码标准（简称GB13000）。中华人民共和国信息产业部把Unicode里的所有东东拿过来，然后自己重新修订发布了下，改为了国家标准GB13000。此标准等同于 ISO/IEC 10646.1:1993和Unicode 1.1。

GBK

        1995年，微软也将自己的操作系统市场布局进中国，进入中国随之而来要解决的就是系统的编码兼容问题。 之前的国家编码标准GB2312基本满足了汉字的计算机处理需要，但对于人名、古汉语等方面出现的罕用字和繁体字，不能处理，因此微软利用了GB2312中未使用的编码空间，收录了GB13000中的所有字符制定了汉字内码扩展规范GBK（K为汉语拼音 Kuo Zhan中“扩”字的首字母）。所以这一关系其实是大陆把Unicode1.1借鉴过来改名为了GB13000，而微软则利用GB2312中未使用的编码空间收录GB13000制定了GBK。所以GBK是向下完全兼容GB2312的。

GB18030（包含GBK中的所有字符，并将Unicode中其他中文字符包括少数民族文字、偏僻字，也一并收录进来重新编码）

        微软到了99年前后，准备将字符集全盘转换成UTF-8，于是中国ZF编写并强制推出了GB18030标准。GB18030的诞生还有一个原因是GBK只包含了大部分的汉字和繁体字等，我们的少数民族兄弟根本木有考虑！中国有56个民族，其中有12个民族有自己的文字，那怎么办呢？在2000年，电子工业标准化研究所起草了GB18030标准，项目代号GB 18030-2000，全称《信息技术-信息交换用汉字编码字符集-基本集的扩充》。此标准推出后，在中国大陆之后的所售产品必须强制支持GB18030标准。

综述

        从以上字符集历史沿革来看，对于中文源头上分两大类字符集，一类是由国际标准化组织（ISO）及国际电工委员会（IEC）创立的unicode，另一类是中国国家标准总局发布的GB2312.

MySQL数据库UTF-8与GBK的排序

        因为达梦数据库可兼容MySQL的collate语法，故探索达梦数据库的中文排序问题首先从MySQL开始。接下来验证MySQL在UTF-8和GBK下中文排序：

UTF-8

        utf8字符集下默认的比较规则是utf8_general_ci，日常中会用到的有utf8_general_ci，utf8_unicode_ci，utf8_bin三种比较规则，其他比较规则基本很少会用，下面简单了解下这三种比较规则的异同。

规则	如何比较	是否区分大小写	优缺点
utf8_general_ci（默认）	Unicode字符之间逐个比较	不区分	校对速度快，比较正确性差
utf8_unicode_ci	中英文和utf8_general_ci无实质差别，支持扩展（德语及其他语言）	不区分	校对速度慢，准确度高
utf8_bin	所有字符看作二进制串，然后从最高位往最低位比对	区分	不常用

        一般情况下，选择默认的utf8_general_ci；有德文或者其他特殊字符的选择utf8_unicode_ci；要求区分大小写选择utf8_bin。

• Unicode字符排序规则

  Unicode Collation Algorithm (UCA) 是 Unicode 规定的如何比较两个字符串大小的算法，也是事实上的标准。

  通常情况下，我们是通过unicode 的UTF-16码点值逐个进行比较大小的来进行排序的。

--Linux系统编码 en_US.UTF-8 
--mysql版本5.7.25 
mysql> show variables like '%character%';
Variable_name           |Value                           |
------------------------|--------------------------------|
character_set_client    |utf8mb4                         |
character_set_connection|utf8mb4                         |
character_set_database  |utf8                            |
character_set_filesystem|binary                          |
character_set_results   |                                |
character_set_server    |utf8                            |
character_set_system    |utf8                            |
character_sets_dir      |/usr/local/mysql/share/charsets/|
​
mysql> create table  lxm.zhtest(i varchar(30) , c varchar(30));
  
mysql> insert into lxm.zhtest(i,c) values ('李文文','李文文'),('争光好','争光好'),('刘国强','刘国强'),('思路路','思路路'),('郑和','郑和'),('刘鑫','刘鑫'),('余晓燕','余晓燕'),('史晓燕','史晓燕'),('石晓研','石晓研'),('刘冰','刘冰'),('李四','李四'),('郑洋','郑洋'),('石云','石云'),('余华','余华'),('史湘云','史湘云'),('争艳','争艳'),('A','A'),('a','a'),(';',';'),('12','12'),('(','('),(')',')'),('啊','啊'),('安华','安华'),('一招','一招');
  
mysql> select * from lxm.zhtest z order by c;
i  |c  |
---|---|
(  |(  |
)  |)  |
12 |12 |
;  |;  |
a  |a  |
A  |A  |
一招 |一招 |
争光好|争光好|
争艳 |争艳 |
余华 |余华 |
余晓燕|余晓燕|
刘冰 |刘冰 |
刘国强|刘国强|
刘鑫 |刘鑫 |
史晓燕|史晓燕|
史湘云|史湘云|
啊  |啊  |
安华 |安华 |
思路路|思路路|
李四 |李四 |
李文文|李文文|
石云 |石云 |
石晓研|石晓研|
郑和 |郑和 |
郑洋 |郑洋 |

        由以上排序测试结果可看出默认排序规则依次为符号，数字，英文，汉字。中文采用康熙字典中定义的偏旁索引顺序排序。

• mysql建表如果不指定charset和collate, 默认charset是utf8,collate是 utf8_general_ci, utf8_general_ci是一种通用的比较规则。

GBK

规则	如何比较	是否区分大小写	优缺点
gbk_chinese_ci（默认）	字符之间逐个比较	不区分	校对速度快，比较正确性差
gbk_bin	所有字符看作二进制串，然后从最高位往最低位比对	区分	不常用

• GBK字符排序规则

        符号，数字，英文，汉字。中文采用新华字典中定义的字母顺序排序。

mysql> show variables like '%character%';
Variable_name           |Value                           |
------------------------|--------------------------------|
character_set_client    |utf8mb4                         |
character_set_connection|utf8mb4                         |
character_set_database  |utf8                            |
character_set_filesystem|binary                          |
character_set_results   |                                |
character_set_server    |utf8                            |
character_set_system    |utf8                            |
character_sets_dir      |/usr/local/mysql/share/charsets/|
​
mysql> CREATE TABLE lxm.zhtest1 (
    i varchar(30) , c varchar(30)
) ENGINE=InnoDB DEFAULT CHARSET=gbk  COLLATE=gbk_chinese_ci;
​
mysql> insert into lxm.zhtest1(i,c) values ('李文文','李文文'),('争光好','争光好'),('刘国强','刘国强'),('思路路','思路路'),('郑和','郑和'),('刘鑫','刘鑫'),('余晓燕','余晓燕'),('史晓燕','史晓燕'),('石晓研','石晓研'),('刘冰','刘冰'),('李四','李四'),('郑洋','郑洋'),('石云','石云'),('余华','余华'),('史湘云','史湘云'),('争艳','争艳'),('A','A'),('a','a'),(';',';'),('12','12'),('(','('),(')',')'),('啊','啊'),('安华','安华'),('一招','一招');
insert into  zhtest2(i,c) values ('李文文','李文文'),('争光好','争光好'),('刘国强','刘国强'),('思路路','思路路'),('郑和','郑和'),('刘鑫','刘鑫'),('余晓燕','余晓燕'),('史晓燕','史晓燕'),('石晓研','石晓研'),('刘冰','刘冰'),('李四','李四'),('郑洋','郑洋'),('石云','石云'),('余华','余华'),('史湘云','史湘云'),('争艳','争艳'),('A','A'),('a','a'),(';',';'),('12','12'),('(','('),(')',')'),('啊','啊'),('安华','安华'),('一招','一招');
​
mysql> select * from lxm.zhtest1 z order by c;
i  |c  |
---|---|
(  |(  |
)  |)  |
12 |12 |
;  |;  |
A  |A  |
a  |a  |
啊  |啊  |
安华 |安华 |
李四 |李四 |
李文文|李文文|
刘冰 |刘冰 |
刘国强|刘国强|
刘鑫 |刘鑫 |
石晓研|石晓研|
石云 |石云 |
史湘云|史湘云|
史晓燕|史晓燕|
思路路|思路路|
一招 |一招 |
余华 |余华 |
余晓燕|余晓燕|
争光好|争光好|
争艳 |争艳 |
郑和 |郑和 |
郑洋 |郑洋 |

UTF-8字符集下数据库中文拼音排序

mysql

方法一 排序规则设置

        mysql数据库的字符集和比较规则可以作用于四个级别，分别是：服务器级别、数据库级别、表级别、列级别。服务器级别的字符集和比较规则由collation_server参数控制，如果创建数据库、表、列时没有显式的指定字符集和比较规则，则会继承上一级。

        具体实例上面有，此处不再赘述.

方法二 编码转换

        对字段进行gbk编码，然后，对编码后的内容根据gbk_chinese_ci进行整理排序。

mysql> select * from lxm.zhtest z order by convert(c using gbk) collate gbk_chinese_ci;
i  |c  |
---|---|
(  |(  |
)  |)  |
12 |12 |
;  |;  |
a  |a  |
A  |A  |
啊  |啊  |
安华 |安华 |
李四 |李四 |
李文文|李文文|
刘冰 |刘冰 |
刘国强|刘国强|
刘鑫 |刘鑫 |
石晓研|石晓研|
石云 |石云 |
史湘云|史湘云|
史晓燕|史晓燕|
思路路|思路路|
一招 |一招 |
余华 |余华 |
余晓燕|余晓燕|
争光好|争光好|
争艳 |争艳 |
郑和 |郑和 |
郑洋 |郑洋 |

达梦

方法一 排序规则设置

        达梦数据库在初始化实例时指定字符集 CHARSET=UTF-8,参数COMPATIBLE_MODE设置为4（部分兼容MYSQL）。创建表时指定字段字符集 GB18030 和编码 gb18030_chinese_ci，测试中文排序还是按照 utf8_general_ci 排序，列级别排序规则设置无效。由此得知，达梦数据库只是兼容了MySQL的语法，并没有完全支持MySQL相关功能。

CREATE TABLE LM.TEST(
 a VARCHAR(900),
 b VARCHAR(900) CHARACTER SET GB18030 COLLATE gb18030_chinese_ci
);
insert into lxm.zhtest(i,c) values ('李文文','李文文'),('争光好','争光好'),('刘国强','刘国强'),('思路路','思路路'),('郑和','郑和'),('刘鑫','刘鑫'),('余晓燕','余晓燕'),('史晓燕','史晓燕'),('石晓研','石晓研'),('刘冰','刘冰'),('李四','李四'),('郑洋','郑洋'),('石云','石云'),('余华','余华'),('史湘云','史湘云'),('争艳','争艳'),('A','A'),('a','a'),(';',';'),('12','12'),('(','('),(')',')'),('啊','啊'),('安华','安华'),('一招','一招');
select  * from LM.TEST order by b;
A   B
(   (
)   )
12  12
;   ;
A   A
a   a
一招  一招
争光好 争光好
争艳  争艳
余华  余华
余晓燕 余晓燕
刘冰  刘冰
刘国强 刘国强
刘鑫  刘鑫
史晓燕 史晓燕
史湘云 史湘云
啊   啊
安华  安华
思路路 思路路
李四  李四
李文文 李文文
石云  石云
石晓研 石晓研
郑和  郑和
郑洋  郑洋

方法二 使用函数排序

        使用DM提供的函数 NLSSORT 对中文进行编码转换。

select  * from LM.TEST order by NLSSORT(b,'NLS_SORT=SCHINESE_PINYIN_M');
A   B
;   ;
(   (
)   )
12  12
A   A
a   a
啊   啊
安华  安华
李四  李四
李文文 李文文
刘冰  刘冰
刘国强 刘国强
刘鑫  刘鑫
石晓研 石晓研
石云  石云
史湘云 史湘云
史晓燕 史晓燕
思路路 思路路
一招  一招
余华  余华
余晓燕 余晓燕
争光好 争光好
争艳  争艳
郑和  郑和
郑洋  郑洋

PostgreSQL

方法一 排序规则设置

        在初始化数据库实例时指定 --lc-collate='zh_CN.utf8'后，字段属性排序会按照拼音排序，测试效果如下：

test=# \l test
                           List of databases
 Name | Owner | Encoding |  Collate   |   Ctype    | Access privileges
------+-------+----------+------------+------------+-------------------
 test | sa    | UTF8     | zh_CN.utf8 | zh_CN.utf8 |
(1 row)
test=# create table test_collate(area varchar(30),province varchar(30));
CREATE TABLE
test=#insert into test_collate values('长宁区','上海'),('徐汇区','上海'),('滨海新区','天津'),('昌平区','北京'),('江北区','重庆');
INSERT 0 5
test=# select * from test_collate order by province;
   area   | province
----------+----------
 昌平区   | 北京
 长宁区   | 上海
 徐汇区   | 上海
 滨海新区 | 天津
 江北区   | 重庆
(5 rows)

                在初始化数据库实例时，如不指定--lc-collate='zh_CN.utf8'，系统采用默认的值en_US.UTF-8，这时可以字段级别指定collate 为'zh_CN.utf8'

postgres=# \l postgres
                              List of databases
   Name   | Owner | Encoding |   Collate   |    Ctype    | Access privileges
----------+-------+----------+-------------+-------------+-------------------
 postgres | sa    | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
(1 row)
postgres=# create table test_collate1(area varchar(30),province varchar(30) collate "zh_CN.utf8");
CREATE TABLE
postgres=# insert into test_collate1 values('长宁区','上海'),('徐汇区','上海'),('滨海新区','天津'),('昌平区','北京'),('江北区','重庆');
INSERT 0 5
postgres=# select * from test_collate1 order by province;
   area   | province
----------+----------
 昌平区   | 北京
 长宁区   | 上海
 徐汇区   | 上海
 滨海新区 | 天津
 江北区   | 重庆
(5 rows)

方法二 编码转换和排序规则指定

        如果不通过数据库实例级别或者字段级别设置collate，则可以在查询时将需要排序的列编码转换成GBK，或者查询时通过collate指定排序规则

postgres=# \l postgres
                              List of databases
   Name   | Owner | Encoding |   Collate   |    Ctype    | Access privileges
----------+-------+----------+-------------+-------------+-------------------
 postgres | sa    | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
(1 row)
postgres=# create table test_collate(area varchar(30),province varchar(30));
CREATE TABLE
postgres=# insert into test_collate values('长宁区','上海'),('徐汇区','上海'),('滨海新区','天津'),('昌平区','北京'),('江北区','重庆');
INSERT 0 5
postgres=# select * from test_collate order by province;
   area   | province
----------+----------
 长宁区   | 上海
 徐汇区   | 上海
 昌平区   | 北京
 滨海新区 | 天津
 江北区   | 重庆
(5 rows)
postgres=# select * from test_collate order by convert_to(province,'GBK');
   area   | province
----------+----------
 昌平区   | 北京
 长宁区   | 上海
 徐汇区   | 上海
 滨海新区 | 天津
 江北区   | 重庆
(5 rows)
postgres=# select * from test_collate order by province collate "zh_CN.utf8";
   area   | province
----------+----------
 昌平区   | 北京
 长宁区   | 上海
 徐汇区   | 上海
 滨海新区 | 天津
 江北区   | 重庆
(5 rows)

ORACLE

        oracle 的中文排序在低版本中支持函数 NLSSORT 排序，也可以通过动态修改变量的方式修改中文排序方式，但这种方式需要查询获取结果后临时排序，多少会对性能有影响。

        oracle 12.2+ 开始支持mysql与postgresql中的collate 排序规则特性，支持基于二进制和语言两种排序规则，其用法类似其他数据库。

结论

        字段的排序和索引建立息息相关，如果字段需要中文排序，建议优先选择设置排序规则的方法，避免使用函数排序消耗额外的时间。

参考资料

字符编码介绍

字符编码的前世今生

ORACLE中的列排序规则

更多技术文章参见达梦社区：https://eco.dameng.com