理解Oracle字元集

什麼是Oracle字元集

Oracle字元集是一個位元組資料解釋的符號集合，有大小之分，有相互的包容關係。

Oracle支援國家語言的體繫結構允許你使用本地化語言來儲存，處理，檢索資料。它使資料庫工具，錯誤訊息，排序次序，日期，時間，貨幣，數字和日曆自動適應本地化語言和平台。

影響oracle資料庫字元集最重要的參數是NLS_LANG參數。它的格式如下：

NLS_LANG = language_territory.charset

它有三個組成部分（語言、地區和字元集），每個成分控制了NLS子集的特性。其中：

Language指定伺服器訊息的語言，territory指定伺服器的日期和數字格式，charset指定字元集。如：AMERICAN _ AMERICA. ZHS16GBK。

從NLS_LANG的組成我們可以看出，真正影響資料庫字元集的其實是第三部分。所以兩個資料庫之間的字元集只要第三部分一樣就可以相互匯入匯出資料，前面影響的只是提示資訊是中文還是英文。

資料庫的字元集

字元集在建立資料庫時指定，在建立後通常不能更改，所以在建立資料庫時能否選擇一個正確的字元集就顯得尤為重要。

在建立資料庫時，我們可以指定字元集(CHARACTER SET)和國家字元集(NATIONAL CHARACTER SET)。

字元集用來儲存:

CHAR, VARCHAR2, CLOB, LONG等類型資料

用來標示諸如表名、列名以及PL/SQL變數等

SQL和PL/SQL程式單元等

國家字元集用以儲存:

NCHAR, NVARCHAR2, NCLOB等類型資料

一旦你的字元集選定了，資料庫中能夠儲存的字元就受到了限制，所以你選擇的字元集的應該可以容納所有你將用到字元。字元集不同，二進位碼的組合就不同。比如有一串二進位資訊：1101,0110,1101,0000,1011,1001,1111,1010，按照16位雙位元組GBK字元集理解，可以代表“中國”兩個字。如果單位元組的字元集，這一串二進位代表ASC碼為214、208、185、250的四個怪字元。這就是字元集的作用，就是以什麼樣的形式理解資訊。

如何查詢Oracle的字元集

很多人都碰到過因為字元集不同而使資料匯入失敗的情況。這涉及三方面的字元集，一是Oracel server端的字元集，二是oracle client端的字元集；三是dmp檔案的字元集。在做資料匯入的時候，需要這三個字元集都一致才能正確匯入。

1、查詢Oracle Server端的字元集

有很多種方法可以查出oracle server端的字元集，比較直觀的查詢方法是以下這種：

SQL>select userenv(‘language’) from dual;

結果類似如下：AMERICAN _ AMERICA. ZHS16GBK.

2、如何查詢dmp檔案的字元集

用Oracle的exp工具匯出的dmp檔案也包含了字元集資訊，dmp檔案的第2和第3個位元組記錄了dmp檔案的字元集。如果dmp檔案不大，比如只有幾M或幾十M，可以用UltraEdit開啟（16進位方式），看第2第3個位元組的內容，如0354，然後用以下SQL查出它對應的字元集：

SQL> select nls_charset_name(to_number('0354','xxxx')) from dual;  ZHS16GBK

如果dmp檔案很大，比如有2G以上（這也是最常見的情況），用文字編輯器開啟很慢或者完全打不開，可以用以下命令（在unix主機上）：

cat exp.dmp |od -x|head -1|awk '{print $2 $3}'|cut -c 3-6

然後用上述SQL也可以得到它對應的字元集。

3、查詢Oracle client端的字元集

這個比較簡單。在Windows平台下，就是註冊表裡面相應OracleHome的NLS_LANG.還可以在Dos視窗裡面自己設定，比如：

set nls_lang=AMERICAN_AMERICA.ZHS16GBK

這樣就隻影響這個視窗裡面的環境變數。在Unix平台下，就是環境變數NLS_LANG.

