簡(jiǎn)述PostgreSQL字符集的幾種形式
作者：大肚熊
今天我們要簡(jiǎn)述一下PostgreSQL字符集的幾種形式，希望通過(guò)本文能讓大家對(duì)字符集有一個(gè)更深刻的理解。
PostgreSQL數(shù)據(jù)庫(kù)支持多種字符集，在配置字符集時(shí)要分清楚服務(wù)器與客戶端的字符集，字符集不一致盡管有時(shí)能夠發(fā)生轉(zhuǎn)換，但帶來(lái)的問(wèn)題也很頭疼。語(yǔ)言環(huán)境的配置也很重要。
服務(wù)器字符集<來(lái)自文檔>：
- 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-2
- 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
- KOI8 KOI8-R(U) Cyrillic Yes 1 KOI8R
- 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, Windows949UTF8 Unicode, 8-bit all Yes 1-4 Unicode
- WIN866 Windows CP866 Cyrillic Yes 1 ALTWIN874
- Windows CP874 Thai Yes 1
- WIN1250 Windows CP1250 Central European Yes 1
- WIN1251 Windows CP1251 Cyrillic Yes 1
- WINWIN1252 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
常用的簡(jiǎn)體中文字符集是UTF8和EUC_CN兩種。
可自動(dòng)轉(zhuǎn)換字符集<來(lái)自文檔>:
- 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, MULE_INTERNAL, SJIS, UTF8
- EUC_KR EUC_KR, MULE_INTERNAL, UTF8
- EUC_TW EUC_TW, BIG5, MULE_INTERNAL, UTF8
- GB18030 not supported as a server encoding
- GBK not supported as a server encoding
- ISO_8859_5 ISO_8859_5, KOI8, MULE_INTERNAL, 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, MULE_INTERNAL, UTF8, WIN866, WIN1251
- LATIN1 LATIN1, MULE_INTERNAL, UTF8
- LATIN2 LATIN2, MULE_INTERNAL, UTF8, WIN1250
- LATIN3 LATIN3, MULE_INTERNAL, UTF8
- LATIN4 LATIN4, MULE_INTERNAL, UTF8
- LATIN5 LATIN5, UTF8
- LATIN6 LATIN6, UTF8
- LATIN7 LATIN7, UTF8
- LATIN8 LATIN8, UTF8
- LATIN9 LATIN9, UTF8
- LATIN10 LATIN10, UTF8
- MULE_INTERNAL MULE_INTERNAL, BIG5, EUC_CN, EUC_JP, EUC_KR, EUC_TW, ISO_8859_5, KOI8, LATIN1 to LATIN4, SJIS, WIN866, WIN1250, WIN1251
- SJIS not supported as a server encoding
- SQL_ASCII any (no conversion will be performed)
- UHC not supported as a server encoding
- UTF8 all supported encodings
- WIN866 WIN866, ISO_8859_5, KOI8, MULE_INTERNAL, UTF8, WIN1251
- WIN874 WIN874, UTF8
- WIN1250 WIN1250, LATIN2, MULE_INTERNAL, UTF8
- WIN1251 WIN1251, ISO_8859_5, KOI8, MULE_INTERNAL, UTF8, WIN866
- WIN1252 WIN1252, UTF8
- WIN1253 WIN1253, UTF8
- WIN1254 WIN1254, UTF8
- WIN1255 WIN1255, UTF8
- WIN1256 WIN1256, UTF8
- WIN1257 WIN1257, UTF8
- WIN1258 WIN1258, UTF8
以下針對(duì)客戶端與服務(wù)器字符集配置問(wèn)題作幾個(gè)小測(cè)試。
測(cè)試一：服務(wù)器、客戶端、語(yǔ)言環(huán)境一致的情況
- [postgre@iss3984 ~]$ echo $LANG
- en_US.UTF-8
- [postgre@iss3984 ~]$ psql daduxiong
- Welcome to psql 8.3.11 (server 8.3.10), the PostgreSQL interactive terminal.
- Type:
- \copyright for distribution terms
- \h for help with SQL commands
- \? for help with psql commands
- \g or terminate with semicolon to execute query
- \q to quitdaduxiong=# \l
- List of databases
- Name | Owner | Encoding
- -----------+---------+----------
- daduxiong | postgre | UTF8
- postgres | postgre | UTF8
- template0 | postgre | UTF8
- template1 | postgre | UTF8(4 rows)
- daduxiong=# show client_encoding;
- client_encoding-----------------
- UTF8(1 row)daduxiong=# insert into t1 values (1,'中國(guó)');
- INSERT 0 1
- daduxiong=# select * from t1; id |
- name
- ----+------------------------
- 1 | 中國(guó) (1 row)
服務(wù)器與客戶端字符集相同，在數(shù)據(jù)錄入時(shí)不發(fā)生字符集轉(zhuǎn)換；因語(yǔ)言環(huán)境也相同所以展現(xiàn)不會(huì)出現(xiàn)亂碼。
測(cè)試二：客戶端與服務(wù)器、語(yǔ)言環(huán)境不一致的情況
- daduxiong=# \encoding
- GBKdaduxiong=# show
- client_encoding;
- client_encoding-----------------
- GBK(1 row)
- daduxiong=# insert into t1 values (2,'日本');
- INSERT 0 1daduxiong=# select * from t1;
- id | name
- ----+-------------------------
- 1 | �й�
- 2 | 日本 (2 rows)
客戶端與服務(wù)器的字符集不一致，在數(shù)據(jù)錄入時(shí)將發(fā)生字符集轉(zhuǎn)換；當(dāng)前展現(xiàn)的第二條記錄非亂碼形式是因?yàn)榭蛻舳俗址癁镚BK，在UTF8下同樣出現(xiàn)亂碼，在使用時(shí)需要語(yǔ)言環(huán)境進(jìn)行配置。
測(cè)試三：服務(wù)器與客戶端、語(yǔ)言環(huán)境不一致的情況
- daduxiong=# \q
- [postgre@iss3984 ~]$ LANG=GBK export LANG
- [postgre@iss3984 ~]$ echo $LANG
- GBK
- [postgre@iss3984 ~]$ psql daduxiong
- Welcome to psql 8.3.11 (server 8.3.10), the PostgreSQL interactive terminal.
- Type:
- \copyright for distribution terms
- \h for help with SQL commands
- \? for help with psql commands
- \g or terminate with semicolon to execute query
- \q to quitdaduxiong=# show client_encoding;
- client_encoding
- -----------------
- UTF8
- (1 row)
- daduxiong=# \encoding GBK
- daduxiong=# select * from t1;
- id | name
- ----+-------------------------
- 1 | �й�
- 2 | 日本
- (2 rows)
- daduxiong=# insert into t1 values (3,'美國(guó)');
- INSERT 0 1daduxiong=# select * from t1;
- id | name
- ----+-------------------------
- 1 | �й�
- 2 | 日本
- 3 | 美國(guó)
- (3 rows)daduxiong=# \q
客戶端、語(yǔ)言環(huán)境均配置為GBK字符集，在當(dāng)前環(huán)境下展現(xiàn)的為非亂碼形式，數(shù)據(jù)錄入時(shí)將發(fā)生字符集轉(zhuǎn)換。
測(cè)試四：服務(wù)器與客戶端、語(yǔ)言環(huán)境恢復(fù)一致的情況
- [postgre@iss3984 ~]$ LANG=en_US.UTF8 export LANG
- [postgre@iss3984 ~]$ psql daduxiong
- Welcome to psql 8.3.11 (server 8.3.10), the PostgreSQL interactive terminal.
- Type: \copyright for distribution terms
- \h for help with SQL commands
- \? for help with psql commands
- \g or terminate with semicolon to execute query
- \q to quitdaduxiong=# show client_encoding; client_encoding
- -----------------
- UTF8(1 row)
- daduxiong=# select * from t1;
- id | name
- ----+-------------------------
- 1 | 中國(guó)
- 2 | 鏃ユ湰
- 3 | 緹庡浗
- (3 rows)daduxiong=#
通過(guò)恢復(fù)原始的PostgreSQL字符集狀態(tài),所有環(huán)境均為UTF8字符集，此時(shí)發(fā)現(xiàn)經(jīng)過(guò)字符集轉(zhuǎn)換后的內(nèi)容為亂碼。
原文標(biāo)題：postgresql 字符集
鏈接：http://www.cnblogs.com/daduxiong/archive/2010/10/14/1851552.html
【編輯推薦】
- PostgreSQL新版提高Oracle兼容性 欲與甲骨文試比高？
- Sun如何處理PostgreSQL和Java DB?
- PostgreSQL 8.2簡(jiǎn)介 附下載地址
- 簡(jiǎn)評(píng)MySQL和PostgreSQL優(yōu)劣
- 將你的網(wǎng)站從MySQL改為PostgreSQL
責(zé)任編輯：彭凡
來(lái)源：
博客園
