注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

PostgreSQL 中文网

 
 
 

日志

 
 

PostgreSQL:关于中文排序问题  

2013-06-21 11:21:26|  分类: PG案例分析 |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

        今天有同事问到中文排序问题,中文排序问题没有怎么深究,主要参考 bbs 和德哥的帖子,
做些测试记录下。

一 环境信息
--1.1 数据库信息
 francs=> select datname,pg_encoding_to_char(encoding),datcollate,datctype from pg_database where datname='francs';
 datname | pg_encoding_to_char | datcollate | datctype 
---------+---------------------+------------+----------
 francs  | UTF8                | C          | C
(1 row)
备注:测试库字符集 UTF8,数据库排序规则:C,测试版本: PostgreSQL 9.2。

--1.2 创建测试表
 CREATE TABLE tbl_area ( id character varying(32),name character varying(1024) );

INSERT INTO tbl_area (id,name) VALUES ('000001', '香港');
INSERT INTO tbl_area (id,name) VALUES ('000002', '澳门');
INSERT INTO tbl_area (id,name) VALUES ('0100', '呼和浩特');
INSERT INTO tbl_area (id,name) VALUES ('011200', '苏尼特右旗');
INSERT INTO tbl_area (id,name) VALUES ('011300', '苏尼特左旗');
INSERT INTO tbl_area (id,name) VALUES ('011600', '清水河县');
INSERT INTO tbl_area (id,name) VALUES ('011700', '武川县');
INSERT INTO tbl_area (id,name) VALUES ('0118', '乌兰察布');
INSERT INTO tbl_area (id,name) VALUES ('011800', '四王子旗');
INSERT INTO tbl_area (id,name) VALUES ('012000', '集宁区');
  
--1.3 根据 name 排序
 francs=> select * From tbl_area order by name;
   id   |    name    
--------+------------
 0118   | 乌兰察布
 0100   | 呼和浩特
 011800 | 四王子旗
 011700 | 武川县
 011600 | 清水河县
 000002 | 澳门
 011200 | 苏尼特右旗
 011300 | 苏尼特左旗
 012000 | 集宁区
 000001 | 香港
(10 rows)
 备注:根据中文字段 name 排序的结果不是我们想要的,参考 bbs 帖子,可以使用 convert_to ()
            函数转换成目标编码的 bytea 形式解决.
       
二 方法一:使用 convert_to 函数
--2.1 convert_to 函数
 francs=> \df convert_to
                             List of functions
   Schema   |    Name    | Result data type | Argument data types |  Type  
------------+------------+------------------+---------------------+--------
 pg_catalog | convert_to | bytea            | text, name          | normal
(1 row)


francs=> select convert_to('香港','gbk');
 convert_to 
------------
 \xcfe3b8db
(1 row
备注:输出为二进制形式。
 
--2.2 根据 convert_to() 函数输出排序
 francs=> select * From tbl_area order by convert_to(name,'gbk');
   id   |    name    
--------+------------
 000002 | 澳门
 0100   | 呼和浩特
 012000 | 集宁区
 011600 | 清水河县
 011800 | 四王子旗
 011200 | 苏尼特右旗
 011300 | 苏尼特左旗
 0118   | 乌兰察布
 011700 | 武川县
 000001 | 香港
(10 rows)
备注:使用 convert_to() 函数转换后得到了想要的结果。


三 方法二:排序时指定字段的 Collate 

         由于创建数据库后不支持更改数据库的 LC_COLLATE (排序规则),但在 order by 时可以指定字段
 的 Collate 属性,如下:
 
--3.1 排序时指定字段的 collate
 francs=> select * From tbl_area order by name;
   id   |    name    
--------+------------
 0118   | 乌兰察布
 0100   | 呼和浩特
 011800 | 四王子旗
 011700 | 武川县
 011600 | 清水河县
 000002 | 澳门
 011200 | 苏尼特右旗
 011300 | 苏尼特左旗
 012000 | 集宁区
 000001 | 香港
(10 rows)

francs=> select * From tbl_area order by name collate "C";
   id   |    name    
--------+------------
 0118   | 乌兰察布
 0100   | 呼和浩特
 011800 | 四王子旗
 011700 | 武川县
 011600 | 清水河县
 000002 | 澳门
 011200 | 苏尼特右旗
 011300 | 苏尼特左旗
 012000 | 集宁区
 000001 | 香港
(10 rows)

francs=> select * From tbl_area order by name collate "zh_CN.utf8";
   id   |    name    
--------+------------
 000002 | 澳门
 0100   | 呼和浩特
 012000 | 集宁区
 011600 | 清水河县
 011800 | 四王子旗
 011200 | 苏尼特右旗
 011300 | 苏尼特左旗
 0118   | 乌兰察布
 011700 | 武川县
 000001 | 香港
(10 rows)
  备注:排序规则“C” 得到的结果不是想要的,  "zh_CN.utf8" 得到了想要的结果,中文字段排序建议使用 "zh_CN.utf8"  
             排序规则。
 
四 参考
           中文排序涉及到字符集,是一个比较复杂的话题,这里不研究了, 以下几篇帖子对中文排序有
   更深入的描述,强烈推荐。
  


  评论这张
 
阅读(9609)| 评论(0)
推荐 转载

历史上的今天

在LOFTER的更多文章

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2016