【字符集】“客户终端字符集”、“NLS_LANG”环境变量以及“数据库字符集”
上一篇 / 下一篇 2009-09-22 17:55:02 / 个人分类:故障处理与分析
自从选用了AL32UTF8字符集做为生产数据库字符集之后,就一直奔走于“乱码”与“转码”之间。
在与“乱码”PK良久之后,有了这个小文儿。
如果想要搞清楚Oracle的字符系统,需要紧紧地抓住三个因素:
一.“客户终端字符集”
二.“NLS_LANG”环境变量
三.“数据库字符集”
如果“NLS_LANG”等于“数据库字符集”时,不需要进行任何转换,直接把字符插入数据库
如果“NLS_LANG”不等于“数据库字符集”,则需要进行转换,乱码的根源就在这里
1.“数据库字符集”是AL32UTF8,具体信息如下:
sys@ora10g> col VALUE for a30
sys@ora10g> 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
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 UTF8
NLS_RDBMS_VERSION 10.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
C:\>sqlplus sec/sec@DB_AL32UTF8
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Sep 22 16:12:14 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options
sec@ora10g> drop table t purge;
Table dropped.
sec@ora10g> create table t (x varchar2(20), y varchar2(20));
Table created.
sec@ora10g> insert into t values ('圣','AL32UTF8');
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
4.客户端使用WE8ISO8859P1字符集进行测试
C:\>set NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1
C:\>sqlplus sec/sec@ DB_AL32UTF8
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Sep 22 16:14:55 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options
sec@ora10g> insert into t values ('圣','WE8ISO8859P1');
1 row created.
sec@ora10g> commit;
Commit complete.
sec@ora10g>
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
5.客户端使用ZHS16GBK字符集进行测试
C:\>set NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
C:\>sqlplus sec/sec@ DB_AL32UTF8
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Sep 22 16:17:13 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options
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.使用PuTTY以ssh方式连接数据库服务器进行测试
ora10g@secDB /home/oracle$ sqlplus sec/sec
SQL*Plus: Release 10.2.0.3.0 - Production on Tue Sep 22 16:21:17 2009
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options
sec@ora10g> insert into t values ('圣','PuTTY AL32UTF8');
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
圣 PuTTY AL32UTF8 Typ=1 Len=3: 229,156,163
7.最后我们将NLS_LANG置空进行一下最后的尝试
C:\>set NLS_LANG=
C:\>sqlplus sec/sec@ DB_AL32UTF8
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Sep 22 16:24:57 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options
sec@ora10g> insert into t values ('圣','unset NLS_LANG');
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
鍦? PuTTY AL32UTF8 Typ=1 Len=3: 229,156,163
圣 unset NLS_LANG Typ=1 Len=2: 202,165
8.Toad中“Execute as script”的执行效果:
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
5 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”执行效果
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
11.PL/SQL Developer中“F8”执行效果
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
12.实验结论
1)如果有可能,尽量保证客户端编码(Windows XP的cmd工具可以使用chcp命令来确认)、NLS_LANG参数和数据库字符集这三个内容一致,这样设置,无论是从性能上,还是从防止编码转换上都是最佳的;
2)如果目的是支持中文,数据库Server端的字符集应该尽量选择ZHS16GBK或AL32UTF8字符集,这样可以减少因不当的“转码”导致的字符乱码故障;
3)(推荐)可已将NLS_LANG参数与操作终端字符编码一致,这样可以保证数据库能正确获得应用终端使用的编码,这时会发生“编码转换”,但是,这样就可以保证正确转码,可以防止错误的编码存入数据库;
4)(不推荐)也可以将NLS_LANG参数与数据库服务器端的编码一致,这样,客户端无论是发送到服务器端还是从服务器接收数据都不会“转码”,这样能保证客户端对字符的显示效果,但是,一定要小心,这时数据库服务器上存放的字符编码很可能是错误的。
5)PL/SQL Developer工具在AL32UTF8字符集下貌似可以保证数据效果,但是“Toad同学”貌似不太“稳定”。
在与“乱码”PK良久之后,有了这个小文儿。
如果想要搞清楚Oracle的字符系统,需要紧紧地抓住三个因素:
一.“客户终端字符集”
二.“NLS_LANG”环境变量
三.“数据库字符集”
如果“NLS_LANG”等于“数据库字符集”时,不需要进行任何转换,直接把字符插入数据库
如果“NLS_LANG”不等于“数据库字符集”,则需要进行转换,乱码的根源就在这里
1.“数据库字符集”是AL32UTF8,具体信息如下:
sys@ora10g> col VALUE for a30
sys@ora10g> 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
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 UTF8
NLS_RDBMS_VERSION 10.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
C:\>sqlplus sec/sec@DB_AL32UTF8
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Sep 22 16:12:14 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options
sec@ora10g> drop table t purge;
Table dropped.
sec@ora10g> create table t (x varchar2(20), y varchar2(20));
Table created.
sec@ora10g> insert into t values ('圣','AL32UTF8');
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
4.客户端使用WE8ISO8859P1字符集进行测试
C:\>set NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1
C:\>sqlplus sec/sec@ DB_AL32UTF8
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Sep 22 16:14:55 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options
sec@ora10g> insert into t values ('圣','WE8ISO8859P1');
1 row created.
sec@ora10g> commit;
Commit complete.
sec@ora10g>
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
5.客户端使用ZHS16GBK字符集进行测试
C:\>set NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
C:\>sqlplus sec/sec@ DB_AL32UTF8
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Sep 22 16:17:13 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options
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.使用PuTTY以ssh方式连接数据库服务器进行测试
ora10g@secDB /home/oracle$ sqlplus sec/sec
SQL*Plus: Release 10.2.0.3.0 - Production on Tue Sep 22 16:21:17 2009
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options
sec@ora10g> insert into t values ('圣','PuTTY AL32UTF8');
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
圣 PuTTY AL32UTF8 Typ=1 Len=3: 229,156,163
7.最后我们将NLS_LANG置空进行一下最后的尝试
C:\>set NLS_LANG=
C:\>sqlplus sec/sec@ DB_AL32UTF8
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Sep 22 16:24:57 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options
sec@ora10g> insert into t values ('圣','unset NLS_LANG');
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
鍦? PuTTY AL32UTF8 Typ=1 Len=3: 229,156,163
圣 unset NLS_LANG Typ=1 Len=2: 202,165
8.Toad中“Execute as script”的执行效果:
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
5 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”执行效果
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
11.PL/SQL Developer中“F8”执行效果
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
12.实验结论
1)如果有可能,尽量保证客户端编码(Windows XP的cmd工具可以使用chcp命令来确认)、NLS_LANG参数和数据库字符集这三个内容一致,这样设置,无论是从性能上,还是从防止编码转换上都是最佳的;
2)如果目的是支持中文,数据库Server端的字符集应该尽量选择ZHS16GBK或AL32UTF8字符集,这样可以减少因不当的“转码”导致的字符乱码故障;
3)(推荐)可已将NLS_LANG参数与操作终端字符编码一致,这样可以保证数据库能正确获得应用终端使用的编码,这时会发生“编码转换”,但是,这样就可以保证正确转码,可以防止错误的编码存入数据库;
4)(不推荐)也可以将NLS_LANG参数与数据库服务器端的编码一致,这样,客户端无论是发送到服务器端还是从服务器接收数据都不会“转码”,这样能保证客户端对字符的显示效果,但是,一定要小心,这时数据库服务器上存放的字符编码很可能是错误的。
5)PL/SQL Developer工具在AL32UTF8字符集下貌似可以保证数据效果,但是“Toad同学”貌似不太“稳定”。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/10951282/viewspace-772296/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/10951282/viewspace-772296/