【轉】ORACLE字元集研究

文章轉自：網際網路

ORACLE字元集研究

對字元集的認識一直都處于一知半解的狀态，目前打算要做不同字元集的庫之間資料傳導，是以專門研究了一下資料庫的字元集問題。轉了一篇很詳細的論文來，論文就是不一樣，講得非常細緻全面，是很好的字元集入門材料。下面是正文：

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

了解ORACLE資料庫字元集

    作者：耿立宏

    中國科學院計算機網絡資訊中心，北京100080

一、引言

    ORACLE資料庫字元集，即Oracle全球化支援(Globalization Support)，或即國家語言支援(NLS)其作用是用本國語言和格式來存儲、處理和檢索資料。利用全球化支援，ORACLE為使用者提供自己熟悉的資料庫母語環境，諸如日期格式、數字格式和存儲序列等。Oracle可以支援多種語言及字元集，其中oracle8i支援48種語言、76個國家地域、229種字元集，而oracle9i則支援57種語言、88個國家地域、235種字元集。由于oracle字元集種類多，且在存儲、檢索、遷移oracle資料時多個環節與字元集的設定密切相關，是以在實際的應用中，資料庫開發和管理人員經常會遇到有關oracle字元集方面的問題。本文通過以下幾個方面闡述，對oracle字元集做簡要分析

二、字元集基本知識

2.1字元集

    實質就是按照一定的字元編碼方案，對一組特定的符号，分别賦予不同數值編碼的集合。Oracle資料庫最早支援的編碼方案是US7ASCII。

    Oracle的字元集命名遵循以下命名規則:

    <Language><bit size><encoding>

    即:  <語言><比特位數><編碼>

    比如: ZHS16GBK表示采用GBK編碼格式、16位(兩個位元組)簡體中文字元集

2.2字元編碼方案

  2.2.1單位元組編碼

    (1)單位元組7位字元集，可以定義128個字元，最常用的字元集為US7ASCII

    (2)單位元組8位字元集，可以定義256個字元，适合于歐洲大部分國家

    例如：WE8ISO8859P1(西歐、8位、ISO标準8859P1編碼)

  2.2.2 多位元組編碼

    (1)變長多位元組編碼

    某些字元用一個位元組表示，其它字元用兩個或多個字元表示，變長多位元組編碼常用于對亞洲語言的支援，例如日語、漢語、印地語等

    例如：AL32UTF8(其中AL代表ALL,指适用于所有語言)、zhs16cgb231280

    (2)定長多位元組編碼

    每一個字元都使用固定長度位元組的編碼方案，目前oracle唯一支援的定長多位元組編碼是AF16UTF16，也是僅用于國家字元集

  2.2.3 unicode編碼

    Unicode是一個涵蓋了目前全世界使用的所有已知字元的單一編碼方案，也就是說Unicode為每一個字元提供唯一的編碼。UTF-16是unicode的16位編碼方式，是一種定長多位元組編碼，用2個位元組表示一個unicode字元，AF16UTF16是UTF-16編碼字元集。

    UTF-8是unicode的8位編碼方式，是一種變長多位元組編碼，這種編碼可以用1、2、3個位元組表示一個unicode字元，AL32UTF8，UTF8、UTFE是UTF-8編碼字元集

2.3 字元集超級

    當一種字元集(字元集A)的編碼數值包含所有另一種字元集(字元集B)的編碼數值，并且兩種字元集相同編碼數值代表相同的字元時，則字元集A是字元集B的超級，或稱字元集B是字元集A的子集。

    Oracle8i和oracle9i官方文檔資料中備有子集-超級對照表(subset-superset pairs)，例如：WE8ISO8859P1是WE8MSWIN1252的子集。由于US7ASCII是最早的Oracle資料庫編碼格式，是以有許多字元集是US7ASCII的超集，例如WE8ISO8859P1、ZHS16CGB231280、ZHS16GBK都是US7ASCII的超集。

