全部产品
云数据库 RDS 版

设置数据库的本土化信息及字符串排序规则

更新时间:2017-06-07 13:26:11   分享:   

初始化数据库集群时,可以设置如下参数,用于设置数据库的字符串排序、字符归类方法、数值\日期\时间\货币的格式等。另外,为了支持国际化,数据库通常会涉及到 LC_COLLATE 和 LC_CTYPE 的概念。

LC_COLLATE String sort order
LC_CTYPE Character classification (What is a letter? Its upper-case equivalent?)
LC_MESSAGES Language of messages
LC_MONETARY Formatting of currency amounts
LC_NUMERIC Formatting of numbers
LC_TIME Formatting of dates and times

您可以利用这些特性,按本土化需求,输出对应的顺序或者格式。本文将通过示例介绍如何设置数据库的本土化信息以及如何设置输出结果按中文的拼音顺序进行排序。

PostgreSQL 支持的字符集类型

您可以通过 PostgreSQL 的官方文档或下表查看对应的字符集支持列表,Server=Yes 表示该字符集支持用于CREATE DATABASE命令,Server=No 表示只支持作为客户端字符集。

Name Description Language Server? Bytes/Char Aliases
BIG5 Big Five Traditional Chinese No 1-2 WIN950, Windows950
EUC_CN Extended UNIX Code-CN Simplified Chinese Yes 1-3 -
EUC_JP Extended UNIX Code-JP Japanese Yes 1-3 -
EUC_JIS_2004 Extended UNIX Code-JP, JIS X 0213 Japanese Yes 1-3 -
EUC_KR Extended UNIX Code-KR Korean Yes 1-3 -
EUC_TW Extended UNIX Code-TW Traditional Chinese, Taiwanese Yes 1-3 -
GB18030 National Standard Chinese No 1-4 -
GBK Extended National Standard Simplified Chinese No 1-2 WIN936, Windows936
ISO_8859_5 ISO 8859-5, ECMA 113 Latin/Cyrillic Yes 1 -
ISO_8859_6 ISO 8859-6, ECMA 114 Latin/Arabic Yes 1 -
ISO_8859_7 ISO 8859-7, ECMA 118 Latin/Greek Yes 1 -
ISO_8859_8 ISO 8859-8, ECMA 121 Latin/Hebrew Yes 1 -
JOHAB JOHAB Korean (Hangul) No 1-3 -
KOI8R KOI8-R Cyrillic (Russian) Yes 1 KOI8
KOI8U KOI8-U Cyrillic (Ukrainian) Yes 1 -
LATIN1 ISO 8859-1, ECMA 94 Western European Yes 1 ISO88591
LATIN2 ISO 8859-2, ECMA 94 Central European Yes 1 ISO88592
LATIN3 ISO 8859-3, ECMA 94 South European Yes 1 ISO88593
LATIN4 ISO 8859-4, ECMA 94 North European Yes 1 ISO88594
LATIN5 ISO 8859-9, ECMA 128 Turkish Yes 1 ISO88599
LATIN6 ISO 8859-10, ECMA 144 Nordic Yes 1 ISO885910
LATIN7 ISO 8859-13 Baltic Yes 1 ISO885913
LATIN8 ISO 8859-14 Celtic Yes 1 ISO885914
LATIN9 ISO 8859-15 LATIN1 with Euro and accents Yes 1 ISO885915
LATIN10 ISO 8859-16, ASRO SR 14111 Romanian Yes 1 ISO885916
MULE_INTERNAL Mule internal code Multilingual Emacs Yes 1-4 -
SJIS Shift JIS Japanese No 1-2 Mskanji, ShiftJIS, WIN932, Windows932
SHIFT_JIS_2004 Shift JIS, JIS X 0213 Japanese No 1-2 -
SQL_ASCII unspecified (see text) any Yes 1 -
UHC Unified Hangul Code Korean No 1-2 WIN949, Windows949
UTF8 Unicode, 8-bit all Yes 1-4 Unicode
WIN866 Windows CP866 Cyrillic Yes 1 ALT
WIN874 Windows CP874 Thai Yes 1 -
WIN1250 Windows CP1250 Central European Yes 1 -
WIN1251 Windows CP1251 Cyrillic Yes 1 WIN
WIN1252 Windows CP1252 Western European Yes 1 -
WIN1253 Windows CP1253 Greek Yes 1 -
WIN1254 Windows CP1254 Turkish Yes 1 -
WIN1255 Windows CP1255 Hebrew Yes 1 -
WIN1256 Windows CP1256 Arabic Yes 1 -
WIN1257 Windows CP1257 Baltic Yes 1 -
WIN1258 Windows CP1258 Vietnamese Yes 1 ABC, TCVN, TCVN5712, VSCII

