Oracle資料庫字元集問題解析

Oracle資料庫字元集問題解析

經常看到一些朋友問ORACLE字元集方面的問題，我想以迭代的方式來介紹一下。

第一次迭代：掌握字元集方面的基本概念。
有些朋友可能會認為這是多此一舉，但實際上正是由於對相關基本概念把握不清，才導致了諸多問題和疑問。
首先是字元集的概念。
我們知道，電子電腦最初是用來進行科學計算的（所以叫做“電腦”），但隨著技術的發展，還需要電腦進行其它方面的應用處理。這就要求電腦不僅能處理數值，還能處理諸如文字、特殊符號等其它資訊，而電腦本身能直接處理的只有數值資訊，所以就要求對這些文字、符號資訊進行數值編碼，最初的字元集是我們都非常熟悉的ASCII，它是用7個二進位位來表示128個字元，而後來隨著不同國家、組織的需要，出現了許許多多的字元集，如表示西歐字元的ISO8859系列的字元集，表示漢字的GB2312-80、GBK等字元集。
字元集的實質就是對一組特定的符號，分別賦予不同的數值編碼，以便於電腦的處理。
字元集之間的轉換。字元集多了，就會帶來一個問題，比如一個字元，在某一字元集中被編碼為一個數值，而在另一個字元集中被編碼為另一個數值，比如我來創造兩個字元集demo_charset1與demo_charset2，在demo_charset1中，我規定了三個符號的編碼為：A（0001），B（0010），？（1111）；而在demo_charset2中，我也規定了三個符號的編碼為：A（1001），C（1011），？（1111），這時我接到一個任務，要編寫一個程式，負責在demo_charset1與demo_charset2之間進行轉換。由於知道兩個字元集的編碼規則，對於demo_charset1中的0001，在轉換為demo_charset2時，要將其編碼改為1001；對於demo_charset1中的1111，轉換為demo_charset2時，其數值不變；而對於demo_charset1中的0010，其對應的字元為B，但在demo_charset2沒有對應的字元，所以從理論上無法轉換，對於所有這類無法轉換的情況，我們可以將它們統一轉換為目標字元集中的一個特殊字元（稱為“替換字元”），比如在這裡我們可以將？作為替換字元，所以B就轉換為了？，出現了資訊的丟失；同樣道理，將demo_charset2的C字元轉換到demo_charset1時，也會出現資訊丟失。
所以說，在字元集轉換過程中，如果源字元集中的某個字元在目標字元集中沒有定義，將會出現資訊丟失。
資料庫字元集的選擇。
我們在建立資料庫時，需要考慮的一個問題就是選擇什麼字元集與國家字元集（通過create database中的CHARACTER SET與NATIONAL CHARACTER SET子句指定）。考慮這個問題，我們必須要清楚資料庫中都需要儲存什麼資料，如果只需要儲存英文資訊，那麼選擇US7ASCII作為字元集就可以；但是如果要儲存中文，那麼我們就需要選擇能夠支援中文的字元集（如ZHS16GBK）；如果需要儲存多國語言文字，那就要選擇UTF8了。
資料庫字元集的確定，實際上說明這個資料庫所能處理的字元的集合及其編碼方式，由於字元集選定後再變更會有諸多的限制，所以在資料庫建立時一定要考慮清楚後再選擇。
而我們許多朋友在建立資料庫時，不考慮清楚，往往選擇一個預設的字元集，如WE8ISO8859P1或US7ASCII，而這兩個字元集都沒有漢字編碼，所以用這種字元集儲存漢字資訊從原則上說就是錯誤的。雖然在有些時候選用這種字元集好象也能正常使用，但它會給資料庫的使用與維護帶來一系列的麻煩，在後面的迭代過程中我們將深入分析。
用戶端的字元集。
有過一些Oracle使用經驗的朋友，大多會知道通過NLS_LANG來設定用戶端的情況，NLS_LANG由以下部分組成：NLS_LANG=<Language>_<Territory>.<Clients Characterset>，其中第三部分<Clients Characterset>的本意就是用來指明用戶端作業系統預設使用的字元集。所以按正規的用法，NLS_LANG應該按照用戶端機器的實際情況進行配置，尤其對於字元集一項更是如此，這樣Oracle就能夠在最大程度上實現資料庫字元集與用戶端字元集的自動轉換（當然是如果需要轉換的話）。
總結一下第一次迭代的重點：
字元集：將特定的符號集編碼為電腦能夠處理的數值；
字元集間的轉換：對於在源字元集與目標字元集都存在的符號，理論上轉換將不會產生資訊丟失；而對於在源字元集中存在而在目標字元集中不存在的符號，理論上轉換將會產生資訊丟失；
資料庫字元集：選擇能夠包含所有將要儲存的資訊符號的字元集；
用戶端字元集設定：指明用戶端作業系統預設使用的字元集。

