Oracle 與 MySQL 的差異分析（10）：字元集

1 資料庫支援的字元集

1.1Oracle

Oracle建立資料庫時指定字元集，一般不能修改，整個資料庫都是一個字元集。雖然還支援指定國家字元集，用于nvarchar2類型，不過很少用到。常用的字元集：AL32UTF8和ZHS16GBK，其中AL32UTF8與UTF8幾乎是等價的。一個漢字在AL32UTF8中占三個位元組，而在ZHS16GBK中占用兩個位元組。

1.2 MySQL

MySQL的字元集比較靈活，可以指定資料庫、表和列的字元集，并且很容易修改資料庫的字元集，不過修改字元集時已有的資料不會更新。

（1）支援的字元集：

查詢支援的字元集：showcharacter set;

其中，defaultcollation表示預設的資料比較規則，_ci表示大小寫不敏感。

（2）資料庫的預設字元集：

show variables like ‘character_set_server’;

查詢目前資料庫的字元集：

show variables like ‘character_set_database’;

2 如何分析資料的字元集

在遇到資料亂碼問題時，需要分析資料的編碼，而不能僅僅根據看到的表象來判斷，因為資料展示出來可能已經發生了轉換，是以看到的亂碼也許實際資料是正确的。

2.1Oracle

在Oracle中，可以用dump查詢資料的編碼，使用lengthb查詢位元組的長度。

eg：select name, dump(name, 16),length(name), lengthb(name) from t_test;

結果：好 Typ=1 Len=3:e5,a5,bd 1 3

2.2 MySQL

hex函數可以查詢資料編碼，length查詢位元組長度，char_length查詢字元長度。

eg：select name, hex(name),char_length(name), length(name) from t_test;

結果：好 E5A5BD 1 1

3 用戶端字元集

用戶端字元集很重要，輸入資料時，包括文本輸入和螢幕輸入等，用戶端會以這個字元集來解析輸入的文本，如果實際輸入的字元集與用戶端字元集不一緻，那麼就可能導緻錄入資料庫的資料出現亂碼；輸出資料時，如果用戶端字元集設定的不合适，就會導緻展示或導出的資料是亂碼。

3.1Oracle

通過環境變量NLS_LANG配置用戶端字元集。

Linux下會話級設定方法：export NLS_LANG =AMERICAN_AMERICA.AL32UTF8

Windows下會話級設定方法：set NLS_LANG =AMERICAN_AMERICA.AL32UTF8

特别要注意一點，用SQLPLUS執行腳本時，NLS_LANG需要跟腳本檔案的字元集保持一緻。如果是UTF8，腳本需要儲存為UTF8無BOM格式。

在用SQLLDR導入資料時，可以在控制檔案中指定資料檔案的字元集，如果不指定，那麼就需要配置NLS_LANG：

load datacharacterset zhs16gbkinfile ‘data/Toneinfo.txt’truncateinto table toneinfofields terminated by ‘1’trailing nullcols(MUSICID, TONEID, ISRC, SPNAME, SPID, TONENAME, TONENAMELETTER)

3.2 MySQL

MySQL的用戶端字元集參數有三個：

character_set_client：用戶端來源資料使用的字元集

character_set_connection：連接配接層字元集

character_set_results：查詢結果字元集

可以用set names ** 統一設定（會話級）。

set names utf8; 它相當于下面的三句指令：

set character_set_client = utf8;

set character_set_connection = utf8;

set character_set_results = utf8;

執行腳本檔案時，用戶端的字元集也要求和腳本檔案字元集一緻，而不是和資料庫一緻。經測試，utf8腳本有沒有BOM都可以。

例如：建立一個腳本檔案 f:\test.sql ：

set names utf8;

truncate table t_test;

insert into t_test values(‘好’, 14);

commit;

如果腳本檔案是UTF8的，那麼用戶端字元集就應該設定為UTF8。