PG的本地化支持、字符集等

本地化的概念

本地化的目的是支持不同国家、地区的语言特性、规则。比如拥有本地化支持后，可以使用支持汉语、法语、日语等等的字符集。除了字符集以外，还有字符排序规则和其他语言相关规则的支持，例如我们知道('a','b')该如何排序，那么('a','A')和('啊','阿')又该如何排序？ 如果通过google去搜本地化、字符集、collation相关信息，可能会得到一些复杂又遥远的知识。最好的老师还是PostgreSQL官方文档：

本地化知识点总共分为3个部分：locale本地化支持、collation校验、字符集。

locale

pg的本地化由由操作系统提供，需要检查操作系统是否支持locale -a。在初始化数据库时可指定locale

也可以单独设置本地化子类：字符串排序、字符归类方法、数值格式、日期格式、时间格式、货币格式等

所有本地化子类：

这些子类又可以分为两部分，其中lc_messages，lc_monetary，lc_numeric，lc_time在初始化后，可以通过参数进行调整。LC_COLLATE，LC_CTYPE属于collation，详见collation的调整。 locale设置会影响如下行为：

• • Sort order in queries using ORDER BY or the standard comparison operators on textual data
• • The upper, lower, and initcap functions
• • Pattern matching operators (LIKE, SIMILAR TO, and POSIX-style regular expressions); locales affect both case insensitive matching and the classification of characters by character-class regular expressions
• • The to_char family of functions
• • The ability to use indexes with LIKE clauses

COLLATION

collation是字符排序的顺序和字符分类行为。一些数据库操作符依赖collation，比如order by、lower, upper、initcap 、to_char等等。 使用如下SQL查询系统表pg_collation，来获取字符集支持的LC_COLLATE和LC_CTYPE信息

encoding是字符集，collname为collation的名字

• • encoding 为空时，表示这个 collation 支持所有的字符集
• • default, C, POSIX是所有平台都支持的collation，由libc提供，其他collation取决于操作系统是否支持(locale -a)
• • default表示使用建库时的collation，可通过\l查看
• • C语义上等价于POSIX，但是PG仍然认为他们是不同的collation。他们的字符都以ASCII码对比，严格按照字节序比对大小。

• • UTF8是最常见的字符集，我们最常见的语言环境是en_US和zh_CN
• • 可以通过CREATE COLLATION ...创建自定义的collation。不过LC_COLLATE和LC_CTYPE不同的情况非常少见

LC_COLLATE

LC_COLLATE影响字符比对（排序、字符操作等等） collate子句可以转化表达式的collation：

注意这里指定的是collation，不是lc_collate。如果没有显示指定collation，数据库默认使用字段的collation，如果字段没有指定collation，使用database的默认collation。

不同的collation排序测试：

这3个不同的collation有不同的lc_collate，排序方法应该是不一样的，从结果来看确实是不一样的，出现了3种排序结果。 collation C为什么A<a? collation C使用的ASCII的编码顺序，ASCII码中大写在小写前面。而en_US.utf8和zh_CN.utf8的英文字母明显不是这个顺序 中文的顺序 同样是utf8字符集，中文环境和英文环境的中文顺序不一样。不同的lc_collate对于不同本地化语言，应该都可以对应到不同的alphabets。其中，lc_collate=C的排序一定是按字节序排的，虽然ASCII没有中文，但是C也可以排序中文，（基本）每个中文都可以对应UTF8的一个编码，而C以其字节序排序。

LC_CTYPE

LC_CTYPE影响字符操作（如upper、initcap等） 如果字符串都是英文，比如是'abcD'，initcap在3种collation下都会转换为'Abcd'，这里不多展示了。 但是加入中文，结果就不一样了：

LC_CTYPE=C时，initcap把每个非连续英文字符串的首字母大写，而en_US.utf8和zh_CN.utf8只会将首个字符大写（中文就不会变），其他英文字符小写。 initcap中文也许处于需求不明的状况，但是我们可以得出结论：不同的LC_CTYPE会导致initcap等字符敏感函数结果不一样。 另外，中文对于大小写不敏感，一些其他本地化语言同样有大小写，不同的LC_CTYPE导致的结果会更复杂。

字符集

字符集基础

PostgreSQL支持不同的字符集character sets（也叫encodings）。字符集于collation是两个概念，但是字符集必须跟LC_CTYPE，LC_COLLATE兼容。就像在pg_collation中看到的那样，C/POSIX支持所有字符集，而其他collation只支持一种字符集（linux系统中）。

PostgreSQL中文相关可用的字符集： (*collation C由libc库提供，部分collation可以由ICU库提供，需提前编译)