第二次迭代：通過執行個體加深對基本概念的理解

下面我將引用網友tellin在ITPUB上發表的“CHARACTER SET研究及疑問”文章，該朋友在文章中列舉了他做的相關實驗，並對實驗結果提出了一些疑問，我將對他的實驗結果進行分析，並回答他的疑問。
實驗結果分析一

QUOTE:
--------------------------------------------------------------------------------
最初由 tellin 發布
設定用戶端字元集為US7ASCII
D:/>SET NLS_LANG=AMERICAN_AMERICA.US7ASCII
查看伺服器字元集為US7ASCII
SQL> SELECT * FROM NLS_DATABASE_PARAMETERS;
PARAMETER                      VALUE
------------------------------ ----------------------------------------
NLS_CHARACTERSET               US7ASCII

    建立測試表
SQL> CREATE TABLE TEST (R1 VARCHAR2(10));

Table created.

    插入資料
SQL> INSERT INTO TEST VALUES('東北');

1 row created.

SQL> SELECT * FROM TEST;

R1
----------
東北

SQL> EXIT

--------------------------------------------------------------------------------

這一部分的實驗資料的存取與顯示都正確，好象沒什麼問題，但實際上卻隱藏著很大的隱患。
首先，要將漢字存入資料庫，而將資料庫字元集設定為US7ASCII是不合適的。US7ASCII字元集只定義了128個符號，並不支援漢字。另外，由於在SQL*PLUS中能夠輸入中文，作業系統預設應該是支援中文的，但在NLS_LANG中的字元集設定為US7ASCII，顯然也是不正確的，它沒有反映用戶端的實際情況。
但實際顯示卻是正確的，這主要是因為Oracle檢查資料庫與用戶端的字元集設定是同樣的，那麼資料在客戶與資料庫之間的存取過程中將不發生任何轉換。具體地說，在用戶端輸入“東北”，“東”的漢字的編碼為182（10110110）、171（10101011），“北”漢字的編碼為177（10110001）、177（10110001），它們將不做任何變化的存入資料庫中，但是這實際上導致了資料庫標識的字元集與實際存入的內容是不相符的，從某種意義上講，這也是一種不一致性，也是一種錯誤。而在SELECT的過程中，Oracle同樣檢查探索資料庫與用戶端的字元集設定是相同的，所以它也將存入的內容原封不動地傳送到用戶端，而用戶端作業系統識別出這是漢字編碼所以能夠正確顯示。
在這個例子中，資料庫與用戶端的設定都有問題，但卻好象起到了“負負得正”的效果，從應用的角度看倒好象沒問題。但這裡面卻存在著極大的隱患，比如在應用length或substr等字串函數時，就可能得到意外的結果。另外，如果遇到匯入/匯出（import /export）將會遇到更大的麻煩。有些朋友在這方面做了大量的測試，如eygle研究了“來源資料庫字元集為US7ASCII，匯出檔案字元集為US7ASCII或ZHS16GBK，目標資料庫字元集為ZHS16GBK”的情況，他得出的結論是 “如果的是在Oracle92中，我們發現對於這種情況，不論怎樣處理，這個匯出檔案都無法正確匯入到Oracle9i資料庫中”、“對於這種情況，我們可以通過使用Oracle8i的匯出工具，設定匯出字元集為US7ASCII，匯出後修改第二、三字元，修改 0001 為0354,這樣就可以將US7ASCII字元集的資料正確匯入到ZHS16GBK的資料庫中”。我想對於這些結論，這樣理解可能更合適一些：由於ZHS16GBK字元集是US7ASCII的超級，所以如果按正常操作，這種轉換應該沒有問題；但出現問題的本質是我們讓本應只儲存英文字元的US7ASCII資料庫，非常規地儲存了中文資訊，那麼在轉化過程中出現錯誤或麻煩就沒什麼奇怪的了，不出麻煩倒是有些奇怪了。
所以說要避免這種情況，就是要在建立資料庫時選擇合適的字元集，不讓標籤（資料庫的字元集設定）與實際（資料庫中實際儲存的資訊）不符的情況發生。
實驗結果分析二

 

 

