多國語系的資料處理與整合技術探討

  • 發行項

**作者：**台灣微軟資料庫講師楊志強

本頁內容

前言
瞭解 Unicode 與多國語系資料處理問題
介紹 SQL Server 處理多國語系的能力
介紹資料處理工具對多國語系統使用技巧
介紹前端開發程式撰寫處理多國語系技巧
結論

前言

全球化趨勢下台灣企業逐漸走向國際化，企業間資料的交換處理與程式開發都將面臨如何配合地區性的不同而調整展示語系與如何在同一資料庫中同時處理多種語系資料交換與查詢，這些都是台灣企業面對國際化，資料處理上的重大議題，文章中將介紹 SQL Server 2000 如何處理多國語系的資料能力，包括定序的設定工具的使用技巧，再搭配前端應用程式的開發說明，逐步引導大家如何在多種資料來源的環境中，正確地處理資料儲存與展示。

瞭解 Unicode 與多國語系資料處理問題

國際組織 Unicode 學術學會針對每個字元提供唯一的字碼點 (Code Point)，其設計目的，是為了涵蓋世界上所有語言的字元。也因此，Unicode 為每個字元提供了一個唯一的編碼，無關於作業平台、程式與語言。Unicode 標準已經被這些工業界的領導們所採用，例如：Apple, HP, IBM, Microsoft, Oracle, SAP, Sun, Sybase, Unisys …，也列入產品的標準如資料庫軟體的 SQL Server , IBM DB2 , Oracle ,Sybase…，作業平台 Microsoft Windows CE, NT, 2000, XP …程式語言的 Java / Visual Studio…，並且，Unicode 是實現 ISO/IEC 10646 的主要方式。Unicode 標準的出現和支援它工具的存在，是近來全球軟體技術最重要的發展趨勢。

Unicode 與編碼的關係

Unicode 不指定字元在記憶體、資料庫與網頁如何呈現，每個字元的顯示必須透過編碼方式進行處理，目前主要使用下列三種編碼結構：UCS-2、UTF-16 和 UTF-8。

UCS-2

在這個機制之下，所有的 Unicode 字元都使用兩個位元組來儲存，透過兩個位元組總共可以提供 65536(2^8*2^8) 的字碼點，UCS-2 是 Microsoft NT 4.0 / SQL 7.0 / SQL 2000 主要編碼機制。如果以兩個位元組表示繁體中文的『強』則表示成 U+5F37，其他文字如簡體中文、日語與韓語在 UCS-2 表示方法如下：

Dd125512.multi01(zh-tw,TechNet.10).gif 表示成 U+7F51

Dd125512.multi02(zh-tw,TechNet.10).gif 表示成 U+3048

Dd125512.multi03(zh-tw,TechNet.10).gif 表示成 U+CC45

UTF -16

UTF-16 針對某些字元使用兩個位元組來進行儲存，其他字元可能用到四個位元組，這是 Windows 2000 使用的主要編碼機制，在 UCS-2 中包含的字元都是屬於 UTF-16 的子集合，其他某些語言的特殊字，如中文字中很罕見，且多半出現在古典文學作品中，或具有歷史意義的字元等，這些字元會被視為替代 (surrogate) 字元，直接以多個位元組儲存在 UTF-16 編碼結構之下，如此可以增加識別 1,048,576 個字元(新增範圍從 U+D800 ~ U+DFFF，1024 Low 字元，1024 High 字元)。SQL Server 2000 使用 UCS-2 儲存，它會將特殊的替代字元視為兩個未定義的 Unicode 字元，在儲存的過程中，當兩兩配對在一起時，便可定義替代字元。如此一來，SQL Server 2000 儲存替代字元時就不會有遺失或損毀的風險。

UTF-8

第三種編碼結構是 UTF-8，它以一到四個位元組之間的可變動長度來儲存 Unicode 資料。許多資料庫程式，像是由 Oracle 和 Sybase 開發的程式，都使用這種編碼結構，SQL Server 2000 針對 XML 資料儲存亦使用 UTF-8 編碼。UTF-8 資料字元沒有固定長度的緣故，所以在處理以下的資料交換時，將有影響

  • COM 元件使用 UTF-8 資料，須額外轉換成 UCS-2/UTF-16

  • Windows NT / 2000 核心都是使用 UCS-2 / UTF-16，使用 UTF-8 儲存格式必須透過額外轉換

  • UTF-8 資料屬於變動資料格式，進行排序、比較和其他字串作業時比 UCS-2 慢

  • UTF-8 資料屬於變動資料格式，需要額外的儲存空間與記憶體的使用

