萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> SQL Server 教學

撰寫跨語言可攜式 Transact-SQL 程式碼:日期、Unicode 與排序規則實戰

前言:國際化資料庫開發的關鍵課題

隨著 SQL Server 7.0 引入 Unicode 資料型別、排序規則 等國際化功能,以及 SQL Server 2000 新增資料行層級排序規則,撰寫可在多語言環境間移植的 T-SQL 程式碼已成為可能。然而,許多開發者仍因對日期格式、字串比較、排序規則行為缺乏深入理解,而寫出在特定語系下失效的程式碼。本文將系統性探討日期處理、Unicode 採用、排序規則相依性三大核心議題,提供實用的可攜式寫法與最佳實務。

建議先閱讀 SQL Server 線上叢書主題「Writing International Transact-SQL Statements」(編寫國際性 Transact-SQL 陳述式)。

一、日期處理:避開語系陷阱

1. 週起始日與 @@DATEFIRST

不同文化對「一週第一天」定義不同:

SET LANGUAGE us_english
SELECT DATEPART(dw, '20060606')  -- 傳回 3 (星期二)

SET LANGUAGE british
SELECT DATEPART(dw, '20060606')  -- 傳回 2 (星期二)

原因在於 @@DATEFIRST 預設值隨語系異動 (us_english=7/週日, british=1/週一)。

解法 A:程序開頭強制設定

SET DATEFIRST 7  -- 強制週日為週起始,後續 DATEPART(dw) 行為一致

解法 B:規格化計算 (語系無關)

DECLARE @ndf_dw INT
SET LANGUAGE us_english
SELECT @ndf_dw = (@@DATEFIRST + DATEPART(dw, '20060606')) % 7  -- 結果 2

SET LANGUAGE british
SELECT @ndf_dw = (@@DATEFIRST + DATEPART(dw, '20060606')) % 7  -- 結果 2 (相同)

2. 日期名稱 (DATENAME) 的語系相依性

SELECT DATENAME(dw, '20060606')
-- us_english → 'Tuesday'
-- French     → 'Mardi'

鐵則:比較/篩選月份或週日時,永遠使用數值日期部份 (MONTH(), DATEPART(dw, ...)),勿依賴字串名稱。

3. 顯示層與資料層分離

  • 二層架構:可直接讓 SQL Server 傳回當地語系名稱 (需確認登入語系正確)
  • 多層/連接池架構:回傳二進位 datetime 值,由用戶端/中介層依使用者喜好格式化,避免登入語系不一致導致顯示錯誤

4. DML 與比較陳述式的日期常數寫法

方法範例適用性
RPC 參數化 (最推薦)SqlCommand.Parameters.Add("@d", SqlDbType.DateTime).Value = dateVal所有語言、所有用戶端技術 (ADO.NET、ODBC、OLE DB)
ODBC 轉義子句{d '2006-06-06'}, {ts '2006-06-06 12:30:00'}任何支援 ODBC 語法的用戶端
ISO 8601 無分隔格式'20060606' (yyyymmdd)所有語系皆正確解譯
CONVERT 指定樣式CONVERT(datetime, '2006-06-06', 120)明確指定來源/目標格式,消除歧義

二、Unicode:字元資料的通用解法

資料型別對應

非 UnicodeUnicode (建議)備註
charnchar固定長度
varcharnvarchar變動長度
textntext大量文字 (SQL 2005+ 建議用 nvarchar(max))

N 前置字元:不可或缺的關鍵

CREATE TABLE #t1 (c1 NCHAR(1))
INSERT #t1 VALUES (N'Ω')   -- 正確:希臘字母 Omega
INSERT #t1 VALUES ('Ω')    -- 錯誤:轉為預設字碼頁,變成 'O'
SELECT * FROM #t1
-- 結果:Ω, O

規則:所有 Unicode 字串常數必須加上大寫 N 前置字元,否則 SQL Server 會先轉換為資料庫預設字碼頁再存入,導致字元遺失。

參數化查詢的優勢

使用 SqlCommand 參數傳遞 Unicode 字串時,參數本身攜帶型別資訊,無需 N 前置字元,RPC 事件天然支援型別安全。

三、排序規則:字串比較的隱形陷阱

案例:十六進位字串轉整數函數的排序規則 Bug

