PostgreSQL 关于中文排序问题

今天有同事问到中文排序问题，中文排序问题没有怎么深究，主要参考 bbs 和德哥的帖子，
做些测试记录下。

一 环境信息
--1.1 数据库信息
 francs=> select datname,pg_encoding_to_char(encoding),datcollate,datctype from pg_database where datname='francs';
 datname | pg_encoding_to_char | datcollate | datctype 
---------+---------------------+------------+----------
 francs  | UTF8                | C          | C
(1 row)
备注：测试库字符集 UTF8，数据库排序规则：C，测试版本: PostgreSQL 9.2。

--1.2 创建测试表
 CREATE TABLE tbl_area ( id character varying(32),name character varying(1024) );

INSERT INTO tbl_area (id,name) VALUES ('000001', '香港');
INSERT INTO tbl_area (id,name) VALUES ('000002', '澳门');
INSERT INTO tbl_area (id,name) VALUES ('0100', '呼和浩特');
INSERT INTO tbl_area (id,name) VALUES ('011200', '苏尼特右旗');
INSERT INTO tbl_area (id,name) VALUES ('011300', '苏尼特左旗');
INSERT INTO tbl_area (id,name) VALUES ('011600', '清水河县');
INSERT INTO tbl_area (id,name) VALUES ('011700', '武川县');
INSERT INTO tbl_area (id,name) VALUES ('0118', '乌兰察布');
INSERT INTO tbl_area (id,name) VALUES ('011800', '四王子旗');
INSERT INTO tbl_area (id,name) VALUES ('012000', '集宁区');
  
--1.3 根据 name 排序
 francs=> select * From tbl_area order by name;
   id   |    name    
--------+------------
 0118   | 乌兰察布
 0100   | 呼和浩特
 011800 | 四王子旗
 011700 | 武川县
 011600 | 清水河县
 000002 | 澳门
 011200 | 苏尼特右旗
 011300 | 苏尼特左旗
 012000 | 集宁区
 000001 | 香港
(10 rows)

备注：根据中文字段 name 排序的结果不是我们想要的，参考 bbs 帖子，可以使用 convert_to () 函数转换成目标编码的 bytea 形式解决.
       
二 方法一：使用 convert_to 函数
--2.1 convert_to 函数
 francs=> \df convert_to
                             List of functions
   Schema   |    Name    | Result data type | Argument data types |  Type  
------------+------------+------------------+---------------------+--------
 pg_catalog | convert_to | bytea            | text, name          | normal
(1 row)


francs=> select convert_to('香港','gbk');
 convert_to 
------------
 \xcfe3b8db
(1 row
备注：输出为二进制形式。
 
--2.2 根据 convert_to() 函数输出排序
 francs=> select * From tbl_area order by convert_to(name,'gbk');
   id   |    name    
--------+------------
 000002 | 澳门
 0100   | 呼和浩特
 012000 | 集宁区
 011600 | 清水河县
 011800 | 四王子旗
 011200 | 苏尼特右旗
 011300 | 苏尼特左旗
 0118   | 乌兰察布
 011700 | 武川县
 000001 | 香港
(10 rows)
备注：使用 convert_to() 函数转换后得到了想要的结果。


三 方法二：排序时指定字段的 Collate 

由于创建数据库后不支持更改数据库的 LC_COLLATE （排序规则），但在 order by 时可以指定字段的 Collate 属性，如下：
 
--3.1 排序时指定字段的 collate

 francs=> select * From tbl_area order by name;
   id   |    name    
--------+------------
 0118   | 乌兰察布
 0100   | 呼和浩特
 011800 | 四王子旗
 011700 | 武川县
 011600 | 清水河县
 000002 | 澳门
 011200 | 苏尼特右旗
 011300 | 苏尼特左旗
 012000 | 集宁区
 000001 | 香港
(10 rows)

francs=> select * From tbl_area order by name collate "C";
   id   |    name    
--------+------------
 0118   | 乌兰察布
 0100   | 呼和浩特
 011800 | 四王子旗
 011700 | 武川县
 011600 | 清水河县
 000002 | 澳门
 011200 | 苏尼特右旗
 011300 | 苏尼特左旗
 012000 | 集宁区
 000001 | 香港
(10 rows)

francs=> select * From tbl_area order by name collate "zh_CN.utf8";
   id   |    name    
--------+------------
 000002 | 澳门
 0100   | 呼和浩特
 012000 | 集宁区
 011600 | 清水河县
 011800 | 四王子旗
 011200 | 苏尼特右旗
 011300 | 苏尼特左旗
 0118   | 乌兰察布
 011700 | 武川县
 000001 | 香港
(10 rows)

备注：排序规则“C” 得到的结果不是想要的，  "zh_CN.utf8" 得到了想要的结果，中文字段排序建议使用 "zh_CN.utf8" 排序规则。
 
四 参考

中文排序涉及到字符集，是一个比较复杂的话题，这里不研究了， 以下几篇帖子对中文排序有更深入的描述，强烈推荐。
  
http://blog.163.com/digoal@126/blog/static/163877040201173003547236/
http://bbs.pgsqldb.com/client/post_show.php?zt_auto_bh=54133
http://bbs.pgsqldb.com/client/post_show.php?zt_auto_bh=51890
http://www.postgresql.org/docs/9.2/static/collation.html