介紹 SQL Server 處理多國語系的能力

儲存 Unicode 資料

SQL Server 2000 處理多國語系時，根據 SQL-92 規範使用 N 代表 National 資料型態，每個字元兩個 Bytes，不受單一定序(Collation)影響可同時支援儲存多國語言，透過下列資料型別，提供定義資料表進行 Unicode 字元資料處理。

NCHAR

固定長度的字元資料，不能超過 4,000 字元

NVARCHAR

變動長度的字元資料，不能超過 4,000 字元

NTEXT

長度可高達 2^30 - 1 (1,073,741,823)

在 SQL Server 2000 中字串函數的處理，可以搭配函數 DATALENGTH 計算出字串位元組數目，LEN 函數計算出字元數。當在 SQL Server 中處理 Unicode 資料時候，特別注意要使用 N 表示 Unicode 的處理，例如新增資料到定義成 NCHAR/NVARCAHR/NTEXT 的欄位時，必須使用以下的寫法

INSERT INTO MY_EMPLOYEES VALUES (N’中文’,N’中文’)

儲存 Non-Unicode 資料

SQL Server 2000 處理一般 Non-Unicode 字元時，每個字元所佔空間與所支援的語系有關，由定序的字碼頁決定。每個字元所佔空間為 1-2 個位元組，例如亞洲語系每個字元使用 2 個位元組，一般英文語系每個字元使用 1 個位元組。一般儲存 Non-Unicode 的字串時可以使用以下的資料型態

CHAR

固定長度的字元資料，不能超過 8,000 字元

VARCHAR

變動長度的字元資料，不能超過 8,000 字元

TEXT

長度可高達 231 - 1 (2,147,483,647 )

一般當 Unicode 資料透過一般字串，插入其中一個非 Unicode 資料型別資料行時，SQL Server 會將資料利用 Windows API 中的 WideCharToMultiByte 與 MultiByteToWideChar 等函數轉換資料型別與資料行定序相關的字碼頁。當字碼頁無法表示字元時，就會以問號 (?) 代替，表示資料已經遺失。資料中若有非預期的字元或問號出現，表示您的資料在某個層次已經從 Unicode 轉換成 Non-Unicode，而在轉換的過程中造成了字元的遺失。再者一般字碼頁決定所支援的語系，例如當字碼頁為 950 表示支援中文的語系，而有些地區僅能選用字碼頁為 0 的 Unicode 字碼支援方式，例如北印度語等。其他相關的字碼頁如下表所示：

語言

字碼頁

簡體中文

936

繁體中文

950

日文

932

韓文

949

Unicode 與 Non-Unicode 儲存方式與效能的比較

Unicode 每個字元使用 2 bytes 進行儲存，所以有以下的特點：

  • 非雙位元組字元集 (DBCS) 仍使用 2 bytes 編碼，需要多一點空間

  • ODBC (3.6 版或更早的版本) 和資料程式庫 API 無法識別 Unicode

  • 亞洲語言使用Unicode固定 2 位元組編碼，比特定字碼頁指定進行處理有效率，主要原因是字碼頁指定下的資料儲存會有混合長度差異

Non-Unicode字元視 Code Page 決定字元所佔為 1~ 2 bytes

  • 中文、日文等亞洲語言使用 Non-Unicode 時，使用雙位元組字元集 (DBCS) 來儲存字元

  • 亞洲語言使用 Non-Unicode 和 Unicode 間執行儲存作業幾乎沒有差別

  • 非亞洲語言進行 Non-Unicode 排序比 Unicode 快 30%

多國語系對日期時間的處理方式

