oracle 数据库字符集研究 下篇-阿里云开发者社区

开发者社区> 长烟慢慢> 正文

oracle 数据库字符集研究 下篇

简介: 整理自:http://blog.itpub.net/519536/viewspace-615379/ 自从选用了AL32UTF8字符集做为生产数据库字符集之后,就一直奔走于“乱码”与“转码”之间。
+关注继续查看

整理自:http://blog.itpub.net/519536/viewspace-615379/

自从选用了AL32UTF8字符集做为生产数据库字符集之后,就一直奔走于“乱码”与“转码”之间。

如果想要搞清楚Oracle的字符系统,需要紧紧地抓住三个因素:
一.“客户终端字符集”
二.“NLS_LANG”环境变量
三.“数据库字符集”

如果“NLS_LANG”等于“数据库字符集”时,不需要进行任何转换,直接把字符插入数据库
如果“NLS_LANG”不等于“数据库字符集”,则需要进行转换,乱码的根源就在这里


1.“数据库字符集”是AL32UTF8,具体信息如下:
SQL> col VALUE for a30
SQL> select * from nls_database_parameters;

PARAMETER       VALUE
------------------------------ ------------------------------
NLS_LANGUAGE       AMERICAN
NLS_TERRITORY       AMERICA
NLS_CURRENCY       $
NLS_ISO_CURRENCY       AMERICA
NLS_NUMERIC_CHARACTERS       .,
NLS_CHARACTERSET       AL32UTF8
NLS_CALENDAR       GREGORIAN
NLS_DATE_FORMAT       DD-MON-RR
NLS_DATE_LANGUAGE       AMERICAN
NLS_SORT       BINARY
NLS_TIME_FORMAT       HH.MI.SSXFF AM


PARAMETER       VALUE
------------------------------ ------------------------------
NLS_TIMESTAMP_FORMAT       DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT       HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT        DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY       $
NLS_COMP       BINARY
NLS_LENGTH_SEMANTICS       BYTE
NLS_NCHAR_CONV_EXCP       FALSE
NLS_NCHAR_CHARACTERSET       AL16UTF16
NLS_RDBMS_VERSION       11.2.0.3.0

20 rows selected.

2.“客户终端字符集”信息如下:
以下实验使用了两种主要客户端(不包括后面提到的PL/SQL Developer和Toad):一个是XP的cmd命令行工具,另一个是PuTTY工具。
1)XP字符集是
C:\>chcp
Active code page: 936
代码页936就是中文字符集GBK,可以参考msdn的资料《Windows Codepage 936》
http://www.microsoft.com/globaldev/reference/dbcs/936.htm

2)PuTTY字符集我的设置:utf8

