个人中心
退出
数据库实现中文汉字排序终极指南
作者: 不剪发的Tony老师
毕业于北京航空航天大学，十多年数据库管理与开发经验，目前在一家全球性的金融公司从事数据库架构设计。CSDN学院签约讲师以及GitChat专栏作者。csdn上的博客收藏于以下地址：https://tonydong.blog.csdn.net
大家好！我是只谈技术不剪发的 Tony 老师。
在创建数据库或者表时，我们需要指定一个字符集（Charset）和排序规则（Collation）。
字符集决定了数据库能够存储哪些字符，比如 ASCII 字符集只能存储简单的英文、数字和一些控制字符；GB2312 字符集可以存储中文；Unicode 字符集能够支持世界上的各种语言。
排序规则定义了字符集中字符的排序顺序，包括是否区分大小写，是否区分重音等。对于中文而言，排序方式与英文有所不同；中文通常需要按照拼音、偏旁部首或者笔画进行排序。
如果想要支持中文排序，最简单的方式就是使用支持中文排序的字符集和排序规则；但是常见的 Unicode 字符集默认不支持中文排序。所以我们需要解决这种情况下的中文排序问题。
Oracle
Oracle 中支持汉字排序的字符集包括 ZHS16GBK 等。如果使用 AL32UTF8 字符编码则不支持中文排序规则，我们可以通过一个转换函数实现该功能。以下示例按照员工姓名的拼音进行排序（示例表和数据点此下载）：
-- Oracle 实现中文拼音排序
SELECT emp_name
FROM employee
WHERE dept_id = 4
ORDER BY NLSSORT(emp_name,'NLS_SORT = SCHINESE_PINYIN_M');
NLSSORT 是一个 Oracle 系统函数，返回了按照某种排序规则得到的字符序列；SCHINESE_PINYIN_M 表示中文的拼音排序规则。该查询的结果如下：
EMP_NAME|
--------+
关平 |
关兴 |
廖化 |
马岱 |
张苞 |
赵氏 |
赵统 |
赵云 |
周仓 |
除了按照拼音排序之外，Oracle 还支持按照偏旁部首（SCHINESE_RADICAL_M）以及笔画（SCHINESE_STROKE_M）进行中文排序。
MySQL
MySQL 支持中文排序的字符集包括 GBK 等。如果使用默认的 utf8mb4 字符编码，中文按照偏旁部首进行排序。我们可以通过一个转换函数实现其他方式的中文排序，以下查询按照员工姓名的拼音进行排序：
-- MySQL实现中文拼音排序
SELECT emp_name
FROM employee
WHERE dept_id = 4
ORDER BY CONVERT(emp_name USING GBK);
其中，CONVERT 是一个 MySQL 系统函数，用于转换数据的字符集编码，中文 GBK 字符集默认使用拼音进行排序。查询返回的结果和上面的 Oracle 示例相同。
Microsoft SQL Server
Microsoft SQL Server 中的字符集和排序规则是同一个概念，安装数据库时默认根据操作系统所在的区域进行设置，中国地区默认使用 Chinese_PRC_CI_AS 排序规则，对于中文按照偏旁部首进行排序。
我们可以通过 COLLATE 关键字实现其他方式的中文排序，以下查询按照员工姓名的拼音进行排序：
-- Microsoft SQL Server 实现中文拼音排序
SELECT emp_name
FROM employee
WHERE dept_id = 4
ORDER BY emp_name COLLATE Chinese_PRC_CI_AI_KS_WS;
其中，COLLATE 表示按照某种排序规则进行排序，Chinese_PRC_CI_AI_KS_WS 表示中文拼音排序规则。查询返回的结果和上面的 Oracle 示例一样。
另外，Microsoft SQL Server 也支持中文按照笔画进行排序（Chinese_PRC_Stroke_CI_AS）。
PostgreSQL
PostgreSQL 推荐使用 UTF8 编码字符集，中文按照偏旁部首进行排序。我们可以通过 COLLATE 关键字实现其他方式的中文排序，以下查询按照员工姓名的拼音进行排序：
-- PostgreSQL 实现中文拼音排序
SELECT emp_name
FROM employee
WHERE dept_id = 4
ORDER BY emp_name COLLATE "zh_CN";
其中，COLLATE 表示按照某种排序规则进行排序，zh_CN 表示中文拼音排序规则。查询返回的结果和上面的 Oracle 示例一样。
另外，我们也可以使用 CONVERT_TO 函数讲汉字从 UTF8 编码转换为 GBK 编码后按照拼音进行排序：
SELECT emp_name
FROM employee
WHERE dept_id = 4
ORDER BY CONVERT_TO(emp_name,'gbk');
SQLite
SQLite 默认使用 UTF-8 字符编码，中文按照偏旁部首进行排序，不支持其他的排序方式。
SELECT emp_name
FROM employee
WHERE dept_id = 4
ORDER BY emp_name;
emp_name|
--------+
关兴 |
关平 |
周仓 |
廖化 |
张苞 |
赵云 |
赵氏 |
赵统 |
马岱 |
自定义排序
除了使用字符集和排序规则定义的排序顺序之外，我们也可以通过 CASE 表达式为不同的汉字指定一个自定义的排序规则。例如：
SELECT emp_name
FROM employee
WHERE dept_id = 4
ORDER BY CASE
WHEN emp_name LIKE '赵%' THEN 1
WHEN emp_name LIKE '钱%' THEN 2
WHEN emp_name LIKE '孙%' THEN 3
WHEN emp_name LIKE '李%' THEN 4
...
ELSE 999
END;
我们可以继续扩展以上 CASE 表达式，实现按照百家姓的姓氏顺序排列。
为了避免每次都需要编写一个很长的 CASE 表达式，我们也可以创建一个百家姓表：
CREATE TABLE names(id INT PRIMARY KEY, last_name VARCHAR(20));
INSERT INTO names VALUES (1, '赵');
INSERT INTO names VALUES (2, '钱');
INSERT INTO names VALUES (3, '孙');
INSERT INTO names VALUES (4, '李');
...
然后再通过连接查询实现自定义的排序规则，例如：
SELECT e.emp_name
FROM employee e
LEFT JOIN names n ON e.emp_name LIKE concat(n.last_name,'%')
WHERE dept_id = 4
ORDER BY n.id;
总结
本文总结了在数据库中实现中文汉字排序的几种方法：
使用支持中文排序的字符集和排序规则；
在查询语句中使用函数将中文转换为特点的字符集和排序规则；
使用 CASE 表达式或者自定义的字典表实现中文排序。
分类导航