SQL Server 對日期時間的儲存資料型態分成：DATETIME/SMALLDATETIME 兩種格式，並可透過 CONVERT 函數轉換成各種型態的輸出格式。其中，DATETIME 資料格式支援日期範圍從公曆 (Gregorian calendar) January 1, 1753 到 December 31, 9999，時間準確度為 (1/300) 秒，在資料庫內部儲存以兩個 4 bytes 整數 (Integer) 儲存值。SMALLDATETIME 資料格式支援範圍比較小，從公曆 January 1, 1900 到 June 6, 2079 ，時間準確度以 29.999 秒 區隔，也就是以分為準確度，資料庫內部儲存以兩個 2 bytes 整數 (Integer) 儲存值。

在日期時間轉換成各個國家表示方式時，可以透過 CONVERT() 函數進行轉換，使用方法如下

SELECT CONVERT(data_type , 時間 , 格式)

例如格式 111 表示 Japan 格式，也就是 yyyy/mm/dd，其中也支援回教太陰曆的轉換格式，但是特別注意輸出字串時，必須使用 Unicode 表達，否則會出現轉換失敗的 ??? 號，相關畫面可以參閱下圖所示。

Dd125512.multi04(zh-tw,TechNet.10).gif

定序的觀念

早期的 SQL Server 6.5 尚無支援 Unicode 的字元，透過字碼頁決定所支援的語系處理功能，每一個 SQL Server 6.5 執行個體對應一個字碼頁，例如選擇 CP950 時可以支援中文。SQL Server 7.0 每一個 SQL Server 執行個體支援一個 Unicode 的定序與 Non-Unicode 的定序，其中 Non-Unicode 包含字碼頁與排序順序方式。SQL Server 2000 將字碼頁與定序合成單一設定，可以在執行個體安裝時候選定，也可以在資料庫建立時選定，更可在資料表建立時候個別欄位獨立指定，或是在T-SQL運算式中使用來決定字串的運算方式，例如排列方式、字元大小、腔調差異與全半形比較等。英語語系可以透過定序決定字母的排序、大小寫區隔，非英語語系決定多樣化的資料排序，例如按照筆畫、注音、字元全半形、假名等，定序更攸關索引建立時索引頁的資料排序方式。

SQL Server 2000 中有兩種定序類型可以選擇：Windows 定序和 SQL 定序。Windows 定序根據與 Windows 地區設定相關的規則，來定義排序規則。要進行這項工作，SQL Server 會複製 Windows Server 的排序規則，並將它們套用到對應的 Windows 定序中，透過這個方法，SQL Server 中給定 Windows 2000 定序的字串比較作業，會與執行相同定序之 Windows 版的比較作業相容。但是，因為 Windows Server 2000 之後的 Windows 版本，如 Windows XP 和 Windows Server 2003 使用不同的排序資料表，因此安裝在這些 OS 上 SQL Server 的 Windows 定序，可能會顯示與主機端 OS 不同的排序方式。SQL 定序與 Windows 地區設定無關，提供的目的是與 SQL Server 早期版本的排序順序相容。

透過內建系統函數 COLLATIONPROPERTY 可以判斷出每一個定序的支援字碼頁與地區編號，如果所找出的字碼頁若為0時，表示該定序僅支援 Unicode 的字串處理方式。下圖展示透過內建函數判斷定序所支援的字碼頁與地區編號。

Dd125512.multi05(zh-tw,TechNet.10).gif

四個定序運用的範圍

