Digoal.zhou

Digoal.zhou

公益是一辈子的事, I'm digoal, Just do it.

the order of chinese char in PostgreSQL DB with different encoding and it’s collate

4 minute read

背景

这是一篇比较老的文章，仅作记录，拼音相关建议看以下文章

《PostgreSQL WHY ERROR: invalid byte sequence for encoding “UTF8”》

《PostgreSQL SQL_ASCII encoding introduce》

《PostgreSQL Server Encoding sql_ascii attention》

《PostgreSQL 按拼音排序 - convert to GBK/EUC_CN coding》

《PostgreSQL 获取拼音首字母的函数 - 摘自互联网》

《在PostgreSQL中实现按拼音、汉字、拼音首字母搜索的例子》

正文

今天群里面在讨论关于中文排序的问题,下面从PostgreSQL数据库编码来整理一下。

下面是在一台Linux(RHEL5)机器上的测试,数据库版本PostgreSQL 9.1beta2

首先,

可以存储简体中文的服务端字符集编码(不含繁体)：

1. EUC_CN

2. SQL_ASCII

3. UTF8

可以显示简体中文的客户端字符集编码(不含繁体)，除以上三种还有如下：

1. GB18030

2. GBK

排序除与字符编码有关，还与COLLATE有关。

在pg_collation系统表可以查找到字符集对应的可以选择的collate和ctype信息。

下面来测试一组中文排序：

1. UTF8编码的数据库

postgres=# create database db_utf8 with owner digoal template template0 encoding 'UTF8';  
CREATE DATABASE  
postgres=# \c db_utf8 digoal  
You are now connected to database "db_utf8" as user "digoal".  
db_utf8=> create table tbl_chinese_order (info text);  
CREATE TABLE  
db_utf8=> insert into tbl_chinese_order values ('刘少奇');  
INSERT 0 1  
db_utf8=> insert into tbl_chinese_order values ('刘德华');  
INSERT 0 1  
db_utf8=> insert into tbl_chinese_order values ('张学友');  
INSERT 0 1

以下SQL得到的结果一致：

select * from tbl_chinese_order order by info;  
select * from tbl_chinese_order order by info collate "C";  
select * from tbl_chinese_order order by info collate "zh_CN";  
select * from tbl_chinese_order order by info collate "zh_CN.utf8";select * from tbl_chinese_order order by info collate "POSIX";  
select * from tbl_chinese_order order by info collate "default";

结果都一样:

  info    
--------  
 刘少奇  
 刘德华  
 张学友  
(3 rows)

2. SQL_ASCII编码的数据库(不推荐 “多字节字符” 使用sql_ascii, 参考手册对应部分)

postgres=# create database db_sql_ascii with owner digoal template template0 encoding 'SQL_ASCII';  
CREATE DATABASE

测试数据同上.

以下SQL得到的结果一致：

select * from tbl_chinese_order order by info;  
select * from tbl_chinese_order order by info collate "C";  
select * from tbl_chinese_order order by info collate "POSIX";  
select * from tbl_chinese_order order by info collate "default";

结果都一样:

  info    
--------  
 刘少奇  
 刘德华  
 张学友  
(3 rows)

3. EUC_CN编码的数据库

postgres=# create database db_euc_cn with owner digoal template template0 encoding 'EUC_CN';  
CREATE DATABASE

测试数据同上.

这次结果有变化:

select * from tbl_chinese_order order by info collate "zh_CN.gb2312";  
select * from tbl_chinese_order order by info collate "zh_CN";

结果如下:

  info    
--------  
 刘少奇  
 刘德华  
 张学友  
(3 rows)

以下SQL按拼音返回顺序了:

select * from tbl_chinese_order order by info;  
select * from tbl_chinese_order order by info collate "C";  
select * from tbl_chinese_order order by info collate "POSIX";  
select * from tbl_chinese_order order by info collate "default";  
  info    
--------  
 刘德华  
 刘少奇  
 张学友  
(3 rows)

根据以上的测试结果，说明EUC_CN字符集的字符对应的编码顺序和SQL_ASCII以及UTF8不一样，因此使用collate=C来排序时得到了不一样的结果。

然而order by info collate “zh_CN”并没有带来所需要的结果，当然这个可能是LIBC有关, 也可能是其他原因.

https://www.postgresql.org/docs/9.6/static/collation.html

补充一个例子,PostgreSQL9.1可以针对列设置collate值，如下:

db_euc_cn=> drop table tbl_chinese_order;  
DROP TABLE  
db_euc_cn=> create table tbl_chinese_order (info text collate "zh_CN");  
CREATE TABLE  
db_euc_cn=> insert into tbl_chinese_order values ('刘少奇');  
INSERT 0 1  
db_euc_cn=> insert into tbl_chinese_order values ('刘少奇');  
INSERT 0 1  
db_euc_cn=> insert into tbl_chinese_order values ('刘德华');  
INSERT 0 1  
db_euc_cn=> insert into tbl_chinese_order values ('张学友');  
INSERT 0 1

