PostgreSQL中的区域和编码

南京富士通南大软件技术有限公司 陈华军 2014/12/12

大纲


 个人介绍 
 区域及编码概述 
 PostgreSQL中的区域和编码 
 字符编码 
 排序规则 
 其它 
 总结

个人介绍

现为富士通南大软件技术有限公司高级工程师，从事数据库产品相关的开发。 
 2002年 加入富士通南大 
 2006年~ 从事富士通关系数据库Symfoware相关的开发和维护 
 2012年~ 伴随Symfoware V12 Open版接触PostgreSQL

(Symfoware V12 Open版基于PostgreSQL定制) 
 2012年~ 向PostgreSQL社区贡献了少量的 Patch

(pgsql,pgjdbc,npgsql合计10+件) 
 2013年 中国PG大会分享《HA故障转移时的应用切换方案实践》

QQ:30041724 Mail:chenhj@cn.fujitsu.com Blog:http://http://blog.chinaunix.net/uid/20726500.html

区域及编码概述


什么是区域 
区域的分类(category) 
OS的区域设置

什么是区域

区域

由于国家和本地习惯的不同，在处理文字、日期、数字、货币格式等问题 都会有所不同，计算机中“区域(Locale)”就是用来解决这个问题的。

区域名的形式

language_territory[.codeset]

例:

特殊的区域(C或者POSIX)

man setlocale The locale "C" or "POSIX" is a portable locale; its LC_CTYPE part corresponds to the 7-bit ASCII character set.

区域的分类(category)



主要的区域分类(category)



分类 LC_COLLATE



作用 字符排序顺序



LC_CTYPE



字符分类(什么是字母?是否区分大小写?)



LC_MESSAGES



消息的语言



LC_MONETARY



货币金额的格式



LC_NUMERIC LC_TIME



数值格式 6 日期和时间格式



注）字符编码一般由LC_CTYPE决定

OS的区域设置

Linux: 通过环境变量(LC_ALL/LC_*/LANG)设置



Windows:



环境变量的优先级： LC_ALL > LC_* > LANG

PostgreSQL中的区域和编码


区域相关的配置 
区域相关配置的影响 
设置区域和编码

区域相关的配置 (1)

数据库固有的区域相关属性


包括以下3种属性

Encoding Collate Ctype


可通过psql的”\l”或系统表pg_database查看


 数据库创建后，不可变更

区域相关的配置(2)

postgresql.conf中的区域相关参数


主要包括以下属性

client_encoding lc_messages lc_monetary lc_numeric lc_time DateStyle default_text_search_config


 可通过SHOW命令或系统表pg_settings查看


 可通过pg_ctl reload命令在线全局变更 
 可通过set命令修改当前会话的参数

区域相关配置的影响



数据库(pg_database)



项目



说明



E数nc据o库din(pgg_database数) 据库编码



影响 postgres进程和数据文件的字符编码



Collate



字符排序顺序



字符比较，ORDER BY，字符列的索引 使用



Ctype



字符分类(什么是字母? 是否区分大小写?)



upper, lower, initcap 大小写不敏感的模式匹配和使用了字 符分类的正则表达式匹配



postgresql.conf

项目

client_encoding lc_messages lc_monetary lc_numeric lc_time DateStyle …



说明 客户端编码 消息的语言



影响 C/S间字符编码转换 错误消息的语言



货币，数值和时间的 货币,数值和日期类型数据的输出格式



显示格式



to_char函数族

设置区域和编码 (1)



创建数据库集群时的区域设置


 initdb时可通过参数指定数据库集群的缺省区域和编码



initdb -E | --encoding --locale --lc-collate --lc-ctype --lc-messages --lc-monetary --lc-numeric --lc-time



postgres template0 template1

postgresql.conf



根据区域设置的pg_database属性： encoding datcollate datctype

根据区域设置的参数： lc_messages lc_monetary lc_numeric lc_time datestyle(lc-time) default_text_search_config(lc-ctype)




 如未明确指定区域和编码，initdb通过OS运行环境判断区域和编码

Linux下是环境变量LC_ALL > LC_* > LANG

设置区域和编码 (2)



创建数据库时的区域设置




 创建数据库时默认继承数据库模板template1的区域设置

Encoding Collate Ctype


 也可以指定其它的区域设置(必须使用template0作为模板)



CREATE DATABASE newdb TEMPLATE=template0 ENCODING='UTF8' LC_COLLATE='zh_CN.utf8' LC_CTYPE='zh_CN.utf8'



newdb template0




 字符编码，LC_COLLATE和LC_CTYPE在数据库创建后不能变更

