oracle 字符集 （轉）

搞懂oracle字符集

作為一個ORACLE DBA,在工作中會經常處理由於字符集產生的一些問題。但是當真正想寫一些這方面的東西時，卻突然又沒有了頭緒。發了半天呆，還是決定用兩個字符集方面的例子作為切入點，倒不失為一個頭緒，說不定在實驗的過程中，問題就會一個接著一個的浮現出來。
現在，讓我們切入正題。
我用的資料庫是oracle10.2.0.3,資料庫字符集是al32utf8。
客戶端就是同一臺機器的windows xp.
下面是演示的例子：
SQL> drop table test purge;
Table dropped.
SQL> create table test(col1 number(1),col2 varchar2(10));
Table created.

--session 1 設定客戶端字符集為 zhs16gbk（修改登錄檔nls_lang項的characterset 為zhs16gbk） 向表中插入兩個中文字元。
SQL> insert into test values(1,'中國'); --1為session 1的標記
1 row created.
SQL> commit;
Commit complete.

--session 2 設定客戶端字符集 al32utf8（修改登錄檔nls_lang項的characterset 為al32utf8），與資料庫字符集相同。 向表中插入兩個和session 1相同的中文字元。
SQL> insert into test values(2,'中國'); --2為session 2的標記
1 row created.
SQL> commit;
Commit complete.

--session 1
SQL> select * from test;
COL1 COL2
---------- --------------------
2 ？??
1 中國
--session 2
SQL> select * from test;
COL1 COL2
---------- ----------
2 中國
1 涓?浗
從session 1和session 2的結果中可以看到，相同的字元（注意，我指的是我們看到的，顯示為相同的字元），在不同的字符集輸入環境下，顯示成了亂碼。
在zhs16gbk字符集的客戶端，我們看到了utf8字符集客戶端輸入的相同的中文變成了亂碼--&gtcol1=2的col2欄位
在utf8字符集客戶端，我們看到zhs16gbk字符集的客戶端輸入的中文變成了另外的字元 --&gtcol1=1的col2欄位
從這個例子裡，我們好像感覺到出了什麼問題，也可能會聯想起現實環境中出現的亂碼問題。
問題似乎有了思路，ok，讓我們繼續把實驗做下去：
--session 1 （或者session 2，在這裡無所謂）
SQL> select col1,dump(col2,1016) from test;
COL1
----------
DUMP(COL2,1016)
--------------------------------------------------------------------------------
2
Typ=1 Len=4 CharacterSet=AL32UTF8: d6,d0,b9,fa
1
Typ=1 Len=6 CharacterSet=AL32UTF8: e4,b8,ad,e5,9b,bd

我們使用了dump函式，結果看起來很明顯了，兩個完全相同的字元，在不同的字符集環境下，在資料庫中儲存成了不同的編碼。
對於ZHS16GBK的字符集客戶端輸入的字元"中國"，AL32UTF8使用了3個位元組來分別儲存一個字元，即：
中--e4,b8,ad
國--e5,9b,bd
我們也可以分別對這個字元進行驗證：
--session 1
SQL> select dump('中',1016) from dual;
DUMP('中',16)
--------------------------------------------
Typ=96 Len=3 CharacterSet=AL32UTF8: e4,b8,ad --字元“中” ，和上面直接從資料庫中讀取儲存的字元編碼一致。
SQL> select dump('國',1016) from dual;
DUMP('國',16)
--------------------------------------------
Typ=96 Len=3CharacterSet=AL32UTF8: e5,9b,bd --字元“國” ，和上面直接從資料庫中讀取儲存的字元編碼一致。
如果使用session 2直接對著兩個字元進行測試，一樣會得到相同的結果（筆者已經做過測試，這裡為了避免冗長，刪掉了）.

讓我們重新來理一下思路，並提出幾個問題：
1：為什麼顯示為相同的字元，儲存到資料庫中卻變成了不同的編碼？
2：我們在向資料庫中插入資料的時候，oracle究竟做了些什麼？
3：作業系統字符集，客戶端字符集，資料庫字符集究竟是什麼關係？

