欄位定義 balance INT UNSIGNED(範圍 0 ~ 4,294,967,295)。執行 UPDATE t SET balance = balance - 1 WHERE id = 1 時,若 balance=0,結果不會報錯,反而變成 4294967295(回繞),導致「窮人一夕致富」。
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
UPDATE t SET bal = bal - 1 WHERE id = 1 AND bal > 0;
ALTER TABLE t MODIFY bal INT SIGNED; -- 範圍 -21億 ~ +21億
ALTER TABLE t MODIFY bal DECIMAL(18,2) NOT NULL DEFAULT 0;
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;
DECIMAL 或 BIGINT UNSIGNED + WHERE col > 0 守衛。sql_mode=STRICT_TRANS_TABLES,NO_UNSIGNED_SUBTRACTION(5.7+),開啟後無符號減法下溢會報錯 BIGINT UNSIGNED value is out of range,而非靜默回繞。WHERE balance >= amount。