oracle 資料庫字元集研究 上篇

一、什麼是Oracle字元集

       Oracle字元集是一個位元組資料的解釋的符号集合,有大小之分,有互相的包容關系。ORACLE 支援國家語言的體系結構允許你使用本地化語言來存儲，處理，檢索資料。它使資料庫工具，錯誤消息，排序次序，日期，時間，貨币，數字，和月曆自動适應本地化語言和平台。

影響Oracle資料庫字元集最重要的參數是NLS_LANG參數。

它的格式如下: NLS_LANG = language_territory.charset

它有三個組成部分(語言、地域和字元集)，每個成分控制了NLS子集的特性。

其中:

Language： 指定伺服器消息的語言， 影響提示資訊是中文還是英文

Territory： 指定伺服器的日期和數字格式，

Charset：  指定字元集。

如:AMERICAN _ AMERICA. ZHS16GBK

從NLS_LANG的組成我們可以看出，真正影響資料庫字元集的其實是第三部分。

是以兩個資料庫之間的字元集隻要第三部分一樣就可以互相導入導出資料，前面影響的隻是提示資訊是中文還是英文。

二．字元集的相關知識：

2.1 字元集

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

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

    <Language><bit size><encoding>

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

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

相老師：oracle資料庫裡有一張表，叫字元集表，這個表有兩個列，左邊列是字元，右邊列數字編碼（二進制）。也就是說字元和字元編碼一一對應，在存資料和取資料的時候都會用到這個表。具體是哪個表，我們一會詳述。

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的更新版本，比utf8更全，建議用這個），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的超集。 

三、資料庫字元集

3.1 資料庫字元集（oracle伺服器端字元集）

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

3.1.1字元集

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

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

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

3.1.2國家字元集：

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

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

3.2 檢視資料庫伺服器端字元集：

資料庫伺服器字元集select * from nls_database_parameters，其來源于props$. 在建立資料庫的時候設定的,一般不會改變.

SQL> select * from nls_database_parameters;

PARAMETER                VALUE

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

NLS_LANGUAGE  AMERICAN

NLS_TERRITORY  AMERICA

NLS_CURRENCY   $

NLS_ISO_CURRENCY  AMERICA

NLS_NUMERIC_CHARACTERS      .,

NLS_CHARACTERSET  AL32UTF8

NLS_CALENDAR  GREGORIAN

NLS_DATE_FORMAT   DD-MON-RR

NLS_DATE_LANGUAGE  AMERICAN

NLS_SORT                  BINARY

NLS_TIME_FORMAT           HH.MI.SSXFF AM

PARAMETER      VALUE

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

NLS_TIMESTAMP_FORMAT                 DD-MON-RR HH.MI.SSXFF AM

NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR

NLS_TIMESTAMP_TZ_FORMAT          DD-MON-RR HH.MI.SSXFF AM TZR

NLS_DUAL_CURRENCY   $

NLS_COMP                 BINARY

NLS_LENGTH_SEMANTICS         BYTE

NLS_NCHAR_CONV_EXCP FALSE

NLS_NCHAR_CHARACTERSET UTF8

NLS_RDBMS_VERSION 11.2.0.1.0

20 rows selected.

SQL> select name ,value$,comment$ from props$;        ——這個表必須用sys使用者查詢。

NAME                                                               VALUE$          COMMENT$

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

DICT.BASE                                                                  2       dictionary base tables version #

DEFAULT_TEMP_TABLESPACE                                  TEMP       Name of default temporary tablespace

DEFAULT_PERMANENT_TABLESPACE                  USERS       Name of default permanent tablespace

DEFAULT_EDITION                                                   ORA$BASE       Name of the database default edition

Flashback Timestamp TimeZone                                      GMT       Flashback timestamp created in GMT

TDE_MASTER_KEY_ID

DST_UPGRADE_STATE                                                 NONE       State of Day Light Saving Time Upgrade

DST_PRIMARY_TT_VERSION                                      11        Version of primary timezone data file

DST_SECONDARY_TT_VERSION                                   0       Version of secondary timezone data file