设置区域和编码 (3)



启动PostgreSQL进程时的区域设置




 PostgreSQL各个进程的启动过程与区域设置

1）根据环境变量设置区域属性 LC_COLLATE, LC_CTYPE,LC_MESSAGES

2 ）设置以下区域属性为”C” LC_MONETARY, LC_NUMERIC, LC_TIME

3）根据postgresql.conf设置区域属性和PostgreSQL区域相关的参数 LC_MESSAGES, LC_MONETARY, LC_NUMERIC, LC_TIME

postmaster



5 ）客户端连接时fork postgres进程

ppoposoststgtgrgreresess

6 ）根据pg_database设置 LC_COLLATE, LC_CTYPE



4） fork 辅助进程



辅助进程

writer wal writer

logger stats collector checkpointer autovacuum launcher

设置区域和编码 (4)




 最终的PostgreSQL进程的区域相关属性由以下因素决定



 postgres进程及个别辅助进程(autovacuum, background worker)



LC_COLLATE



LC_CTYPE



LC_MESSAGES LC_MONETARY LC_NUMERIC



pg_database



Y



Y



postgresql.conf



YYY



系统环境

*) postgresql.conf中省略lc_messages时生效



Y(*)



LC_TIME Y



 postmaster及其它辅助进程



LC_COLLATE



LC_CTYPE



postgresql.conf



系统环境



YY



*) postgresql.conf中省略lc_messages时生效



LC_MESSAGES LC_MONETARY LC_NUMERIC



YY Y(*)



Y



LC_TIME Y

字符编码




字符编码和乱码




PostgreSQL支持的中文字符编码




C/S自动字符编码转换




client_encoding的设置




服务端的字符编码检查




编码检查不能完全杜绝乱码




小结

字符编码和乱码

字符编码

字符编码是字符在计算机中以二进制值表示的方法。比如’a’的ACSII码 是0x61。支持简体中文的字符编码包括: UTF8, GB2312(EUC_CN), GBK, GB18030

乱码

字符编码解码的过程中出现错误就会产生乱码。出现乱码可从以下几个方 面找原因： 显示的字体和字符编码 处理过程中的编码转换 数据的源头是否有非法字符

PostgreSQL支持的中文字符编码

服务端支持的简体中文字符编码

UTF8,EUC_CN

客户端支持的简体中文字符编码

UTF8,EUC_CN,GBK,GB18030

为什么服务端不支持GBK和GB18030?



PostgreSQL使用的flex按照ASCII编码解析SQL语句， ASCII不兼容的编码会让PostgreSQL头疼。



编码名 UTF8



编码范围

单字节:00-7F(ASCII) 多字节:80-XX



备注 ASCII兼容



EUC_CN(GB2312) 双字节:A1-F7,A1-FE



ASCII兼容



GBK GB18030



双字节:81-FE,40-7E/80-FE

单字节:00-7F(ASCII) 双字节:81-FE,40-7E/80-FE 四字节:81-FE,30-39,81-FE,30-39



ASCII不兼容 ASCII不兼容

C/S自动字符编码转换

C/S间编码不一致时服务端自动进行字符编码转换



Application GBK



set client_encoding to 'GBK'



postgres



GBK



UTF8



编码转换



UTF8



客户端通过client_encoding通知服务端自己的编码

设置client_encoding到的几种方式(越往下优先级越高)  postgresql.conf  连接包(StartupMessage)的参数  set client_encoding to ‘XXX’

client_encoding的设置



几个常见客户端设置client_encoding的不同方法



客户端（驱动）

libpq/ecpg/psql

pgjdbc Npgsql psqlODBC(unicode)

psqlODBC(ansi)



设置client_encoding的方法 1)客户端区域环境 2)PGCLIENTENCODING 3)连接参数client_encoding 4)API调用PQsetClientEncoding() 注)越往下优先级越高

UTF8

UTF8

UTF8

1)客户端区域环境 2)PGCLIENTENCODING 3) ConnSettings=set client_encoding to ‘XXX‘ 注)越往下优先级越高

服务端的字符编码检查(1)

PostgreSQL服务端对不符合数据库编码的字符报错

试图在EUC_CN的数据库上使用EUC_CN不支持的“丂”字时PostgreSQL报错

服务端的字符编码检查（2）

在UTF8的数据库上可以使用“丂”字

在UTF8的数据库上使用非法字符(\xff)同样报错

编码检查不能完全阻止非法字符(1)

通过convert_from插数据可以跳过数据库的编码检查

通过convert_from插入了EUC_CN编码外的字符“丂”(\x8140)到EUC_CN编码的数据库



