循序漸進Oracle：數據庫的字符集和字符集文件

• 2019 年 10 月 4 日
• 筆記

導讀：本文來自『墨天輪』專欄「循序漸進Oralcle」（https://www.modb.pro/topic/6289，複製到瀏覽器中打開或者點擊「閱讀原文」），介紹《循序漸進Oracle》第三章的3.1-3.4節：字符集的基本知識、數據庫的字符集、字符集文件及字符支持、NLS_LANG的設置與影響、導入導出及字符轉換。

Oracle全球支持(即Globalization Support)允許我們使用本地語言和格式來存儲和檢索數據。通過全球支持，Oracle可以支持多種語言及字符集，得以展示數據庫的強大魅力。這篇介紹第三章的3.1-3.4：字符集的基本知識、數據庫的字符集、字符集文件及字符支持、NLS_LANG的設置與影響、導入導出及字符轉換。

在創建數據庫的過程中，如圖3-1所示的界面用於選擇字符集。

圖3-1 選擇字符集

由於不同語言及字符集的共同存儲存在設置上具有一定的複雜性，字符集一度成為普遍困擾大家的一個主要問題。

本章就字符集一些常見問題進行討論，並對字符集轉化等本質內容進行探索。

3.1 字符集的基本知識

如果從頭說起，字符集最早的編碼方案來自於與ASCII，這也是最常見的編碼方式。該方案起源於20世紀60年代初期，最初是美國國會圖書館制定用來作為美國圖書館界書目交換的共同標準，最後完善成為美國的國家標準ASCII（American Standard Code for Information Interchange），之後進一步演變成世界性的計算機字符編碼標準ISO646（其全名為7-bit coded character set for information interchange），於是成為計算機編碼方案的基礎。

ASCII第一次作為規範標準是在1967年發表，最後一次更新則是在1986年，至今為止共定義了128個字符，其中33個字符無法顯示，這33個字符多數是陳廢的控制字符，控制字符的用途主要是用來操控已經處理過的文字，在33個字符之外的是95個可顯示的字符，包含用鍵盤敲下空白鍵所產生的空白字符也算1個可顯示字符（顯示為空白）。

Oracle數據庫最早支持的編碼方案也就是US7ASCII。

由於英文字符一般是以一個位元組來存儲的，7位的編碼方案最多只能代表128個字符；經過擴展的8位編碼方案也只能代表256個字符，這遠遠不能滿足計算機發展的需要，對於亞洲國家複雜的字符存儲需要更多的碼位，於是各種編碼方案隨之而生。

為了容納全世界各種語言的所有字符和符號，解決不同編碼之間的兼容和轉換問題，1991年1月，十多家公司共同出資，組建Unicode協會，隨後Unicode編碼產生了。

Unicode協會的口號是：給每個字符提供了一個唯一的數字，不論是什麼平台，不論是什麼程序，不論什麼語言。

最初Unicode編碼使用2-Byte（16bit）來進行編碼，但是最多只能容納65536個字符，仍然不夠使用，後來進行了擴充，也就是Unicode 3.1標準，增加了額外的補充字符定義，現在Unicode 5.2標準已經發佈，具體可以參考Unicode官方站點（http://www.unicode.org ）。

Unicode編碼方案主要有3個實施標準：UTF-8、USC-2和UTF-16。Oracle從7.2開始支持UTF-8編碼，提供Unicode編碼支持。

按照各種標準的含義，Oracle推薦，如果數據庫需要存放不同語言的不同符號和字符，建議使用Unicode編碼方案。誠然，Unicode方案可以表示更多的字符，但是由於多位的存儲，需要額外的存儲空間和網絡傳輸，所以選擇最適合的數據庫字符集仍然需要慎重考慮。

3.2 數據庫的字符集

字符集在創建數據庫時指定，在創建後一般不能更改，所以在創建數據庫時能否選擇一個正確的字符集就顯得尤為重要。

在創建數據庫時，可以指定字符集（CHARACTER SET）和國家字符集（NATIONAL CHARACTER SET）。

字符集的主要作用如下:

用於存儲CHAR, VARCHAR2, CLOB, LONG等類型數據