3.客户端使用AL32UTF8字符集进行测试
C:\>set NLS_LANG=AMERICAN_AMERICA.AL32UTF8  Linux环境(export NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
[oracle@lyg ~]$ export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
[oracle@lyg ~]$ echo $LANG
en_US.UTF-8
[oracle@lyg ~]$ echo $NLS_LANG
AMERICAN_AMERICA.AL32UTF8

[oracle@lyg ~]$ sqlplus "/as sysdba"
SQL*Plus: Release 11.2.0.3.0 Production on Thu Oct 30 14:11:16 2014
Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> drop table t purge;
drop table t purge
           *
ERROR at line 1:
ORA-00942: table or view does not exist
SQL>  create table t (x varchar2(20), y varchar2(20));
Table created.
SQL> insert into t values ('圣','AL32UTF8');
1 row created.
SQL> commit;
Commit complete.

SQL> col x for a10
SQL> col dump(x) for a30
SQL> select x, y, dump(x) from t;

X   Y DUMP(X)
---------- -------------------- ------------------------------
圣         AL32UTF8             Typ=1 Len=3: 229,156,163


4.客户端使用WE8ISO8859P1字符集进行测试
C:\>set NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1    Linux环境(export NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1
[oracle@lyg ~]$ export NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1
[oracle@lyg ~]$ echo $NLS_LANG
AMERICAN_AMERICA.WE8ISO8859P1
[oracle@lyg ~]$ sqlplus "/as sysdba"
SQL*Plus: Release 11.2.0.3.0 Production on Thu Oct 30 14:15:48 2014
Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> insert into t values ('圣','WE8ISO8859P1');
1 row created.
SQL> commit;
Commit complete.

SQL> col x for a10
SQL> col dump(x) for a30
SQL> select x, y, dump(x) from t;
X   Y DUMP(X)
---------- -------------------- ------------------------------
¿   AL32UTF8 Typ=1 Len=3: 229,156,163
  WE8ISO8859P1 Typ=1 Len=6: 195,165,194,156,194,163

5.客户端使用ZHS16GBK字符集进行测试
C:\>set NLS_LANG=AMERICAN_AMERICA.ZHS16GBK     Linux环境(export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
[oracle@lyg ~]$ echo $LANG
en_US.UTF-8
[oracle@lyg ~]$ echo $NLS_LANG
AMERICAN_AMERICA.ZHS16GBK
[oracle@lyg ~]$ sqlplus "/as sysdba"
SQL*Plus: Release 11.2.0.3.0 Production on Thu Oct 30 14:21:02 2014
Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options


SQL> insert into t values ('圣','ZHS16GBK');
ERROR:
ORA-01756: quoted string not properly terminated       ——11.2.0.3的环境,插不进去,网上查了下,应该是字符集设置的问题。

SQL> insert into t values ('A','ZHS16GBK');        ——尝试插了个A,是可以的。
1 row created.

又尝试插入了一个汉字“一”,倒是可以,但是插入“二”就又不行了。

SQL> select x, y, dump(x) from t;

X   Y DUMP(X)
---------- -------------------- ------------------------------
ʥ   AL32UTF8 Typ=1 Len=3: 229,156,163
£¿£¿¡   WE8ISO8859P1 Typ=1 Len=6: 195,165,194,156,194,163
A   ZHS16GBK Typ=1 Len=1: 65
  ZHS16GBK Typ=1 Len=6: 230,182,147,226,130,172

下面这是原作者10g中的测试:
sec@ora10g> insert into t values ('圣','ZHS16GBK');
1 row created.
sec@ora10g> commit;
Commit complete.

sec@ora10g> col x for a10
sec@ora10g> col dump(x) for a30
sec@ora10g> select x, y, dump(x) from t;
X          Y                    DUMP(X)
---------- -------------------- ------------------------------
?         AL32UTF8             Typ=1 Len=2: 202,165
ꥠ       WE8ISO8859P1         Typ=1 Len=4: 195,138,194,165
圣         ZHS16GBK             Typ=1 Len=3: 229,156,163


6.我把xshell设置长GB2312/16GB分别以ssh方式连接数据库服务器进行测试

SQL> insert into t values ('圣','xshgb2312 AL32UTF8');
1 row created.

SQL> commit;
Commit complete.

SQL> select x, y, dump(x) from t;
X   Y DUMP(X)
---------- -------------------- ------------------------------
?   AL32UTF8 Typ=1 Len=3: 229,156,163
??#   WE8ISO8859P1 Typ=1 Len=6: 195,165,194,156,194,163
A   ZHS16GBK Typ=1 Len=1: 65
??   ZHS16GBK Typ=1 Len=6: 230,182,147,226,130,172
??   xshgb2312 AL32UTF8 Typ=1 Len=6: 239,191,189,239,191,189

接下来,我再换成16GBK试试:


SQL> insert into t values ('圣','xsh GBK');
1 row created.


SQL> commit;
Commit complete.

SQL> col x for a10
SQL> col dump(x) for a30
SQL> select x, y, dump(x) from t;

X   Y                                 DUMP(X)
---------- --------------------                ------------------------------
?   AL32UTF8                  Typ=1 Len=3: 229,156,163
??#   WE8ISO8859P1          Typ=1 Len=6: 195,165,194,156,194,163
A   ZHS16GBK           Typ=1 Len=1: 65
??   ZHS16GBK           Typ=1 Len=6: 230,182,147,226,130,172
??   xshgb2312 AL32UTF8  Typ=1 Len=6: 239,191,189,239,191,189
??   xsh GBK Typ=1      Len=6: 239,191,189,239,191,189

6 rows selected.



7.最后我们将NLS_LANG置空进行一下最后的尝试
C:\>set NLS_LANG=        Linux环境(export NLS_LANG= 
 [oracle@lyg ~]$ export NLS_LANG= 
[oracle@lyg ~]$ echo $NLS_LANG
[oracle@lyg ~]$ sqlplus "/as sysdba"
SQL*Plus: Release 11.2.0.3.0 Production on Thu Oct 30 14:52:00 2014
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options


SQL> insert into t values ('圣','unset NLS_LANG');
1 row created.

SQL> commit;
Commit complete.

SQL> col x for a10
SQL> col dump(x) for a30
SQL> select x, y, dump(x) from t;
X   Y DUMP(X)
---------- -------------------- ------------------------------
?   AL32UTF8 Typ=1 Len=3: 229,156,163
??#   WE8ISO8859P1 Typ=1 Len=6: 195,165,194,156,194,163
A   ZHS16GBK Typ=1 Len=1: 65
??   ZHS16GBK Typ=1 Len=6: 230,182,147,226,130,172
??   xshgb2312 AL32UTF8 Typ=1 Len=6: 239,191,189,239,191,189
??   xsh GBK Typ=1 Len=6: 239,191,189,239,191,189
???   unset NLS_LANG Typ=1 Len=9: 239,191,189,239,191,189,239,191,189

7 rows selected.



9.Toad中“F9”的执行效果
X          Y                    DUMP(X)
---------- -------------------- ------------------------------
圣         AL32UTF8             Typ=1 Len=2: 202,165
脢楼         WE8ISO8859P1         Typ=1 Len=4: 195,138,194,165
鍦         ZHS16GBK             Typ=1 Len=3: 229,156,163
鍦         PuTTY AL32UTF8       Typ=1 Len=3: 229,156,163
圣         unset NLS_LANG       Typ=1 Len=2: 202,165

10.PL/SQL Developer中“Command Window”执行效果(我plsql dev设置的字符集是16GBK的(因为我的windows系统环境变量的设置))


11.PL/SQL Developer中“F8”执行效果(我plsql dev设置的字符集是16GBK的(因为我的windows系统环境变量的设置))



12.实验结论
1)如果有可能,尽量保证客户端编码(Windows XP的cmd工具可以使用chcp命令来确认)、NLS_LANG参数和数据库字符集这三个内容一致,这样设置,无论是从性能上,还是从防止编码转换上都是最佳的;
2)如果目的是支持中文,数据库Server端的字符集应该尽量选择ZHS16GBK或AL32UTF8字符集,这样可以减少因不当的“转码”导致的字符乱码故障;
3)(推荐)可已将NLS_LANG参数与操作终端字符编码一致,这样可以保证数据库能正确获得应用终端使用的编码,这时会发生“编码转换”,但是,这样就可以保证正确转码,可以防止错误的编码存入数据库;
4)(不推荐)也可以将NLS_LANG参数与数据库服务器端的编码一致,这样,客户端无论是发送到服务器端还是从服务器接收数据都不会“转码”,这样能保证客户端对字符的显示效果,但是,一定要小心,这时数据库服务器上存放的字符编码很可能是错误的。
5)PL/SQL Developer工具在AL32UTF8字符集下貌似可以保证数据效果,但是“Toad同学”貌似不太“稳定”。