但是，查询数据时现了形



BUG

编码检查不能完全阻止非法字 符(2)

在convert_from函数中使用SQL_ASCII,同样可以插入非法字符(\xff)到UTF8数据库 查询时也没有报错

不报错只是暂时的，强迫PostgreSQL做C/S转码，就会报错了

编码检查不能完全阻止非法字 符(3)


 前面的例子中暴露的问题  SQL_ASCII向其它编码转换时，PostgeSQL的一些地方没有检查字符代码是否 在目的编码范围内。  原编码和目的编码间没有定义默认的转换函数时，PostgeSQL没有报错。 (PostgreSQL中没有定义GBK/GB18030/EUC_CN间的默认转码函数)

上面2个问题已在PostgreSQL 9.4rc1中被修复！

9.4确保进入数据库的字符都经过了严格的编码检查。

参考：http://blog.163.com/digoal@126/blog/static/16387704020141139372877/

编码检查不能完全阻止非法字 符(4)

PostgreSQL不能检查出合法编码范围内的乱码

终端软件的编码是UTF8，但把client_encoding设成GBK



Xshell的终端编码设置



真的对吗？

数据插进去了，并且查出来也是”对的”。 换一个设置正确的终端查看数据(终端软件编码和client_encoding都是UTF8)

已经凌乱了

编码检查不能完全杜绝乱码(5)

SQL_ASCII编码的数据库完全没有编码检查

SQL_ASCII并不是指单纯的7bit的ASCII，而是1~255通吃(0是特例)，所以可以 用SQL_ASCII存0字节以外的任何东西。包括各种编码字符的混合，或者不属于任 何编码的字节序列。

强烈建议不要用SQL_ASCII作为数据库编码！

http://58.58.27.50:8079/doc/html/9.3.1_zh/multibyte.html SQL_ASCII设置与其它设置表现得相当不同。如果服务器字符集 是SQL_ASCII， 服务器根据ASCII标准解析0-127的字节值，而字节值为128255的则当作未解析的字符。 如果设置为SQL_ASCII就不会有编码转换。因 此，这个设置基本不用来声明所使用的编码， 因为这个声明会忽略编码。在 大多数情况下，如果你使用了任何非ASCII数据， 那么使用SQL_ASCII设置都 是不明智的，因为PostgreSQL 会无法帮助你转换或者校验非ASCII字符。

编码相关的注意事项


服务端使用UTF8作为数据库编码  SQL_ASCII缺少编码检查  EUC_CN的字符不够多，并且PostgreSQL没有定义EUC_CN

和GBK/GB18030间的默认转码函数 
客户端设置合适的区域环境和client_encoding  psql等工具能根据环境自动设置合适的client_encoding，因此执

行psql前需要设置好合适的区域环境。  使用libpq编程时需注意client_encoding的设置

排序规则


排序规则的影响 
排序规则的设置 
中文拼音排序规则的缺陷

排序规则的影响(1)

排序规则在PostgreSQL中的影响

 字符比较  ORDER BY  字符列的索引使用

PostgreSQL中字符比较的实现

C或POSIX 按照数据库字符编码下的字符二进制代码比较

其他区域 通过调用操作系统提供的strcoll()实现字符比较。中文区域通常按拼音比较。

排序规则的影响(2)



例1:排序



LC_COLLATE=C (按汉字编码排序)



UTF8:



LC_COLLATE=zh_CN (按汉字拼音排序)



GB2312:



NO 汉字 UTF8



GB2312



1 貂 E8 B2 82 F5 F5 2 王 E7 8E 8B CD F5



3 西 E8 A5 BF CE F7



4 杨 E6 9D A8 D1 EE



注：测试系统的OS是RHEL6



GB2312的一级汉字按拼音排序，二级汉字（ 如:”貂”）按部首排序。所以按GB编码排序 可作为“近似的”拼音排序。

排序规则的影响(3)

例2:索引

环境准备: 查询的collate和索引的collate一致时会使用索引：

查询的collate和索引的collate不一致时不会使用索引：

排序规则的设置(1)

作为数据库属性的排序规则不能变更，但可以在列和表达式上指定不同的排序规则

指定列的排序规则

CREATE TABLE test1 ( a text COLLATE “C", b text COLLATE "zh_CN", ...

);

指定表达式的排序规则

create index on test1(a collate "zh_CN"); select * from test1 order by a collate "zh_CN";

Collate的优先级

表达式 > 列定义 > 数据库定义

排序规则的设置(2)

可动态指定的排序规则一览

