MySQL BIGINT UNSIGNED valueis out of range...的問題和解決
MySQL處理兩個整數(INT)相減的時候,如果其中有一個是UNSIGNED INT類型的,那麼結果就被當做是UNSIGNED的。
如果相減的結果是負數:
在MySQL 5.5.5之前,結果變成了最大的整數(18446744073709551615)
從MySQL 5.5.5開始,這種情況會返回一個錯誤:BIGINT UNSIGNED value is out of range... 比如: 如果結果為負數: [SQL] SELECT (CAST(0 AS UNSIGNED)-1); [Err] 1690 - BIGINT UNSIGNED value is out of range in '(cast(0 as unsigned) - 1)'
[SQL] SELECT (1-CAST(2 AS UNSIGNED)); [Err] 1690 - BIGINT UNSIGNED value is out of range in '(1 - cast(2 as unsigned))'
如果結果為正數: [SQL] SELECT (1-CAST(0 AS UNSIGNED)); 1
解決方法: 1、如果確實可以接受負數的結果,那麼把其中的UNSIGNED字段強轉為SIGNED類型: [SQL] SELECT (1-CAST(2 AS
SIGNED)); -1
如果計算結果是要插入到另外一個UNSIGNED INT字段中的話,那麼就不要強轉了,直接捕捉並報告異常。