版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
阿里云服务器怎么设置密码?怎么停机?怎么重启服务器?
如果在创建实例时没有设置密码,或者密码丢失,您可以在控制台上重新设置实例的登录密码。本文仅描述如何在 ECS 管理控制台上修改实例登录密码。
4054 0
阿里云服务器端口号设置
阿里云服务器初级使用者可能面临的问题之一. 使用tomcat或者其他服务器软件设置端口号后,比如 一些不是默认的, mysql的 3306, mssql的1433,有时候打不开网页, 原因是没有在ecs安全组去设置这个端口号. 解决: 点击ecs下网络和安全下的安全组 在弹出的安全组中,如果没有就新建安全组,然后点击配置规则 最后如上图点击添加...或快速创建.   have fun!  将编程看作是一门艺术,而不单单是个技术。
4398 0
[20130106]整和数据库--修改字符集.txt
[20130106]整和数据库--修改字符集.txt工作需要,需要把几个数据库合并在一起,由于以前数据库使用的字符集是AMERICAN_AMERICA.US7ASCII,而现在的数据库使用SIMPLIFIED CHINESE_CHINA.ZHS16GBK,整合的时候需要修改字符集,统一到字符集SIMPLIFIED CHINESE_CHINA.ZHS16GBK。
640 0
第6章 Spring Boot数据库集成
第6章 Spring Boot数据库层集成 6.1 Spring Boot集成mybatis 6.2 Spring Boot集成jpa 6.3 Spring Boot集成mongodb开发 参考资料: 1.
778 0
+关注
长烟慢慢
系统架构师
806
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
文娱运维技术
立即下载
《SaaS模式云原生数据仓库应用场景实践》
立即下载
《看见新力量:二》电子书
立即下载