SQL Server 2000 的定序的使用方式非常具有彈性，包含以下四個範圍：

  • 系統安裝( Server Collation )

    透過 SELECT SERVERPROPERTY('collation')查詢出來執行個體所選定的定序設定，如果當執行個體安裝完畢之後，需要更改執行個體的定序時，必須透過公用程式 RebuildM.exe，才可以重新修改執行個體的定序設定。內定該公用程式所在位置為 C:\Program Files\Microsoft SQL Server\80\Tools\Binn\ rebuildm.exe，作業設定如下。

    Dd125512.multi06(zh-tw,TechNet.10).gif

    啟動 Rebuildm.exe 程式畫面

    Dd125512.multi07(zh-tw,TechNet.10).gif

    選擇定序的設定畫面

  • 資料庫建立 (Database Collation)

    第二階層設定定序的層級是資料庫建立時可以選定，當建立資料庫時內定使用執行個體的定序設定，若需要特殊定序則必須在建立資料庫時，透過下列畫面選定。當完成資料庫建立後，可以透過 SELECT DATABASEPROPERTYEX(‘DBName, ‘collation’) 查詢出來所屬的資料庫的定序設定值。一般情況在 Enterprise Manager 的畫面時，是無法直接修改既定的資料庫定序值，但可以透過 T-SQL 的指定進行資料庫定序的更改，修改指令如下：

    ALTER DATABASE Northwind COLLATE Chinese_Taiwan_Bin

    Dd125512.multi08(zh-tw,TechNet.10).gif

    一般建立完成後的資料庫定序，無法直接從 Enterprise Manager 畫面修改

  • 資料表欄位 (Column Collation)

    第三種層級的定序設定就是在資料表建立時，個別欄位中指定不同的定序，透過這樣的彈性設定可以讓同一個資料表中在一般的 char/varchar/text 資料型態，支援不同語系的字串資料。例如一個跨國企業的訂單彙總表，可以選擇第一個欄位存放簡體中文資料、第二欄位存放繁體中文資料，第三個欄位存放日語資料，透過這樣的彈性設定可以讓資料彙總更具彈性與靈活。下圖可以展示出不同資料欄位定序與多國語系資料設定的技巧。

    Dd125512.multi09(zh-tw,TechNet.10).gif

    第一個欄位屬於 Chinese_PRC_Stroke_CI_AS 的定序設定

    Dd125512.multi10(zh-tw,TechNet.10).gif

    第二個欄位屬於 Chinese_Taiwan_Stroke_CI_AS 的定序設定

    Dd125512.multi11(zh-tw,TechNet.10).gif

    第三個欄位屬於 Japanese_CI_AS 的定序設定

    Dd125512.multi12(zh-tw,TechNet.10).gif

    透過 Query Analyzer 可以直接瀏覽不同定序欄位的資料

  • 運算式 ( Expression Collation)

    利用 Collate 關鍵字可以在 T-SQL 所撰寫的 Script 中指定定序的使用，例如透過定序指定原先不分字元大小寫的字串，轉換成區分成字元的大小寫;也可以透過定序指定中文字的排序方式，可以依照注音符號或是按照筆畫進行排序等。以下的範例將展示如何透過定序在運算式中改變資料的排序方式，原先的資料如果是一般的 Chinese_Taiwan_Stroke_CI_AS 時，資料輸出是按照比較次序排列，如以下畫面

    Dd125512.multi13(zh-tw,TechNet.10).gif

    若是透過定序在排序時改變成按照注音符號進行排序，則出來的結果將有明顯的不同，將依照ㄅㄆㄇㄈ等進行字的排序，如下圖所示

    Dd125512.multi14(zh-tw,TechNet.10).gif