用來標示諸如表名、列名以及PL/SQL變量等

用於存儲SQL和PL/SQL代碼等

國家字符集用以存儲NCHAR、NVARCHAR2、NCLOB等類型數據。

這些設置在數據庫創建時指定，回顧一下前面章節中曾引用的數據庫創建腳本：

CREATE DATABASE "eygle"  MAXINSTANCES 8 MAXLOGHISTORY 1 MAXLOGFILES 16 MAXLOGMEMBERS 3 MAXDATAFILES 100  DATAFILE SIZE 300M AUTOEXTEND ON NEXT  10240K MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL  SYSAUX DATAFILE SIZE 120M AUTOEXTEND ON NEXT  10240K MAXSIZE UNLIMITED  SMALLFILE DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE SIZE 20M AUTOEXTEND ON NEXT  640K MAXSIZE UNLIMITED  SMALLFILE UNDO TABLESPACE "UNDOTBS1" DATAFILE SIZE 200M AUTOEXTEND ON NEXT  5120K MAXSIZE UNLIMITED  CHARACTER SET ZHS16GBK  NATIONAL CHARACTER SET AL16UTF16  ……

以上用粗體顯示的就是數據庫的字符集和國家字符集設置。

在創建數據庫的過程中，一定要注意選擇字符集。對於簡體中文平台，一般缺省的字符集是ZHS16GBK。一旦字符集選定了，數據庫中能夠存儲的字符就受到了限制，所以選擇字符集應該儘可能多地容納所有將用到字符。

常見的中文字符集有：

ZHS16CGB231280 CGB2312-80 16-bit Simplified Chinese   MB, ASCII     ZHS16GBK       GBK        16-bit Simplified Chinese MB, ASCII,   UDC

其中GB2312碼是中華人民共和國國家漢字信息交換用編碼，全稱《信息交換用漢字編碼字符集──基本集》，由國家標準總局發佈，1981年5月1日實施，通行於中國內地。新加坡等地也使用此編碼。

GBK編碼是1995年12月頒佈的指導性規範，GBK與國家標準GB 2312-80信息處理交換碼所對應的、事實上的內碼標準兼容；同時，在字彙一級支持ISO/IEC 10646-1和GB 13000-1 的全部中日韓（CJK）漢字（20902字），包含了更多的編碼。

但是，ZHS16GBK並非是ZHS16CGB231280的嚴格超集（雖然後者的漢字在前者中都存在，但是同樣的編碼在不同兩個字符集中可能表達不同的漢字），所以在做數據庫字符轉換時仍然需要特別注意。

Oracle的字符集命名通常遵循以下命名規則：

<Language><bit size><encoding>，

即<語言><比特位數><編碼>，例如ZHS ·16·GBK。

但是需要說明的是，有些字符集命名違背了這個規範，Oracle 8/Oralce 8i中的UTF-8是第一個打破這個命名規範的字符集。

可以看到一類字符集以AL開頭，如AL16UTF16，其中AL代表ALL，指適用於所有語言（All Languages），按照這個標準當年UTF-8本應被命名為AL24UTF8。

3.3 字符集文件及字符支持

Oracle的全球支持是通過Oracle NLS Runtime Library（NLSRTL）來實施的，NLS運行庫通過獨立的函數來完成運行時和語言相關的轉換及控制。字符集相關的文件在數據庫服務器上是單獨存儲的，這些文件的位置受環境變量參數ORA_NLS*參數的控制，在不同版本中，這個環境變量的參數並不相同，在Oracle 7.2中，這個環境變量是ORA_NLS；在Oracle 7.3中，這個環境變量值變更為ORA_NLS32；在Oracle 8/Oracle 8i/Oracle 9i的各個版本中，該環境變量為ORA_NLS33；在Oracle 10g中，該環境變量參數又變更為ORA_NLS10。通常缺省地，可以不設置這個變量。

在Oracle 9i中，這個參數指向的字符文件缺省位置為$ORACLE_HOME/ocommon/nls/ admin/data。在Oracle 10g/11g中，這個參數指向的字符文件缺省位置為$ORACLE_HOME/ nls/data。

