監控系統使用位元運算在 INT (32 位) 中儲存多狀態:
聚合時需 MAX 取最嚴重狀態,超出 INT 範圍需暫用 BIGINT 運算,再轉回 INT 存儲:
SELECT CAST(MAX(CAST(StateFlags AS BIGINT) | (Level << 30)) AS INT) FROM MonitoringLog;
**報錯**:「將 expression 轉換為數據類型 int 時出現算術溢出錯誤。」因結果設定了 Bit 31,超出有號 INT 範圍 (-2,147,483,648 ~ 2,147,483,647)。
INT 最高位為符號位:0=正、1=負。0x80000000 (2,147,483,648) 在 INT 中代表 **-2,147,483,648**。CAST/CONVERT 從 BIGINT 到 INT 會檢查數值範圍,超出即拋錯,**不會自動截斷**。CREATE FUNCTION dbo.BigintToInt (@Value BIGINT)
RETURNS INT
AS
BEGIN
-- 判斷 INT 符號位 (Bit 31) 是否被設定
IF @Value & 0x80000000 <> 0
-- 設定了符號位:保留低 32 位,高 32 位全置 1 (符號擴展),形成負數補碼
RETURN @Value & 0xFFFFFFFF | 0xFFFFFFFF00000000;
-- 未設定符號位:直接取低 32 位
RETURN @Value & 0xFFFFFFFF;
END;
SELECT dbo.BigintToInt(0x00000000) AS Zero; -- 0
SELECT dbo.BigintToInt(0x7FFFFFFF) AS MaxPos; -- 2147483647
SELECT dbo.BigintToInt(0x80000000) AS MinNeg; -- -2147483648
SELECT dbo.BigintToInt(0xFFFFFFFF) AS NegOne; -- -1
SELECT dbo.BigintToInt(0x12345678) AS Normal; -- 305419896
SELECT dbo.BigintToInt(0x80000001) AS NegLarge; -- -2147483647
SELECT dbo.BigintToInt(0x100000000) AS Overflow33; -- 0 (低32位為0,符號位0)
SELECT dbo.BigintToInt(0x180000000) AS Overflow33s; -- -2147483648 (低32位0x80000000,符號位1)
SELECT dbo.BigintToInt(MAX(CAST(StateFlags AS BIGINT) | (Level << 30))) AS AggState
FROM MonitoringLog;
| 方案 | 優點 | 缺點 |
|---|---|---|
| 函數法 (本文) | 封裝邏輯、可重用、效能極高 (內聯純量函數) | 需部署函數 |
| CASE 表達式內聯 | 無物件依賴 | SQL 冗長、難維護 |
| 改用 BIGINT 儲存 | 徹底避免溢位 | 修改結構、應用層需同步、儲存空間加倍 |
| TRY_CAST (SQL 2012+) | 失敗回傳 NULL 不報錯 | 需額外處理 NULL,語意改變 |
若業務完全非負,可改用 BIGINT 並應用層視為無符號 32 位,查詢時 CAST(Value AS BIGINT) 並用 Value & 0xFFFFFFFF 取低 32 位,避開符號位陷阱。
掌握位元運算與補碼本質,讓類型轉換不再是隱形炸彈。