一个字段i，如果是unsigned int，值为0

执行 UPDATE t SET i= i - 1

在4.1和5.0.13以后会有不同的结果

4.1中结果为0，5.0.13以后为该字段允许的最大整型

解决方法

1. 用 UPDATE t SET i = CAST(i - 1 AS SIGNED);

2. 用 UPDATE t SET i = GREATEST(i - 1, 0);

3. 打开 NO_UNSIGNED_SUBTRACTION

参考

http://dev.mysql.com/doc/refman/5.0/en/upgrading-from-4-1.html

http://bugs.mysql.com/bug.php?id=15472

http://bugs.mysql.com/bug.php?id=14543

Incompatible change: By default, integer subtraction involving an unsigned value should produce an unsigned result. Tracking of the “unsignedness” of an expression was improved in MySQL 5.0.13. This means that, in some cases where an unsigned subtraction would have resulted in a signed integer, it now results in an unsigned integer. One context in which this difference manifests itself is when a subtraction involving an unsigned operand would be negative.

Suppose that i is a TINYINT UNSIGNED column and has a value of 0. The server evaluates the following expression using 64-bit unsigned integer arithmetic with the following result:

mysql> SELECT i - 1 FROM t;
+----------------------+
| i - 1                |
+----------------------+
| 18446744073709551615 |
+----------------------+

If the expression is used in an UPDATE t SET i = i - 1 statement, the expression is evaluated and the result assigned to i according to the usual rules for handling values outside the column range or 0 to 255. That is, the value is clipped to the nearest endpoint of the range. However, the result is version-specific:

• Before MySQL 5.0.13, the expression is evaluated but is treated as the equivalent 64-bit signed value (-1) for the assignment. The value of -1 is clipped to the nearest endpoint of the column range, resulting in a value of 0:

  mysql> UPDATE t SET i = i - 1; SELECT i FROM t;
  +------+
  | i    |
  +------+
  |    0 |
  +------+

• As of MySQL 5.0.13, the expression is evaluated and retains its unsigned attribute for the assignment. The value of 18446744073709551615 is clipped to the nearest endpoint of the column range, resulting in a value of 255:

  mysql> UPDATE t SET i = i - 1; SELECT i FROM t;
  +------+
  | i    |
  +------+
  |  255 |
  +------+

To get the older behavior, use CAST() to convert the expression result to a signed value:

UPDATE t SET i = CAST(i - 1 AS SIGNED);

Alternatively, set the NO_UNSIGNED_SUBTRACTION SQL mode. However, this will affect all integer subtractions involving unsigned values.