理解ORACLE 字符集【轉】

一、引言

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

    二、字符集基本知識

    2.1字符集

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

    Oracle 的字符集命名遵循以下命名規則 ：

    即：  <語言><位元位數><編碼 >

    比如： 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 、props$、 v$nls_parameters查詢結果中NLS_CHARACTERSET表示字符集，NLS_NCHAR_CHARACTERSET表示國家字符集

    2.4.4 修改資料庫字符集

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

    2.5 客戶端字符集（NLS_LANG引數）

    2.5.1 客戶端字符集含義

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

    2.5.2 NLS_LANG 引數格式

    NLS_LANG=_.

    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.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
     ____________   _________________  _____________
     |imp匯入檔案|<-><->
     ------------   -----------------  -------------    
    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引數

    （4）目標資料庫字符集出現亂碼原因

    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的資料庫中。

    六、結束語

    為了避免在資料庫遷移過程中由於字符集不同導致的資料損失，oracle提供了字符集掃描工具（character set scanner），透過這個工具我們可以測試在資料遷移過程中由於字符集轉換可能帶來的問題，然後根據測試結果，確定資料遷移過程中最佳字符集解決方案
本文轉自：
http://learningtips.blog.163.com/blog/static/1652183320098174114195/?fromdm&fromSearch&isFromSearchEngine=yes