默认的collate就不再是数据库初始化时默认的C了, 而是指定的zh_CN.

db_euc_cn=> select * from tbl_chinese_order order by info;  
  info    
--------  
 刘少奇  
 刘少奇  
 刘德华  
 张学友  
(4 rows)  
  
db_euc_cn=> select * from tbl_chinese_order order by info collate "POSIX";  
  info    
--------  
 刘德华  
 刘少奇  
 刘少奇  
 张学友  
(4 rows)

抛开数据库，来看看操作系统下的排序

1. 首先是LINUX(RHEL5)操作系统里面的排序风格：

postgres@db5-> export LANG=en_US.utf8  
postgres@db5-> cat order   
刘德华  
张学友  
刘少奇  
postgres@db5-> cat order |sort  
刘少奇  
刘德华  
张学友  
  
  
postgres@db5-> export LANG=zh_CN.gbk  
postgres@db5-> cat order |sort  
张学友  
刘德华  
刘少奇  
  
  
postgres@db5-> export LANG=zh_CN.utf8  
postgres@db5-> cat order |sort  
刘少奇  
刘德华  
张学友  
  
  
postgres@db5-> export LC_COLLATE=zh_CN.gbk  
postgres@db5-> cat order |sort  
张学友  
刘德华  
刘少奇  
  
  
postgres@db5-> export LC_COLLATE=C  
postgres@db5-> cat order |sort  
刘少奇  
刘德华  
张学友  
  
  
postgres@db5-> export LC_COLLATE=POSIX  
postgres@db5-> cat order |sort  
刘少奇  
刘德华  
张学友

结果与在PostgreSQL数据库中的测试相当。

2. WINDOW7中文旗舰版下面，操作系统对文本的排序。

D:\>sort ./test  
"刘德华"  
"刘德华"  
"刘少奇"  
"张学友"

3. MAC LION系统下面，操作系统对文本的排序。

sort ./test  
刘少奇  
刘德华  
张学友

4. 泥鳅哥在某LINUX版本下的测试:

[root@dbserver ~]# export LANG=zh_CN.UTF-8  
[root@dbserver ~]# cat ts.txt | sort  
 刘德华  
 刘少奇  
 张学友  
  
[root@dbserver ~]# uname -a  
Linux dbserver.zhenghongkeji.com 2.6.32-131.6.1.el6.x86_64 #1 SMP Tue Jul 12 17:14:50 CDT 2011 x86_64 x86_64 x86_64 GNU/Linux  
[root@dbserver ~]# cat /etc/issue  
Scientific Linux release 6.1 (Carbon)

5. CENTOS6.0版本下的测试:

export LANG=zh_CN.UTF-8  
sort ./test  
 刘德华  
 刘少奇  
 张学友

RHEL5的中文字符集包, fonts-chinese-3.02-12.el5

RHEL6的中文字符集包, 已经不使用这个包了.

后来我们这边的同事发现操作系统RHEL5有对中文排序的BUG。见最后的附录.

附录

postgres=# \l  
                               List of databases  
     Name     |  Owner   | Encoding  | Collate | Ctype |   Access privileges     
--------------+----------+-----------+---------+-------+-----------------------  
 db_euc_cn    | digoal   | EUC_CN    | C       | C     |   
 db_sql_ascii | digoal   | SQL_ASCII | C       | C     |   
 db_utf8      | digoal   | UTF8      | C       | C     |

这里要注意，如果在9.0下面测试,collate是库级别的,不能按列级别来调整.所以我选用9.1来测试就比较方便了.

另一种强制中文排序的方法是使用convert_to将字符转换成目标字符集的编码, 以bytea输出, 根据转换后的bytea排序即可.

digoal=> select *,convert_to(info,'gbk'),info::bytea from t order by convert_to(info,'gbk');  
 id |  info  |   convert_to   |         info           
----+--------+----------------+----------------------  
  3 | 郭富城 | \xb9f9b8bbb3c7 | \xe983ade5af8ce59f8e  
  4 | 黎明   | \xc0e8c3f7     | \xe9bb8ee6988e  
  1 | 刘德华 | \xc1f5b5c2bbaa | \xe58898e5beb7e58d8e  
  2 | 刘少奇 | \xc1f5c9d9c6e6 | \xe58898e5b091e5a587  
  5 | 张学友 | \xd5c5d1a7d3d1 | \xe5bca0e5ada6e58f8b  
(5 rows)  
digoal=> select *,convert_to(info,'euc_cn'),info::bytea from t order by convert_to(info,'euc_cn');  -- 转换排序, 可以使用PostgreSQL表达式索引, 实现拼音排序走索引  
 id |  info  |   convert_to   |         info           