繁体中文： BIG5是最常见的繁体中文字符集标准。之前是业界标准，后来被录入为国家标准。 简体中文： GB是国标的意思，GB2312、GB18030、GBK都是我国的国家字符集标准。由于生僻字等问题，并经过多年发展产生了一些历史版本，所以标准看上去有多个。 其中EUC_CN全称为 Extended UNIX Code-CN ，其实就是GB2312，但它也不能处理所有罕见字。类似命名的还有EUC_KR,EUC_JP,EUC_TW等等。 国际标准： 上面的字符集都是国家标准，他们除了支持英、中外不支持其他语言。而国际标准支持世界上所有语言，这就是unicode国际编码标准(甚至emoji也包含其中 :+1:)。（还有个著名的国际标准组织ISO也在维护字符集，他俩有交集，这里先忽略ISO）。 由于Unicode编码方案的不同又有UTF-8、UTF-16、UTF-32三种编码方式。

UTF-8编码格式:

UTF8编码是变长的。 0x00-0x7F之间的字符(1字节），UTF-8编码与ASCII（American Standard Code for Information Interchange 美国标准信息交换代码）编码完全相同，所以UTF-8是完全兼容ASCII的。 由于同源、同义、相似性，在unicode中中日韩越使用了同一编码，称为中日韩统一表意文字（或称中日韩越统一表意文字）。 中日韩统一表意文字编码范围为：3400-4DBF/4E00-9FFF/20000-3FFFF

字符集转换

server_encoding与client_encoding不一致，可发生自动转换Server查出来的字符集。设置服务端字和客户端字符集参考设置字符集一节。 中文相关字符集Server/Client可转化表：

以上是可以转换的字符集，仍需要CONVERSATION的支持。PG内置了一些转换函数，可通过pg_conversion查看：

可通过create conversation语句创建自定义的转换，需指定转换的function。 有些字符集间看上去可以转换，但是server端根本不支持存储这些字符集（如big5、gb18030、gbk），所以也没啥用。我们这里仅需要知道euc_cn和utf8能相互转换就可以了。 没有CONVERSATION是不能发生转换的：

字符集转换测试： 需要注意客户端的字符集设置（如CRT的 "session"-"Appearance"-"Character encoding"） 至少有3个端有字符集的概念：数据库server、数据库client、UI客户端。CONVERSATION也只能控制：数据库server -> 数据库client 1.server为UTF8的转换测试：

2.server为EUC_CN的转换测试：

同样报错〇不能从UTF8转换为EUC_CN。EUC_CN（GB2312）中文编码不完全与UTF8相同，EUC_CN（GB2312）不是所有中文都包含的，特别是罕见字。

设置locale、collation和字符集

上面已经了解过本地化和字符集设置了，这里做一个汇总

database cluster的locale、collation、字符集

初始化时可设置database cluster的locale和字符集，参考：

initdb会创建postgres，template1，和template0三个库。create database语句时默认使用template1创建库。

• • encoding设置字符集；locale设置LC_COLLATE，LC_CTYPE，LC_MESSAGES，LC_MONETARY，LC_NUMERIC，LC_TIME，除非特别指定（如--lc_collate）
• • LC_COLLATE，LC_CTYPE称为collation，还可在database、列、索引上设置。LC_MESSAGES，LC_MONETARY，LC_NUMERIC，LC_TIME为实例参数，可随时修改。
• • encoding只能在初始化、创建database时设置，一旦设置不可修改。

database的collation、字符集

创建database时可设置database的字符集、lc_collate、lc_ctype。

create database ,createdb都可以在创建database时指定字符集，一旦创建就不能修改database的字符集。两个命令都是使用template库来创建database

template又有template0和template1，官方文档有这样一句话：

Another common reason for copying template0 instead of template1 is that new encoding and locale settings can be specified when copying template0, whereas a copy of template1 must use the same settings it does. This is because template1might contain encoding-specific or locale-specific data, while template0 is known not to.

template1是可写数据的模板库，可能包含本地化过的数据，而template0不能写数据，所以要创建不同的本地化库应使用template0。

而且要显示使用template0，因为不指定的话默认是template1。所以在创建database时没有指定template1且指定其他字符集会报错：

另外，不能在创建database时通过指定locale来设置字符集

报错表示需要指定LC_CYTPE子选项，把collation相关子选项全部加上仍然报错：

LOCALE又不能跟LC_CTYPE等子选项一起使用 然后把locale去掉，通过设置字符集、LC_COLLATE、LC_CTYPE 来设置，可以成功。

创建指定字符集的database的正确姿势：

• • create database

• • createdb 通过cli命令createdb来创建，createdb封装了create database，他俩是等价的：

查看database字符集：

1. \1. \l
2. \2. pg_database

1. \1. show参数

SERVER_ENCODING,LC_COLLATE,LC_CTYPE三个参数都是不可更改的，分别展示当前database的server端字符集、LC_COLLATE、LC_CTYPE

列的collation

collation只跟字符排序、字符函数相关，跟编码不相关。在没有索引的情况下，修改列的collation相当于只是在调整这个列的default排序输出，有索引的情况下会重建索引。不指定列的collation默认与database一致。

后续精彩内容已被小麦苗无情隐藏，请输入验证码解锁本站所有文章

验证码：

请关注本站微信公众号，回复“小麦苗博客”，获取验证码。在微信里搜索“DB宝”或者“www_xmmup_com”或者微信扫描右侧二维码都可以关注本站微信公众号。