CREATE FUNCTION hexnum(@hexstr VARCHAR(10)) RETURNS BIGINT AS BEGIN
  IF LEFT(@hexstr,2) IN ('0x','0X') SET @hexstr = SUBSTRING(@hexstr,3,10)
  DECLARE @i INT, @res BIGINT, @l INT, @c CHAR
  SELECT @i=1, @l=LEN(@hexstr), @res=0
  IF @hexstr IS NULL OR @l=0 RETURN NULL
  WHILE @i <= @l BEGIN
    SET @c = UPPER(SUBSTRING(@hexstr,@i,1))
    IF @c < '0' OR @c > 'F' RETURN NULL  -- ❌ 排序規則相依!
    -- ...
  END
  RETURN @res
END

在某些排序規則下,字元 'G' 可能排在 '0'~'F' 之間,導致無效字元通過檢查。測試 SELECT dbo.hexnum('0xG') 竟回傳 145 而非 NULL。

修正:使用 ASCII 碼比較 (排序規則無關)

DECLARE @ascii0 INT = ASCII('0'), @asciiF INT = ASCII('F')
-- 在迴圈中:
IF NOT ASCII(@c) BETWEEN @ascii0 AND @asciiF RETURN NULL

完整修正版函數見文末程式碼區塊。

案例二:不分大小寫比較的排序規則陷阱

-- ❌ 錯誤:假設 LOWER/UPPER 在所有語系行為一致
SELECT au_id FROM authors WHERE LOWER(au_lname) = LOWER(@au_lname)
-- 土耳其語:'I' 小寫是 'ı' (無點),'İ' 大寫是 'i' (有點),破壞比較邏輯

效能與正確性雙贏:資料行層級排序規則

CREATE TABLE authors (
  au_lname VARCHAR(40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
  -- CI = Case Insensitive, AS = Accent Sensitive
)
  • 查詢直接 WHERE au_lname = @au_lname 即可不分大小寫比對
  • 利用索引,避免函數包裹資料行導致掃描
  • 無需計算欄位 + 索引的變通方案
核心觀念:在支援排序規則的語言中,勿嘗試用 T-SQL 手寫排序邏輯,直接宣告資料行排序規則才是正道。

完整修正版 hexnum 函數

CREATE FUNCTION dbo.hexnum(@hexstr VARCHAR(10)) RETURNS BIGINT AS BEGIN
  IF LEFT(@hexstr,2) IN ('0x','0X') SET @hexstr = SUBSTRING(@hexstr,3,10)
  DECLARE @i INT, @res BIGINT, @l INT, @c CHAR, @ascii0 INT, @asciiF INT
  SELECT @i=1, @l=LEN(@hexstr), @res=0, @ascii0=ASCII('0'), @asciiF=ASCII('F')
  IF @hexstr IS NULL OR @l=0 RETURN NULL
  WHILE @i <= @l BEGIN
    SET @c = UPPER(SUBSTRING(@hexstr,@i,1))
    IF NOT ASCII(@c) BETWEEN @ascii0 AND @asciiF RETURN NULL
    SET @res = @res + CAST(1.0 AS BIGINT) * 
      CASE WHEN ISNUMERIC(@c)=1 THEN CAST(@c AS INT) ELSE ASCII(@c)-55 END
      * POWER(16, @l-@i)
    SET @i = @i + 1
  END
  RETURN @res
END

總結:可攜式 T-SQL 開發檢核清單

  • ☐ 所有字元資料型別使用 nchar/nvarchar/ntext
  • ☐ Unicode 字串常數加上 N 前置字元
  • ☐ 日期常數採 yyyymmdd 或參數化/RPC 傳遞
  • ☐ 避免 DATENAME、DATEPART(dw) 用於邏輯判斷,改用規格化計算或 SET DATEFIRST
  • ☐ 字串比較/篩選勿假設 ASCII 排序,改用 ASCII()、COLLATE 或資料行層級排序規則
  • ☐ 大小寫不敏感比較透過 CI 排序規則實現,而非 LOWER/UPPER 包裹

掌握這些原則,配合 SQL Server 2000 完善的國際化工具集,即可撰寫真正跨語言、跨地區部署的強健 T-SQL 程式碼。

延伸閱讀

  • The Guru's Guide to SQL Server Architecture and Internals (Ken Henderson)
  • The Guru's Guide to SQL Server Stored Procedures, XML, and HTML
copyright © 萬盛學電腦網 all rights reserved