MySQL UNSIGNED 用法詳解與減法溢出 (補數) 完美解決方案

UNSIGNED 基礎知識

• 僅適用於整數類型 (TINYINT~BIGINT)；FLOAT/DOUBLE/DECIMAL 不支援。
• 範圍擴展：正數上限約 ×2 (如 TINYINT -128~127 → UNSIGNED 0~255)。
• 儲存大小不變，僅改變解讀方式。

整數類型範圍對照表

MySQL 5.x 減法溢出問題

MySQL 為追求速度，整數運算直接在二進制層面進行。UNSIGNED 欄位做 0 - 1 會產生極大正數 (補數回繞)，而非報錯或截斷。

UPDATE `user` SET `tk` = `tk` - 8 WHERE `id` = 1330;  -- tk 為 UNSIGNED，若 tk < 8 會溢出

官方推薦解法：CAST/CONVERT 簽名轉換

UPDATE `user` 
SET `tk` = CONVERT(`tk` + (-8), SIGNED) 
WHERE `id` = 1330;

• CONVERT(expr, SIGNED) 將運算中間結果視為有號整數，允許負數暫存，寫回時再隱式轉回 UNSIGNED (若結果 < 0 會截斷為 0 或報錯，視 SQL_MODE 而定)。
• 同理 CAST(expr AS SIGNED) 效果相同。

最佳實踐建議

1. 業務邏輯層 (PHP/Java/Go) 先判斷 if (current >= deduct) 再發 SQL，避免資料庫層處理邊界。
2. 若必須在 SQL 完成，統一使用 CONVERT(col + delta, SIGNED) 模式。
3. 設計階段評估：扣減類欄位 (餘額、庫存) 建議用 SIGNED BIGINT，保留負數檢查彈性；純計數器 (ID、PV) 才用 UNSIGNED。
4. 啟用 SQL_MODE=STRICT_TRANS_TABLES,NO_UNSIGNED_SUBTRACTION 讓溢出直接報錯而非靜默回繞。

結語

UNSIGNED 是儲存空間優化利器，但減法溢出是隱形坑。採用「應用層預檢 + SQL 簽名轉換 + 嚴格 SQL_MODE」三層防護，既享受空間紅利，又杜絕補數陷阱。