$echo $NLS_LANG AMERICAN_AMERICA.ZHS16GBK

用戶端的字元集要求與伺服器一致，才能正確顯示資料庫的非Ascii字元。如果多個設定存在的時候，alter session>環境變數>註冊表>參數檔案

字元集要求一致，但是語言設定卻可以不同，語言設定建議用英文。如字元集是zhs16gbk，則nls_lang可以是American_America.zhs16gbk。

 

1.1、資料庫需要儲存的資料類型是字元集選擇的首要考慮目標。
    對於只儲存英文資訊的資料庫等來說，一般採用US7ASCII或WE8ISO8859P1等單位元組的字元集就比較合適，在效能和空間上也是最優，

    同樣，儲存了中文資訊的資料庫，如果採用單位元組的字元集，也是不合適的。在這種情況下，資料庫的字元集雖然是US7ASCII或WE8ISO8859P1編碼，但裡面儲存的資料編碼實際上卻是另外的編碼格式，這種不一致的情況很容易引起問題，建議不要這樣使用。ORACLE提供了很多種類的字元集供客戶選擇，就是要滿足各種文字不同的編碼需要。

1.2、字元集的選擇需要優先考慮應用程式的需要。
    目前出於國際化的需要，軟體需要可以對不同的語言文字進行處理，尤其一個系統中需要容納多種語言文字的時候，一般都會採用Unicode這樣的通用解決方案，即使會有一些空間和運行效率的損失也是值得的。此時資料庫字元集建議可以採用AL32UTF8或UTF8編碼。

客戶在應用程式中輸入資料，此時資料的編碼格式是由客戶作業系統的地區及語言設定決定的，如在簡體中文XP的環境下，輸入的中文編碼屬於GBK編碼。在客戶輸入結束後，程式首先判斷客戶的本地環境，並把編碼轉換成UNICODE，並通過NET傳送到伺服器端。由於用戶端與伺服器資料庫的字元集均為UTF8格式，ORACLE在傳送過程中不會進行字元轉換，直接把資料按UTF8格式儲存到資料庫中。查詢時是一個反向的過程，應用程式從資料庫中取出UTF8編碼的資料，再由應用程式根據客戶的本地環境，把UTF8編碼的資料轉換成客戶本地的編碼格式，最後把結果資料顯示給客戶。此方案的關鍵在於應用程式要能很好的支援UNICODE編碼，編碼的轉換由應用程式來負責，資料庫只是提供了一個資料存放區功能。
    對於部分程式來說，由於對UNICODE支援不夠，沒有提供編碼的轉換功能，則可以使用ORACLE提供的字元集轉換功能來實現同樣的目的。客戶在應用程式中輸入資料，此時資料的編碼格式是由客戶作業系統的地區及語言設定決定的，如在簡體中文XP的環境下，輸入的中文編碼屬於GBK編碼。在客戶輸入結束後，程式直接把資料並通過NET傳送到伺服器端。由於用戶端與伺服器資料庫的字元集不一致，因此ORACLE會把用戶端的編碼轉換成UTF8格式，再把資料按UTF8格式儲存到資料庫中。這種方案的優點就是程式可以不用支援UNICODE，由ORACLE資料庫自動進行轉換。由於資料庫的字元集為UTF8，是其它字元集的超集，因此在轉換過程中不會發生資料丟失的情況。對於英文的字元符號，在UTF8中使用單位元組儲存，轉換的工作量很小，可以忽略，而對於一些亞洲字元集，在UTF8中一般需要兩到三個位元組儲存，需要的資料庫空間增加，而且轉換的工作量也相對大一些，效能會有一些損失。

備忘：

對於Windows的簡體中文平台，預設的字元集是ZHS16GBK

英文平台：預設的字元集是WE8ISO8859P1  西歐語言

支援多語言平台：AL32UTF8

 

二． 字元集的更改

