之前一直困惑為什麼數據庫字符集和客戶端字符集是一致的但是當數據庫插入到表裡卻成了亂碼，今天在群裡看見一位前輩講解了這個問題，因此也就跟著做了一個實驗驗證下，結果發現了其中的奧秘：
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進制碼的命令。