該目錄下存放3類文件分別用來定義語言（NLS_LANGUAGE）、區域（NLS_TERRITORY）和字符集（NLS_CHARACTERSET），其中中文語言的文件為lx00023.nlb，如圖3-2所示。

圖3-2 選擇語言文件

用Oracle提供的Locale Builder工具打開該文件，就可以看到在中文語言環境下的相關設置，如月份、星期等，如圖3-3所示。

圖3-3 月份和星期格式

當然最終要得文件是字符集文件，用Locale Builder工具打開相關的字符集文件（ZHS16GBK字符集文件為lx20354.nlb），如圖3-4所示，就可以看到和字符集相關的設置信息。

圖3-4 打開字符集文件

通過字符集文件，Oracle將所有漢字編碼存放在數據庫之中，這樣當存放和轉換漢字時才有了依據，我們看一下字符編碼，如圖3-5所示。

圖3-5 字符編碼

在上圖中可以看到，漢字「蓋」的編碼為0xb8c7，這也就是Oracle在數據庫中存放漢字時的編碼，有了這個編碼方案，Oracle才能知道在一個字符集中，字符0xb8c7真正代表什麼：

SQL> select dump('蓋') from dual;  DUMP('蓋')  ---------------------  Typ=96 Len=2: 184,199    SQL> select concat(to_char(184,'xx'),to_char(199,'xx')) from dual;  CONCAT  ------   b8 c7

這個過程完全可逆，在同樣字符集的數據庫中，可以進行如下轉換：

SQL> select to_number('b8c7','xxxxxx') from dual;  TO_NUMBER('B8C7','XXXXXX')  --------------------------                       47303  SQL> select chr(47303) from dual;  CH  --  蓋

通過以上介紹可以知道這些字符文件非常重要，如果這個字符文件丟失或損壞，則數據庫將會出現故障。在Windows上做一個簡單的測試：

C:oracle10.2.0nlsdata>dir lx20354.nlb   2006-10-10  14:52           477,772 lx20354.nlb  C:oracle10.2.0nlsdata>move lx20354.nlb lx20354.nlb.b  C:oracle10.2.0nlsdata>net start oracleserviceeygle  OracleServiceEYGLE 服務正在啟動 ...  OracleServiceEYGLE 服務已經啟動成功。    C:oracle10.2.0nlsdata>sqlplus "/ as sysdba"  Error 5 initializing SQL*Plus  NLS initialization error

以上測試顯示，當字符集文件被移除之後，由於不能正常讀取字符集文件，SQL*Plus已經不能正常登錄，此時可以修改NLS_LANG參數設置其他字符集，使得SQL*Plus可以完成初始化：

C:oracle10.2.0nlsdata>set nls_lang=AMERICAN_AMERICA.US7ASCII  C:oracle10.2.0nlsdata>sqlplus "/ as sysdba"    SQL*Plus: Release 10.2.0.3.0 - Production on Mon Jun 18 17:10:46 2007  Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.    ERROR:  ORA-03113: end-of-file on communication channel

此時SQL*Plus可以執行，但是無法登錄數據庫，這是因為SQL*Plus執行時就需要調用字符集文件，在Linux系統下，可以通過系統命令strace跟蹤到這些調用行為。

以下命令通過strace命令將SQL*Plus的調用過程跟蹤寫入sqlplus.log文件：

[oracle@wapdb ~]$ strace -o sqlplus.log sqlplus /nolog  SQL*Plus: Release 11.1.0.6.0 - Production on Mon Sep 27 10:23:52 2010  Copyright (c) 1982, 2007, Oracle.  All rights reserved.    SQL> exit

過濾一下跟蹤文件，可以得到SQL*Plus啟動依次調用的字符文件：

[oracle@wapdb ~]$ grep nls sqlplus.log  open("/opt/oracle/product/11.1.0/nls/data/lx1boot.nlb", O_RDONLY) = 3  open("/opt/oracle/product/11.1.0/nls/data/lx00001.nlb", O_RDONLY) = 3  open("/opt/oracle/product/11.1.0/nls/data/lx20354.nlb", O_RDONLY) = 3  open("/opt/oracle/product/11.1.0/nls/data/lx10035.nlb", O_RDONLY) = 3  open("/opt/oracle/product/11.1.0/nls/data/lx20001.nlb", O_RDONLY) = 3  open("/opt/oracle/product/11.1.0/nls/data/lx40001.nlb", O_RDONLY) = 3  open("/opt/oracle/product/11.1.0/nls/data/lx10001.nlb", O_RDONLY) = 8

