PostgreSQL的中文拼音排序

前一段时间开发人员咨询，说postgresql里面想根据一个字段做中文的拼音排序，但是不得其解。So,Take a Loooook。

环境：
OS：CentOS 6.3
DB：PostgreSQL 9.2.4
TABLE: tbl_kenyon

场景:

postgres=#dtbl_kenyon
Table"public.tbl_kenyon"
Column|Type|Modifiers
--------+------+---------------
vname|text|

--使用排序后的结果，不是很理想
postgres=#selectvnamefromtbl_kenyonorderbyvname;
vname
-------
上海
北京
杭州
浙江
(4rows)

说明：
postgresql的排序除了受到数据库的编码影响外，还有一个初始化参数是locale也会影响(initdb)，,通常我的选择是C，这可以让postgres数据库通过strcmp()这个函数来比较字符串，而不是strcoll()函数。这个参数可以在数据库里查看，如

postgres=#l
Listofdatabases
Name|Owner|Encoding|Collate|Ctype|Accessprivileges
-----------------+----------+----------+---------+-------+-----------------------
dkenyon|u_kenyon|UTF8|C|C|
postgres|postgres|UTF8|C|C|
template0|postgres|UTF8|C|C|=c/postgres+
|||||postgres=CTc/postgres
template1|postgres|UTF8|C|C|=c/postgres+
|||||postgres=CTc/postgres
(6rows)

--简体中文在系统表里的支持
postgres=#selectcollname,collcollate,collctype,b.nspname,c.rolnameascollowner
postgres-#frompg_collationa,pg_namespaceb,pg_authidc
postgres-#wherea.collnamespace=b.oidanda.collowner=c.oidandlower(collname)like'%zh_cn%';
collname|collcollate|collctype|nspname|collowner
--------------+--------------+--------------+------------+-----------
zh_CN|zh_CN|zh_CN|pg_catalog|postgres
zh_CN|zh_CN.utf8|zh_CN.utf8|pg_catalog|postgres
zh_CN.gb2312|zh_CN.gb2312|zh_CN.gb2312|pg_catalog|postgres
zh_CN.utf8|zh_CN.utf8|zh_CN.utf8|pg_catalog|postgres
(4rows)

因为初始化时选择的locale是C，所以数据库的默认排序也是C，要想字段内容按照中文拼音排序，需要将UTF8格式存储的内容转换为GBK方式。

解决办法
1.转换字段的方式,加个convert_to前缀函数

postgres=#selectvnamefromtbl_kenyonorderbyconvert_to(vname,'GBK');
vname
-------
北京
杭州
上海
浙江
(4rows)

--convert_to函数输入参数是text形式,输出编码是bytea形式，是将字符转换为目标编码的函数,如
postgres=#selectconvert_to('浙江','UTF8'),('浙江','GBK');
convert_to|row
----------------+------------
xe6b599e6b19f|(浙江,GBK)
(1row)

2.列指定zh_cn的方式存储

postgres=#altertabletbl_kenyonaddcnametextcollate"zh_CN";
ALTERTABLE
postgres=#dtbl_kenyon
Table"public.tbl_kenyon"
Column|Type|Modifiers
--------+------+---------------
vname|text|
cname|text|collatezh_CN

postgres=#select*fromtbl_kenyon;
vname|cname
-------+-------
浙江|浙江
杭州|杭州
上海|上海
北京|北京
(4rows)

postgres=#select*fromtbl_kenyonorderbyvname;
vname|cname
-------+-------
上海|上海
北京|北京
杭州|杭州
浙江|浙江
(4rows)

postgres=#select*fromtbl_kenyonorderbycname;
vname|cname
-------+-------
北京|北京
杭州|杭州
上海|上海
浙江|浙江
(4rows)

3.查询时指定collate

postgres=#select*fromtbl_kenyonorderbyvnamecollate"C";
vname|cname
-------+-------
上海|上海
北京|北京
杭州|杭州
浙江|浙江
(4rows)

postgres=#select*fromtbl_kenyonorderbyvnamecollate"zh_CN";
vname|cname
-------+-------
北京|北京
杭州|杭州
上海|上海
浙江|浙江
(4rows)

其他问题：
1.在用了方法一的convert_to函数转换一段时间后，开发告诉我说有异常,报错 character with byte sequence 0xc2 0xae in encoding "UTF8" has no equivalent in encoding "GBK"

###Errorqueryingdatabase.Cause:org.postgresql.util.PSQLException:ERROR:characterwithbytesequence0xc20xaeinencoding"UTF8"hasnoequivalentinencoding"GBK"

排查了一下，发现数据库里存了一些比较奇怪的字符导致的，比如Mircle® city,niwhite®town。后对该表重建了一下，用方法二解决,所以convert_to函数使用对一些奇怪的字符转换时需要注意。

2.对于多音字，仍然会产生一定的歧义，比如重庆，会按Z去排序

上述办法能满足大部分汉字的拼音排序，但仍有一些不足。比较理想的解决办法是对这类基础数据录入时就指定拼音规则，或者数据库里存一份数据的拼音字典来关联使用。

其他：
使用zh_cn存储时测试字段大小，未测试取值速度

postgres=#insertintotbl_kenyonselectrepeat('浙江GDOOASASHOME爱你',5000),repeat('浙江GDOOASASHOME爱你',5000);
INSERT01
postgres=#insertintotbl_kenyonselectrepeat('浙江GDOOASASHOME爱你',50000),50000);
INSERT01
postgres=#insertintotbl_kenyonselectrepeat('浙江GDOOASASHOME爱你',100000),100000);
INSERT01

postgres=#selectpg_column_size(cname),pg_column_size(vname)fromtbl_kenyon;
pg_column_size|pg_column_size
----------------+----------------
1410|1406
13769|13769
27506|27506
(3rows)

存储差异并不大

参考： http://www.postgresql.org/docs/9.2/static/collation.html http://blog.163.com/digoal@126/blog/static/163877040201173003547236/ http://en.wikipedia.org/wiki/Collation

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!