欧美bbbwbbbw肥妇,免费乱码人妻系列日韩,一级黄片

PostgreSQL的中文拼音排序案例

 更新時(shí)間：2021年01月12日 10:27:03   作者：kenyon_君羊  
這篇文章主要介紹了PostgreSQL的中文拼音排序案例，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過(guò)來(lái)看看吧

前一段時(shí)間開發(fā)人員咨詢，說(shuō)postgresql里面想根據(jù)一個(gè)字段做中文的拼音排序，但是不得其解

環(huán)境：

OS：CentOS 6.3

DB：PostgreSQL 9.2.4

TABLE: tbl_kenyon

場(chǎng)景：

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)

說(shuō)明：

postgresql的排序除了受到數(shù)據(jù)庫(kù)的編碼影響外，還有一個(gè)初始化參數(shù)是locale也會(huì)影響(initdb)，,通常我的選擇是C，這可以讓postgres數(shù)據(jù)庫(kù)通過(guò)strcmp()這個(gè)函數(shù)來(lái)比較字符串，而不是strcoll()函數(shù)。

這個(gè)參數(shù)可以在數(shù)據(jù)庫(kù)里查看，如

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)

--簡(jiǎn)體中文在系統(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ù)庫(kù)的默認(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)

其他問(wèn)題：

1.在用了方法一的convert_to函數(shù)轉(zhuǎn)換一段時(shí)間后，開發(fā)告訴我說(shuō)有異常,報(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ù)庫(kù)里存了一些比較奇怪的字符導(dǎo)致的，比如Mircle® city,niwhite®town。后對(duì)該表重建了一下，用方法二解決,所以convert_to函數(shù)使用對(duì)一些奇怪的字符轉(zhuǎn)換時(shí)需要注意。

2.對(duì)于多音字，仍然會(huì)產(chǎn)生一定的歧義，比如重慶，會(huì)按Z去排序

上述辦法能滿足大部分漢字的拼音排序，但仍有一些不足。比較理想的解決辦法是對(duì)這類基礎(chǔ)數(shù)據(jù)錄入時(shí)就指定拼音規(guī)則，或者數(shù)據(jù)庫(kù)里存一份數(shù)據(jù)的拼音字典來(lái)關(guān)聯(lián)使用。

其他：

使用zh_cn存儲(chǔ)時(shí)測(cè)試字段大小，未測(cè)試取值速度

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ǔ)充

#高版本可能不支持,或者語(yǔ)法不對(duì)？
select * from store order by storename collate 'zh_CN';

以上為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。如有錯(cuò)誤或未考慮完全的地方，望不吝賜教。

相關(guān)文章

最新評(píng)論