[Oracle] 徹底搞懂Oracle字元集_oracle

基本概念
字元集（Character set）：是一個系統支援的所有抽象字元的集合。字元是各種文字和符號的總稱，包括各國家文字、標點符號、圖形符號、數字等。常見的字元集有ASCII，ZHS16GB231280，ZHS16GBK等。

字元編碼（Character Encoding）：是一套法則，使用該法則能夠對自然語言的字元的一個集合（如字母表或音節表），與其它的一個集合（如電腦編碼）進行配對。即在符號集合與數字系統之間建立對應關係。與字元集相對應，常見的字元編碼有：ASCii，ZHS16GBK，ZHT16BIG5，ZHS32GB18030等。
字元集的定義其實就是字元的集合，而字元編碼則是指怎麼將這些字元變成位元組用於儲存、讀取和傳輸。

萬國碼（Unicode）：包含了幾乎人類所有可用的字元，每年還在不斷的增加，可以看作是一種通用的字元集。它將全世界所有的字元統一化，統一編碼，不會再出現字元不相容和字元轉換的問題。
它有以下三種編碼方式：
1.UTF-32編碼：固定使用4個位元組來表示一個字元，存在空間利用效率的問題。
2.UTF-16編碼：對相對常用的60000餘個字元使用兩個位元組進行編碼，其餘的使用4位元組。
3.UTF- 8編碼：相容ASCII編碼；拉丁文、希臘文等使用兩個位元組；包括漢字在內的其它常用字元使用三個位元組；剩下的極少使用的字元使用四個位元組。

Oracle字元集基本原理
在搞懂Oracle字元集基本原理之前，一定要先分清以下三個概念：
1. Oracle資料庫伺服器字元集：即Oracle以哪種字元編碼儲存字元，可以通過以下語句查出資料庫字元集的設定。

複製代碼 代碼如下:

SQL> select * from v$nls_parameters where parameter='NLS_CHARACTERSET';
PARAMETER                      VALUE
------------------------------ -----------------
NLS_CHARACTERSET               AL32UTF8

2. 用戶端作業系統字元集：即用戶端作業系統以哪種字元編碼儲存字元。
如果是Windows，可以使用chcp命令獲得字碼頁（code page）：

複製代碼 代碼如下:

C:\Users\xianzhu>chcp
Active code page: 936

根據該字碼頁，到微軟的官方文檔《National Language Support (NLS) API Reference》找到其對應的字元集。
如果是Linux，字元集在/etc/sysconfig/i18n設定：

複製代碼 代碼如下:

LANG="zh_CN.GB2312" (指定當前作業系統的字元集)
SUPPORTED="zh_CN.GB2312"(指定當前作業系統支援的字元集)
SYSFONT="lat0-sun16"(指定當前作業系統的字型)

3. 用戶端NLS_LANG參數：該參數用於向Oracle指示用戶端作業系統的字元集。
有了以上3個基本概念之後，我來闡述一下Oracle字元集轉換的基本原則：
1.設定用戶端的NLS_LANG為用戶端作業系統的字元集
2.如果資料庫字元集等於NLS_LANG，資料庫和用戶端傳輸字元時不作任何轉換
3.如果它們倆不等，則需要在不同字元集間轉換，只有用戶端作業系統字元集是資料庫字元集子集的基礎上才能正確轉換，否則會出現亂碼。
幾種常見情況分析
下面先看一個例子，再透過現象看本質，我們會針對這個例子進行分析。
該例子如下：

複製代碼 代碼如下:

1. 資料庫字元集為Unicode（UTF-8編碼）
我們的資料庫版本是10.2.0.4.0，資料庫字元集是：
SQL> select * from v$nls_parameters where parameter='NLS_CHARACTERSET';
PARAMETER                                VALUE
---------------------------------------- ------------------------------
NLS_CHARACTERSET               AL32UTF8
2. 用戶端作業系統字元集為字碼頁936（字元集為ZHS16GBK）
可以使用chcp獲得windows的字碼頁(code page)
C:\Documents and Settings\a105024\Desktop>chcp
Active code page: 936
3. 建立測試表
SQL> create table test(id number,var varchar2(30));
Table created.
4. 插入資料
這裡在同一個作業系統啟動兩個session，session1的NLS_LANG設為和資料庫字元集一樣（即AL32UTF8）：
C:\Documents and Settings\a105024\Desktop>set nls_lang=Simplified Chinese_China.AL32UTF8
串連資料庫並插入一條資料：
Session_1>insert into test values(1,'中國');
1 row created.
Session_1>commit;
Commit complete.
session2的NLS_LANG設為和用戶端作業系統一樣（即ZHS16GBK）：
C:\Documents and Settings\a105024\Desktop>set nls_lang=Simplified Chinese_China.ZHS16GBK
串連資料庫並插入一條資料：
Session_2>insert into test values(2,'中國');
1 row created.
Session_2>commit;
Commit complete.
5. 執行查詢
在session 1上執行查詢：
Session_1>select * from test;
        ID VAR
---------- ---------------------
         1 中國
         2 涓   浗
在session 2上執行查詢：
Session_2>select * from test;
        ID VAR
---------- --------------------
         1 ？??
         2 中國