可以在列和表达式上指定的”COLLATE”是定义在pg_collation中的对象，相当 于LC_COLLATE+LC_CTYPE组合的别名。

中文拼音排序规则的缺陷


zh_CN字符比较的性能比C差很多

恢复数据：性能差1倍 vacuum analyze:性能差5倍 建索引:性能差5倍 参考: http://bbs.pgsqldb.com:8079/client/post_show.php?zt_auto_bh=57394 http://my.oschina.net/quanzl/blog/143167 
某些多音字的拼音排序结果不一定符合预期 
依赖于操作系统实现，不同OS下结果可能不一样

考虑到性能，可以把数据库的Collate设成C，只在真正需要按拼音排序的列或者 表达式上使用Collate “zh_CN”。

其它


字符分类 
错误消息 
数据输出格式

字符分类(1)



影响的功能


 upper(),lower(),initcap() 
 大小写不敏感的模式匹配 
 使用了字符分类的正则表达式匹配

例:

Collate为“C”



Collate为“zh_CN”

字符分类(2)

正则表达式不支持全角英文和数字的分类:

但是OS上是支持的

PostgreSQL的正则表达式实现出于性能原因，缓存了unicode代码点0~0x7FF的字符 的字符分类，全角英文和数字不在这个范围内。

参考:http://blog.chinaunix.net/uid-20726500-id-4680122.html

错误消息 (1)

影响的功能

 Log中输出消息的语言  客户端捕获的服务端消息的语言



例:

LC_MESSAGES=“C”



LC_MESSAGES=“zh_CN”

错误消息 (2)

输出中文消息时的注意点

 服务端消息日志可能会包含不同字符编码的消息

不同字符编码的多个数据库的postgres进程输出的消息编码不同。

Postmaster及部分辅助进程可能输出的消息编码和postgres进程不同。  客户端得到的连接错误消息可能是乱码

连接时服务端还不知道客户端的client_encoding，如果客户端和服务库编码不一致可能 会导致乱码。同理，数据库名，用户名和密码中如果有中文，也可能会导致客户端无法建 立连接。

参考:http://blog.chinaunix.net/uid-20726500-id-4675473.html

数据输出格式 (1)

LC_MONETARY影响money数据类型的输出

例:

LC_MONETARY=“C”



LC_MONETARY=“zh_CN”



money类型比较鸡肋 不如使用numeric

数据输出格式 (2)

LC_NUMERIC和LC_TIME似乎对数据输出没有影响



例:

LC_NUMERIC =“C”



LC_NUMERIC =“zh_CN”



LC_TIME=“C”



LC_TIME =“zh_CN”

数据输出格式 (3)

DateStyle影响日期数据的输出 例:

DateStyle =“C”

DateStyle =sql

注) 其它一些影响数据输出格式的参数可参考PostgreSQL手册 http://www.postgresql.org/docs/9.4/static/runtime-config-client.html#RUNTIME-CONFIG-CLIENT-FORMAT

数据输出格式 (4)



数据输出格式依赖于区域的弊端



 数据输出格式如果不确定将不利于数据处理,所以很多客户端接口会在连 接建立时就重新设置这些参数。比如Npgsql：



NpgsqlConnector.cs:



var parameters = new Dictionary<String, String> {



{ "database",



databaseName },



{ "user",



username },



{ "DateStyle",



"ISO" },



{ "client_encoding", "UTF8" },



{ "lc_monetary", "C"



},



};



 使用libpq编程时需要开发者自己留意数据输出格式

总结



相关建议



 服务端使用UTF8作为数据库编码  客户端设置合适的区域环境和client_encoding  数据库的Collate设成C

然后只在真正需要按拼音排序的列或者表达式上使用Collate“zh_CN”。  使用标准的区域无关的数据输出格式

例:

创建数据库集群：

initdb -E UTF8 --locale=C data



启动数据库：

export LC_ALL=zh_CN.UTF8

pg_ctl -D data -l logfile start



客户端建立连接：

export LC_ALL=zh_CN.UTF8

psql

参考

• http://www.postgresql.org/docs/9.4/static/runtime-config-client.html#RUNTIME-CONFIGCLIENT-FORMAT

• http://www.postgresql.org/docs/9.4/static/charset.html • http://blog.163.com/digoal@126/blog/static/16387704020141139372877/ • http://blog.163.com/digoal@126/blog/static/163877040201173003547236/ • http://bbs.pgsqldb.com:8079/client/post_show.php?zt_auto_bh=57394 • http://my.oschina.net/quanzl/blog/143167 • http://www.unisys.co.jp/services/atlasbase/pdf/dbm_1001_postresql.pdf

47 47