注意以上調用中lx1boot.nlb是字符集的初始化文件，而lx20354.nlb正是SQL*Plus軟件啟動時需要調用的ZHS16GBK字符集文件。如果沒有了字符集文件，應用軟件（包括SQL*Plus和Oracle執行軟件）都是無法完成初始化的。

3.4 NLS_LANG的設置與影響

導入導出（IMP/EXP）是一個常用的數據遷移及轉化工具，因其導出文件具有平台無關性，所以在跨平台遷移中，最為常用；進一步的，從Oracle10g開始，數據泵（IMPDP/EXPDP）作為新一代高效數據導入導出工具被引入進來。

在使用EXP工具進行導出操作時，非常重要的是客戶端的字符集設置，也就是客戶端的NLS_LANG設置。

NLS_LANG參數由以下部分組成：

NLS_LANG=<Language>_<Territory>.<Clients Characterset>

其中LANGUAGE參數指定Oracle消息使用的語言，日期中月份和日的顯示；TERRITORY參數指定貨幣和數字格式，地區和計算星期及日期的習慣；CHARACTERSET控制客戶端應用程序使用的字符集，這個字符集用於對數據庫傳輸過來的數據進行解碼。

來看一個簡單的測試，來了解一下這幾個參數的作用及其對於數據顯示的影響，在LANGUAGE設置為簡體中文時，提示是中文輸出：

E:>set NLS_LANG=SIMPLIFIED CHINESE_CHINA.ZHS16GBK  E:>sqlplus "/ as sysdba"  連接到:Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production  With the Partitioning, Oracle Label Security, OLAP and Oracle Data Mining options  JServer Release 9.2.0.4.0 - Production    SQL> select sysdate from dual;  SYSDATE  ----------  01-11月-03  已選擇 1 行。

當LANGUAGE設置為American時，提示信息等是英文輸出：

E:>set NLS_LANG=AMERICAN_AMERICA.ZHS16GBK  E:>sqlplus "/ as sysdba"  Connected to:Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production  With the Partitioning, Oracle Label Security, OLAP and Oracle Data Mining options  JServer Release 9.2.0.4.0 - Production    SQL> select sysdate from dual;  SYSDATE  ---------  01-NOV-03  1 row selected.

客戶端的NLS_LANG設置不僅影響數據轉儲，還會影響到查詢顯示，是極其重要的參數。查看客戶端NLS_LANG設置可以使用以下方法：

· Windows客戶端的NLS_LANG設置受註冊表參數影響，所以可從註冊表中找到當前的參數設置，具體鍵值位於HKEY_LOCAL_MACHINESOFTWAREORACLE下。

對於不同的版本，其具體鍵值可能不同，如Oracle 10g的鍵值可能為KEY_OraDb10g，Oracle 11g的鍵值可能為KEY_OraDb11g_home1，其他客戶端安裝可能使用鍵值HOMExx（xx指存在多個ORACLE_HOME時系統編號）。

通常需要設置這相應鍵值下的NLS_LANG和數據庫字符集相同，以避免查詢亂碼等麻煩。

· 在UNIX環境下可以使用從環境變量中取得當前的NLS_LANG設置，例如：

[oracle@wapdb ~]$ env|grep NLS  NLS_LANG=SIMPLIFIED CHINESE_CHINA.ZHS16GBK

傳統的導入和導出工具（IMP/EXP）是客戶端軟件，同SQL*PLUS和Oracle Forms一樣，因此，使用EXP/IMP工具將同樣按照NLS_LANG定義的方式調用字符集文件，並且在服務器和客戶端之間根據設置進行字符集轉換：

