简述PostgreSQL字符集的几种形式

前端之家收集整理的这篇文章主要介绍了简述PostgreSQL字符集的几种形式前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。

简述Postgresql字符集的几种形式

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

猜你在找的Postgre SQL相关文章