PostgreSQL数据库支持多种字符集，在配置字符集时要分清楚服务器与客户端的字符集，字符集不一致尽管有时能够发生转换，但带来的问题也很头疼。语言环境的配置也很重要。
服务器字符集<来自文档>：
- NameDescriptionLanguageServer?Bytes/CharAliases
- BIG5BigFiveTraditionalChineseNo1-2WIN950,Windows950
- EUC_CNExtendedUNIXCode-CNSimplifiedChineseYes1-3
- EUC_JPExtendedUNIXCode-JPJapaneseYes1-3
- EUC_JIS_2004ExtendedUNIXCode-JP,JISX0213JapaneseYes1-3
- EUC_KRExtendedUNIXCode-KRKoreanYes1-3
- EUC_TWExtendedUNIXCode-TWTraditionalChinese,TaiwaneseYes1-3
- GB18030NationalStandardChineseNo1-2
- GBKExtendedNationalStandardSimplifiedChineseNo1-2WIN936,Windows936
- ISO_8859_5ISO8859-5,ECMA113Latin/CyrillicYes1
- ISO_8859_6ISO8859-6,ECMA114Latin/ArabicYes1
- ISO_8859_7ISO8859-7,ECMA118Latin/GreekYes1
- ISO_8859_8ISO8859-8,ECMA121Latin/HebrewYes1
- JOHABJOHABKorean(Hangul)No1-3
- KOI8KOI8-R(U)CyrillicYes1KOI8R
- LATIN1ISO8859-1,ECMA94WesternEuropeanYes1ISO88591
- LATIN2ISO8859-2,ECMA94CentralEuropeanYes1ISO88592
- LATIN3ISO8859-3,ECMA94SouthEuropeanYes1ISO88593
- LATIN4ISO8859-4,ECMA94NorthEuropeanYes1ISO88594
- LATIN5ISO8859-9,ECMA128TurkishYes1ISO88599
- LATIN6ISO8859-10,ECMA144NordicYes1ISO885910
- LATIN7ISO8859-13BalticYes1ISO885913
- LATIN8ISO8859-14CelticYes1ISO885914
- LATIN9ISO8859-15LATIN1withEuroandaccentsYes1ISO885915
- LATIN10ISO8859-16,ASROSR14111RomanianYes1ISO885916
- MULE_INTERNALMuleinternalcodeMultilingualEmacsYes1-4
- SJISShiftJISJapaneseNo1-2Mskanji,ShiftJIS,WIN932,Windows932
- SHIFT_JIS_2004ShiftJIS,JISX0213JapaneseNo1-2
- SQL_ASCIIunspecified(seetext)anyYes1
- UHCUnifiedHangulCodeKoreanNo1-2
- WIN949,Windows949UTF8Unicode,8-bitallYes1-4Unicode
- WIN866WindowsCP866CyrillicYes1ALTWIN874
- WindowsCP874ThaiYes1
- WIN1250WindowsCP1250CentralEuropeanYes1
- WIN1251WindowsCP1251CyrillicYes1
- WINWIN1252WindowsCP1252WesternEuropeanYes1
- WIN1253WindowsCP1253GreekYes1
- WIN1254WindowsCP1254TurkishYes1
- WIN1255WindowsCP1255HebrewYes1
- WIN1256WindowsCP1256ArabicYes1
- WIN1257WindowsCP1257BalticYes1
- WIN1258WindowsCP1258VietnameseYes1ABC,TCVN,TCVN5712,VSCII
常用的简体中文字符集是UTF8和EUC_CN两种。
可自动转换字符集<来自文档>:
- ServerCharacterSetAvailableClientCharacterSets
- BIG5notsupportedasaserverencoding
- EUC_CNEUC_CN,MULE_INTERNAL,UTF8
- EUC_JPEUC_JP,SJIS,UTF8
- EUC_KREUC_KR,UTF8
- EUC_TWEUC_TW,BIG5,UTF8
- GB18030notsupportedasaserverencoding
- GBKnotsupportedasaserverencoding
- ISO_8859_5ISO_8859_5,KOI8,UTF8,WIN866,WIN1251
- ISO_8859_6ISO_8859_6,UTF8
- ISO_8859_7ISO_8859_7,UTF8
- ISO_8859_8ISO_8859_8,UTF8
- JOHABJOHAB,UTF8
- KOI8KOI8,ISO_8859_5,WIN1251
- LATIN1LATIN1,UTF8
- LATIN2LATIN2,WIN1250
- LATIN3LATIN3,UTF8
- LATIN4LATIN4,UTF8
- LATIN5LATIN5,UTF8
- LATIN6LATIN6,UTF8
- LATIN7LATIN7,UTF8
- LATIN8LATIN8,UTF8
- LATIN9LATIN9,UTF8
- LATIN10LATIN10,UTF8
- MULE_INTERNALMULE_INTERNAL,EUC_CN,EUC_JP,EUC_KR,EUC_TW,LATIN1toLATIN4,WIN1250,WIN1251
- SJISnotsupportedasaserverencoding
- SQL_ASCIIany(noconversionwillbeperformed)
- UHCnotsupportedasaserverencoding
- UTF8allsupportedencodings
- WIN866WIN866,WIN1251
- WIN874WIN874,UTF8
- WIN1250WIN1250,LATIN2,UTF8
- WIN1251WIN1251,WIN866
- WIN1252WIN1252,UTF8
- WIN1253WIN1253,UTF8
- WIN1254WIN1254,UTF8
- WIN1255WIN1255,UTF8
- WIN1256WIN1256,UTF8
- WIN1257WIN1257,UTF8
- WIN1258WIN1258,UTF8
以下针对客户端与服务器字符集配置问题作几个小测试。
测试一：服务器、客户端、语言环境一致的情况
- [postgre@iss3984~]$echo$LANG
- en_US.UTF-8
- [postgre@iss3984~]$psqldaduxiong
- Welcometopsql8.3.11(server8.3.10),thePostgreSQLinteractiveterminal.
- Type:
- copyrightfordistributionterms
- hforhelpwithSQLcommands
- ?forhelpwithpsqlcommands
- gorterminatewithsemicolontoexecutequery
- qtoquitdaduxiong=#l
- Listofdatabases
- Name|Owner|Encoding
- -----------+---------+----------
- daduxiong|postgre|UTF8
- postgres|postgre|UTF8
- template0|postgre|UTF8
- template1|postgre|UTF8(4rows)
- daduxiong=#showclient_encoding;
- client_encoding-----------------
- UTF8(1row)daduxiong=#insertintot1values(1,'中国');
- INSERT01
- daduxiong=#select*fromt1;id|
- name
- ----+------------------------
- 1|中国(1row)
服务器与客户端字符集相同，在数据录入时不发生字符集转换；因语言环境也相同所以展现不会出现乱码。
测试二：客户端与服务器、语言环境不一致的情况
- daduxiong=#encoding
- GBKdaduxiong=#show
- client_encoding;
- client_encoding-----------------
- GBK(1row)
- daduxiong=#insertintot1values(2,'日本');
- INSERT01daduxiong=#select*fromt1;
- id|name
- ----+-------------------------
- 1|?й?
- 2|日本(2rows)
客户端与服务器的字符集不一致，在数据录入时将发生字符集转换；当前展现的第二条记录非乱码形式是因为客户端字符集为GBK，在UTF8下同样出现乱码，在使用时需要语言环境进行配置。
测试三：服务器与客户端、语言环境不一致的情况
- daduxiong=#q
- [postgre@iss3984~]$LANG=GBKexportLANG
- [postgre@iss3984~]$echo$LANG
- GBK
- [postgre@iss3984~]$psqldaduxiong
- Welcometopsql8.3.11(server8.3.10),thePostgreSQLinteractiveterminal.
- Type:
- copyrightfordistributionterms
- hforhelpwithSQLcommands
- ?forhelpwithpsqlcommands
- gorterminatewithsemicolontoexecutequery
- qtoquitdaduxiong=#showclient_encoding;
- client_encoding
- -----------------
- UTF8
- (1row)
- daduxiong=#encodingGBK
- daduxiong=#select*fromt1;
- id|name
- ----+-------------------------
- 1|?й?
- 2|日本
- (2rows)
- daduxiong=#insertintot1values(3,'美国');
- INSERT01daduxiong=#select*fromt1;
- id|name
- ----+-------------------------
- 1|?й?
- 2|日本
- 3|美国
- (3rows)daduxiong=#q
客户端、语言环境均配置为GBK字符集，在当前环境下展现的为非乱码形式，数据录入时将发生字符集转换。
测试四：服务器与客户端、语言环境恢复一致的情况
- [postgre@iss3984~]$LANG=en_US.UTF8exportLANG
- [postgre@iss3984~]$psqldaduxiong
- Welcometopsql8.3.11(server8.3.10),thePostgreSQLinteractiveterminal.
- Type:copyrightfordistributionterms
- hforhelpwithSQLcommands
- ?forhelpwithpsqlcommands
- gorterminatewithsemicolontoexecutequery
- qtoquitdaduxiong=#showclient_encoding;client_encoding
- -----------------
- UTF8(1row)
- daduxiong=#select*fromt1;
- id|name
- ----+-------------------------
- 1|中国
- 2|?ユ?
- 3|缇??
- (3rows)daduxiong=#
通过恢复原始的PostgreSQL字符集状态,所有环境均为UTF8字符集，此时发现经过字符集转换后的内容为乱码。
原文标题：postgresql 字符集
链接：
http://www.cnblogs.com/daduxiong/archive/2010/10/14/1851552.html
【编辑推荐】
- PostgreSQL新版提高Oracle兼容性 欲与甲骨文试比高？
- Sun如何处理PostgreSQL和Java DB?
- PostgreSQL 8.2简介 附下载地址
- 简评MySQL和PostgreSQL优劣
- 将你的网站从MySQL改为PostgreSQL
【责任编辑：
彭凡 TEL：（010）68476606】
