Difference between int unsigned in Mysql 4 and 5

All Integer types can have an optional (non-standard) attribute UNSIGNED. You can use an unsigned value when you want to allow only non-negative numbers in a column and the column requires a large upper limit value range.

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 |
+ ------------ +
| 1, 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 |
+ --------- + ------ + ----------------------------------------------------- +

Check:
When processing values:

MySQL4 will be automatically changed to 0 before the specified value is inserted into the table.
For speed, Mysql5 only stores the number of binary data. In addition and subtraction, it is also a binary operation.

Therefore, when using unsigned, be careful with the 0-1 operation. Try to make a judgment before such operation.

Source:Http://coolriver.cublog.cn, Wubingxi # gmail.com