2.4 資料庫字元集(oracle伺服器端字元集)

    資料庫字元集在建立資料庫時指定，在建立後通常不能更改。在建立資料庫時，可以指定字元集(CHARACTER SET)和國家字元集(NATIONAL CHARACTER SET)。

  2.4.1字元集

    (1)用來存儲CHAR, VARCHAR2, CLOB, LONG等類型資料

    (2)用來标示諸如表名、列名以及PL/SQL變量等

    (3)用來存儲SQL和PL/SQL程式單元等

  2.4.2國家字元集：

    (1)用以存儲NCHAR, NVARCHAR2, NCLOB等類型資料

    (2)國家字元集實質上是為oracle選擇的附加字元集，主要作用是為了增強oracle的字元處理能力，因為NCHAR資料類型可以提供對亞洲使用定長多位元組編碼的支援，而資料庫字元集則不能。國家字元集在oracle9i中進行了重新定義，隻能在unicode編碼中的AF16UTF16和UTF8中選擇，預設值是AF16UTF16

  2.4.3查詢字元集參數

    可以查詢以下資料字典或視圖檢視字元集設定情況

    nls_database_parameters、v$nls_parameters

    或者用：select userenv('language') from dual;

    查詢結果中NLS_CHARACTERSET表示字元集，NLS_NCHAR_CHARACTERSET表示國家字元集

  2.4.4修改資料庫字元集

    按照上文所說，資料庫字元集在建立後原則上不能更改。如果需要修改字元集，通常需要導出資料庫資料，重建資料庫，再導入資料庫資料的方式來轉換，或通過ALTER DATABASE CHARACTER SET語句修改字元集，但建立資料庫後修改字元集是有限制的，隻有新的字元集是目前字元集的超集時才能修改資料庫字元集，例如UTF8是US7ASCII的超集，修改資料庫字元集可使用ALTERDATABASE CHARACTER SET UTF8。

2.5 用戶端字元集(NLS_LANG參數)

  2.5.1用戶端字元集含義

    用戶端字元集定義了用戶端字元資料的編碼方式，任何發自或發往用戶端的字元資料均使用用戶端定義的字元集編碼,用戶端可以看作是能與資料庫直接連接配接的各種應用，例如sqlplus,exp/imp等。用戶端字元集是通過設定NLS_LANG參數來設定的。

  2.5.2 NLS_LANG參數格式

    NLS_LANG=<language>_<territory>.<client character set>

    Language:顯示oracle消息,校驗，日期命名

    Territory：指定預設日期、數字、貨币等格式

    Client character set：指定用戶端将使用的字元集

    例如：NLS_LANG=AMERICAN_AMERICA.US7ASCII 

    AMERICAN是語言，AMERICA是地區，US7ASCII是用戶端字元集

  2.5.3用戶端字元集設定方法

    1)UNIX環境

      $NLS_LANG="simplified chinese"_china.zhs16gbk

      $export NLS_LANG

      編輯oracle使用者的profile檔案

    2)Windows環境

      編輯系統資料庫

      Regedit.exe---HKEY_LOCAL_MACHINE---SOFTWARE---ORACLE—HOME0

  2.5.4 NLS參數查詢

    Oracle提供若幹NLS參數定制資料庫和使用者機以适應本地格式，例如有NLS_LANGUAGE,NLS_DATE_FORMAT,NLS_CALENDER等，可以通過查詢以下資料字典或v$視圖檢視。

    NLS_DATABASE_PARAMETERS--顯示資料庫目前NLS參數取值，包括資料庫字元集取值

    NLS_SESSION_PARAMETERS--顯示由NLS_LANG 設定的參數，或經過alter session 改變後的參數值(不包括由NLS_LANG 設定的用戶端字元集)

    NLS_INSTANCE_PARAMETE--顯示由參數檔案init<SID>.ora 定義的參數V$NLS_PARAMETERS--顯示資料庫目前NLS參數取值

  2.5.5修改NLS參數

    使用下列方法可以修改NLS參數

    (1)修改執行個體啟動時使用的初始化參數檔案

    (2)修改環境變量NLS_LANG

    (3)使用ALTER SESSION語句，在oracle會話中修改

    (4)使用某些SQL函數

    NLS作用優先級别：Sql function>alter session>環境變量或系統資料庫>參數檔案>資料庫預設參數

三、導入/導出與字元集轉換

3.1 EXP/IMP

    Export 和 Import 是一對讀寫Oracle資料的工具。Export 将 Oracle 資料庫中的資料輸出到作業系統檔案中, Import 把這些檔案中的資料讀到Oracle 資料庫中，由于使用exp/imp進行資料遷移時，資料從源資料庫到目标資料庫的過程中有四個環節涉及到字元集，如果這四個環節的字元集不一緻，将會發生字元集轉換。

EXP

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

     |exp導出檔案|<-|環境變量NLS_LANG|<-|源資料庫字元集|

