一、前言
先看一個比較有意思的案例
上面這個sql,查詢了a和b兩個字段,均為"張三"兩個漢字,并且使用length函數(shù)檢查,長度均為2。
但是,當你看到下面這幾個sql的輸出結果時,很有可能第一反應是:
"這特喵的怎么可能？"
其實,你所看到的兩個"張三",的確長得是一模一樣,用顯微鏡去看也不可能看到區(qū)別。
但為什么a和b不相等呢？
這是因為組成他們的成分不一樣,這個成分就是 字符集
二、什么是字符集？
百度百科
簡單來說,
字符（Character）是各種文字和符號的總稱，包括各國家文字、標點符號、圖形符號、數(shù)字等。
字符集（Character set）是多個字符的集合
眾所周知,計算機系統(tǒng)底層是由二進制1和0組成的,而且這個1和0也不過是表示電信號的"通"和"斷",根本就不存在任何字符。字符集其實可以理解為一個有索引的字典,根據(jù)指定的二進制數(shù)據(jù),去查這個字典對應位置的字符是什么,然后從字符集里把這個字以選定的字體顯示出來。
比如二進制數(shù)據(jù)"1011000010100001"(十六進制為"B0A1"),在GB2312字符集中,對應漢字"啊"。
就算是英文字母和數(shù)字,也同樣也必須依賴字符集,比如二進制數(shù)據(jù)"1100001"(十六進制為"61"),表示小寫字母"a",當然,目前幾乎所有的字符集里,這個二進制數(shù)據(jù)都表示a。
所以,對于我們目前所有在用的計算機系統(tǒng),如果要識別字符,必須使用這個字符的二進制數(shù)據(jù)去查某一個特定字符集,尋找對應的字符,如果找錯了字符集,找到的可能就是亂碼了。
三、ORACLE數(shù)據(jù)庫的字符集
oracle數(shù)據(jù)庫在安裝時,會有個選項,選擇數(shù)據(jù)庫的字符集,這個大多數(shù)DBA都知道,也不用多說。
但是目前很多人在通過網(wǎng)絡上的文章去了解oracle字符集的時候,經(jīng)常會被弄得一頭霧水:
AL32UTF8是什么？為什么不是UTF8?
環(huán)境變量里設置的SIMPLIFIED CHINESE_CHINA.ZHS16GBK是什么？為什么不直接設GBK？
在數(shù)據(jù)庫里的各個視圖里查到的字符集究竟是客戶端的還是數(shù)據(jù)庫的？
I.字符集代碼
ORACLE中的字符集代碼,并不等于目前通用的字符集名稱。先來看看官方提供的
ORACLE字符集與通用字符集對照表
其實根據(jù)這個表格中的"Description"可以了解到,目前這些通用的字符集名稱都太長了,且毫無規(guī)則,不能作為代碼使用;并且還存在一些特殊情況,比如"JA16EUCTILDE"和"JA16EUC"兩種字符集,實際上都是"EUC 24-bit Japanese",只有波浪線不一樣,就分成了兩個字符集。
所以oracle必須自己再對這些字符集進行一次統(tǒng)一命名,因此有了"AL32UTF8"以及"ZHS16GBK"這樣的字符集代碼
II.NLS_LANG
在系統(tǒng)環(huán)境變量(或注冊表)"NLS_LANG"中,經(jīng)常會配置類似下面這樣的值
SIMPLIFIED CHINESE_CHINA.ZHS16GBK
AMERICAN_AMERICA.AL32UTF8
其實這個環(huán)境變量由3個部分組成
<Language>_<territory>.<character>
即
語言_地區(qū).字符集
只有最后一截才是真正表示字符集
所以"SIMPLIFIED CHINESE_CHINA.ZHS16GBK"表示:“簡體中文”_“中國地區(qū)”.“16位GBK簡體中文”
同理,"AMERICAN_AMERICA.AL32UTF8"表示:“美式英語”_“美國地區(qū)”.“Unicode12.1通用字符集UTF-8編碼方案”。
當然,在環(huán)境變量中,這3個并非全部必填,你甚至可以用 "_.AL32UTF8"表示使用數(shù)據(jù)庫默認的語言和地區(qū),但客戶端使用你指定的字符集；用 "_AMERICA."只指定客戶端地區(qū),語言使用該地區(qū)的默認語言,字符集使用該地區(qū)默認語言對應的字符集。
---查詢比利時地區(qū)的語言
DECLARE
retval UTL_I18N.STRING_ARRAY;
cnt INTEGER;
BEGIN
retval := UTL_I18N.GET_LOCAL_LANGUAGES('BELGIUM');
for i in 0..retval.count-1 loop
DBMS_OUTPUT.PUT_LINE(retval(i));
END LOOP;
END;
/
---查詢法語的默認字符集
select UTL_I18N.GET_DEFAULT_CHARSET('French') from dual;
---從ISO標準語言代碼轉(zhuǎn)換成ORACLE中的語言 和 地區(qū)
select UTL_I18N.MAP_LANGUAGE_FROM_ISO('en_US') ,
UTL_I18N.MAP_TERRITORY_FROM_ISO('en_US') from dual;
---將ORACLE語言 和 地區(qū)轉(zhuǎn)換成標準ISO標準語言代碼
select UTL_I18N.MAP_LOCALE_TO_ISO('American','America') from dual;
實際用戶場景中,一個地區(qū)可以不止有一種語言,一種字符集也可以表示多種語言文字。通過靈活配置這個環(huán)境變量,可以讓數(shù)據(jù)庫對各個地區(qū)的時區(qū)、語言、貨幣、文字等的兼容性更好。
但是,你可能又會問了:
不是都說要讓數(shù)據(jù)庫的環(huán)境變量和客戶端的環(huán)境變量保持一致么？
對,沒錯,的確建議保持一致,但這并非絕對。目前多國家多語言的公司多得去了，在使用同一個數(shù)據(jù)庫時，肯定會有不同的需要。
分三部分
A.語言
首先,對于語言,我們做個這樣的測試：
- 數(shù)據(jù)庫安裝時,語言選擇了"AMERICAN",然后客戶端環(huán)境變量的"NLS_LANG"設置成"SIMPLIFIED CHINESE_CHINA.AL32UTF8",打開客戶端軟件，連接數(shù)據(jù)庫后執(zhí)行一段錯誤的sql
- 然后把客戶端環(huán)境變量的"NLS_LANG"改成"AMERICAN_CHINA.AL32UTF8",并重新打開客戶端軟件，連接數(shù)據(jù)庫后執(zhí)行同樣的sql
對，客戶端環(huán)境變量的這個語言就是用來干這個的，用于在客戶端顯示不同語言的報錯或者提示信息，你甚至可以設置成日語或者法語，這些系統(tǒng)提示信息在數(shù)據(jù)庫內(nèi)部本就是有記錄多種語言的。另外，也可以通過數(shù)據(jù)庫自帶plsql包"UTL_LMS"將對應的系統(tǒng)提示信息轉(zhuǎn)換成非客戶端設置的語言。
DECLARE
s varchar2(200);
i pls_integer;
BEGIN
i:= utl_lms.get_message(26052, 'rdbms', 'ora', 'french', s);
dbms_output.put_line('法語 : '||s);
i:= utl_lms.get_message(26052, 'rdbms', 'ora', 'AMERICAN', s);
dbms_output.put_line('美式英語 is: '||s);
i:= utl_lms.get_message(26052, 'rdbms', 'ora', 'SIMPLIFIED CHINESE', s);
dbms_output.put_line('簡體中文 is: '||s);
END;
/
輸出為：
法語 : Type %d non pris en charge pour l’expression SQL sur la colonne %s.
美式英語 : Unsupported type %d for SQL expression on column %s.
簡體中文 : 不支持 SQL 表達式類型 %d (位于 %s 列)。
當然，我們也得看目前的字符集是否支持當前語言，可參考官方文檔
ORACLE語言和支持的字符集對照表
B.地區(qū)
對于地區(qū)，除了上面說過的，在沒有設置語言的時候取地區(qū)的默認語言這個功能外，還影響時區(qū)、貨幣、日期格式，這個與本篇關系不大，就略過了。
C.字符集
接下來就是最后一個參數(shù)，字符集
我通過網(wǎng)絡上的文章，以及自己在數(shù)據(jù)庫中的摸索，得到以下這些sql，可以查詢語言、地區(qū)、字符集
select * from nls_database_parameters where parameter in ('NLS_TERRITORY','NLS_LANGUAGE','NLS_CHARACTERSET');
select * from nls_instance_parameters where parameter in ('NLS_TERRITORY','NLS_LANGUAGE','NLS_CHARACTERSET');
select * from nls_session_parameters where parameter in ('NLS_TERRITORY','NLS_LANGUAGE','NLS_CHARACTERSET');
select * from v$parameter;
select * from v$nls_parameters;
select * from v$system_parameter where name like 'nls%';
select name,substr(value$, 1, 64) from x$props where name like 'NLS%';
select userenv('language') from dual;
select sys_context('USERENV','LANGUAGE') from dual;
但是，發(fā)現(xiàn)一個問題，這些sql分別可以讓我們知道數(shù)據(jù)庫和客戶端中使用的語言及地區(qū)，但是對于字符集，要么沒有，要么顯示為數(shù)據(jù)庫字符集，而客戶端環(huán)境變量中設置的字符集通過任何一個sql都查不到！
這在Oracle官方文檔中也有對應的說明
FAQ-NLS-LANG
SELECT USERENV (‘language’) FROM DUAL; gives the session’s
_ but the DATABASE character set not the client, so the value returned is not the client’s complete NLS_LANG setting!
SELECT USERENV (‘language’) FROM DUAL;得到當前會話的 語言_地區(qū) 以及 數(shù)據(jù)庫字符集,不是客戶端的字符集,所以它返回的值不是完整的客戶端NLS_LANG設置!
先不討論為什么ORACLE不提供客戶端字符集在數(shù)據(jù)庫中的查詢方式，我們重點來看看，字符集不一樣到底會產(chǎn)生哪些問題。
我創(chuàng)建了一張表，有兩個字段，分別為行號、值。然后編寫了4個文本文件，都是執(zhí)行insert into 一條記錄到這個表，行號分別為1、2、3、4，值都為"張三"。測試場景及結果整理如下
插入數(shù)據(jù)
| 行號 | 數(shù)據(jù)庫語言 | 數(shù)據(jù)庫字符集 | 客戶端語言 | 客戶端字符集 | 插入sql的文件編碼 | 數(shù)據(jù)庫內(nèi)的十六進制數(shù)據(jù) | 在plsql developer中的可見字符 |
|---|---|---|---|---|---|---|---|
| 1 | AMERICAN | AL32UTF8 | SIMPLIFIED CHINESE | ZHS16GBK | UTF-8 | E5AFAEE78AB1E7AC81 | 寮犱笁 |
| 2 | AMERICAN | AL32UTF8 | SIMPLIFIED CHINESE | ZHS16GBK | ANSI | E5BCA0E4B889 | 張三 |
| 3 | AMERICAN | AL32UTF8 | SIMPLIFIED CHINESE | AL32UTF8 | UTF-8 | E5BCA0E4B889 | 張三 |
| 4 | AMERICAN | AL32UTF8 | SIMPLIFIED CHINESE | AL32UTF8 | ANSI | D5C5C8FD | 張三 |
(實測,在plsql developer選項中啟用了unicode時,客戶端字符集不影響其數(shù)據(jù)顯示)
用sqlplus查詢數(shù)據(jù)
| 客戶端字符集 | 行號 | 可見字符(chcp 936) | 可見字符(chcp 65001) |
|---|---|---|---|
| AL32UTF8 | 1 | 瀵姳絎 | 寮犱笁 |
| AL32UTF8 | 2 | 寮犱笁 | 張三 |
| AL32UTF8 | 3 | 寮犱笁 | 張三 |
| AL32UTF8 | 4 | 張三 | 張三 |
| 客戶端字符集 | 行號 | 可見字符(chcp 936) | 可見字符(chcp 65001) |
|---|---|---|---|
| ZHS16GBK | 1 | 寮犱笁 | 張三 |
| ZHS16GBK | 2 | 張三 | |
| ZHS16GBK | 3 | 張三 | |
| ZHS16GBK | 4 | ？？ |
根據(jù)上述測試，可以得到以下幾點
- 在插入數(shù)據(jù)時,數(shù)據(jù)文件的字符集要保證和客戶端字符集一致,可避免亂碼(需要對應的文字在兩個字符集中都存在),就算和數(shù)據(jù)庫字符集不一致也沒關系(比如第2行和第3行均正確且一致)
- 在插入數(shù)據(jù)時,客戶端字符集和數(shù)據(jù)文件字符集不一致時,可能會造成亂碼（第1行）,也可能不亂碼（第4行）,但是否亂碼只是"顯示效果",它的值在某種意義上是錯的
- 查詢時,客戶端字符集和數(shù)據(jù)庫字符集保持一致,能提高正確顯示文字的概率
- 查詢時,代碼頁(chcp)和客戶端字符集保持一致,能提高正確顯示文字的概率
- 代碼頁(chcp)和數(shù)據(jù)文件編碼是一種東西,也就是說,這個測試涉及到的只有3個變量,數(shù)據(jù)庫字符集、客戶端字符集、數(shù)據(jù)編碼(受代碼頁或文件編碼影響)
- 要想在最大程度上減少"亂碼"概率的發(fā)生,需要數(shù)據(jù)庫字符集、客戶端字符集、數(shù)據(jù)編碼三者都保持一致
- 如果無法保持三者一致,比如數(shù)據(jù)庫字符集不可控時,也應該盡量讓客戶端字符集及數(shù)據(jù)編碼保持一致
- 數(shù)據(jù)庫字符集和客戶端字符集不一致的情況下,比如數(shù)據(jù)庫字符集為ZHS16GBK,但客戶端字符集為AL32UTF8,那么客戶端插入數(shù)據(jù)時,可能部分文字無法進行轉(zhuǎn)換(UTF-8的字比GBK多),但反過來問題不大,只要確保訪問同一個數(shù)據(jù)的所有客戶端的字符集統(tǒng)一
三、在Oracle中還原亂碼
事前要確保字符集一致，可避免亂碼，但如果事前沒有保證字符集一致，導致產(chǎn)生了亂碼，事后該如何挽救這批亂碼的數(shù)據(jù)？
部分亂碼可以通過以下方式進行還原
select utl_i18n.raw_to_char(UTL_I18N.string_to_raw('寮犱笁', 'ZHS16GBK'),
'AL32UTF8')
from dual;
但以上僅僅是只以ZHS16GBK及AL32UTF8來進行舉例，實際場景不乏有日語、韓語、法語等等，要判斷一個亂碼到底是哪個字符集轉(zhuǎn)哪個字符集出了問題，其實很難。
所以我開發(fā)了一個plsql包，窮舉轉(zhuǎn)換，輸出一個表格，用戶可肉眼排查亂碼是怎么產(chǎn)生的(為減少數(shù)據(jù)輸出條數(shù)，僅針對某一方有亞洲語言字符集參與的亂碼)
create or replace package charset_util_pkg is
type convert_any_charset_type is RECORD(
original_str varchar2(4000),
original_HEX RAW(32767),
to_str varchar2(4000),
to_hex raw(32767),
from_charset varchar2(50),
to_charset varchar2(50),
sql_text varchar2(200));
type convert_any_charset_table is table of convert_any_charset_type;
function convert_any_charset(original_str varchar2)
return convert_any_charset_table
PIPELINED;
end charset_util_pkg;
/
create or replace package body charset_util_pkg is
charsetlist ora_mining_varchar2_nt := ora_mining_varchar2_nt('JA16EUC',
--'JA16EUCTILDE',
'JA16SJIS',
--'JA16SJISTILDE',
'KO16MSWIN949',
'TH8TISASCII',
'VN8MSWIN1258',
'ZHS16GBK',
'ZHT16HKSCS',
--'ZHT16MSWIN950',
'ZHT32EUC',
'BLT8ISO8859P13',
'BLT8MSWIN1257',
'CL8ISO8859P5',
'CL8MSWIN1251',
'EE8ISO8859P2',
'EL8ISO8859P7',
'EL8MSWIN1253',
'EE8MSWIN1250',
'NE8ISO8859P10',
'NEE8ISO8859P4',
'WE8ISO8859P15',
'WE8MSWIN1252',
'AR8ISO8859P6',
'AR8MSWIN1256',
'IW8ISO8859P8',
'IW8MSWIN1255',
'TR8MSWIN1254',
'WE8ISO8859P9',
'AL32UTF8');
ASIA_charsetlist ora_mining_varchar2_nt := ora_mining_varchar2_nt('JA16EUC',
-- 'JA16EUCTILDE',
'JA16SJIS',
-- 'JA16SJISTILDE',
'KO16MSWIN949',
'TH8TISASCII',
'VN8MSWIN1258',
'ZHS16GBK',
'ZHT16HKSCS',
--'ZHT16MSWIN950',
'ZHT32EUC');
function convert_any_charset(original_str varchar2)
return convert_any_charset_table
PIPELINED is
to_str varchar2(4000);
begin
for rec in (select f.column_value from_charset,
t.column_value to_charset
from table(charsetlist) f, table(charsetlist) t
where f.column_value <> t.column_value
AND (F.column_value IN
(SELECT column_value FROM TABLE(ASIA_charsetlist)) OR
T.column_value IN
(SELECT column_value FROM TABLE(ASIA_charsetlist)))) loop
begin
to_str := utl_i18n.raw_to_char(UTL_I18N.string_to_raw(original_str,
rec.from_charset),
rec.to_charset);
exception
when others then
begin
to_str := utl_i18n.raw_to_char(UTL_I18N.string_to_raw(original_str ||
chr(0),
rec.from_charset),
rec.to_charset);
exception
when others then
to_str := 'convert error!';
end;
end;
if to_str in (rpad(chr(63), length(original_str), chr(63)),
rpad(UTL_RAW.cast_to_varchar2('C2BF'),
length(original_str),
UTL_RAW.cast_to_varchar2('C2BF'))) then
CONTINUE;
end if;
PIPE ROW(convert_any_charset_type(original_str,
UTL_RAW.cast_to_raw(original_str),
to_str,
UTL_RAW.cast_to_raw(to_str),
rec.to_charset,
rec.from_charset,
'utl_i18n.raw_to_char(UTL_I18N.string_to_raw(%s,''' ||
rec.from_charset || '''),''' ||
rec.to_charset || ''')'));
end loop;
end;
end charset_util_pkg;
/
select A.* from charset_util_pkg.convert_any_charset('寮犱笁') A;
或者限定當前數(shù)據(jù)庫字符集以減少條目
select A.*
from charset_util_pkg.convert_any_charset('寮犱笁') A
where from_charset =
(select value
from nls_database_parameters
where parameter = 'NLS_CHARACTERSET');
或者使用列作為傳入?yún)?shù)
select A.*
from test_20220122_1 b,charset_util_pkg.convert_any_charset(b.b) A
where b.b='寮犱笁' and from_charset =
(select value
from nls_database_parameters
where parameter = 'NLS_CHARACTERSET');
另外，還可以從結果中復制其對應的sql_text以快速獲得正確的轉(zhuǎn)換代碼。
總結
經(jīng)過以上研究可以得知，如果開發(fā)的應用項目亂碼,其應用開發(fā)人員應該占首要責任,因為客戶端字符集及數(shù)據(jù)編碼都是由開發(fā)人員指定的，在一個只有應用輸入數(shù)據(jù)的數(shù)據(jù)庫中，亂碼與數(shù)據(jù)庫字符集的關系并不大（除非數(shù)據(jù)庫安裝時指定了一個偏門的字符集）。
如果在應用項目開發(fā)時不注意字符集的統(tǒng)一，就會出現(xiàn)各種奇怪的問題，比如不帶條件能查到數(shù)據(jù)，復制那個值作為條件去查又查不到；還有各種奇怪的亂碼。
回到開頭的那個案例，其實a和b兩個值就是上面這個測試中的第3行和第4行的值，雖然顯示一樣，但實際一個是UTF8編碼，一個是GBK編碼。
附開頭的測試數(shù)據(jù)及sql：
create table test_20220122_1 as
select utl_raw.cast_to_varchar2('E5BCA0E4B889') a,
utl_raw.cast_to_varchar2('D5C5C8FD') b
from dual;
select a,
b,
length(a),
length(b),
lengthb(a),
lengthb(b),
UTL_RAW.cast_to_raw(a),
UTL_RAW.cast_to_raw(B)
from test_20220122_1;
注：以上僅針對ORACLE數(shù)據(jù)庫，其他數(shù)據(jù)庫的情況會有所區(qū)別
- 本文作者： DarkAthena
- 本文鏈接： https://www.darkathena.top/archives/about-oracle-charset
- 版權聲明： 本博客所有文章除特別聲明外，均采用CC BY-NC-SA 3.0 許可協(xié)議。轉(zhuǎn)載請注明出處！