介紹資料處理工具對多國語系統使用技巧

  • DTS (資料轉換服務)

    當透過 DTS 匯入字元資料時，在伺服器端定義 Unicode 可確保匯入的資料會完整無缺的接收，並適當的儲存。此外，當來源或目的不是 SQL Server 的執行個體時，您必須依靠在呼叫端 ODBC 驅動程式或 OLE DB Provider 的轉譯運作方式，例如如果要將法文 OEM 字碼頁資料，匯入到 SQL Server 1252 字碼頁，且來源電腦未正確執行 1252 字碼頁的轉換，可能造成資料損毀。當使用 Unicode 資料時，「DTS 匯入/匯出精靈」包含特定對話方塊，可能無法正常顯示 Unicode 資料，但是在傳輸期間不會損毀資料。此外透過 Copy SQL Server Objects Task 可以處理不同定序間轉換的問題，可以將資料按照原本的型態進行複製。

  • Bulk Insert Program 公用程式 / Bulk Insert 指令

    複製非 Unicode 資料時，若要防止資料遭到損毀，還可透過 SQL Server BCP 公用程式來達成，方法如下：使用 bcp 將資料匯出到一般檔案。藉由指定 –w 或 –N 命令列旗標 (參閱如下)，將該資料會在處理程序中轉換成 Unicode。但是，這個方法仍會遺失來自來源資料但不存在於目的字碼頁的字元。

    BCP DB.OWNER.XXX out C:\XXX.txt -w -T -Ssvrname

    BCP DB.OWNER.XXX out C:\XXX.txt -N -T -Ssvrname

    旗標

    意義

    附註

    -C xxx

    字碼頁規範

    xxx 指定資料轉換成 ANSI、OEM、RAW (不轉換而直接複製) 或特定的字碼頁號碼。

    -N

    Unicode 原生格式

    針對所有非字元資料，將資料轉換成原生資料庫資料型別，並針對所有字元資料，將資料轉換成 Unicode 字元格式。

    -w

    Unicode 字元格式

    針對所有資料行，將資料轉換成 Unicode 字元資料格式。

    透過 T-SQL 的 Bulk Insert 指令時，可以透過 CODEPAGE 與 DATAFILETYPE 的指定，進行多國語系字串資料的搬移，指令說明如下。

        BULK INSERT[['database_name'.]['owner'].]{'table_name' FROM 'data_file'}
     [WITH
      ( 
    [BATCHSIZE[=batch_size]]
    [[,]CHECK_CONSTRAINTS]
    [[,]CODEPAGE[='ACP'|'OEM'|'RAW'|'code_page']]
    [[,]DATAFILETYPE[={'char'|'native'|'widechar'|'widenative'}]]

    字碼頁的值

    描述

    OEM (這是預設的運作方式)

    資料匯入到 SQL Server 執行個體時，char、varchar 或 text 資料型別的資料行，會從系統 OEM 字碼頁轉換到 SQL Server 字碼頁，相同地，從 SQL Server 執行個體匯出資料時亦然。

    ACP

    資料匯入到 SQL Server 執行個體時，char、varchar 或 text 資料型別的資料行，會從 ANSI/Windows 字碼頁 (ISO 1252) 轉換到 SQL Server 字碼頁，相同地，從 SQL Server 執行個體匯出資料時亦然。

    RAW

    不會發生字碼頁間轉換的情形，因此這是最快的選項。

    <值>

    這代表特定的字碼頁號碼 (例如 850)。

  • Replication (複寫)

    透過 Replication 機制進行資料複寫時，可以在發行集的發行項中屬性頁中[定序]，進行指明使用定序相容處理機制，如此資料進行複寫時，可以完整的進行移轉，設定畫面如下所示。

    Dd125512.multi15(zh-tw,TechNet.10).gif

介紹前端開發程式撰寫處理多國語系技巧

  • Win32 應用程式

    在 Microsoft Visual Basic 應用程式中，字元字串以 UCS-2 編碼結構處理，因此，在這些應用程式和 SQL Server 之間，不需要明確地指定編碼結構的轉換，VB.NET 本身支援多國語系資料的展示，亦無須特別設定。

  • Web 應用程式

    對於以 Web 為基礎的應用程式，您在用戶端 HTML 網頁的 META 屬性之下，指定 CHARSET 碼。例如，如果用戶端的 Unicode 編碼結構是 UTF-8，則指定 CHARSET = utf-8。在伺服器端，使用 session.codepage 屬性或 @codepage 屬性指示詞，指定用戶端的編碼結構。例如，codepage=65001 指定 UTF-8 編碼結構。如果您遵守這些方法，Internet Information Services (IIS) 5.0 或更新版本會正確地將 UTF-8 轉換成 UCS-2，並轉換回來，不需要執行其他動作。

    Dd125512.multi16(zh-tw,TechNet.10).gif

    若需處理這個轉換的額外選項，可以在 https://support.microsoft.com/?kbid=232580 取得。

結論

多國語系資料處理的重點上必須瞭解資料庫定序影響層面有執行個體安裝設定，更改時必須重建 Master 資料庫；資料庫建立時設定，若要進行修改可以透過 Alter Database 完成；資料表欄位設定，可以運用在多樣字元處理方式或是在 SQL 運算式中使用，可以變化資料展示與查詢方式。對於 Unicode 字串的使用可以在多國語系中簡單化資料的交換與處理，使用 Unicode 必須注意字元的所佔空間，在應用程式的使用上面透過資料庫多國語系功能簡單化前端程式開發。