tinyint存1个字节(unsigned 存0--255,无 存-128 --127) smallint存2个字节(unsigned 存0—65535 ,无 存-32768--32767) int存4个字节(unsigned 存2的32次方,无 存-2的31次方 –2的31次方-1) bigint存8个字节(unsigned 存2的64次方,无 存-2的63次方 –2的63次方-1)
做个实验: mysql> create table bb(a int(1) zerofill,b int(2) zerofill,c int(3) zerofill); Query OK, 0 rows affected (0.18 sec) mysql> show create table bb; CREATE TABLE bb ( aint(1) unsigned zerofill DEFAULT NULL, b int(2) unsigned zerofill DEFAULT NULL, cint(3) unsigned zerofill DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8; 三个字段括号中的参数不同,分别都加了zerofill,我们来看下有什么影响 mysql> insert into bb values(1,1,1); Query OK, 1 row affected (0.00 sec)
mysql> select * from bb;
a | b | c |
1 | 01 | 001 |
1 row in set (0.00 sec)
mysql> alter table bb add d int(4); Query OK, 1 row affected (0.27 sec) Records: 1 Duplicates: 0 Warnings: 0
mysql> select * from bb;
a | b | c | d | e |
1 | 01 | 001 | NULL | NULL |
1 row in set (0.00 sec)
mysql> update bb set d =1,e =1 where a=1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from bb;
a | b | c | d | e |
1 | 01 | 001 | 1 | 1 |
1 row in set (0.00 sec)
由此可见:括号中的数字表示数据库中显示数字的位数,一般情况下不起左右,只有在zerofill情况下才会起作用。