PostgreSQL的中文拼音排序案例
前一段時間開發(fā)人員咨詢，說postgresql里面想根據(jù)一個字段做中文的拼音排序，但是不得其解
環(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ù)庫的編碼影響外，還有一個初始化參數(shù)是locale也會影響(initdb)，,通常我的選擇是C，這可以讓postgres數(shù)據(jù)庫通過strcmp()這個函數(shù)來比較字符串，而不是strcoll()函數(shù)。
這個參數(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)
因為初始化時選擇的locale是C，所以數(shù)據(jù)庫的默認排序也是C，要想字段內(nèi)容按照中文拼音排序，需要將UTF8格式存儲的內(nèi)容轉(zhuǎn)換為GBK方式。
解決辦法：
1.轉(zhuǎn)換字段的方式,加個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的方式存儲
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函數(shù)轉(zhuǎn)換一段時間后，開發(fā)告訴我說有異常,報錯 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)致的，比如Mircle® city,niwhite®town。后對該表重建了一下，用方法二解決,所以convert_to函數(shù)使用對一些奇怪的字符轉(zhuǎn)換時需要注意。
2.對于多音字，仍然會產(chǎn)生一定的歧義，比如重慶，會按Z去排序
上述辦法能滿足大部分漢字的拼音排序，但仍有一些不足。比較理想的解決辦法是對這類基礎(chǔ)數(shù)據(jù)錄入時就指定拼音規(guī)則，或者數(shù)據(jù)庫里存一份數(shù)據(jù)的拼音字典來關(guān)聯(lián)使用。
其他：
使用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';
以上為個人經(jīng)驗，希望能給大家一個參考，也希望大家多多支持腳本之家。如有錯誤或未考慮完全的地方，望不吝賜教。
相關(guān)文章
Mybatis調(diào)用PostgreSQL存儲過程實現(xiàn)數(shù)組入?yún)鬟f
這篇文章主要介紹了mybatis調(diào)用postgresql自定義函數(shù)傳遞數(shù)組參數(shù)的解決方案,具有一定的參考價值，感興趣的小伙伴們可以參考一下2016-11-11
postgresql 循環(huán)函數(shù)的簡單實現(xiàn)操作
這篇文章主要介紹了postgresql 循環(huán)函數(shù)的簡單實現(xiàn)操作，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2021-01-01
PostgreSQL將數(shù)據(jù)加載到buffer cache中操作方法
這篇文章主要介紹了PostgreSQL將數(shù)據(jù)加載到buffer cache中，我們可以使用pg_prewarm插件來將指定的表加載到OS Buffer或者pg shared buffer中，具體操作方法跟隨小編一起看看吧2021-04-04
postgreSQL數(shù)據(jù)庫 實現(xiàn)向表中快速插入1000000條數(shù)據(jù)
這篇文章主要介紹了postgreSQL數(shù)據(jù)庫 實現(xiàn)向表中快速插入1000000條數(shù)據(jù)，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2020-12-12
PostgreSQL中json數(shù)據(jù)類型詳解
json數(shù)據(jù)也可以被存儲為text，但是 與text數(shù)據(jù)類型相比，JSON 數(shù)據(jù)類型的優(yōu)勢在于能強制要求每個被存儲的值符合 JSON 規(guī)則，這篇文章主要介紹了PostgreSQL中json數(shù)據(jù)類型,需要的朋友可以參考下2023-04-04
這篇文章主要介紹了postgresql 利用xlog進行熱備操作，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2021-01-01
pgsql之create user與create role的區(qū)別介紹
這篇文章主要介紹了pgsql之create user與create role的區(qū)別介紹，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2021-01-01
這篇文章給大家介紹了PostgreSQL拆分字符串的三種方式,字符串轉(zhuǎn)為數(shù)組,字符串轉(zhuǎn)為列表和字符串轉(zhuǎn)為數(shù)據(jù)項,并通過代碼示例給大家介紹的非常詳細,需要的朋友可以參考下2024-01-01
PostgreSQL自定義函數(shù)并且調(diào)用方式
這篇文章主要介紹了PostgreSQL如何自定義函數(shù)并且調(diào)用，本文通過示例代碼給大家介紹的非常詳細，對大家的學(xué)習(xí)或工作具有一定的參考借鑒價值,需要的朋友可以參考下2023-06-06
PostgreSQL 恢復(fù)誤刪數(shù)據(jù)的操作
這篇文章主要介紹了PostgreSQL 恢復(fù)誤刪數(shù)據(jù)的操作，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2021-01-01