[oracle@wapdb ~]$ export NLS_LANG=SIMPLIFIED CHINESE_CHINA.ZHS16GBK  [oracle@wapdb ~]$ strace -o exp.log exp    Export: Release 11.1.0.6.0 - Production on 星期一 9月 27 10:39:05 2010  Copyright (c) 1982, 2007, Oracle.  All rights reserved.  Username:  Password:  [oracle@wapdb ~]$ grep nls exp.log  open("/opt/oracle/product/11.1.0/nls/data/lx1boot.nlb", O_RDONLY) = 3  open("/opt/oracle/product/11.1.0/nls/data/lx00023.nlb", O_RDONLY) = 3  open("/opt/oracle/product/11.1.0/nls/data/lx20354.nlb", O_RDONLY) = 3  open("/opt/oracle/product/11.1.0/nls/data/lx10035.nlb", O_RDONLY) = 3  open("/opt/oracle/product/11.1.0/nls/data/lx20001.nlb", O_RDONLY) = 3  open("/opt/oracle/product/11.1.0/nls/data/lx00001.nlb", O_RDONLY) = 3  open("/opt/oracle/product/11.1.0/nls/data/lx10001.nlb", O_RDONLY) = 3  open("/opt/oracle/product/11.1.0/nls/data/lx207d0.nlb", O_RDONLY) = 9

在執行導出數據操作時，導出使用的字符集將會記錄在導出的DMP文件中，當文件導入時，將會檢查導出時使用的字符集設置，如果這個字符集不同於導入客戶端的NLS_LANG設置，字符集將根據導入客戶端NLS_LANG設置進行轉換，如果必要，在數據插入數據庫之前還會進行進一步轉換。

所以通常在執行導出操作時，最好把客戶端字符集設置得和數據庫相同，這樣可以避免在導出時發生不必要的數據轉換，導出文件將和數據庫具有相同的字符集，數據得以完好備份。即使將來會把導出文件導入到不同字符集的數據庫中，這樣做也可以把轉換延緩至導入時刻。

3.4 導入導出及字符轉換

當進行數據導入時，主要存在以下兩種情況。

· 源數據庫和目標數據庫具有相同字符集設置。

這時，只需要設置NLS_LANG等於數據庫字符集即可導入（前提是，導出使用的是和源數據庫相同字符集，即三者相同）

· 源數據庫和目標數據庫字符集不同。

如果導出時候使用的NLS_LANG是和源數據庫相同的字符集，那麼導入時就可以設置客戶端NLS_LANG等於導出時使用的字符集，這樣轉換隻發生在數據庫端，而且只發生一次。

例如進行從WE8MSWIN1252到UTF8的轉換，以下是一個常用的參考步驟：

（1）使用NLS_LANG=AMERICAN_AMERICA.WE8MSWIN1252導出數據庫。這時創建的導出文件包含WE8MSWIN1252的數據。

（2）導入時使用NLS_LANG=AMERICAN_AMERICA.WE8MSWIN1252。這時轉換僅發生在insert數據到UTF8的數據庫中。

以上假設的轉換隻在目標數據庫字符集是源數據庫字符集的超集時才能轉換。如果不同，一般就需要進行一些特殊的處理。

再來看一下執行導入時Oracle的一些判斷和處理過程（以Oracle 8i為例）：

（1）首先確定導出數據庫字符集環境。通過讀取導出文件頭，可以獲得導出文件的字符集設置

（2）確定導入session的字符集，即導入Session使用的NLS_LANG環境變量。

（3）通過IMP讀取導出文件。讀取導出文件字符集ID，和導入進程的NLS_LANG進行比較。

（4）如果導出文件字符集和導入Session字符集相同，那麼在這一步驟內就不需要轉換，如果不同，就需要把數據轉換為導入Session使用的字符集。然而這種轉換隻能在單byte字符集之間進行。

來看一個測試，首先設置導入session NLS_LANG為US7ASCII：

E:nls2>set NLS_LANG=AMERICAN_AMERICA.US7ASCII

執行導入操作：

E:nls2>e:oracleora8ibinimp eygle/eygle file=Sus7ascii-Cus7ascii-exp817.dmp fromuser=eygle touser=eygle tables=test

這個導出文件是從US7ASCII數據庫導出，導出客戶端NLS_LANG也是US7ASCII：

