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同学”貌似不太“稳定”。

Oracle 12.2 新特性 | PDB不同字符集变更深入解析 从12c版本12.1开始，就有了新特性——Pluggable Database，相比较之前通过schema的管理方式实现多用户管理，Pluggable Database实现了进一步的隔离，其中包含如下特点：
将业务用户元数据和数据库本身的元数据进行分离，减少核心数据字典的性能衰减，增加管理便捷性。