----+--------+----------------+----------------------  
  3 | 郭富城 | \xb9f9b8bbb3c7 | \xe983ade5af8ce59f8e  
  4 | 黎明   | \xc0e8c3f7     | \xe9bb8ee6988e  
  1 | 刘德华 | \xc1f5b5c2bbaa | \xe58898e5beb7e58d8e  
  2 | 刘少奇 | \xc1f5c9d9c6e6 | \xe58898e5b091e5a587  
  5 | 张学友 | \xd5c5d1a7d3d1 | \xe5bca0e5ada6e58f8b  
(5 rows)  
digoal=> select *,convert_to(info,'euc_cn'),info::bytea from t order by t.info::bytea;  -- 原始排序  
 id |  info  |   convert_to   |         info           
----+--------+----------------+----------------------  
  2 | 刘少奇 | \xc1f5c9d9c6e6 | \xe58898e5b091e5a587  
  1 | 刘德华 | \xc1f5b5c2bbaa | \xe58898e5beb7e58d8e  
  5 | 张学友 | \xd5c5d1a7d3d1 | \xe5bca0e5ada6e58f8b  
  3 | 郭富城 | \xb9f9b8bbb3c7 | \xe983ade5af8ce59f8e  
  4 | 黎明   | \xc0e8c3f7     | \xe9bb8ee6988e  
(5 rows)

参考

1. http://www.postgresql.org/docs/9.1/static/multibyte.html

2. catalog : pg_collation

3. http://www.postgresql.org/docs/9.1/static/sql-createcollation.html

4. http://www.postgresql.org/docs/9.1/static/collation.html

5. http://en.wikipedia.org/wiki/Collation

RHEL5中文排序BUG

这其实是因为中文本地化数据并没有包含拼音排序的信息。即glibc中的local数据不包含对中文按拼音和笔画的排序

官方上bug的提交如下：

http://sources.redhat.com/ml/glibc-bugs/2007-02/msg00068.html

http://sourceware.org/bugzilla/show_bug.cgi?id=4024

现在给出解决方案，如下：

现在通过安装一种额外的中文本地化数据zh_CN@pinyin就可以让你在UTF-8和GB18030字符集下都可以正确的排序中文。

即locale-pinyin

locale-pinyin 介绍

按拼音排序的中文locale。

在类Unix中为了能够正确的排序电脑中的中文，你必须有能够正确对中文进行排序的locale数据。而glibc包含的locale数据一直没能对中文按照拼音或者笔画排序。本软件则是为你在电脑中增加一种按照拼音排序的中文locale。

locale-pinyin 安装

系统需求：

1. 系统需要有符合X/Open标准的localedef工具

2. 5.8.1以上的perl

安装：

$make  
  
#make install

locale-pinyin 配置

为了生效，你必须正确配置locale环境变量。

推荐仅设置LC_COLLATE为本locale，比如：

LANG=zh_CN.UTF-8  
  
LC_COLLATE=zh_CN@pinyin.utf8

注意：不要同时设置LC_ALL和LC_COLLATE，这样可能会导致本locale不能正确生效。

再跟踪glibc，发现官方在比较新的版本中已经把local_pinyin合并进去了(glibc2.6以上)，如下：

http://sourceware.org/cgi-bin/cvsweb.cgi/libc/localedata/locales/iso14651_t1_pinyin?rev=1.1&content-type=text/x-cvsweb-markup&cvsroot=glibc

升级glibc是一个比较大的工程，涉及也比较广，建议在用rhel5时先打上local_pinyin补丁，或者可以考虑使用rhel6

Flag Counter

digoal’s 大量PostgreSQL文章入口

You May Also Enjoy

PostgreSQL(PPAS 兼容Oracle) 从零开始入门手册 - 珍藏版

17 minute read

背景 云数据库PPAS版，是阿里云与EnterpriseDB公司(简称EDB)合作基于PostgreSQL高度兼容Oracle语法的数据库服务，为用户提供易于操作的迁移工具，兼容范围涵盖：PL/SQL、数据类型、高级函数、表分区等。 用户可以直接在阿里云购买PPAS进行使用。 如果在购买PPAS前，想试用一下...

PostgreSQL pipelinedb 流计算插件 - IoT应用 - 实时轨迹聚合

1 minute read

背景 IoT场景，车联网场景，共享单车场景，人的行为位点等，终端实时上报的是孤立的位点，我们需要将其补齐成轨迹。 例如共享单车，下单，开锁，生成订单，骑行，关闭订单，关锁。这个过程有一个唯一的订单号，每次上报的位点会包含时间，订单号，位置。 根据订单号，将点聚合为轨迹。 使用pipelinedb插件，可以实...

PostgreSQL datediff 日期间隔（单位转换）兼容SQL用法

2 minute read

背景 使用datediff，对时间或日期相减，得到的间隔，转换为目标单位(日、月、季度、年、小时、秒。。。等)的数值。 DATEDIFF ( datepart, {date|timestamp}, {date|timestamp} ) 周 select datediff(week,'2009-01-0...