Import: Release 8.1.7.1.1 - Production on Fri Nov 7 00:59:22 2003  (c) Copyright 2000 Oracle Corporation.  All rights reserved.    Connected to: Oracle8i Enterprise Edition Release 8.1.7.1.1 - Production  With the Partitioning option  JServer Release 8.1.7.1.1 - Production

這時導入，在DMP文件和NLS_LANG之間不需要進行字符集轉換，但是由於導出文件字符集和數據庫字符集（ZHS16GBK）不同，在數據導入Server時需要進行轉換。

Export file created by EXPORT:V08.01.07 via conventional path  import done in US7ASCII character set and ZHS16GBK NCHAR character set  import server uses ZHS16GBK character set (possible charset conversion)  export server uses UTF8 NCHAR character set (possible ncharset conversion)  . . importing table                         "TEST"          2 rows imported  Import terminated successfully without warnings.

（5）對於多Byte字符集的導入（如UTF8），需要設置導入Session字符集和導出字符集相同，否則就會遇到以下錯誤：IMP-16 "Required character set conversion (type %lu to %lu) not supported"。

E:nls2>set NLS_LANG=AMERICAN_AMERICA.ZHS16GBK

導入Session字符集設置為ZHS16GBK，導入US7ASCII的導出文件：

E:nls2>e:oracleora8ibinimp eygle/eygle file=Sus7ascii-Cus7ascii-exp817.dmp fromuser=eygle touser=eygle    Import: Release 8.1.7.1.1 - Production on Fri Nov 7 00:38:55 2003  (c) Copyright 2000 Oracle Corporation.  All rights reserved.    Connected to: Oracle8i Enterprise Edition Release 8.1.7.1.1 - Production  With the Partitioning option  JServer Release 8.1.7.1.1 - Production    IMP-00016: required character set conversion (type 1 to 852) not supported  IMP-00000: Import terminated unsuccessfully

在從導出文件US7ASCII到導入NLS_LANG設置為ZHS16GBK的過程中，不支持單Byte字符集向多Byte轉換，報出以上錯誤。

（6）導入Session字符集應該是導出字符集的超集，否則，專有的字符將難以正確轉換。

（7）當數據轉換為導入Session字符集設置以後，如果導入Session字符集仍然不同於目標數據庫字符集，那麼數據在插入數據庫之前還需要進行最後一步轉換，這要求目標數據庫字符集是導入session字符集的超集，否則某些專有字符將不能正常轉換。

繼續看上面的兩個過程，這裡有這樣兩個原則：

· 如果NLS_LANG的設置和數據庫相同，那麼數據（在傳輸過程中當然是二進制碼）不經過轉換就直接插入數據庫中。

· 如果NLS_LANG的設置和數據庫不同，那麼數據需要轉換後才能插入數據庫中。

再回頭來看上面的第一個例子：

Export file created by EXPORT:V08.01.07 via conventional path  import done in US7ASCII character set and ZHS16GBK NCHAR character set  import server uses ZHS16GBK character set (possible charset conversion)  export server uses UTF8 NCHAR character set (possible ncharset conversion)  . . importing table                         "TEST"          2 rows imported  Import terminated successfully without warnings.

這時候經過第一步轉換後的數據，US7ASCII到ZHS16GBK丟失首位，原樣插入數據庫，可以看到這時數據庫中存放的就是錯誤的字符（在後面部分做了詳細的轉換）：

E:nls2>sqlplus eygle/eygle  Connected to:Oracle8i Enterprise Edition Release 8.1.7.1.1 - Production  With the Partitioning option  JServer Release 8.1.7.1.1 - Production    SQL> select * from test;  NAME  --------------------  2bJT  test

對於Oracle 10g開始引入得數據泵（expdp/impdp）工具，不再存在以前EXP/IMP的字符集轉換問題，expdp/impdp實際上是通過工具在數據庫服務器上提交一個任務，真正的導出及導入操作都是在數據庫服務器上完成的，這就簡化了Oracle的數據轉儲工作。 原文：https://www.modb.pro/topic/6289（複製到瀏覽器中打開或者點擊「閱讀原文」）