IMP

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

     |imp導入檔案|->|環境變量NLS_LANG|->|目标資料庫字元集|

    四個字元集是：

   (1)源資料庫字元集

   (2)Export過程中使用者會話字元集(通過NLS_LANG設定)

   (3)Import過程中使用者會話字元集(通過NLS_LANG設定)

   (4)目标資料庫字元集

3.2導出的轉換過程

    在Export過程中，如果源資料庫字元集與Export使用者會話字元集不一緻，會發生字元集轉換，并在導出檔案的頭部幾個位元組中存儲Export使用者會話字元集的ID号。在這個轉換過程中可能發生資料的丢失。

    例：如果源資料庫使用ZHS16GBK，而Export使用者會話字元集使用US7ASCII，由于ZHS16GBK是16位字元集,而US7ASCII是7位字元集，這個轉換過程中，中文字元在US7ASCII中不能夠找到對等的字元，是以所有中文字元都會丢失而變成“??”形式，這樣轉換後生成的Dmp檔案已經發生了資料丢失。

    是以如果想正确導出源資料庫資料，則Export過程中使用者會話字元集應等于源資料庫字元集或是源資料庫字元集的超集

3.3導入的轉換過程

    (1) 确定導出資料庫字元集環境。通過讀取導出檔案頭，可以獲得導出檔案的字元集設定

    (2) 确定導入session的字元集。即導入Session使用的NLS_LANG環境變量

    (3) IMP讀取導出檔案。讀取導出檔案字元集ID，和導入程序的NLS_LANG進行比較

    (4) 如果導出檔案字元集和導入Session字元集相同，那麼在這一步驟内就不需要轉換，如果不同，就需要把資料轉換為導入Session使用的字元集。可以看出，導入資料到資料庫過程中發生兩次字元集轉換：

      第一次：導入檔案字元集與導入Session使用的字元集之間的轉換，如果這個轉換過程不能正确完成，Import向目标資料庫的導入過程也就不能完成。

      第二次：導入Session字元集與資料庫字元集之間的轉換。

      然而,oracle8i的這種轉換隻能在單位元組字元集之間進行,oracle8i導入Session不支援多位元組字元集之間的轉換，是以為了避免第一次轉換，導入Session使用的NLS_LANG與導出檔案字元集相同，第二次轉換（通過SQL*Net）支援任何兩種字元集。以上情況在Oracle9i中略有不同

四、亂碼問題

    oracle在資料存儲、遷移過程中經常發生字元亂碼問題，歸根到底是由于字元集使用不當引起。下面以使用用戶端sqlplus向資料庫插入資料和導入/導出(EXP/IMP)過程為例，說明亂碼産生的原因。

4.1使用用戶端sqlplus向資料庫存儲資料

    這個過程存在3個字元集設定

    (1) 用戶端應用字元集

    (2) 用戶端NLS_LANG參數設定

    (3) 伺服器端資料庫字元集(Character Set)設定

    用戶端應用sqlplus中能夠顯示什麼樣的字元取決于用戶端作業系統語言環境(用戶端應用字元集)，但在應用中錄入這些字元後，這些字元能否在資料庫中正常存儲，還與另外兩個字元集設定緊密相關，其中用戶端NLS_LANG參數主要用于字元資料傳輸過程中的轉換判斷。常見的亂碼大緻有兩種情形：

    (1) 漢字變成問号“?”；

        當從字元集A 轉換成字元集B時，如果轉換字元之間不存在對應關系，NLS_LANG使用替代字元“?”替代無法映射的字元

    (2)漢字變成未知字元(雖然有些是漢字，但與原字元含義不同)

        轉換存在對應關系，但字元集A 中的字元編碼與字元集B 中的字元編碼代表不同含義

4.2發生亂碼原因 

    亂碼産生是由于幾個字元集之間轉換不比對造成，分以下幾種情況：

    (注：字元集之間如果不存在子集、超集對應關系時的情況不予考慮，因為這種情況下字元集之間轉換必産生亂碼)  

    1) 伺服器端資料庫字元集與用戶端應用字元集相同，與用戶端NLS_LANG參數設定不同

    如果用戶端NLS_LANG字元集是其它兩種字元集的子集，轉換過程将出現亂碼。

    解決方法：将三種字元集設定成同一字元集，或NLS_LANG字元集是其它兩種字元集的超集

    2) 伺服器端資料庫字元集與用戶端NLS_LANG參數設定相同，與用戶端應用字元集不同

    如果用戶端應用字元集是其它兩種字元集的超集時，轉換過程将出現亂碼，但對于單位元組編碼存儲中文問題，可參看本文第5章節的分析

    3) 用戶端應用字元集、用戶端NLS_LANG參數設定、伺服器端資料庫字元集互不相同

    此種情況較為複雜，但三種字元集之間隻要有不能轉換的字元，則必産生亂碼

