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

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位的了

数据库中存储, 没有错,

oracle字符集乱码本质验证(一)