表字段定義 count INT UNSIGNED,執行扣減:
UPDATE t SET count = count - 1 WHERE id = 1;
當 count = 0 時再扣減,**不報錯**,數值直接溢出變為 4294967295(2³²-1),業務邏輯崩壞。
NO_UNSIGNED_SUBTRACTION 僅影響結果集顯示,不影響 UPDATE 存儲行為UPDATE t
SET count = CONVERT(count + (-1), SIGNED)
WHERE id = 1;
原理:CONVERT(val, SIGNED) 將無符號值轉為有符號 BIGINT 進行運算,結果為負數時 MySQL 會**截斷為 0** 存回無符號列(而非環繞)。
CREATE TABLE t_test (c INT UNSIGNED NOT NULL DEFAULT 0);
INSERT INTO t_test VALUES (0);
-- 錯誤寫法:溢出
UPDATE t_test SET c = c - 1;
SELECT c FROM t_test; -- 4294967295
-- 重置
UPDATE t_test SET c = 0;
-- 正確寫法:歸零
UPDATE t_test SET c = CONVERT(c + (-1), SIGNED);
SELECT c FROM t_test; -- 0
| 方案 | SQL 寫法 | 優點 | 缺點 |
|---|---|---|---|
| CONVERT SIGNED(推薦) | CONVERT(col + (-1), SIGNED) | 單條 SQL、無需事務、兼容所有版本 | 略顯繁瑣 |
| CASE WHEN 保護 | CASE WHEN col > 0 THEN col - 1 ELSE 0 END | 語義清晰、可讀性強 | 稍長、需重複列名 |
| 應用層預檢查 | 先 SELECT 再決策 UPDATE | 邏輯集中應用層 | 雙倍 RTT、並發下需樂觀鎖 |
| 存儲過程封裝 | 封裝扣減邏輯 | 復用性好 | 增加維護成本 |
CONVERT(col + (-delta), SIGNED) 模式UPDATE ... SET unsigned_col = unsigned_col - ... 模式SHOW ENGINE INNODB STATUS 中的錯誤計數、業務指標異常波動