MySQL数据类型int、bigint、smallint 和 tinyint的区别
1. MySQL数据类型int、bigint、smallint 和 tinyint的区别2. tinyint概述2.1 tinyint有符号与无符号区别2.2 tinyint(1)与tinyint（2）区别1. MySQL数据类型int、bigint、smallint 和 tinyint的区别
int(M) 在 integer 数据类型中，M 表示最大显示宽度。在 int(M) 中，M 的值跟 int(M) 所占多少存储空间并无任何关系。和数字位数也无关系 int(3)、int(4)、int(8) 在磁盘上都是占用 4 btyes 的存储空间。
2. tinyint概述
2.1 tinyint有符号与无符号区别
tinyint 型的字段如果设置为UNSIGNED类型:只能存储从0到255的整数,不能用来储存负数。tinyint 型的字段如果不设置UNSIGNED类型:存储-128到127的整数。
2.2 tinyint(1)与tinyint（2）区别
tinyint后面的括号带的数字,以后称之为M,和存贮的值没有任何关系,只是在某些情况下和显示的宽度有关系。
数据库初始化
-- ---------------------------------------- 验证tinyint是否设置符号区别-- ---------------------------------------- 创建表test，设置无符号类型tinyintCREATE TABLE `test` (`id` INT ( 11 ) NOT NULL AUTO_INCREMENT,`str` VARCHAR ( 255 ) NOT NULL,`state` TINYINT ( 1 ) UNSIGNED ZEROFILL DEFAULT NULL,`state2` TINYINT ( 2 ) UNSIGNED ZEROFILL DEFAULT NULL,`state3` TINYINT ( 3 ) UNSIGNED ZEROFILL DEFAULT NULL,`state4` TINYINT ( 4 ) UNSIGNED ZEROFILL DEFAULT NULL,PRIMARY KEY ( `id` ) ) ENGINE = MyISAM AUTO_INCREMENT = 6 DEFAULT CHARSET = utf8;insert into test (str,state,state2,state3,state4) values('csdn',6,6,6,6);insert into test (str,state,state2,state3,state4) values('csdn',66,66,66,66);insert into test (str,state,state2,state3,state4) values('csdn',254,254,254,254);insert into test (str,state,state2,state3,state4) values('csdn',255,255,255,255);-- cahxunselect * from test;-- --------------------------------------idstrstatestate2state3state46csdn60600600067csdn666606600668csdn25425425402549csdn2552552550255-- ---------------------------------------- 创建表test，不设置无符号类型tinyintCREATE TABLE `testtiny` (`id` INT ( 11 ) NOT NULL AUTO_INCREMENT,`str` VARCHAR ( 255 ) NOT NULL,`state` TINYINT ( 1 ) DEFAULT NULL,`state2` TINYINT ( 2 ) DEFAULT NULL,`state3` TINYINT ( 3 ) DEFAULT NULL,`state4` TINYINT ( 4 ) DEFAULT NULL,PRIMARY KEY ( `id` ) ) ENGINE = MyISAM AUTO_INCREMENT = 6 DEFAULT CHARSET = utf8;insert into testtiny (str,state,state2,state3,state4) values('csdn',6,6,6,6);insert into testtiny (str,state,state2,state3,state4) values('csdn',66,66,66,66);insert into testtiny (str,state,state2,state3,state4) values('csdn',100,100,100,100);insert into testtiny (str,state,state2,state3,state4) values('csdn',127,127,127,127);insert into testtiny (str,state,state2,state3,state4) values('csdn',128,128,128,128);-- chaxunselect * from testtiny;-- --------------------------------------idstrstatestate2state3state46csdn66667csdn666666668csdn1001001001009csdn127127127127-- --------------------------------------
