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

PostgreSQL的中文拼音排序案例

 更新時間：2021年01月12日 10:27:03   作者：kenyon_君羊  
這篇文章主要介紹了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)換為目標編碼的函數(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ù)庫里存了一些比較奇怪的字符導致的，比如Mircle® city,niwhite®town。后對該表重建了一下，用方法二解決,所以convert_to函數(shù)使用對一些奇怪的字符轉(zhuǎn)換時需要注意。

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

上述辦法能滿足大部分漢字的拼音排序，但仍有一些不足。比較理想的解決辦法是對這類基礎數(shù)據(jù)錄入時就指定拼音規(guī)則，或者數(shù)據(jù)庫里存一份數(shù)據(jù)的拼音字典來關聯(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)驗，希望能給大家一個參考，也希望大家多多支持腳本之家。如有錯誤或未考慮完全的地方，望不吝賜教。

相關文章

最新評論