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他们不是超集关系,但是存入之后也显示正常。


 

结论:

1.)数据库字符集(创建的时候设置的,后期没事别自己去update props$) 
 
2.)客户端字符集NLS_LANG(数据库机器上你设置的环境变量 echo $NLS_LANG)
 
3.)个人工具连接到服务器上,工具(putty/securecrt等等各种SSH客户端等等)设置的字符集,保证客户端字符集 NLS_LANG 和 个人工具显示的字符集一致,并且这个字符集是可以正常转换为数据库字符集就OK
 

[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 ——将当前文件转换回文本格式。
*/

 

如果你使用SQL脚本, 要注意脚本的编码。/**/封起来的那段是,用vi 查看文件的16进制码的命令。

 

 

oracle 字符集乱码本质验证,古老的榕树,5-wow.com

郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。