DEFAULT_TBS_TYPE                                          SMALLFILE       Default tablespace type

NLS_LANGUAGE                                          AMERICAN       Language

NLS_TERRITORY                                      AMERICA       Territory

NLS_CURRENCY                                       $       Local currency

NLS_ISO_CURRENCY                                AMERICA       ISO currency

NLS_NUMERIC_CHARACTERS                     .,                                 Numeric characters

NLS_CHARACTERSET                                AL32UTF8       Character set

NLS_CALENDAR                              GREGORIAN       Calendar system

NLS_DATE_FORMAT                            DD-MON-RR       Date format

NLS_DATE_LANGUAGE                              AMERICAN       Date language

NLS_SORT                                                  BINARY       Linguistic definition

NLS_TIME_FORMAT                                   HH.MI.SSXFF AM       Time format

NLS_TIMESTAMP_FORMAT       DD-MON-RR HH.MI.SSXFF AM       Time stamp format

NLS_TIME_TZ_FORMAT                        HH.MI.SSXFF AM TZR        Time with timezone format

NLS_TIMESTAMP_TZ_FORMAT       DD-MON-RR HH.MI.SSXFF AM TZR     Timestamp with timezone format

NLS_DUAL_CURRENCY                                           $       Dual currency symbol

NLS_COMP      BINARY       NLS comparison

NLS_LENGTH_SEMANTICS                                BYTE       NLS length semantics

NLS_NCHAR_CONV_EXCP                              FALSE       NLS conversion exception

NLS_NCHAR_CHARACTERSET                        UTF8       NCHAR Character set

NLS_RDBMS_VERSION                                       11.2.0.1.0        RDBMS version for NLS parameters

GLOBAL_DB_NAME                                           MAS       Global database name

EXPORT_VIEWS_VERSION                                     8       Export views revision #

WORKLOAD_CAPTURE_MODE           CAPTURE implies workload capture is in progress

WORKLOAD_REPLAY_MODE       PREPARE implies external replay clients can connect; REPLAY implies workload replay is in progress

NO_USERID_VERIFIER_SALT      8BF56230FC49D6ECFE53EB96A5922DD5

DBTIMEZONE                                              00:00       DB time zone

36 rows selected.

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

3.3 檢視用戶端字元集（NLS_LANG參數）：

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

用戶端字元集環境select * from nls_instance_parameters,其來源于v$parameter，由參數檔案init<SID>.ora 定義的參數，該值不會受系統環境變量（.bash_profile）影響：測試結果參考：http://blog.csdn.net/cymm_liu/article/details/40588023

3.3.1 NLS_LANG參數格式

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

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

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

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

    例如：NLS_LANG=AMERICAN_AMERICA.US7ASCII 

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

另如：

export NLS_LANG='AMERICAN_AMERICA.ZHS16GBK'

注意，還有個概念叫：會話字元集

select * from nls_session_parameters，其來源于v$nls_parameters，表示會話自己的設定，可能是會話的環境變量或者是alter session完成，如果會話沒有特殊的設定，将與nls_instance_parameters一緻。動态視圖:v$nls_parameters，查詢出來的結果并不是資料庫的字元集，它顯示目前會話值 ,受用戶端nls 的控制。

V$NLS_PARAMETERScontains current values of NLS parameters。

3.4、修改 

3.4.1 修改NLS參數

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

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

    （2）修改環境變量NLS_LANG

    （3）使用ALTER SESSION語句，在oracle會話中修改

    （4）使用某些SQL函數

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

3.4.2 修改資料庫字元集

    按照上文所說，資料庫字元集在建立後原則上不能更改。不過有2種方法可行。

1. 如果需要修改字元集，通常需要導出資料庫資料，重建資料庫，再導入資料庫資料的方式來轉換。

2. 通過ALTER DATABASE CHARACTER SET語句修改字元集，但建立資料庫後修改字元集是有限制的，隻有新的字元集是目前字元集的超集時才能修改資料庫字元集，例如UTF8是US7ASCII的超集，修改資料庫字元集可使用ALTER DATABASE CHARACTER SET UTF8。