当前位置: 萬仟网 > IT编程>数据库>MSSQL > PostgreSQL的中文拼音排序案例

PostgreSQL的中文拼音排序案例

2021年01月12日  | 萬仟网IT编程  | 我要评论
前一段时间开发人员咨询,说postgresql里面想根据一个字段做中文的拼音排序,但是不得其解环境:os:centos 6.3db:postgresql 9.2.4table: tbl_kenyon场

前一段时间开发人员咨询,说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"

排查了一下,发现数据库里存了一些比较奇怪的字符导致的,比如mircle® city,niwhite®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';

以上为个人经验,希望能给大家一个参考,也希望大家多多支持萬仟网。如有错误或未考虑完全的地方,望不吝赐教。

如您对本文有疑问或者有任何想说的,请点击进行留言回复,万千网友为您解惑!

相关文章:

验证码:
Copyright © 2017-2021  萬仟网 保留所有权利. 粤ICP备17035492号-1
站长QQ:2386932994 | 联系邮箱:2386932994@qq.com