查询字符集支持的 LC_COLLATE 和 LC_CTYPE 信息

您可以使用如下 SQL 查询系统表 pg_collation,来获取字符集支持的 LC_COLLATE 和 LC_CTYPE 信息。

  1. test=> select pg_encoding_to_char(collencoding) as encoding,collname,collcollate,collctype from pg_collation ;

返回结果如下所示,encoding 为空时,表示这个 collation 支持所有的字符集。

  1. encoding | collname | collcollate | collctype
  2. ------------+-----------------------+-----------------------+-----------------------
  3. | default | |
  4. | C | C | C
  5. | POSIX | POSIX | POSIX
  6. UTF8 | aa_DJ | aa_DJ.utf8 | aa_DJ.utf8
  7. LATIN1 | aa_DJ | aa_DJ | aa_DJ
  8. LATIN1 | aa_DJ.iso88591 | aa_DJ.iso88591 | aa_DJ.iso88591
  9. UTF8 | aa_DJ.utf8 | aa_DJ.utf8 | aa_DJ.utf8
  10. UTF8 | aa_ER | aa_ER | aa_ER
  11. UTF8 | aa_ER.utf8 | aa_ER.utf8 | aa_ER.utf8
  12. .......
  13. EUC_CN | zh_CN | zh_CN | zh_CN
  14. UTF8 | zh_CN | zh_CN.utf8 | zh_CN.utf8
  15. EUC_CN | zh_CN.gb2312 | zh_CN.gb2312 | zh_CN.gb2312
  16. UTF8 | zh_CN.utf8 | zh_CN.utf8 | zh_CN.utf8
  17. UTF8 | zh_HK | zh_HK.utf8 | zh_HK.utf8
  18. UTF8 | zh_HK.utf8 | zh_HK.utf8 | zh_HK.utf8
  19. EUC_CN | zh_SG | zh_SG | zh_SG
  20. UTF8 | zh_SG | zh_SG.utf8 | zh_SG.utf8
  21. EUC_CN | zh_SG.gb2312 | zh_SG.gb2312 | zh_SG.gb2312
  22. UTF8 | zh_SG.utf8 | zh_SG.utf8 | zh_SG.utf8
  23. EUC_TW | zh_TW | zh_TW.euctw | zh_TW.euctw
  24. UTF8 | zh_TW | zh_TW.utf8 | zh_TW.utf8
  25. EUC_TW | zh_TW.euctw | zh_TW.euctw | zh_TW.euctw
  26. UTF8 | zh_TW.utf8 | zh_TW.utf8 | zh_TW.utf8
  27. UTF8 | zu_ZA | zu_ZA.utf8 | zu_ZA.utf8
  28. LATIN1 | zu_ZA | zu_ZA | zu_ZA
  29. LATIN1 | zu_ZA.iso88591 | zu_ZA.iso88591 | zu_ZA.iso88591
  30. UTF8 | zu_ZA.utf8 | zu_ZA.utf8 | zu_ZA.utf8
  31. (869 rows)

设置数据库的本土化(collate)信息

关于如何设置字符集、LC_COLLATE 及 LC_CTYPE 的信息,请参见文档 CREATE DATABASE 命令的具体使用方法

设置字段的本土化

前提条件

执行如下 SQL 命令,查询当前数据库的字符集(encoding)类型,并了解清楚与您当前数据库字符集兼容的 collate。

  1. postgres=# select datname,pg_encoding_to_char(encoding) as encoding from pg_database;

返回结果如下所示:

  1. datname | encoding
  2. --------------------+-----------
  3. template1 | UTF8
  4. template0 | UTF8
  5. db | SQL_ASCII
  6. db1 | EUC_CN
  7. contrib_regression | UTF8
  8. test01 | UTF8
  9. test02 | UTF8
  10. postgres | UTF8
  11. (8 rows)

