mysql无符号整型溢出

下午用sql的时候突然想到这个问题,徒手测试了一下,结果还真令人意外:

首先创建一张测试用表

mysql> CREATE TABLE `t1` (
-> `id`  int UNSIGNED NOT NULL AUTO_INCREMENT ,
-> `val`  int UNSIGNED NOT NULL DEFAULT 0 ,
-> PRIMARY KEY (`id`)
-> );
Query OK, 0 rows affected (0.04 sec)

初始化一条数据:

mysql> insert into t1(val) values(0);
Query OK, 1 row affected (0.00 sec)

查看结果:

mysql> select * from t1;
+----+-----+
| id | val |
+----+-----+
|  1 |   0 |
+----+-----+
1 row in set (0.00 sec)

进行溢值计算:

mysql> update t1 set t1.val = t1.val - 1;
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 1

产生了一个警告,查看下当前结果:

mysql> select * from t1;
+----+------------+
| id | val        |
+----+------------+
|  1 | 4294967295 |
+----+------------+
1 row in set (0.00 sec)

这个结果不难推测:0-1=-1,而-1(int)刚刚好是2^32-1.
将val置0:

mysql> update t1 set t1.val = 0;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

查看当前数据:

mysql> select * from t1;
+----+-----+
| id | val |
+----+-----+
|  1 |   0 |
+----+-----+
1 row in set (0.00 sec)

进行溢值计算:

mysql> update t1 set t1.val = t1.val - 2;
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 1

猜猜结果是多少?

mysql> select * from t1;
+----+------------+
| id | val        |
+----+------------+
|  1 | 4294967295 |
+----+------------+
1 row in set (0.00 sec)

为什么不是2^32-2呢?暂时不知道原因

再进行一个溢值操作:

mysql> update t1 set t1.val = t1.val + 1;
Query OK, 0 rows affected, 1 warning (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 1

可以看到Changed: 0,查看下数据:

mysql> select * from t1;
+----+------------+
| id | val        |
+----+------------+
|  1 | 4294967295 |
+----+------------+
1 row in set (0.00 sec)

很有意思.记录一下