int类型占4个字节,32位
无符号范围为0-4,294,967,295
有符号范围为-2147483648 ----2147483647
mysql> create table t1(id int(40) unsigned zerofill);
Query OK, 0 rows affected (0.01 sec)mysql> insert into t1 set id= 4294967295;
Query OK, 1 row affected (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.01 sec)
mysql> select * from t1;
+------------------------------------------+
| id |
+------------------------------------------+
| 0000000000000000000000000000004294967295 |
+------------------------------------------+
1 row in set (0.00 sec)
mysql> select length(id) from t1;
+------------+
| length(id) |
+------------+
| 40 |
+------------+
1 row in set (0.00 sec)
----------------int (N) zerofill 就是该列长度为N个字节,数字长度不足N,以0补齐
默认创建的int类型字段是有符号的
mysql> create table t001 (id int);
Query OK, 0 rows affected (0.01 sec)
mysql> insert into t001 set id= -43;
Query OK, 1 row affected (0.01 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> desc t001;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)
创建表时指明为有符号signed + zerofill 则zerofill情况下转为unsigned,以便前面补充0
mysql> create table t001 (id int signed zerofill);
Query OK, 0 rows affected (0.00 sec)
mysql> insert into t001 set id= -43;
ERROR 1264 (22003): Out of range value for column 'id' at row 1
mysql> desc t001;
+-------+---------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------------------+------+-----+---------+-------+
| id | int(10) unsigned zerofill | YES | | NULL | |
+-------+---------------------------+------+-----+---------+-------+
1 row in set (0.00 sec)
mysql> create table t001 (id int(2555));
ERROR 1439 (42000): Display width out of range for column 'id' (max = 255)
显示最大宽度为255,前面能填充0
mysql> create table t001 (id int);
Query OK, 0 rows affected (0.01 sec)
mysql> insert into t001 set id=4294967295;
ERROR 1264 (22003): Out of range value for column 'id' at row 1
mysql> insert into t001 set id=2147483647;
Query OK, 1 row affected (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
int 默认有符号 范围从 -2147483648 ----2147483647;
mysql> create table t001 (id int unsigned);
Query OK, 0 rows affected (0.01 sec)
mysql> insert into t001 set id=4294967295;
Query OK, 1 row affected (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.01 sec)
mysql> insert into t001 set id=-1;
ERROR 1264 (22003): Out of range value for column 'id' at row 1
mysql> desc t001;
+-------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| id | int(10) unsigned | YES | | NULL | |
+-------+------------------+------+-----+---------+-------+
1 row in set (0.00 sec)
创建表时,指明int unsigned时的范围为 0------4294967295;