資料庫建立以後，如果需要修改字元集，通常需要重建資料庫，通過匯入匯出的方式來轉換。

我們也可以通過以下方式更改 轉換字元集，資料庫應該在RESTRICTED模式下進行.

通過修改props$的方式更改字元集在Oracle7之後是一種極其危險的方式，應該盡量避免。

我們又知道，通過ALTER DATABASE CHARACTER SET更改字元集雖然安全可靠，但是有嚴格的子集和超集的約束，實際上我們很少能夠用到這種方法。實際上Oracle還存在另外一種更改字元集的方式.

 

SQL> shutdown immediate

SQL> startup mount

SQL> alter system enable restricted session;

SQL> alter system set job_queue_processes=0;

SQL> alter system set aq_tm_processes=0;

SQL> alter database open;

SQL> alter session set events '10046 trace name context forever,level 12';

SQL> alter database character set INTERNAL_USE ZHS16GBK

和之前ALTER DATABASE CHARACTER SET操作的內部過程是完全相同的，也就是說INTERNAL_USE提供的協助就是使Oracle資料庫繞過了子集與超集的校正.

這一方法在某些方面是有用處的，比如測試；應用於產品環境大家應該格外小心，最好複製一份資料庫出來使用，要不除了你以外，沒有人會為此帶來的後果負責:

 

對於DBA來說，有一個很重要的原則就是:不要把你的資料庫置於危險的境地！

這就要求我們，在進行任何可能對資料庫結構發生改變的操作之前，先做有效備份，很多DBA沒有備份的操作中得到了慘痛的教訓。

 

三、匯出匯入與轉換

匯入匯出是我們常用的一個資料移轉及轉化工具，因其匯出檔案具有平台無關性，所以在跨平台遷移中，最為常用。

在匯出操作時，非常重要的是用戶端的字元集設定，也就是用戶端的NLS_LANG設定。

NLS_LANG參數由以下部分組成:

NLS_LANG=<Language>_<Territory>.<Clients Characterset>

language：指定oracle訊息使用的語言，日期中日和月的顯示。

Territory：指定貨幣和數位格式，地區和計算星期及日期的習慣。

Characterset：控制用戶端應用程式使用的字元集。通常設定或者等於用戶端(如Windows)字碼頁，或者對於unicode應用設定為UTF8，在Windows上查看當前系統的字碼頁可以使用chcp命令

提示：NLS_LANG的定義的所有組件都是可選的，任何不指定項目使用其預設值。如果指定領土或字元集，則必須包括上述分隔[底線（_的領土），句號（.）的字元集]。否則，該值分析作為一種語言的名稱。

例如，只設定NLS_LANG的領土的一部分，使用以下格式：NLS_LANG的= _JAPAN

查看用戶端NLS_LANG設定可以使用以下方法:

Windows上查看：在[HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_OraDb10g_home1] NLS_LANG索引值

臨時會話中設定：set %NLS_LANG%=SIMPLIFIED CHINESE_CHINA.ZHS16GBK

Linux上查看：echo $NLS_LANG

臨時會話中設定：在命令視窗 export NLS_LANG=SIMPLIFIED CHINESE_CHINA.ZHS16GBK

 

四、修改匯出的檔案字元集

我們知道在匯出檔案中，記錄著匯出使用的字元集id,通過查看匯出檔案頭的第2、3個位元組，我們可以找到16進位表示的字元集ID,在Windows上，我們可以使用UltraEdit等工具開啟dmp檔案，查看其匯出字元集::修改dmp檔案，如果dmp檔案太大，如大於500M，可使用anysql的工具更改dmp檔案中的字元集 — dmp2utf8，連結在此http://www.anysql.net/tools/new_tool_dmp2utf8.html

 

在Unix上我們可以通過以下命令來查看:

cat expdat.dmp | od -x | head

 

Oracle提供標準函數，對字元集名稱及ID進行轉換:

