之前一直困惑為什麼資料庫字符集和客戶端字符集是一致的但是當資料庫插入到表裡卻成了亂碼，今天在群裡看見一位前輩講解了這個問題，因此也就跟著做了一個實驗驗證下，結果發現了其中的奧秘,並整理了下共享下，同時感謝群友不吃魚的貓協助我做實驗，非常感謝。下面是驗證的過程

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

資料庫中儲存, 沒有錯, 但是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

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

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 字符集亂碼本質驗證

相關文章