oracle 字符集乱码本质验证
之前一直困惑为什么数据库字符集和客户端字符集是一致的但是当数据库插入到表里却成了乱码，今天在群里看见一位前辈讲解了这个问题，因此也就跟着做了一个实验验证下，结果发现了其中的奥秘：
1） 如果恰巧数据库的字符集也是UTF8, 那么Oracle就不作任何转换直接插入到数据中.
2） 如果数据库的字符集是ZHS16GBK, 那么Oracle会根据内部的MAP,按UTF8截取客户端发来的字符串, 转换成ZHS16GBK
3）如果您指定NLS_LANG是utf8, 但是, 输入的却是zhs16gbk的编码, 那么Oracle也会不作任何转换, 将ZHS16GBK的字符编码直接存入数据库. --这叫garbage-in--garbage-out
4）如果数据库的字符是AL32UTF8, 您指定NLS_LANG为ZHS16GBK, 但是, 您真正输入的是UTF8的字符, 那么,Oracle会把您输入的UTF8字符当作ZHS16GBK字符转换为UTF8存入数据库. 这种情况会出现乱码.
5）之前的客户端字符集一定要和服务器字符集一致或者是超集才不会出现乱码，这个结论是片面的，本实验GBK和utf8他们不是超集关系，但是存入之后也显示正常。
结论：
[oracle@hxy ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Wed Mar 26 10:53:59 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
SQL> col parameter for a30
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 ZHS16GBK
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 10.2.0.1.0
20 rows selected.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64 bit Production
With the Partitioning, OLAP and Data Mining options
实验一：数据库字符集，客户端字符集，个人工具字符集一致
1）设置NLS_LANG为ZHS16GBK
[oracle@hxy ~]$ export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
2）把个人工具的编码也设置成ZHS16GBK
3）连接数据库，插入数据
[oracle@hxy ~]$ sqlplus / as sysdba
SQL>insert into t2 values(‘ZHS16GBK‘,‘ZHS16GBK‘,‘中国‘);
SQL> select * from t2;
NLS_LANG INPUT_CHARSET C1
-------------------- -------------------- --------------------
ZHS16GBK ZHS16GBK 中国
SQL> select c1,dump(c1,16) from t2;
C1 DUMP(C1,16)
-------------------- --------------------------------------------------------------------------------
中国 Typ=1 Len=4:
d6,d0,b9,fa ZHS16GBK编码是2个字符
此时编码显示正常， 如果恰巧数据库的字符集也是ZHS16GBK, 那么Oracle就不作任何转换直接插入到数据中.
4）把个人工具编码设置成UTF8，之后向数据库里插入数据
SQL> insert into t2 values(‘ZHS16GBK‘,‘UTF8‘,‘中国‘);
1 row created.
SQL> commit ;
Commit complete.
SQL> select * from t2;
NLS_LANG INPUT_CHARSET C1
-------------------- -------------------- --------------------
ZHS16GBK ZHS16GBK ?й? 此处显示了乱码
ZHS16GBK UTF8 中国 后插入的数据正常显示
SQL> select c1, input_charset,dump(c1,16) from t2;
C1 INPUT_CHARSET DUMP(C1,16)
-------------------- ------------------------- -------------------------------------------------------
?й? ZHS16GBK Typ=1 Len=4: d6,d0,b9,fa
中国 UTF8
Typ=1 Len=6: e4,b8,ad,e5,9b,bd
用下dump函数查看后发现存入的编码长度改变utf8的3个字符的了
数据库中存储, 没有错, 但是iterm2将UTF8的码按照GB2312来解释, 并打在屏幕上, 明显编码长度是有问题的.
由此可以得出结论为：若数据库的字符集是ZHS16GBK, 那么Oracle会根据内部的MAP,按UTF8截取客户端发来的字符串, 转换成ZHS16GBK，因此显示的结果是正常的，但是存入的数据编码却变了。
实验 2.
~~~~~~~~~~~~~
a) 设置个人工具的字符集为 GB2312
b) 设置NLS_LANG=american_america.AL32UTF8
[oracle@hxy ~]$ export NLS_LANG=american_america.AL32UTF8
[oracle@hxy ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Wed Mar 26 10:57:12 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
SQL> desc t2
Name Null? Type
----------------------------------------- -------- ----------------------------
NLS_LANG VARCHAR2(20)
INPUT_CHARSET VARCHAR2(20)
C1 VARCHAR2(20)
SQL> insert into t2 values(‘UTF8‘,‘ZHS16GBK‘,‘中国‘ );
SQL> select c1,dump(c1,16) from t2;
SQL> insert into t2 values(‘UTF8‘,‘ZHS16GBK,‘中国‘ );
SQL> commit;
Commit complete.
SQL> select * from t2;
NLS_LANG INPUT_CHARSET C1
------------------------------------------------------------ ---------------------------------- -------------------------- ------------------------------------------------------------
UTF8 ZHS16GBK 锛??
ZHS16GBK ZHS16GBK 涓浗
ZHS16GBK UTF8 娑擃厼娴?
全部变成乱码了。
SQL> select c1, input_charset,dump(c1,16) from t2;
C1 INPUT_CHARSET DUMP(C1,16)
----------------------------------- ------------------------------------------------------------ ----------------------------------------------------
锛?? ZHS16GBK Typ=1 Len=4: a3,bf,3f,3f
涓浗 ZHS16GBK Typ=1 Len=4: d6,d0,b9,fa
娑擃厼娴? UTF8 Typ=1 Len=6: e4,b8,ad,e5,9b,bd
上面标黄色的编码明显是错误的，这种情况叫garbage-in--garbage-out, 这是最有欺骗性的一种设置.
将个人工具的字符集修改回与NLS_LANG相同的设置---UTF8就会出现问题.
SQL> select c1, input_charset,dump(c1,16) from t2;
C1 INPUT_CHARSET DUMP(C1,16)
---------------------------------------- --------------------------------- ----------------------------------------------------------
？?? <<=== ZHS16GBK Typ=1 Len=4: a3,bf,3f,3f
中国 ZHS16GBK Typ=1 Len=4: d6,d0,b9,fa
涓浗 UTF8 Typ=1 Len=6: e4,b8,ad,e5,9b,bd
此编码是不能显示正常，出现了乱码行为，这就是一种欺骗性的，日常工作中经常容易发生，但是很难发现问题，这个一定要小心。
实验 3.
个人工具: UTF8
NLS_LANG: american_america.UTF8
SQL>insert into t2 values(‘UTF8‘,‘UTF8‘,‘中国‘);
SQL> set line 200
SQL> select c1, input_charset,dump(c1,16) from t2;
C1 INPUT_CHARSET DUMP(C1,16)
----------------------------------- ------------------------------- --------------------------------------------
？?? ZHS16GBK Typ=1 Len=4: a3,bf,3f,3f
中国 UTF8 Typ=1 Len=4: d6,d0,b9,fa
中国 ZHS16GBK Typ=1 Len=4: d6,d0,b9,fa
涓浗 UTF8 Typ=1 Len=6: e4,b8,ad,e5,9b,bd
可以看到只要个人工具的字符集和nls_lang的字符集是是一致的，并且数据库字符集和客户端字符集可以相互转换就不会出现乱码，
不出现乱码并不是之前所说的客户端字符集并一定是和数据库字符集一致。
3. 关于export/import的字符集问题.
a) 导出时NLS_LANG的设置, 决定存地DMP文件中的字符集.
b) 导入时的字符集转换情况分三步:
b.1 读取DMP文件的字符集设置, 一般存在文件的2~3个字节. 10g以前, 可以通过更改这两个字节的值, 来修改字符集. 但是, 10G,11G以后, 字符集还存在于其它地方, 基本没有修改的可能.
b.2 将DMP文件里的字符转换成, import时NLS_LANG所设置的字符集.
b.3 导入时, 将字符从 NLS_LANG转为数据库字集.
$ export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
$ exp \"/ as sysdba\" file=demo.dmp tables=t2;
Export: Release 11.2.0.4.0 - Production on Sun Mar 23 19:50:24 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)
About to export specified tables via Conventional Path ...
. . exporting table T2 3 rows exported
$ cat demo.dmp | od -x | head
0000000 0303 4554 5058 524f 3a54 3156 2e31 3230 <<===0354
0000020 302e 0a30 5344 5359 520a 4154 4c42 5345
0000040 380a 3931 0a32 0a30 3237 300a 030a 0354
0000060 0769 00d0 0001 0000 0000 0000 0000 0008
0000100 2020 2020 2020 2020 2020 2020 2020 2020
*
0000140 2020 2020 2020 2020 7553 206e 614d 2072
0000160 3332 3120 3a39 3035 323a 2035 3032 3431
0000200 6564 6f6d 642e 706d 0000 0000 0000 0000
0000220 0000 0000 0000 0000 0000 0000 0000 0000
SQL> select nls_charset_name(to_number(‘0354‘,‘xxxx‘)) from dual;
NLS_CHARSET_NAME(TO_NUMBER(‘0354‘,‘XXXX‘
----------------------------------------
ZHS16GBK
/*
select to_char(nls_charset_id(‘ZHS16GBK‘),‘XXXX‘) from dual;
在vi的命令状态下 :
:%!xxd ——将当前文本转换为16进制格式。
:%!od ——将当前文本转换为16进制格式。
:%!xxd -c 12——将当前文本转换为16进制格式,并每行显示12个字节。
:%!xxd -r ——将当前文件转换回文本格式。
*/
郑重声明：本站内容如果来自互联网及其他传播媒体，其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享，并不代表本站赞同其观点和对其真实性负责，也不构成任何其他建议。