帶著這些疑惑，讓我們接著做實驗，所有的疑團和猜測都會在試驗中得以驗證。
我的思路是，先取得測試環境的相關引數。
1：windows字符集(codepage)
我們使用chcp命令來獲得windows使用的字符集
c:chcp
活動的內碼表: 936
透過oracle的官方文件閱讀，我們可以將它等同於ZHS16GBK字符集（在安裝oracle時，oracle會找到安裝平臺的字符集，並預設將對應的字符集設定成與它相同，在這裡，資料庫預設的字符集本身應該是ZHS16GBK,但我強制將它修改為AL32UTF8）。
所以現在我們可以認為，我們使用的作業系統是ZHS16GBk字符集，那麼我們在這個環境下輸入的字元（也可以說是顯示的字元，用的就是這個字符集的編碼）。
讓我們繼續討論問題。
我們現在要討論一下客戶端字符集究竟是用來做什麼的。
我們知道，很多字符集都有自己的編碼方式，換句話說，相同的字元，在不同的字符集裡對應的編碼可能是不一樣的。
客戶端的字符集就是為了讓資料庫知道我們傳遞過去的字元是屬於那種字符集，以便於oracle在儲存字元時做相應的編碼對映。
拿上面的例子來說：
比如字元"中國"
在ZHS16GBK字符集中，它的編碼是：d6,d0，b9,fa
在AL32UTF8字符集中，它的編碼是:e4,b8,ad,e5,9b,bd
讓我們看看例子中兩個session輸入的相同字元在資料庫中儲存對應的編碼：
SQL> select col1,dump(col2,1016) from t1;
COL1
----------
DUMP(COL2,1016)
--------------------------------------------------------------------------------
2
Typ=1 Len=4 CharacterSet=AL32UTF8: d6,d0,b9,fa
1
Typ=1 Len=6 CharacterSet=AL32UTF8: e4,b8,ad,e5,9b,bd
對於session 1,我們設定的客戶端字符集為zhs16gbk。
當我們和資料庫建立session後，資料庫將認為這個客戶端以zhs16gbk字符集編碼的方式向資料庫傳送字元，因為資料庫的字符集是al32utf8,所以字元要以這個字符集的編碼來儲存，此時oracle就會做一個字元編碼轉換，也就是將字符集zhs16gbk中編碼為d6,d0,b9,fa 的字元編碼對映成字符集為al32utf8編碼為e4,b8,ad,e5,9b,bd，在字符集al32utf8的編碼裡，e4,b8,ad,e5,9b,bd對應的字元為"中國".
對於session 2,我們設定的客戶端字符集為al32utf8。
當我們和資料庫建立session後，資料庫看到客戶端的字符集和資料庫的字符集一致，此時oracle將不會再對字元作轉換，因為它認為兩邊的字元編碼是一致的。而此時，我們欺騙了資料庫，儘管我們將客戶端字符集設定為和資料庫一致，但是其實我們使用的是zhs16gbk字符集編碼（因為此時windows使用的就是這個字元編碼），對於字元"中國",zhs16gbk字符集裡對應的編碼為d6,d0,b9,fa。此時，oracle不加理會的直接將這個編碼儲存到了資料庫中。當我們分別將這兩個字元dump出來的時候，就得到下面這樣的結果。
SQL> select col1,dump(col2,1016) from test;
COL1
----------
DUMP(COL2,1016)
--------------------------------------------------------------------------------
2
Typ=1 Len=4 CharacterSet=AL32UTF8: d6,d0,b9,fa
1
Typ=1 Len=6 CharacterSet=AL32UTF8: e4,b8,ad,e5,9b,bd
下面我們就進入到了我們最關心的地方，亂碼，讓我們繼續我們的試驗。