操作步骤

  1. 在创建表时,执行如下命令,指定兼容当前字符集的 collate。

    1. CREATE TABLE test1 (
    2. a text COLLATE "de_DE",
    3. b text COLLATE "es_ES",
    4. ...
    5. );
  2. 执行如下命令,修改列 collate。

    注意:修改列 collate 时,会导致 rewrite table,大表请谨慎操作。

    1. alter table a alter c1 type text COLLATE "zh_CN";

在 SQL 使用本土化

  • 使用本土化,改变 order by 输出排序。

    1. test=# select * from a order by c1 collate "C";
    2. c1
    3. --------
    4. 刘少奇
    5. 刘德华
    6. (2 rows)
    7. test=# select * from a order by c1 collate "zh_CN";
    8. c1
    9. --------
    10. 刘德华
    11. 刘少奇
    12. (2 rows)
  • 使用本土化,改变操作符的结果。

    1. test=# select * from a where c1 > '刘少奇' collate "C";
    2. c1
    3. --------
    4. 刘德华
    5. (1 row)
    6. test=# select * from a where c1 > '刘少奇' collate "zh_CN";
    7. c1
    8. ----
    9. (0 rows)

使用本土化索引进行排序

排序语句中的 collate 与索引的 collate 保持一致,才能使用这个索引进行排序。

  1. postgres=# create index idxa on a(c1 collate "zh_CN");
  2. CREATE INDEX
  3. postgres=# explain select * from a order by c1 collate "zh_CN";
  4. QUERY PLAN
  5. ------------------------------------------------------------------------
  6. Index Only Scan using idxa on a (cost=0.15..31.55 rows=1360 width=64)
  7. (1 row)

设置输出结果按拼音排序

您可以通过如下四种方法来设置按拼音排序:

  • 使用本土化 SQL。该方法不修改原有数据。

    1. test=# select * from a order by c1 collate "zh_CN";
    2. c1
    3. --------
    4. 刘德华
    5. 刘少奇
    6. (2 rows)
  • 使用本土化字段。若已有数据,使用该方法时需要调整原有数据。

    1. alter table a alter c1 type text COLLATE "zh_CN";
  • 使用本土化索引以及本土化 SQL。该方法不修改原有数据。

    1. postgres=# create index idxa on a(c1 collate "zh_CN");
    2. CREATE INDEX
    3. postgres=# explain select * from a order by c1 collate "zh_CN";
    4. QUERY PLAN
    5. ------------------------------------------------------------------------
    6. Index Only Scan using idxa on a (cost=0.15..31.55 rows=1360 width=64)
    7. (1 row)
  • 将数据库的 collate 设置为 zh_CN,数据会将默认使用这个 collate 按拼音排序。

    1. test02=# create database test03 encoding 'UTF8' lc_collate 'zh_CN.utf8' lc_ctype 'zh_CN.utf8' template template0;
    2. CREATE DATABASE
    3. test02=# \c test03
    4. You are now connected to database "test03" as user "postgres".
    5. test03=# select * from (values ('刘德华'),('刘少奇')) as a(c1) order by c1 ;
    6. c1
    7. --------
    8. 刘德华
    9. 刘少奇
    10. (2 rows)

注意:在设置按拼音排序时,要注意多音字。例如重庆(chongqing),在编码时,”重”可能会按照 zhong 编码,影响输出,如下面的例子所示:

  1. test03=# select * from (values ('中山'),('重庆')) as a(c1) order by c1 collate "zh_CN";
  2. c1
  3. ------
  4. 中山
  5. 重庆
  6. (2 rows)

在 Greenplum 中设置输出结果按拼音排序

Greenplum 不支持单列设置 collate,按拼音排序有些许不同。

在 Greenplum 中,可以使用字符集转换,按对应二进制排序,得到拼音排序的效果,如下面的例子所示:

  1. postgres=# select * from (values ('刘德华'), ('刘少奇')) t(id) order by byteain(textout(convert(id,'UTF8','EUC_CN')));
  2. id
  3. --------
  4. 刘德华
  5. 刘少奇
  6. (2 rows)

参考文档

本文导读目录
本文导读目录
以上内容是否对您有帮助?