類型溢出 · MySQL SQL開發規范

# 類型溢出以MySQL5版本，int類型為例： # 建表 root@localhost(test2)14:46>create table test2 (a int(10) UNSIGNED); Query OK, 0 rows affected (0.12 sec) # 插入數據 root@localhost(test2)14:56>insert test2 values (10); Query OK, 1 row affected (0.00 sec) # 模擬更新溢出 root@localhost(test2)14:56>update test2 set a=a-11; Query OK, 1 row affected, 1 warning (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 1 # 查看warnings root@localhost(test2)14:57>show warnings; +---------+------+--------------------------------------------+ | Level | Code | Message | +---------+------+--------------------------------------------+ | Warning | 1264 | Out of range value for column 'a' at row 1 | +---------+------+--------------------------------------------+ 1 row in set (0.00 sec) # 確定實際得到的值已經溢出 root@localhost(test2)14:57>select * from test2; +------------+ | a | +------------+ | 4294967295 | +------------+ 1 row in set (0.00 sec) # 清理數據 root@localhost(test2)14:59>delete from test2; Query OK, 1 row affected (0.00 sec) # 模擬插入溢出 root@localhost(test2)14:59>insert test2 values (-1); Query OK, 1 row affected, 1 warning (0.00 sec) # 查看warnings root@localhost(test2)14:59>show warnings; +---------+------+--------------------------------------------+ | Level | Code | Message | +---------+------+--------------------------------------------+ | Warning | 1264 | Out of range value for column 'a' at row 1 | +---------+------+--------------------------------------------+ 1 row in set (0.00 sec) # 確定實際得到的值已經溢出 root@localhost(test2)14:59>select * from test2; +------+ | a | +------+ | 0 | +------+ 1 row in set (0.00 sec) # 原因 int占用4個字節，而int又分為無符號型和有符號性。對于無符號型的范圍是0 到 4294967295；有符號型的范圍是-2147483648 到 2147483647。舉一反三，其他類型都可能有類似問題，均需要考量。 # 控制方法可以通過sql_mode參數控制，但一般建議程序控制，比如：對表單項的值進行校驗。