1. 云图网首页
  2. 技术专区
  3. 数据库

PostgreSQL: 关于中文排序问题

• 数据库

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

环境信息

1.1 数据库信息

1
2
3
4
5
 
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 创建测试表

1
2
3
4
5
6
7
8
9
10
11
12
 
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 排序

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
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 函数

1
2
3
4
5
6
7
8
9
10
11
12
 
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() 函数输出排序

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
 
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” 排序规则。

参考

中文排序涉及到字符集，是一个比较复杂的话题，这里不研究了， 以下几篇帖子对中文排序有

更深入的描述，强烈推荐。

原创文章，作者：745907710，如若转载，请注明出处：https://blog.ytso.com/237990.html

赞 (0)
0
上一篇 2022年1月29日
下一篇 2022年1月29日

相关推荐

发表回复

登录后才能评论