前一段時間開發人員咨詢，說postgresql里面想根據一個字段做中文的拼音排序，但是不得其解
環境：
OS：CentOS 6.3
DB：PostgreSQL 9.2.4
TABLE: tbl_kenyon
場景：
postgres=# \d tbl_kenyon
Table "public.tbl_kenyon"
Column | Type | Modifiers
--------+------+---------------
vname | text |
--使用排序后的結果，不是很理想
postgres=# select vname from tbl_kenyon order by vname;
vname
-------
上海
北京
杭州
浙江
(4 rows)
說明：
postgresql的排序除了受到數據庫的編碼影響外，還有一個初始化參數是locale也會影響(initdb)，,通常我的選擇是C，這可以讓postgres數據庫通過strcmp()這個函數來比較字符串，而不是strcoll()函數。
這個參數可以在數據庫里查看，如
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------------+----------+----------+---------+-------+-----------------------
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
(6 rows)
--簡體中文在系統表里的支持
postgres=# select collname,collcollate,collctype,b.nspname,c.rolname as collowner
postgres-# from pg_collation a,pg_namespace b,pg_authid c
postgres-# where a.collnamespace = b.oid and a.collowner = c.oid and lower(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
(4 rows)
因為初始化時選擇的locale是C，所以數據庫的默認排序也是C，要想字段內容按照中文拼音排序，需要將UTF8格式存儲的內容轉換為GBK方式。
解決辦法：
1.轉換字段的方式,加個convert_to前綴函數
postgres=# select vname from tbl_kenyon order by convert_to(vname,'GBK');
vname
-------
北京
杭州
上海
浙江
(4 rows)
--convert_to函數輸入參數是text形式,輸出編碼是bytea形式，是將字符轉換為目標編碼的函數，如
postgres=# select convert_to('浙江','UTF8'),('浙江','GBK');
convert_to | row
----------------+------------
\xe6b599e6b19f | (浙江,GBK)
(1 row)
2.列指定zh_cn的方式存儲
postgres=# alter table tbl_kenyon add cname text collate "zh_CN";
ALTER TABLE
postgres=# \d tbl_kenyon
Table "public.tbl_kenyon"
Column | Type | Modifiers
--------+------+---------------
vname | text |
cname | text | collate zh_CN
postgres=# select * from tbl_kenyon;
vname | cname
-------+-------
浙江 | 浙江
杭州 | 杭州
上海 | 上海
北京 | 北京
(4 rows)
postgres=# select * from tbl_kenyon order by vname;
vname | cname
-------+-------
上海 | 上海
北京 | 北京
杭州 | 杭州
浙江 | 浙江
(4 rows)
postgres=# select * from tbl_kenyon order by cname;
vname | cname
-------+-------
北京 | 北京
杭州 | 杭州
上海 | 上海
浙江 | 浙江
(4 rows)
3.查詢時指定collate
postgres=# select * from tbl_kenyon order by vname collate "C";
vname | cname
-------+-------
上海 | 上海
北京 | 北京
杭州 | 杭州
浙江 | 浙江
(4 rows)
postgres=# select * from tbl_kenyon order by vname collate "zh_CN";
vname | cname
-------+-------
北京 | 北京
杭州 | 杭州
上海 | 上海
浙江 | 浙江
(4 rows)
其他問題：
1.在用了方法一的convert_to函數轉換一段時間后，開發告訴我說有異常,報錯 character with byte sequence 0xc2 0xae in encoding "UTF8" has no equivalent in encoding "GBK"
Error querying database. Cause: org.postgresql.util.PSQLException: ERROR: character with byte sequence 0xc2 0xae in
encoding "UTF8" has no equivalent in encoding "GBK"
排查了一下，發現數據庫里存了一些比較奇怪的字符導致的，比如Mirclereg; city,niwhitereg;town。后對該表重建了一下，用方法二解決,所以convert_to函數使用對一些奇怪的字符轉換時需要注意。
2.對于多音字，仍然會產生一定的歧義，比如重慶，會按Z去排序
上述辦法能滿足大部分漢字的拼音排序，但仍有一些不足。比較理想的解決辦法是對這類基礎數據錄入時就指定拼音規則，或者數據庫里存一份數據的拼音字典來關聯使用。
其他：
使用zh_cn存儲時測試字段大小，未測試取值速度
postgres=# insert into tbl_kenyon select repeat('浙江GDOOASASHOME愛你',5000), repeat('浙江GDOOASASHOME愛你',5000) ;
INSERT 0 1
postgres=# insert into tbl_kenyon select repeat('浙江GDOOASASHOME愛你',50000), repeat('浙江GDOOASASHOME愛你',50000) ;
INSERT 0 1
postgres=# insert into tbl_kenyon select repeat('浙江GDOOASASHOME愛你',100000), repeat('浙江GDOOASASHOME愛你',100000) ;
INSERT 0 1
postgres=# select pg_column_size(cname),pg_column_size(vname) from tbl_kenyon ;
pg_column_size | pg_column_size
----------------+----------------
1410 | 1406
13769 | 13769
27506 | 27506
(3 rows)
存儲差異并不大
補充
#高版本可能不支持,或者語法不對？
select * from store order by storename collate 'zh_CN';
以上為個人經驗，希望能給大家一個參考，也希望大家多多支持腳本之家。如有錯誤或未考慮完全的地方，望不吝賜教。
您可能感興趣的文章:- PostGreSql 判斷字符串中是否有中文的案例
- 自定義函數實現單詞排序并運用于PostgreSQL(實現代碼)
- PostgreSQL將數據加載到buffer cache中操作方法
- 在PostgreSQL中使用ltree處理層次結構數據的方法
- postgresql 中的時間處理小技巧(推薦)
- Postgresql限制用戶登錄錯誤次數的實例代碼
- PostgreSQL用戶登錄失敗自動鎖定的處理方案
- postgresql影子用戶實踐場景分析
- 如何使用PostgreSQL進行中文全文檢索
