萬盛學電腦網

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

SQL Server 排序規則衝突完整解析:成因、診斷與三種解決方案

什麼是排序規則

排序規則定義字元的位元模式、比較規則與排序順序。SQL Server 2005+ 名稱結構:字元集_大小寫_重音_假名_全半角_二進位,例如 `Chinese_PRC_CI_AS_WS`。

後綴含義值
CI / CSCase Insensitive / Sensitive不區分/區分大小寫
AI / ASAccent Insensitive / Sensitive不區分/區分重音
KI / KSKana Insensitive / Sensitive不區分/區分平假名/片假名
WI / WSWidth Insensitive / Sensitive不區分/區分全形/半形
BIN / BIN2Binary Sort依碼位值比較,效能最佳但非語意排序

衝突觸發條件

跨資料庫、跨伺服器、或同資料庫不同欄位定義不同 Collation 時,進行 `=`, `JOIN`, `UNION`, `LIKE`, `IN` 等字串比較操作,將拋出錯誤:

無法解決 equal to 操作中 "Chinese_PRC_BIN" 和 "Chinese_PRC_CI_AS" 之間的排序規則衝突。

重現範例

CREATE TABLE TableA (TagName NVARCHAR(64) COLLATE Chinese_PRC_BIN);
CREATE TABLE TableB (TagName NVARCHAR(64) COLLATE Chinese_PRC_CI_AS);

SELECT * FROM TableA A JOIN TableB B ON A.TagName = B.TagName; -- 報錯

三大解決方案

方案一:查詢層級指定 COLLATE (臨時、無結構變更)

-- 指定一方轉換為另一方
SELECT * FROM TableA A JOIN TableB B ON A.TagName = B.TagName COLLATE Chinese_PRC_CI_AS;

-- 或使用 DATABASE_DEFAULT 讓引擎自動取資料庫預設排序
SELECT * FROM TableA A JOIN TableB B ON A.TagName COLLATE DATABASE_DEFAULT = B.TagName COLLATE DATABASE_DEFAULT;

優點: 即時生效、無結構變更風險。
缺點: 導致索引失效 (Index Scan)、查詢計畫無法重用、需修改所有受影響查詢。

方案二:修改欄位排序規則 (永久、需鎖定資料表)

ALTER TABLE TableA ALTER COLUMN TagName NVARCHAR(64) COLLATE Chinese_PRC_CI_AS NOT NULL;

注意:

  • 大表執行時會鎖定資料表 (Sch-M 鎖),造成封鎖。
  • 若欄位有索引/主鍵/外鍵/CHECK 約束/計算欄位參考,**必須先刪除相關物件**,修改後重建。
  • 建議維護窗口執行,並備妥回滾腳本。

方案三:資料庫/伺服器層級統一 (根治)

  1. 資料庫預設: `ALTER DATABASE [DBName] COLLATE Chinese_PRC_CI_AS;` (僅影響新建物件)。
  2. 伺服器層級: 重建 `master` 資料庫 (需離線、備份所有資料庫、記錄登入/作業/連結伺服器等)。

診斷與排查腳本

-- 1. 查詢伺服器/資料庫/欄位目前排序規則
SELECT SERVERPROPERTY('Collation') AS ServerCollation;
SELECT DATABASEPROPERTYEX('DBName', 'Collation') AS DBCollation;
SELECT name, collation_name FROM sys.columns WHERE object_id = OBJECT_ID('TableName');

-- 2. 尋找所有非預設排序規則的欄位
SELECT OBJECT_SCHEMA_NAME(o.object_id) AS SchemaName,
       o.name AS TableName,
       c.name AS ColumnName,
       c.collation_name AS ColumnCollation
FROM sys.columns c
JOIN sys.objects o ON c.object_id = o.object_id
WHERE c.collation_name IS NOT NULL
  AND c.collation_name <> DATABASEPROPERTYEX(DB_NAME(), 'Collation')
  AND o.type = 'U';

最佳實務建議

  1. 新專案統一規劃 Collation (建議 `Latin1_General_100_CI_AS_SC` 或 `Chinese_PRC_CI_AS`),文件化納入架構標準。
  2. 跨資料庫/伺服器查詢,強制在 `JOIN/WHERE` 條件加上 `COLLATE DATABASE_DEFAULT`。
  3. 遷移/合併資料庫前,先用上述腳本盤點 Collision 風險。
  4. 使用含補充字元支援的 `_SC` 後綴排序規則 (SQL 2012+) 正確處理 Emoji/生僻字。
copyright © 萬盛學電腦網 all rights reserved