QUOTE:
--------------------------------------------------------------------------------
[ 更改用戶端字元集為ZHS16GBK
D:/>SET NLS_LANG=AMERICAN_AMERICA.ZHS16GBK

D:/>SQLPLUS "/ AS SYSDBA"

無法正常顯示資料

SQL> SELECT * FROM TEST;

R1
--------------------
6+11

疑問1：ZHS16GBK為US7ASCII的超集，為什麼在ZHS16GBK環境下無法正常顯示
--------------------------------------------------------------------------------

這主要是因為Oracle檢查探索資料庫設定的字元集與用戶端配置字元集不同，它將對資料進行字元集的轉換。資料庫中實際存放的資料為182（10110110）、171（10101011）、177（10110001）、177（10110001），由於資料庫字元集設定為US7ASCII，它是一個7bit的字元集，儲存在8bit的位元組中，則Oracle忽略各位元組的最高bit，則182（10110110）就變成了54（0110110），在ZHS16GBK中代表數字記號“6”（當然在其它字元集中也是“6”），同樣過程也發生在其它3個位元組，這樣“東北”就變成了“6+11”。
實驗結果分析三

 

 

QUOTE:
--------------------------------------------------------------------------------
最初由 tellin 發布
用ZHS16GBK插入資料
SQL> INSERT INTO TEST VALUES('東北');

1 row created.

SQL> SELECT * FROM TEST;

R1
--------------------
6+11
??

SQL> EXIT
--------------------------------------------------------------------------------

當用戶端字元集設定為ZHS16GBK後向資料庫插入“東北”，Oracle檢查探索資料庫設定的字元集為US7ASCII與用戶端不一致，需要進行轉換，但字元集ZHS16GBK中的“東北”兩字在US7ASCII中沒有對應的字元，所以Oracle用統一的“替換字元”插入資料庫，在這裡為“？”，編碼為63（00111111），這時，輸入的資訊實際上已經丟失，不管字元集設定如何改變（如下面引用的實驗結果），第二行SELECT出來的結果也都是兩個“？”號（注意是2個，而不是4個）。

QUOTE:
--------------------------------------------------------------------------------
 更改用戶端字元集為US7ASCII
D:/>SET NLS_LANG=AMERICAN_AMERICA.US7ASCII

D:/>SQLPLUS "/ AS SYSDBA"

無法顯示用ZHS16GBK插入的字元集，但可以顯示用US7ASCII插入的字元集
SQL> SELECT * FROM TEST;

R1
----------
東北
??

更改伺服器字元集為ZHS16GBK
SQL> update props$ set value$='ZHS16GBK' WHERE NAME='NLS_CHARACTERSET';

1 row updated.

SQL> COMMIT;

更改用戶端字元集為ZHS16GBK
D:/>SET NLS_LANG=AMERICAN_AMERICA.ZHS16GBK

D:/>SQLPLUS "/ AS SYSDBA"

可以顯示以前US7ASCII的字元集，但無法顯示用ZHS16GBK插入的資料，說明用ZHS16GBK插入的資料為亂碼。

SQL> SELECT * FROM TEST;

R1
--------------------
東北
??
--------------------------------------------------------------------------------

需要指出的是，通過“update props$ set value$='ZHS16GBK' WHERE NAME='NLS_CHARACTERSET';”來修改資料庫字元集是非常規作法，很可能引起問題，在這裡只是原文引用網友的實驗結果。

實驗結果分析四

 

 

QUOTE:
--------------------------------------------------------------------------------
SQL> INSERT INTO TEST VALUES('東北');

1 row created.

SQL> SELECT * FROM TEST;

R1
--------------------
東北
??
東北

SQL> EXIT
--------------------------------------------------------------------------------

由於此時資料庫與用戶端的字元集設定均為ZHS16GBK，所以不會發生字元集的轉換，第一行與第三行資料顯示正確，而第二行由於儲存的資料就是63（00111111），所以顯示的是“？”號。

QUOTE:
--------------------------------------------------------------------------------
 更改用戶端字元集為US7ASCII

D:/>SET NLS_LANG=AMERICAN_AMERICA.US7ASCII

D:/>SQLPLUS "/ AS SYSDBA"

無法顯示資料

SQL> SELECT * FROM TEST;

R1
----------
??
??
??

疑問2：第一行資料是用US7ASCII環境插入的，為何無法正常顯示？
--------------------------------------------------------------------------------

將用戶端字元集設定改為US7ASCII後進行SELECT，Oracle檢查探索資料庫設定的字元集為ZHS16GBK，資料需要進行字元集轉換，而第一行與第三行的漢字“東”與“北”在用戶端字元集US7ASCII中沒有對應字元，所以轉換為“替換字元”（“？”），而第二行資料在資料庫中存的本來就是兩個“？”號，所以雖然在用戶端顯示的三行都是兩個“？”號，但在資料庫中儲存的內容卻是不同的。

實驗結果分析五

 

 

QUOTE:
--------------------------------------------------------------------------------
SQL> INSERT INTO TEST VALUES('東北');

1 row created.

SQL> EXIT
更改用戶端字元集為ZHS16GBK
D:/>SET NLS_LANG=AMERICAN_AMERICA.ZHS16GBK

D:/>SQLPLUS "/ AS SYSDBA"

無法顯示用US7ASCII插入的字元集，但可以顯示用ZHS16GBK插入的字元集
SQL> SELECT * FROM TEST;

R1
--------------------
東北
??
東北
6+11

SQL>
疑問3：US7ASCII為ZHS16GBK的子集，為何在US7ASCII環境下插入的資料無法顯示？
--------------------------------------------------------------------------------

在用戶端字元集設定為US7ASCII時，向字元集為ZHS16GBK的資料庫中插入“東北”，需要進行字元轉換，“東北”的ZHS16GBK編碼為182（10110110）、171（10101011）與177（10110001）、177（10110001），由於US7ASCII為7bit編碼，Oracle將這兩個漢字當作四個字元，並忽略各位元組的最高位，從而存入資料庫的編碼就變成了54（00110110）、43（00101011）與49（00110001）、49（00110001），也就是“6+11”，原始資訊被改變了。這時，將用戶端字元集設定為ZHS16GBK再進行SELECT，資料庫中的資訊不需要改變傳到用戶端，第一、三行由於存入的資訊沒有改變能顯示“東北”，而第二、四行由於插入資料時資訊改變，所以不能顯示原有資訊了。
分析了這麼多的內容，但實際上總結起來也很簡單

分析了這麼多的內容，但實際上總結起來也很簡單，要想在字元集方面少些錯誤與麻煩，需要堅持兩條基本原則：
在資料庫端：選擇需要的字元集（通過create database中的CHARACTER SET與NATIONAL CHARACTER SET子句指定）；
在用戶端：設定作業系統實際使用的字元集（通過環境變數NLS_LANG設定）。