SQL> select nls_charset_id('ZHS16GBK') from dual;

NLS_CHARSET_ID('ZHS16GBK')

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

                       852

 

SQL> select nls_charset_name(852) from dual;

NLS_CHAR

--------

ZHS16GBK

 

SQL> select to_char(nls_charset_id('ZHS16GBK'), 'xxxx') from dual;  --十進位轉換十六進位:

TO_CHAR('8

----------

  0354

 

對應上面的圖中第2、3位元組，我們知道該匯出檔案字元集為ZHS16GBk.

 

五、與字元集相關的問題分析
5.1、在UTF8環境下運行SQL語句報錯的問題：
    SQL*PLUS工具不提供編碼自動轉換的功能，當資料庫字元集為UTF8，用戶端的NLS_LANG如果也是UTF8，那麼在SQL*PLUS中運行SQL語句時，語句全是英文，不會出現問題，如果語句包含了中文或其它一些特殊字元，SQL語句運行時就會報錯。對於返回的含中文的結果，SQL*PLUS也會顯示亂碼。造成此錯誤的原因在於當SQL語句中包含漢字等一些特殊字元時，由於這些字元的編碼屬於GBK，ORACLE沒有進行字元轉換，而是直接把SQL語句送到伺服器上進行解析。此時伺服器的字元集是UTF8，因此它按UTF8編碼格式對SQL語句中GBK編碼的字元解析時就會產生錯誤。如果把用戶端的NLS_LANG設定為本地環境的字元集，如ZHS16GBK，此時可以直接在SQL*PLUS中輸入包含中文的SQL語句，ORACLE在把SQL語句提交到伺服器時會自動轉換成UTF8編碼格式，因此SQL語句可以正常運行。對於英文字母，由於它在UTF8中的編碼數值採用的還是ASCII的編碼數值，因此英文字母可以直接使用而不需要轉換，這就是如果SQL語句或輸出結果全是英文時不會出現錯誤的原因。正確的做法是先把需要啟動並執行SQL做成指令檔，用代碼轉換工具把它轉換成UTF8編碼格式的檔案，（注意！XP中的記事本是提供了代碼轉換功能的，可以在儲存檔案或選擇檔案另存新檔的時候，彈出的對話方塊最後一項，編碼，選擇UTF8，再儲存，即可把檔案轉換成UTF8編碼格式）。完成後用IE開啟這個指令碼，選擇編碼－》UTF8，觀察此時SQL指令碼是否含有亂碼或“？”符號。如果沒有，說明編碼格式已經是UTF8了，此時在SQL*PLUS中運行這個指令碼就不會產生錯誤了。運行結束後，輸出的結果中如果包含中文，需要把結果SPOOL輸出到一個檔案中，然後用代碼轉換工具把這個結果檔案由UTF8轉換成本地編碼格式，再用寫字板開啟，才能看到正常顯示的漢字。由於IE具有代碼轉換功能，因此也可以不用代碼轉換工具，直接在IE中開啟輸出的結果檔案，選擇UTF8編碼，也能正常顯示含中文的結果檔案。

.2、資料庫出現亂碼的問題：
    資料庫出現亂碼的問題主要和客戶的本地化環境，用戶端NLS_LANG設定，伺服器端的資料庫字元集設定這三者有關，如果它們的設定不一致或者某個設定錯誤，就會很容易出現亂碼，下面我們簡要介紹以下幾種情況：
5.2.1、資料庫字元集設定不當引起的亂碼：
    例如：一個儲存簡體中文字元的資料庫，它的字元集選用了US7ASCII，當它的用戶端NLS_LANG也選用US7ASCII時，這個系統單獨使用是沒有問題的，因為兩者設定一致，因此ORACLE不會進行字元集的轉換，客戶輸入的GBK碼被直接在資料庫中儲存起來，當查詢資料時，實際用戶端取出來的資料也是GBK的編碼，因此顯示也是正常的。但當其它的系統需要從這個資料庫取資料，或者它的資料要EXP出來，IMP到其它資料庫時，問題就會開始出現了。其它系統的字元集一般是ZHS16GBK，或者其它系統用戶端的NLS_LANG設定為ZHS16GBK，此時必然會產生字元集的轉換。雖然資料庫字元集設定為US7ASCII，但我們知道，實際儲存的資料編碼是ZHS16GBK的。可惜ORACLE不會知道，它會把儲存的ZHS16GBK編碼資料當作US7ASCII編碼的資料，按照US7ASCII轉換成ZHS16GBK的轉換演算法進行轉換，可以想象，這種情況下，亂碼的產生是必然的。

4.2.2、資料庫字元集與用戶端NLS_LANG設定不同引起的亂碼：
    例如：對於一個需要儲存簡體文資訊的資料庫來說，它的字元集設定和用戶端NLS_LANG設定一般可以使用ZHS16GBK編碼。但是如果資料庫字元集選用了UTF8的話，也是可以的，因為ZHS16GBK編碼屬於UTF8的子集。ORACLE在資料庫與用戶端進行資料交換時自動進行編碼的轉換，在資料庫中實際儲存的也是UTF8編碼的資料。此時其它資料庫和此資料庫也可以正常的進行資料交換，因為ORACLE會自動進行資料的轉換。在實際使用中，遇到過繁體XP的字元集ZHT16MSWIN950轉換成AL32UTF8字元集時，一些特殊的字元和個別冷僻的漢字會變成亂碼。後來證實是XP需要安裝一個字型檔補丁軟體，最後順利解決此問題。

4.2.3、用戶端NLS_LANG與本地化環境不同引起的亂碼：
    一般情況下，用戶端NLS_LANG與本地化環境採用了不同的字元集會出現亂碼，除非本地化環境的字元集是用戶端NLS_LANG設定字元集的子集。如果把用戶端NLS_LANG設定為UTF8就屬於這種情況，由於目前還沒有可以直接使用UNICODE字元集的作業系統，因此客戶本地化環境使用的字元集只能是某種語言支援的字元集，它屬於UTF8的子集。下面我們就著重討論這種情況。
    雖然目前WINDOWS的核心是支援UNICODE的，但是WINDOWS並不支援直接顯示UNICODE編碼的字元,而且它並不知道目前的字元採用了何種字元集，所以預設情況下，它使用預設的字碼頁來解釋字元。因此，對於其它類型的編碼，需要先進行轉換，變成系統目前的預設字碼頁支援的字元集才能正常使用。
    WINDOWS中的預設字碼頁是由控制台設定中的語言及地區的選擇所決定的，屬於客戶本地化的環境設定。簡體中文WINDOWS的字元編碼就是GBK，它的預設字碼頁是936。對於其它非WINDOWS的作業系統，我們可以把它們目前預設使用的字元集作為使用者的本地化環境設定。另外，我們使用的大部分工具，如寫字板，SQL*PLUS等，它們沒有提供編碼轉換功能，因此在用戶端直接輸入或查詢資料往往都會遇到亂碼的問題，必須由應用程式或一些工具去做編碼的轉換，才能保證正常的使用。

 

五、實驗操作：只為理解字元集Characterset ，關於亂碼。

環境：

用戶端Windows中文簡體系統，Oracle10G，用戶端字元集為：

伺服器與客戶機為同一PC機，資料庫字元集為：AL32UTF8 國家字元集為：UTF8

說明：關於用戶端與服務端字元集查看請看上面的文檔。

 

參考資料：

http://www.oracle.com/technology/tech/globalization/htdocs/nls_lang%20faq.htm#_Toc110410543 nls_lang faq

http://www.eygle.com/special/ 尋找字元集部分

http://www.itpub.net/viewthread.php?tid=838447&highlight=%D7%D6%B7%FB%BC%AF  搞懂oracle字元集