萬盛學電腦網

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

MySQL UNSIGNED INT 陷阱:扣款變加款的無符號整數溢位問題

現象

欄位定義 balance INT UNSIGNED(範圍 0 ~ 4,294,967,295)。執行 UPDATE t SET balance = balance - 1 WHERE id = 1 時,若 balance=0,結果不會報錯,反而變成 4294967295(回繞),導致「窮人一夕致富」。

根本原因

  • MySQL 數值運算預設按運算元型別計算。UNSIGNED - SIGNED 結果仍為 UNSIGNED。
  • 無符號整數減法下溢直接回繞,不拋出錯誤(非嚴格模式下)。

重現驗證


CREATE TABLE t (id INT PRIMARY KEY, bal INT UNSIGNED);
INSERT INTO t VALUES (1, 0);
UPDATE t SET bal = bal - 1 WHERE id = 1;
SELECT bal FROM t WHERE id = 1;  -- 4294967295

解決方案

方案一:WHERE 守衛(最簡單、效能最好)


UPDATE t SET bal = bal - 1 WHERE id = 1 AND bal > 0;
  • 僅在餘額足夠時執行扣減,避免下溢。
  • 應用層同樣需檢查餘額,資料庫層面做最後防線。

方案二:使用 SIGNED 型別(若業務允許負數)


ALTER TABLE t MODIFY bal INT SIGNED;  -- 範圍 -21億 ~ +21億
  • 負數語意明確(欠款),但需應用層處理負數顯示。

方案三:DECIMAL 定點數(金融級推薦)


ALTER TABLE t MODIFY bal DECIMAL(18,2) NOT NULL DEFAULT 0;
  • 精確儲存金額,無浮點誤差,支援負數,無溢位回繞問題。
  • 效能略低於 INT,但金融系統準確性優先。

方案四:觸發器/存儲過程封裝業務邏輯


CREATE TRIGGER before_update_bal
BEFORE UPDATE ON t FOR EACH ROW
BEGIN
  IF NEW.bal < 0 THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '餘額不足';
  END IF;
END;
  • 資料庫層強制約束,但觸發器有效能開銷,高併發慎用。

最佳實踐總結

  1. 金額/點數/庫存等不可為負的業務欄位,優先用 DECIMAL 或 BIGINT UNSIGNED + WHERE col > 0 守衛。
  2. 啟用 sql_mode=STRICT_TRANS_TABLES,NO_UNSIGNED_SUBTRACTION(5.7+),開啟後無符號減法下溢會報錯 BIGINT UNSIGNED value is out of range,而非靜默回繞。
  3. 代碼審查檢查點:所有扣減操作必須帶 WHERE balance >= amount。
copyright © 萬盛學電腦網 all rights reserved