萬盛學電腦網

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

SQL Server 字串大小寫敏感比較:二進位轉換與 Collation 兩大實戰方案

SQL Server 預設安裝排序規則通常為 **不區分大小寫 (Case-Insensitive, CI)**,如 `Chinese_PRC_CI_AS`。密碼驗證、代碼比對、區分大小寫代碼表等場景需**區分大小寫 (Case-Sensitive, CS)**。本文詳解兩大標準方案:`VARBINARY` 轉換法與 `COLLATE` 指定排序規則法,並完整解析 Collation 後綴含義。

方案一:轉換為 VARBINARY 進行二進位比較

原理:字元編碼 (ASCII/Unicode) 下,大小寫字元碼值不同 (如 `'a'=0x61`, `'A'=0x41`)。轉為 `VARBINARY` 後直接比較二進位位元組序列,天然區分大小寫、重音、寬度等所有差異。

-- 範例:驗證密碼 'Admin' (區分大小寫)
SELECT * 
FROM T_User 
WHERE CAST(Password AS VARBINARY) = CAST('Admin' AS VARBINARY);

優缺點

  • ✅ 簡單、直觀、無需修改資料表/欄位屬性。
  • ✅ 臨時查詢、動態 SQL 適用。
  • ❌ **無法使用索引** (SARGable 失效) —— `CAST` 導致索引掃描/全表掃描,大表效能極差。
  • ❌ 比較語義固定為二進位 (Code Point 比較),無法自訂語言規則 (如德語 ß=ss、土耳其語 İ/i)。

方案二:指定 COLLATE 排序規則 (推薦用於生產查詢)

在比較運算式中顯式指定 **CS (Case-Sensitive)** 排序規則,優化器**可使用索引** (若欄位預設排序規則相容或建立相同排序規則索引)。

-- 範例:用戶名不區分大小寫,密碼區分大小寫
SELECT * 
FROM T_User 
WHERE UserName = 'admin'           -- 使用資料庫/欄位預設 CI 排序規則
  AND Password COLLATE Chinese_PRC_CS_AS_WS = 'Admin';  -- 指定 CS 排序規則

Collation (排序規則) 後綴完整解碼

格式:`<語言/字集>____[_<版本>_]`

後綴全稱含義範例差異
CI / CSCase Insensitive / Sensitive**是否區分大小寫**CI: 'a'='A' ; CS: 'a'≠'A'
AI / ASAccent Insensitive / Sensitive**是否區分重音/變音符號**AI: 'é'='e' ; AS: 'é'≠'e' (法文/德文關鍵)
KI / KSKana Insensitive / Sensitive**是否區分日語平假名/片假名**KI: 'ひらがな'='ヒラガナ' ; KS: 不等
WI / WSWidth Insensitive / Sensitive**是否區分全形/半形 (寬度)**WI: 'A' (全形) = 'A' (半形) ; WS: 不等
BIN / BIN2Binary / Binary Code Point**二進位比較** (最快、最嚴格,忽略所有語言規則)BIN: 舊版排序邏輯;BIN2: 真正 Code Point 比較 (推薦)
VSS / _100 / _140Variation Selector Sensitive / 版本支援 Unicode 變體選擇器 / 排序規則版本 (SQL 2008/2012/2017+)_140 為 SQL 2017+ 最新版,支援補充字元

常見中文排序規則選擇指南

  • `Chinese_PRC_CI_AS` (預設):簡體中文、不區分大小寫、區分重音。**最通用**。
  • `Chinese_PRC_CS_AS`:簡體中文、**區分大小寫**、區分重音。**密碼、代碼驗證首選**。
  • `Chinese_PRC_CS_AS_WS`:加上區分全半形。**代碼含全半形混雜時**。
  • `Chinese_PRC_BIN2`:純二進位比較,**效能最好、語義最嚴格**,適合精確匹配、雜湊比對、二進位資料。
  • `Chinese_Taiwan_Stroke_CI_AS`:繁體中文、筆畫排序、不區分大小寫、區分重音。

三、 進階應用:欄位/資料庫/伺服器層級設定

1. 欄位層級指定 (建表/修改表)

CREATE TABLE T_User (
    UserName NVARCHAR(50) COLLATE Chinese_PRC_CI_AS,   -- 不區分大小寫
    Password NVARCHAR(100) COLLATE Chinese_PRC_CS_AS   -- 區分大小寫
);

查詢時無需 `COLLATE` 子句,欄位自帶排序規則。

2. 資料庫層級預設

CREATE DATABASE MyDB COLLATE Chinese_PRC_CS_AS; -- 整庫預設區分大小寫
-- 或修改現有庫 (需單用戶模式)
ALTER DATABASE MyDB COLLATE Chinese_PRC_CS_AS;

3. 伺服器層級 (安裝時決定,極難變更)

影響 `master`、`tempdb`、變數、游標、GOTO 標籤等系統物件比較行為。**安裝時務必審慎選擇**,生產環境強烈建議預設 `CI_AS`,查詢層級按需 `COLLATE CS`。

四、 效能與最佳實踐總結

場景推薦方案理由
臨時腳本、小表、動態 SQL`CAST(... AS VARBINARY)`寫法快、無索引需求
生產查詢、大表、需走索引`COLLATE Chinese_PRC_CS_AS` (或欄位預設 CS)**SARGable,可用索引 Seek**,效能穩定
整張表/資料庫預設區分大小寫欄位/資料庫定義 `COLLATE ..._CS_...`一次設定、處處生效,避免漏加 COLLATE
最高效能、純二進位比對 (雜湊、Token、Base64)`COLLATE Chinese_PRC_BIN2` 或 `VARBINARY`無語言規則開銷,CPU 最省

結語

**區分大小寫本質是排序規則屬性**。生產環境**首選 `COLLATE Chinese_PRC_CS_AS` (或 `_BIN2`)**,保留索引可用性;`VARBINARY` 僅作臨時/小表備用。建表時對敏感欄位 (密碼、Token、代碼) 直接指定 `CS`/`BIN2` 排序規則,是架構層面的最佳防禦。切記:**無 `ORDER BY` 不保證順序;無 `COLLATE` 不保證大小寫語義**。

copyright © 萬盛學電腦網 all rights reserved