PostgreSQL的中文拼音排序案例
前一段时间开发人员咨询，说postgresql里面想根据一个字段做中文的拼音排序，但是不得其解
环境：
OS：CentOS6.3
DB：PostgreSQL9.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函数转换一段时间后，开发告诉我说有异常,报错characterwithbytesequence0xc20xaeinencoding"UTF8"hasnoequivalentinencoding"GBK"
Errorqueryingdatabase.Cause:org.postgresql.util.PSQLException:ERROR:characterwithbytesequence0xc20xaein encoding"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),repeat('浙江GDOOASASHOME爱你',50000);
INSERT01
postgres=#insertintotbl_kenyonselectrepeat('浙江GDOOASASHOME爱你',100000),repeat('浙江GDOOASASHOME爱你',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)
存储差异并不大
补充
#高版本可能不支持,或者语法不对？ select*fromstoreorderbystorenamecollate'zh_CN';
以上为个人经验，希望能给大家一个参考，也希望大家多多支持毛票票。如有错误或未考虑完全的地方，望不吝赐教。