postgresql 字符集

postgresql 数据库支持多种字符集，在配置字符集时要分清楚服务器与客户端的字符集，字符集不一致尽管有时能够发生转换，但带来的问题也很头疼。语言环境的配置也很重要。
服务器字符集<来自文档>：

代码 
      
    
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    3     
EUC_JP    Extended UNIX CodeJP    Japanese    Yes         
EUC_JIS_2004    Extended UNIX CodeJP,JIS X 0213    Japanese    Yes         
EUC_KR    Extended UNIX CodeKR    Korean    Yes         
EUC_TW    Extended UNIX CodeTW    Traditional Chinese,Taiwanese    Yes         
GB18030    National Standard    Chinese    No         
GBK    Extended National Standard    Simplified Chinese    No        WIN936,Windows936
ISO_8859_5    ISO 88595,ECMA 113    LatinCyrillic    Yes         
ISO_8859_6    ISO 6114Arabic    Yes         
ISO_8859_7    ISO 7118Greek    Yes         
ISO_8859_8    ISO 8121Hebrew    Yes         
JOHAB    JOHAB    Korean (Hangul)    No         
KOI8    KOI8R(U)    Cyrillic    Yes        KOI8R
LATIN1    ISO 94    Western European    Yes        ISO88591
LATIN2    ISO     Central European    Yes        ISO88592
LATIN3    ISO     South European    Yes        ISO88593
LATIN4    ISO 4    North European    Yes        ISO88594
LATIN5    ISO 9128    Turkish    Yes        ISO88599
LATIN6    ISO 10144    Nordic    Yes        ISO885910
LATIN7    ISO 13    Baltic    Yes        ISO885913
LATIN8    ISO 14    Celtic    Yes        ISO885914
LATIN9    ISO 15    LATIN1 with Euro and accents    Yes        ISO885915
LATIN10    ISO 1614111    Romanian    Yes        ISO885916
MULE_INTERNAL    Mule internal code    Multilingual Emacs    Yes         
SJIS    Shift JIS    Japanese    No        Mskanji,ShiftJIS,WIN932,Windows932
SHIFT_JIS_2004    Shift JIS,0)">    Japanese    No         
sql_ASCII    unspecified (see text)    any    Yes         
UHC    Unified Hangul Code    Korean    No        WIN949,Windows949
UTF8    Unicode,bit    all    Yes        Unicode
WIN866    Windows CP866    Cyrillic    Yes        ALT
WIN874    Windows CP874    Thai    Yes         
WIN1250    Windows CP1250    Central European    Yes         
WIN1251    Windows CP1251    Cyrillic    Yes        WIN
WIN1252    Windows CP1252    Western European    Yes         
WIN1253    Windows CP1253    Greek    Yes         
WIN1254    Windows CP1254    Turkish    Yes         
WIN1255    Windows CP1255    Hebrew    Yes         
WIN1256    Windows CP1256    Arabic    Yes         
WIN1257    Windows CP1257    Baltic    Yes         
WIN1258    Windows CP1258    Vietnamese    Yes        ABC,TCVN,TCVN5712,VSCII
 
      
    

常用的简体中文字符集是UTF8和EUC_CN两种。
可自动转换字符集<来自文档>:

代码

Server Character Set Available Client Character Sets BIG5 not supported as a server encoding EUC_CN EUC_CN,MULE_INTERNAL,UTF8 EUC_JP EUC_JP,SJIS,UTF8 EUC_KR EUC_KR,UTF8 EUC_TW EUC_TW,BIG5,UTF8 GB18030 not supported a server encoding GBK not supported a server encoding ISO_8859_5 ISO_8859_5,KOI8,UTF8,WIN866,WIN1251 ISO_8859_6 ISO_8859_6,UTF8 ISO_8859_7 ISO_8859_7,UTF8 ISO_8859_8 ISO_8859_8,UTF8 JOHAB JOHAB,UTF8 KOI8 KOI8,ISO_8859_5,WIN1251 LATIN1 LATIN1,UTF8 LATIN2 LATIN2,WIN1250 LATIN3 LATIN3,UTF8 LATIN4 LATIN4,UTF8 LATIN5 LATIN5,UTF8 LATIN6 LATIN6,UTF8 LATIN7 LATIN7,UTF8 LATIN8 LATIN8,UTF8 LATIN9 LATIN9,UTF8 LATIN10 LATIN10,UTF8 MULE_INTERNAL MULE_INTERNAL,EUC_CN,EUC_JP,EUC_KR,EUC_TW,LATIN1 to LATIN4,WIN1250,WIN1251 SJIS not supported a server encoding sql_ASCII any (no conversion will be performed) UHC not supported a server encoding UTF8 all supported encodings WIN866 WIN866,WIN1251 WIN874 WIN874,UTF8 WIN1250 WIN1250,LATIN2,UTF8 WIN1251 WIN1251,WIN866 WIN1252 WIN1252,UTF8 WIN1253 WIN1253,UTF8 WIN1254 WIN1254,UTF8 WIN1255 WIN1255,UTF8 WIN1256 WIN1256,UTF8 WIN1257 WIN1257,UTF8 WIN1258 WIN1258,UTF8

以下针对客户端与服务器字符集配置问题作几个小测试。

测试一：服务器、客户端、语言环境一致的情况

代码

[postgre@iss3984 ~]$ echo $LANG en_US.UTF [postgre@iss3984 ]$ p sql daduxiong Welcome to psql 8.3.11 (server ),the Postgresql interactive terminal. Type: \copyright for distribution terms \h help with sql commands \ help with psql commands \g or terminate with semicolon to execute query \q to quit daduxiong=# \l List of databases Name | Owner Encoding -----------+---------+---------- daduxiong postgre UTF8 postgres UTF8 template0 UTF8 template1 UTF8 ( rows) daduxiong# show client_encoding; client_encoding ----------------- UTF8 ( row) daduxiong# insert into t1 values ('中国'); INSERT 0 daduxiong# select * from t1; id name ----+------------------------ 中国 ( row)

服务器与客户端字符集相同，在数据录入时不发生字符集转换；因语言环境也相同所以展现不会出现乱码。

测试二：客户端与服务器、语言环境不一致的情况

代码

daduxiong# \encoding GBK daduxiong GBK (日本 name ----+------------------------- �й� 日本 ( rows)

客户端与服务器的字符集不一致，在数据录入时将发生字符集转换；当前展现的第二条记录非乱码形式是因为客户端字符集为GBK，在UTF8下同样出现乱码，在使用时需要语言环境进行配置。

测试三：服务器与客户端、语言环境不一致的情况

代码

# \q [postgre@iss3984 ]$ LANGGBK export LANG [postgre@iss3984 ]$ echo $LANG GBK [postgre@iss3984 美国日本美国 ( rows) daduxiong# \q

客户端、语言环境均配置为GBK字符集，在当前环境下展现的为非乱码形式，数据录入时将发生字符集转换。

测试四：服务器与客户端、语言环境恢复一致的情况

代码

en_US.UTF8 export LANG [postgre@iss3984 中国鏃ユ湰缇庡浗 (#