個(gè)人中心
退出
數(shù)據(jù)庫(kù)實(shí)現(xiàn)中文漢字排序終極指南
作者: 不剪發(fā)的Tony老師
畢業(yè)于北京航空航天大學(xué)，十多年數(shù)據(jù)庫(kù)管理與開(kāi)發(fā)經(jīng)驗(yàn)，目前在一家全球性的金融公司從事數(shù)據(jù)庫(kù)架構(gòu)設(shè)計(jì)。CSDN學(xué)院簽約講師以及GitChat專欄作者。csdn上的博客收藏于以下地址：https://tonydong.blog.csdn.net
大家好！我是只談技術(shù)不剪發(fā)的 Tony 老師。
在創(chuàng)建數(shù)據(jù)庫(kù)或者表時(shí)，我們需要指定一個(gè)字符集（Charset）和排序規(guī)則（Collation）。
字符集決定了數(shù)據(jù)庫(kù)能夠存儲(chǔ)哪些字符，比如 ASCII 字符集只能存儲(chǔ)簡(jiǎn)單的英文、數(shù)字和一些控制字符；GB2312 字符集可以存儲(chǔ)中文；Unicode 字符集能夠支持世界上的各種語(yǔ)言。
排序規(guī)則定義了字符集中字符的排序順序，包括是否區(qū)分大小寫，是否區(qū)分重音等。對(duì)于中文而言，排序方式與英文有所不同；中文通常需要按照拼音、偏旁部首或者筆畫進(jìn)行排序。
如果想要支持中文排序，最簡(jiǎn)單的方式就是使用支持中文排序的字符集和排序規(guī)則；但是常見(jiàn)的 Unicode 字符集默認(rèn)不支持中文排序。所以我們需要解決這種情況下的中文排序問(wèn)題。
Oracle
Oracle 中支持漢字排序的字符集包括 ZHS16GBK 等。如果使用 AL32UTF8 字符編碼則不支持中文排序規(guī)則，我們可以通過(guò)一個(gè)轉(zhuǎn)換函數(shù)實(shí)現(xiàn)該功能。以下示例按照員工姓名的拼音進(jìn)行排序（示例表和數(shù)據(jù)點(diǎn)此下載）：
-- Oracle 實(shí)現(xiàn)中文拼音排序
SELECT emp_name
FROM employee
WHERE dept_id = 4
ORDER BY NLSSORT(emp_name,'NLS_SORT = SCHINESE_PINYIN_M');
NLSSORT 是一個(gè) Oracle 系統(tǒng)函數(shù)，返回了按照某種排序規(guī)則得到的字符序列；SCHINESE_PINYIN_M 表示中文的拼音排序規(guī)則。該查詢的結(jié)果如下：
EMP_NAME|
--------+
關(guān)平 |
關(guān)興 |
廖化 |
馬岱 |
張苞 |
趙氏 |
趙統(tǒng) |
趙云 |
周倉(cāng) |
除了按照拼音排序之外，Oracle 還支持按照偏旁部首（SCHINESE_RADICAL_M）以及筆畫（SCHINESE_STROKE_M）進(jìn)行中文排序。
MySQL
MySQL 支持中文排序的字符集包括 GBK 等。如果使用默認(rèn)的 utf8mb4 字符編碼，中文按照偏旁部首進(jìn)行排序。我們可以通過(guò)一個(gè)轉(zhuǎn)換函數(shù)實(shí)現(xiàn)其他方式的中文排序，以下查詢按照員工姓名的拼音進(jìn)行排序：
-- MySQL實(shí)現(xiàn)中文拼音排序
SELECT emp_name
FROM employee
WHERE dept_id = 4
ORDER BY CONVERT(emp_name USING GBK);
其中，CONVERT 是一個(gè) MySQL 系統(tǒng)函數(shù)，用于轉(zhuǎn)換數(shù)據(jù)的字符集編碼，中文 GBK 字符集默認(rèn)使用拼音進(jìn)行排序。查詢返回的結(jié)果和上面的 Oracle 示例相同。
Microsoft SQL Server
Microsoft SQL Server 中的字符集和排序規(guī)則是同一個(gè)概念，安裝數(shù)據(jù)庫(kù)時(shí)默認(rèn)根據(jù)操作系統(tǒng)所在的區(qū)域進(jìn)行設(shè)置，中國(guó)地區(qū)默認(rèn)使用 Chinese_PRC_CI_AS 排序規(guī)則，對(duì)于中文按照偏旁部首進(jìn)行排序。
我們可以通過(guò) COLLATE 關(guān)鍵字實(shí)現(xiàn)其他方式的中文排序，以下查詢按照員工姓名的拼音進(jìn)行排序：
-- Microsoft SQL Server 實(shí)現(xiàn)中文拼音排序
SELECT emp_name
FROM employee
WHERE dept_id = 4
ORDER BY emp_name COLLATE Chinese_PRC_CI_AI_KS_WS;
其中，COLLATE 表示按照某種排序規(guī)則進(jìn)行排序，Chinese_PRC_CI_AI_KS_WS 表示中文拼音排序規(guī)則。查詢返回的結(jié)果和上面的 Oracle 示例一樣。
另外，Microsoft SQL Server 也支持中文按照筆畫進(jìn)行排序（Chinese_PRC_Stroke_CI_AS）。
PostgreSQL
PostgreSQL 推薦使用 UTF8 編碼字符集，中文按照偏旁部首進(jìn)行排序。我們可以通過(guò) COLLATE 關(guān)鍵字實(shí)現(xiàn)其他方式的中文排序，以下查詢按照員工姓名的拼音進(jìn)行排序：
-- PostgreSQL 實(shí)現(xiàn)中文拼音排序
SELECT emp_name
FROM employee
WHERE dept_id = 4
ORDER BY emp_name COLLATE "zh_CN";
其中，COLLATE 表示按照某種排序規(guī)則進(jìn)行排序，zh_CN 表示中文拼音排序規(guī)則。查詢返回的結(jié)果和上面的 Oracle 示例一樣。
另外，我們也可以使用 CONVERT_TO 函數(shù)講漢字從 UTF8 編碼轉(zhuǎn)換為 GBK 編碼后按照拼音進(jìn)行排序：
SELECT emp_name
FROM employee
WHERE dept_id = 4
ORDER BY CONVERT_TO(emp_name,'gbk');
SQLite
SQLite 默認(rèn)使用 UTF-8 字符編碼，中文按照偏旁部首進(jìn)行排序，不支持其他的排序方式。
SELECT emp_name
FROM employee
WHERE dept_id = 4
ORDER BY emp_name;
emp_name|
--------+
關(guān)興 |
關(guān)平 |
周倉(cāng) |
廖化 |
張苞 |
趙云 |
趙氏 |
趙統(tǒng) |
馬岱 |
自定義排序
除了使用字符集和排序規(guī)則定義的排序順序之外，我們也可以通過(guò) CASE 表達(dá)式為不同的漢字指定一個(gè)自定義的排序規(guī)則。例如：
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;
我們可以繼續(xù)擴(kuò)展以上 CASE 表達(dá)式，實(shí)現(xiàn)按照百家姓的姓氏順序排列。
為了避免每次都需要編寫一個(gè)很長(zhǎng)的 CASE 表達(dá)式，我們也可以創(chuàng)建一個(gè)百家姓表：
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, '李');
...
然后再通過(guò)連接查詢實(shí)現(xiàn)自定義的排序規(guī)則，例如：
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;
總結(jié)
本文總結(jié)了在數(shù)據(jù)庫(kù)中實(shí)現(xiàn)中文漢字排序的幾種方法：
使用支持中文排序的字符集和排序規(guī)則；
在查詢語(yǔ)句中使用函數(shù)將中文轉(zhuǎn)換為特點(diǎn)的字符集和排序規(guī)則；
使用 CASE 表達(dá)式或者自定義的字典表實(shí)現(xiàn)中文排序。
分類導(dǎo)航