MySQL unsigned int類型數據使用一些事項

在 MySQL 開一個 INT 欄位的時候，有時候我們會把欄位屬性設定為 Unsigned ，這樣可以儲存更多的正整數。

以 Signed 為例，可以儲存 -2147483648 to 2147483647 而以 Unsigned 為例，可以儲存 0 to 4294967295 不過在操作這類欄位的時候，必須要小心，以避免刪過頭，反而變成補血補滿。

正常來講，MySQL 在操作數字加減的時候，可以這樣做

PgSQL

 代碼如下 復制代碼

-- transaction start--
SELECT `example` FROM `test` WHERE ID = :ID

-- 用程式計算 example 的新值，然後調回去

UPDATE `test` SET `example` = n WHERE ID = :ID;
-- commit transaction
-- transaction start--SELECT `example` FROM `test` WHERE ID = :ID

-- 用程式計算 example 的新值，然後調回去

UPDATE `test` SET `example` = n WHERE ID = :ID;

-- commit transaction 

不過比較聰明的人，大概都會這樣做，以減少 Query 次數 (假設一次減 1)

PgSQL

 代碼如下 復制代碼

UPDATE `test` SET `example` = `example` - 1;

UPDATE `test` SET `example` = `example` - 1; 

不過，上面這行 SQL Statement 有個風險。由於沒有做邊界檢查，所以當 example = 0 的時候，再減掉 1 就會變成 4294967295 。

以下圖為例：

這是我自己的頁面截圖，圖中的 Plurk 數達到 4,294,967,294 ，就是 42 億多筆，假設我一秒發一筆出去，在我有生之年都戳不完 (1 * 86400 * 365 * 100 = 3153600000, 35 億) 。頁面連結

所以，當這個欄位是記錄”金額”或是”點數”的時候，就會對資料造成很大的影響 (只要程式 transaction/work flow 沒控制好，窮人也可以一夕致富) 。

雖然問題很嚴重，不過改善的方法也很簡單，只要加上 WHERE 條件限制，就可以馬上避免掉這個問題。

PgSQL

 代碼如下 復制代碼

UPDATE `test` SET  `example` = `example` - '1' WHERE  `example` > 0;

UPDATE `test` SET  `example` = `example` - '1' WHERE  `example` > 0; 

備?：關於這個問題已經回報官方了，不知道這個 BUG 何時會被修正過來