博客

1.MySQL数据类型int、bigint、smallint 和 tinyint的区别

2.tinyint概述

2.1tinyint有符号与无符号区别

tinyint 型的字段如果设置为UNSIGNED类型:
只能存储从0到255的整数,不能用来储存负数。

tinyint 型的字段如果不设置UNSIGNED类型:
存储-128到127的整数。

2.2 tinyint(1)与tinyint（2）区别

tinyint后面的括号带的数字,以后称之为M,和存贮的值没有任何关系,只是在某些情况下和显示的宽度有关系。

数据库初始化

-- --------------------------------------
-- 验证tinyint是否设置符号区别
-- --------------------------------------
-- 创建表test，设置无符号类型tinyint
CREATE 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);
-- cahxun
select * from test;
-- --------------------------------------
id	str	state	state2	state3	state4
6	csdn	6	06	006	0006
7	csdn	66	66	066	0066
8	csdn	254	254	254	0254
9	csdn	255	255	255	0255
-- --------------------------------------

-- 创建表test，不设置无符号类型tinyint
CREATE 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);
-- chaxun
select * from testtiny;

-- --------------------------------------
id	str	state	state2	state3	state4
6	csdn	6	6	6	6
7	csdn	66	66	66	66
8	csdn	100	100	100	100
9	csdn	127	127	127	127
-- --------------------------------------