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