搞懂oracle字符集
作為一個ORACLE DBA,在工作中會經(jīng)常處理由于字符集產(chǎn)生的一些問題。但是當真正想寫一些這方面的東西時,卻突然又沒有了頭緒。發(fā)了半天呆,還是決定用兩個字符集方面的例子作為切入點,倒不失為一個頭緒,說不定在實驗的過程中,問題就會一個接著一個的浮現(xiàn)出來。
現(xiàn)在,讓我們切入正題。
我用的數(shù)據(jù)庫是oracle10.2.0.3,數(shù)據(jù)庫字符集是al32utf8。
客戶端就是同一臺機器的windows xp.
下面是演示的例子:
PHP code:
SQL> drop table test purge;
Table dropped.
SQL> create table test(col1 number(1),col2 varchar2(10));
Table created.
--session 1 設(shè)置客戶端字符集為 zhs16gbk(修改注冊表nls_lang項的characterset 為zhs16gbk) 向表中插入兩個中文字符。PHP code:
SQL> insert into test values(1,'中國'); --1為session 1的標記
1 row created.
SQL> commit;
Commit complete.
--session 2 設(shè)置客戶端字符集 al32utf8(修改注冊表nls_lang項的characterset 為al32utf8),與數(shù)據(jù)庫字符集相同。 向表中插入兩個和session 1相同的中文字符。PHP code:
SQL> insert into test values(2,'中國'); --2為session 2的標記
1 row created.
SQL> commit;
Commit complete.
--session 1
SQL> select * from test;
COL1 COL2
---------- --------------------
2 ???
1 中國
--session 2
SQL> select * from test;
COL1 COL2
---------- ----------
2 中國
&nb
從session 1和session 2的結(jié)果中可以看到,相同的字符(注意,我指的是我們看到的,顯示為相同的字符),在不同的字符集輸入環(huán)境下,顯示成了亂碼。
在zhs16gbk字符集的客戶端,我們看到了utf8字符集客戶端輸入的相同的中文變成了亂碼-->col1=2的col2字段
在utf8字符集客戶端,我們看到zhs16gbk字符集的客戶端輸入的中文變成了另外的字符 -->col1=1的col2字段
從這個例子里,我們好像感覺到出了什么問題,也可能會聯(lián)想起現(xiàn)實環(huán)境中出現(xiàn)的亂碼問題。
問題似乎有了思路,ok,讓我們繼續(xù)把實驗做下去:PHP code:
--session 1 (或者session 2,在這里無所謂)
SQL> select col1,dump(col2,1016) from test;
COL1
----------
DUMP(COL2,1016)
--------------------------------------------------------------------------------
2
Typ=1 Len=4 CharacterSet=AL32UTF8: d6,d0,b9,fa
1
Typ=1 Len=6 CharacterSet=AL32UTF8: e4,b8,ad,e5,9b,
--session 1
SQL> select dump('中',1016) from dual;
DUMP('中',16)
--------------------------------------------
Typ=96 Len=3 CharacterSet=AL32UTF8: e4,b8,ad --字符“中” ,和上面直接從數(shù)據(jù)庫中讀取存儲的字符編碼一致。
SQL> select dump('國',1016) from dual;
DUMP('國',16)
--------------------------------------------
Typ=96 Len=3CharacterSet=AL32UTF8: e5,9b,bd --字符“國” ,和上面直接從數(shù)據(jù)庫中讀取存儲
如果使用session 2直接對著兩個字符進行測試,一樣會得到相同的結(jié)果(筆者已經(jīng)做過測試,這里為了避免冗長,刪掉了).
讓我們重新來理一下思路,并提出幾個問題:
1:為什么顯示為相同的字符,存儲到數(shù)據(jù)庫中卻變成了不同的編碼?
2:我們在向數(shù)據(jù)庫中插入數(shù)據(jù)的時候,oracle究竟做了些什么?
3:操作系統(tǒng)字符集,客戶端字符集,數(shù)據(jù)庫字符集究竟是什么關(guān)系?
帶著這些疑惑,讓我們接著做實驗,所有的疑團和猜測都會在試驗中得以驗證。
我的思路是,先取得測試環(huán)境的相關(guān)參數(shù)。
1:windows字符集(codepage)
我們使用chcp命令來獲得windows使用的字符集PHP code:
c:chcp
活動的代碼頁:
SQL> select col1,dump(col2,1016) from t1;
COL1
----------
DUMP(COL2,1016)
--------------------------------------------------------------------------------
2
Typ=1 Len=4 CharacterSet=AL32UTF8: d6,d0,b9,fa
1
Typ=1 Len=6 CharacterSet=AL32UTF8: e4,b8,ad,e5,9b,
SQL> select col1,dump(col2,1016) from test;
COL1
----------
DUMP(COL2,1016)
--------------------------------------------------------------------------------
2
Typ=1 Len=4 CharacterSet=AL32UTF8: d6,d0,b9,fa
1
Typ=1 Len=6 CharacterSet=AL32UTF8: e4,b8,ad,e5,9b,
--session 1
SQL>
SQL> insert into t1 values('中國',1);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from t1;
COL COL2
------------ ----------
中國 1
??? 2
--session 2
SQL> insert into t1 values('中國',2);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from t1;
COL COL2
------ ----------
涓浗 1
中國 &nb
session 1,我們看到session 2輸入的字符"中國"變成了亂碼"???",
session 2,我們看到session 1輸入的字符"中國"變成了另外的字符"涓浗",
下面我們來分析一下這中間數(shù)據(jù)庫,客戶端和操作系統(tǒng)都發(fā)生了那些事情。
上面已經(jīng)討論了:
session 1 輸入的字符"中國" 在數(shù)據(jù)庫中存儲的字符編碼為”e4,b8,ad,e5,9b,bd".
session 2 輸入的字符"中國" 在數(shù)據(jù)庫中存儲的字符編碼為”d6,d0,b9,fa".
當session 1開始查詢時,oracle從表中取出這兩個字符,并按照字符集al32utf8和字符集zhs16gbk的編碼映射表,將它的轉(zhuǎn)換成zhs16gbk字符編碼,對于編碼“e4,b8,ad,e5,9b,bd”,它對應(yīng)的zhs16gbk的字符編碼為"d6,d0,b9,fa",這個編碼對應(yīng)的字符為”中國“,所以我們看到了這個字符正常顯示出來了,而對于字符集al32utf8字符編碼“d6,d0,b9,fa”,由于我們用于顯示字符的windows環(huán)境使用的是zhs16gbk字符集,而在zhs16gbk字符集里面并沒有對應(yīng)這個編碼的字符或者屬于無法顯示的符號,于是使用了"?"這樣的字符來替換,這就是為什么我們看到session 2輸入的字符變成了這樣的亂碼。
當session 2開始查詢時,oracle從表中取出這兩個字符,由于客戶端(nls_lang)和數(shù)據(jù)庫的字符集設(shè)置一致,oracle將忽略字符的轉(zhuǎn)換問題,于是直接將數(shù)據(jù)庫中存儲的字符返回給客戶端。對于編碼為"d6,d0,b9,fa"的字符,返回給客戶端,而客戶端顯示所用的字符集正好是zhs16gbk,在這個字符集里,這個編碼對應(yīng)的是"中國"兩個字符,所以就正常顯示出來了。對于字符編碼“e4,b8,ad,e5,9b,bd”,返回到客戶端後,因為在zhs16gbk里采用的是雙字節(jié)存儲字符方式,所以這6字節(jié)對應(yīng)了zhs16gbk字符集的3個字符,也就是我們看到的"涓浗".
到現(xiàn)在為止,我想我們基本上搞清楚了為什么日常查詢時會遇到亂碼的問題。
其實亂碼,說到底就是用于顯示字符的操作系統(tǒng)沒有在字符編碼中找到對應(yīng)的字符導致的,造成這種現(xiàn)象的主要原因就是:
1:輸入操作的os字符編碼和查詢的os字符編碼不一致導致出現(xiàn)亂碼。
2:輸入操作的客戶端字符集(nls_lang)和查詢客戶端字符集(nls_lang)不同,也可能導致查詢返回亂碼或者錯誤的字符。
還有一個問題需要解釋一下:
在上面的例子中,相同的字符在不同的字符集中對應(yīng)著不同的字符編碼,這個通常稱為字符集不兼容或者不完全兼容,比如zhs16gbk和al32utf8,他們存儲的ascii碼的字符編碼都是相同的,但對于漢字卻是不同的。
如果兩個字符集對于相同的字符采用的相同的字符編碼,我們稱之為字符兼容,范圍大的叫做范圍小的字符集的超級。我們通常遇到的zhs16cgb231280,zhs16gbk就是這樣的情況,后者是前者的超級。
在實際的環(huán)境中除了字符顯示之外,還有其他的地方會涉及到字符集問題。比如:
1:exp/imp
2:sql*lorder
3:應(yīng)用程序的字符輸入
......
一個誤區(qū):
看到很多人在出現(xiàn)亂碼的時候都首先要做的就是將客戶端字符集設(shè)置和數(shù)據(jù)庫一致,其實這是沒有太多根據(jù)的。
設(shè)想一下,假如數(shù)據(jù)庫字符集是al32utf8,里面存儲這一些中文字符,而我的客戶端操作系統(tǒng)是英文的,此時我將客戶端的nls_lang設(shè)置成al32utf8,這樣會解決問題嗎?這樣客戶端就能顯示中文了嗎?客戶端就能輸入中文了嗎?現(xiàn)在客戶端是英文的,它的字符集里根本就沒有漢字的編碼,我們簡單的修改一下客戶端的字符集又有什么用?前面已經(jīng)討論了,這個設(shè)置無非就是告訴oracle我將以什么樣的字符集與數(shù)據(jù)庫進行數(shù)據(jù)交換,對于解決亂碼問題毫無關(guān)系。
正確的做法是將客戶端的操作系統(tǒng)改成支持中文字符,并將客戶端字符集改成和操作系統(tǒng)一致的字符集,這樣才能真正的解決問題。
--作者 alantany