上面例子看起來很詭異，session1和2都能正常顯示自己插入的字串，又都不能正常顯示對方插入的字串。為了弄清楚，我們首先得知道資料庫裡對這兩個字串是怎麼儲存的。我們可以使用dump函數獲得字元在資料庫的編碼:

複製代碼 代碼如下:

SQL> select id,dump(var,1016) from test;
ID DUMP(VAR,1016)
-- ------------------------------------------------------------
 1 Typ=1 Len=4 CharacterSet=AL32UTF8: d6,d0,b9,fa
 2 Typ=1 Len=6 CharacterSet=AL32UTF8: e4,b8,ad,e5,9b,bd

根據AL32UTF8的編碼，“中國”兩字的正確編碼為（都為3個位元組）：
中--e4,b8,ad
國--e5,9b,bd
因此session 1插入的字串在資料庫中的編碼是錯誤的，session 2正確。這也是為什麼一定要設定NLS_LANG為用戶端作業系統的字元集。
但是根據上面實驗我們可以知道，資料庫中儲存正確，並不代表用戶端能正常顯示；同樣地，即時資料庫沒有正確儲存，有時候用戶端也能夠正常顯示，這又是為什麼呢？別急，請聽我慢慢道來：

情境1：session 1插入，session 1查詢，在資料庫中儲存錯誤，但顯示正確。
插入過程：
”中國“兩字在用戶端作業系統字元集ZHS16GBK中的編碼是”d6,d0,b9,fa"，由於NLS_LANG和資料庫字元集相同，資料庫端對用戶端傳過來的字元編碼不進行任何轉換直接存入資料庫，因此資料庫中儲存的編碼也是“d6,d0,b9,fa”，
讀取過程：
資料庫端讀取的編碼是“d6,d0,b9,fa”，由於NLS_LANG和資料庫字元集相同，用戶端對資料庫端傳過來的字元編碼不進行任何轉換直接顯示，編碼”d6,d0,b9,fa“在用戶端作業系統字元集ZHS16GBK對應的漢字為“中國”。

從以上分析可知，雖然讀取時正確的，但那是因為負負得正，實際上資料庫中儲存是錯誤的，因此要特別小心這種情況，在產生庫中要避免。其實只要對它進行length操作就能輕易揭開它的假面具：

複製代碼 代碼如下:

Session_1>select length(var) from test where id=1;
LENGTH(VAR)
-----------
          3

得出的長度居然為3！實際的長度只是2，這會帶來很多麻煩。

情境2：session 1插入，session 2查詢，在資料庫中儲存錯誤，顯示也錯誤。
插入過程和情境1一樣，這裡就不再累述。
讀取過程：
資料庫端讀取的編碼是“d6,d0,b9,fa”，由於NLS_LANG和資料庫字元集不同，用戶端對資料庫端傳過來的字元編碼進行轉換，資料庫端字元集AL32UTF8裡編為“d6,d0,b9,fa”無法在用戶端作業系統字元集ZHS16GBK裡找到對應的編碼，所以只好用?代替。

情境3：session 2插入，session 1查詢，在資料庫中儲存正確，但顯示錯誤。
插入過程：
”中國“兩字在用戶端作業系統字元集ZHS16GBK中的編碼是”d6,d0,b9,fa"，由於NLS_LANG和資料庫字元集不同，Oracle會進行字元編碼轉換，也就是將字元集ZHS16GBK裡“中國”的編碼“d6,d0,b9,fa"轉換為字元集"AL32UTF8"裡”中國“的編碼”e4,b8,ad,e5,9b,bd“。
讀取過程：
資料庫端讀取的編碼是”e4,b8,ad,e5,9b,bd“，由於NLS_LANG和資料庫字元集相同，用戶端對資料庫端傳過來的字元編碼不進行任何轉換直接顯示，編碼”e4,b8,ad,e5,9b,bd“在用戶端作業系統字元集ZHS16GBK對應的漢字為“涓   浗”（原本2個字元，現在變成了3個字元，因為ZHS16GBK的漢字以2個位元組編碼）。

情境4：session 2插入，session 2查詢，在資料庫中儲存正確，顯示也正確。
插入過程和情境3類似。
讀取過程：
資料庫端讀取的編碼是”e4,b8,ad,e5,9b,bd“，由於NLS_LANG和資料庫字元集不同，用戶端對資料庫端傳過來的字元編碼進行轉換，資料庫端字元集AL32UTF8裡”中國“兩字的編碼”e4,b8,ad,e5,9b,bd“轉換成用戶端作業系統字元集ZHS16GBK裡“中國”兩字的編碼“d6,d0,b9,fa"，並正常顯示。
這種情況雖然經過了兩次轉換，都確實最正確、最推薦的方式。

附錄：Oracle 字元集超集和子集的對應關係可查看：http://download.oracle.com/docs/cd/B19306_01/server.102/b14225/applocaledata.htm

結論：NLS_LANG只和用戶端作業系統的字元集相關，如果用戶端作業系統的字元集和資料庫字元集間無法正確轉換，則應該首先改變用戶端終端的字元集，而不是簡單地把NLS_LANG設為和資料庫字元集一樣。