4.3導入/導出過程出現亂碼原因

    這個過程存在4個字元集設定，在3.1章節中已分析

   (1)源資料庫字元集

   (2)EXP過程中NLS_LANG參數

   (3)IMP過程中NLS_LANG參數

    出現亂碼原因:

    1) 當源資料庫字元集不等于EXP過程中NLS_LANG參數，且源資料庫字元集是EXP過程中NLS_LANG的子集，才能保證導出檔案正确，其他情況則導出檔案字元亂碼

    2) EXP過程中NLS_LANG字元集不等于IMP過程中NLS_LANG字元集，且EXP過程中NLS_LANG字元集是IMP過程中NLS_LANG字元集的子級, 才能保證第一次轉換正常，否則第一次轉換中出現亂碼。

    3) 如果第一次轉換正常，IMP過程中NLS_LANG字元集是目标資料庫字元集的子集或相同，才能保證第二次轉換正常，否則則第二次轉換中出現亂碼

五、單位元組編碼存儲中文問題

    由于曆史的原因，早期的oracle沒有中文字元集(如oracle6、oracle7、oracle7.1)，但有的使用者從那時起就使用資料庫了，并用US7ASCII字元集存儲了中文，或是有的使用者在建立資料庫時，不考慮清楚，随意選擇一個預設的字元集，如WE8ISO8859P1或US7ASCII，而這兩個字元集都沒有漢字編碼，雖然有些時候選用這種字元集好象也能正常使用，但用這種字元集存儲漢字資訊從原則上說就是錯誤的，它會給資料庫的使用與維護帶來一系列的麻煩。

    正常情況下，要将漢字存入資料庫，資料庫字元集必須支援中文，而将資料庫字元集設定為US7ASCII等單位元組字元集是不合适的。US7ASCII字元集隻定義了128個符号，并不支援漢字。另外，如果在SQL*PLUS中能夠輸入中文，作業系統預設應該是支援中文的，但如果在NLS_LANG中的字元集設定為US7ASCII，顯然也是不正确的，它沒有反映用戶端的實際情況。但在實際應用中漢字顯示卻是正确的，這主要是因為Oracle檢查資料庫與用戶端的字元集設定是同樣的，那麼資料在客戶與資料庫之間的存取過程中将不發生任何轉換，但是這實際上導緻了資料庫辨別的字元集與實際存入的内容是不相符的。而在SELECT的過程中，Oracle同樣檢查發現資料庫與用戶端的字元集設定是相同的，是以它也将存入的内容原封不動地傳送到用戶端，而用戶端作業系統識别出這是漢字編碼是以能夠正确顯示。

    在這個例子中，資料庫與用戶端都沒有設定成中文字元集，但卻能正常顯示中文，從應用的角度看好象沒問題。然而這裡面卻存在着極大的隐患，比如在應用length或substr等字元串函數時，就可能得到意外的結果。

    對于早期使用US7ASCII字元集資料庫的資料遷移到oracle8i/9i中(使用zhs16gbk)，由于原始資料已經按照US7ASCII格式存儲，對于這種情況，可以通過使用Oracle8i的導出工具，設定導出字元集為US7ASCII，導出後使用UltraEdit等工具打開dmp檔案，修改第二、三字元，修改 0001 為0354，這樣就可以将US7ASCII字元集的資料正确導入到ZHS16GBK的資料庫中。

    注：已知字元代碼看字元集的方法是：select nls_charset_name(to_number('0354','xxxx')) from dual; 相反已知字元集算代碼的方法是：select to_char(nls_charset_id('ZHS16GBK'), 'xxxx') from dual; --可用于修改dump檔案。

六、結束語

    為了避免在資料庫遷移過程中由于字元集不同導緻的資料損失，Oracle提供了字元集掃描工具(character set scanner)，通過這個工具我們可以測試在資料遷移過程中由于字元集轉換可能帶來的問題，然後根據測試結果，确定資料遷移過程中最佳字元集解決方案。