Chinaunix首页 | 论坛 | 博客
  • 博客访问: 91067896
  • 博文数量: 19283
  • 博客积分: 9968
  • 博客等级: 上将
  • 技术积分: 196062
  • 用 户 组: 普通用户
  • 注册时间: 2007-02-07 14:28
文章分类

全部博文(19283)

文章存档

2011年(1)

2009年(125)

2008年(19094)

2007年(63)

分类:

2008-05-21 14:56:28

From PgsqlWiki

Jump to: ,

排序问题

在PG中存储中文,现在大家的典型解决方法是用UTF8做数据库编码,但是用UTF8作数据库编码有一个问题,就是中文排序的问题。

在PG中,缺省的时候是按照编码排序的,也就是按照UTF8的编码对字段排序,但是,UTF8本身的编码顺序和人们习惯的中文的发音的拼音顺序完全不同,因而排序完全不是期望的拼音排序。那么如何解决呢?

众所周知,GBK编码是按照拼音发音顺序排序的,因此,解决方法之一就是把UTF8编码转换成GBK编码。在这方面,PG提供了很好的支持函数。

 PG提供的一些转换函数

PG提供了很好的编码转换函数:convert(),使用这个函数,我们可以有效地把UTF8编码转换成GBK编码。比如,要把UTF8编码转换成GBK编码:

  select convert('UTF8编码字串或字段' using utf8_to_gbk);

这样就可以了,那么,是不是我们可以这样实现GBK的读音序编码呢:

  select * from table order by convert( column_need_to_sort_in_utf8 using utf8_to_gbk);

但是实际上试验的结果,仍然不对,这是为什么呢?

 如何解决

原因在于,对于文本字段(text, varchar, char类型的字段),PG是使用底层OS的locale相关的函数进行字符串比较的,众所周知,排序的一个重要的事情就是需要字符串比较函数(几乎所有的排序算法都涉及大于、小于、等于等过程)。而遗憾的是,因为各种原因(国家没有投入是一个重要原因),在各种OS上的locale相关的函数集(比如stroll),对汉字的排序比较都不是很标准;也不是很正确。

怎么解决呢?从根本分析入手,就是要让PG不使用OS的locale相关的东西,这样,解法之一是 initdb 的时候,使用C做locale,这个时候PG会用strcmp,而不是stroll来比较字串大小。但是这样也不一定很好,因为strcmp有时候在某些特殊的编码的时候也会有些问题,并且,我们很多时候也需要locale,比如在中文的全文索引的时候。

那么,有什么办法让PG一定用类似memcp(1)这样的接口来比较数据么?

答案当然是有的:还记得PG有个数据类型是二进制数据类型么?它就是BYTEA,在PG中,所有的变长类型:TEXT、BYTEA、VARCHAR等的底层结构都是一样的,但是每个类型在SQL子句中调用的OS处理函数不同,比如BYTEA就是使用memcp(1)进行排序的比较的,因此,我们可以想办法把TEXT的类型转换成BYTEA进行ORDER BY,这样就可以即使用locale,又绕开stroll的限制。

因为没有内置的TEXT到BYTEA的转换函数,我们需要自己做一个:

  create or replace function text2bytea(text) returns bytea as
  $$
     return $1;
  $$ language plpgsql immutable;

这个函数很简单,利用了PG提供的text_in()和bytea_out的输入输出转换函数,直接生成BYTEA的数据。

于是,我们可以这样进行中文的语音排序了:

  select * from table order by text2bytea(convert(column_need_to_sort_in_utf8 using utf8_to_gbk));
阅读(1978) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~