Mysql 4和5的int unsigned区别

Mysql 4和5的int unsigned区别

所有整数类型可以有一个可选(非标准)属性UNSIGNED。当你想要在列内只允许非负数和该列需要较大的上限数值范围时可以使用无符号值。

mysql4:
mysql> create table wubx(a TINYINT unsigned not null default '0');
Query OK, 0 rows affected (0.04 sec)
mysql> select * from wubx;
Empty set (0.00 sec)
mysql> insert wubx values(0);
Query OK, 1 row affected (0.00 sec)
mysql> select * from wubx;
+------+
| a |
+------+
| 0 |
+------+
1 row in set (0.00 sec)
mysql> update wubx set a=a-1;
Query OK, 0 rows affected, 1 warning (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 1
mysql> select * from wubx;
+------+
| a |
+------+
| 0 |
+------+
1 row in set (0.00 sec)

mysql5:

(root@localhost) [test]> create table wubx(a int(11) unsigned not null default '0');
Query OK, 0 rows affected (18.44 sec)
(root@localhost) [test]> select * from wubx;
Empty set (0.00 sec)
(root@localhost) [test]> insert into wubx values(0);
Query OK, 1 row affected (0.00 sec)
(root@localhost) [test]> update wubx set a=a-1;
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 1
(root@localhost) [test]> select * from wubx;
+------------+
| a |
+------------+
| 4294967295 |
+------------+
1 row in set (0.00 sec)
mysql> show warnings;
+---------+------+-----------------------------------------------------+
| Level | Code | Message |
+---------+------+-----------------------------------------------------+
| Warning | 1264 | Out of range value adjusted for column 'a' at row 1 |
+---------+------+-----------------------------------------------------+

查了一下:
在对于数值处理时：

MySQL4 会在不合规定的值插入表前自动修改为 0
Mysql5 为了速度，只存放数二进制数据，而且在加减运算中，也是二进制的运算．

所以在使用unsigned 是小心0-1 的操作．尽量在这类操作前先做一个判断．

作/译者：吴炳锡　来源：http://coolriver.cublog.cn，wubingxi#gmail.com