--session 1
SQL>
SQL> insert into t1 values('中國',1);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from t1;
COL COL2
------------ ----------
中國 1
？?? 2
--session 2
SQL> insert into t1 values('中國',2);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from t1;
COL COL2
------ ----------
涓?浗 1
中國 2
session 1,我們看到session 2輸入的字元"中國"變成了亂碼"？??",
session 2,我們看到session 1輸入的字元"中國"變成了另外的字元"涓?浗",
下面我們來分析一下這中間資料庫，客戶端和作業系統都發生了那些事情。
上面已經討論了：
session 1 輸入的字元"中國" 在資料庫中儲存的字元編碼為”e4,b8,ad,e5,9b,bd".
session 2 輸入的字元"中國" 在資料庫中儲存的字元編碼為”d6,d0,b9,fa".
當session 1開始查詢時，oracle從表中取出這兩個字元，並按照字符集al32utf8和字符集zhs16gbk的編碼對映表，將它的轉換成zhs16gbk字元編碼，對於編碼“e4,b8,ad,e5,9b,bd”，它對應的zhs16gbk的字元編碼為"d6,d0,b9,fa",這個編碼對應的字元為”中國“，所以我們看到了這個字元正常顯示出來了，而對於字符集al32utf8字元編碼“d6,d0,b9,fa”，由於我們用於顯示字元的windows環境使用的是zhs16gbk字符集,而在zhs16gbk字符集裡面並沒有對應這個編碼的字元或者屬於無法顯示的符號，於是使用了"?"這樣的字元來替換，這就是為什麼我們看到session 2輸入的字元變成了這樣的亂碼。
當session 2開始查詢時，oracle從表中取出這兩個字元，由於客戶端（nls_lang)和資料庫的字符集設定一致，oracle將忽略字元的轉換問題，於是直接將資料庫中儲存的字元返回給客戶端。對於編碼為"d6,d0,b9,fa"的字元，返回給客戶端，而客戶端顯示所用的字符集正好是zhs16gbk，在這個字符集裡，這個編碼對應的是"中國"兩個字元，所以就正常顯示出來了。對於字元編碼“e4,b8,ad,e5,9b,bd”，返回到客戶端後，因為在zhs16gbk裡採用的是雙位元組儲存字元方式，所以這6位元組對應了zhs16gbk字符集的3個字元,也就是我們看到的"涓?浗".

到現在為止，我想我們基本上搞清楚了為什麼日常查詢時會遇到亂碼的問題。
其實亂碼，說到底就是用於顯示字元的作業系統沒有在字元編碼中找到對應的字元導致的，造成這種現象的主要原因就是：
1：輸入操作的os字元編碼和查詢的os字元編碼不一致導致出現亂碼。
2：輸入操作的客戶端字符集(nls_lang)和查詢客戶端字符集（nls_lang)不同，也可能導致查詢返回亂碼或者錯誤的字元。

還有一個問題需要解釋一下：
在上面的例子中，相同的字元在不同的字符集中對應著不同的字元編碼，這個通常稱為字符集不相容或者不完全相容，比如zhs16gbk和al32utf8,他們儲存的ascii碼的字元編碼都是相同的，但對於漢字卻是不同的。
如果兩個字符集對於相同的字元采用的相同的字元編碼，我們稱之為字元相容，範圍大的叫做範圍小的字符集的超級。我們通常遇到的zhs16cgb231280,zhs16gbk就是這樣的情況，後者是前者的超級。

在實際的環境中除了字元顯示之外，還有其他的地方會涉及到字符集問題。比如：
1:exp/imp
2:sql*lorder
3：應用程式的字元輸入
......
一個誤區：
看到很多人在出現亂碼的時候都首先要做的就是將客戶端字符集設定和資料庫一致，其實這是沒有太多根據的。
設想一下，假如資料庫字符集是al32utf8,裡面儲存這一些中文字元，而我的客戶端作業系統是英文的，此時我將客戶端的nls_lang設定成al32utf8，這樣會解決問題嗎？這樣客戶端就能顯示中文了嗎？客戶端就能輸入中文了嗎？現在客戶端是英文的，它的字符集里根本就沒有漢字的編碼，我們簡單的修改一下客戶端的字符集又有什麼用？前面已經討論了，這個設定無非就是告訴oracle我將以什麼樣的字符集與資料庫進行資料交換，對於解決亂碼問題毫無關係。
正確的做法是將客戶端的作業系統改成支援中文字元，並將客戶端字符集改成和作業系統一致的字符集，這樣才能真正的解決問題。

[@more@]