前一段時(shí)間開發(fā)人員咨詢，說postgresql里面想根據(jù)一個(gè)字段做中文的拼音排序，但是不得其解
環(huán)境：
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 |
--使用排序后的結(jié)果，不是很理想
postgres=# select vname from tbl_kenyon order by vname;
vname
-------
上海
北京
杭州
浙江
(4 rows)
說明：
postgresql的排序除了受到數(shù)據(jù)庫的編碼影響外，還有一個(gè)初始化參數(shù)是locale也會(huì)影響(initdb)，,通常我的選擇是C，這可以讓postgres數(shù)據(jù)庫通過strcmp()這個(gè)函數(shù)來比較字符串，而不是strcoll()函數(shù)。
這個(gè)參數(shù)可以在數(shù)據(jù)庫里查看，如
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)
--簡體中文在系統(tǒng)表里的支持
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)
因?yàn)槌跏蓟瘯r(shí)選擇的locale是C，所以數(shù)據(jù)庫的默認(rèn)排序也是C，要想字段內(nèi)容按照中文拼音排序，需要將UTF8格式存儲(chǔ)的內(nèi)容轉(zhuǎn)換為GBK方式。
解決辦法：
1.轉(zhuǎn)換字段的方式,加個(gè)convert_to前綴函數(shù)
postgres=# select vname from tbl_kenyon order by convert_to(vname,'GBK');
vname
-------
北京
杭州
上海
浙江
(4 rows)
--convert_to函數(shù)輸入?yún)?shù)是text形式,輸出編碼是bytea形式，是將字符轉(zhuǎn)換為目標(biāo)編碼的函數(shù)，如
postgres=# select convert_to('浙江','UTF8'),('浙江','GBK');
convert_to | row
----------------+------------
\xe6b599e6b19f | (浙江,GBK)
(1 row)
2.列指定zh_cn的方式存儲(chǔ)
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.查詢時(shí)指定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函數(shù)轉(zhuǎn)換一段時(shí)間后，開發(fā)告訴我說有異常,報(bào)錯(cuò) 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"
排查了一下，發(fā)現(xiàn)數(shù)據(jù)庫里存了一些比較奇怪的字符導(dǎo)致的，比如Mirclereg; city,niwhitereg;town。后對(duì)該表重建了一下，用方法二解決,所以convert_to函數(shù)使用對(duì)一些奇怪的字符轉(zhuǎn)換時(shí)需要注意。
2.對(duì)于多音字，仍然會(huì)產(chǎn)生一定的歧義，比如重慶，會(huì)按Z去排序
上述辦法能滿足大部分漢字的拼音排序，但仍有一些不足。比較理想的解決辦法是對(duì)這類基礎(chǔ)數(shù)據(jù)錄入時(shí)就指定拼音規(guī)則，或者數(shù)據(jù)庫里存一份數(shù)據(jù)的拼音字典來關(guān)聯(lián)使用。
其他：
使用zh_cn存儲(chǔ)時(shí)測試字段大小，未測試取值速度
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)
存儲(chǔ)差異并不大
補(bǔ)充
#高版本可能不支持,或者語法不對(duì)？
select * from store order by storename collate 'zh_CN';
以上為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。如有錯(cuò)誤或未考慮完全的地方，望不吝賜教。
您可能感興趣的文章:- PostGreSql 判斷字符串中是否有中文的案例
- 自定義函數(shù)實(shí)現(xiàn)單詞排序并運(yùn)用于PostgreSQL(實(shí)現(xiàn)代碼)
- PostgreSQL將數(shù)據(jù)加載到buffer cache中操作方法
- 在PostgreSQL中使用ltree處理層次結(jié)構(gòu)數(shù)據(jù)的方法
- postgresql 中的時(shí)間處理小技巧(推薦)
- Postgresql限制用戶登錄錯(cuò)誤次數(shù)的實(shí)例代碼
- PostgreSQL用戶登錄失敗自動(dòng)鎖定的處理方案
- postgresql影子用戶實(shí)踐場景分析
- 如何使用PostgreSQL進(jìn)行中文全文檢索
