MySQL数据库（数据类型-整数）

男儿欲遂平生志，六经勤向窗前读。这篇文章主要讲述MySQL数据库：数据类型-整数相关的知识，希望能为你提供帮助。
数据类型-整数

字段类型	名称	字节数	表示范围
tinyint	迷你整型	1个字节=8位	0-255
smallint	小整型	2个字节	0-65535
mediumint	中整型	3个字节
int	整型（标准整型）	4个字节
bigint	大整型	8个字节

通常使用较多的是int和tinyint
示例

create table my_int( tiny_int tinyint, small_int smallint, medium_int mediumint, int_ int, big_int bigint ); -- 插入正确的值 insert into my_int (tiny_int, small_int, medium_int, int_, big_int) values (10, 10000, 100000, 10000000, 1000000); mysql> select * from my_int; +----------+-----------+------------+----------+---------+ | tiny_int | small_int | medium_int | int_| big_int | +----------+-----------+------------+----------+---------+ |10 |10000 |100000 | 10000000 | 1000000 | +----------+-----------+------------+----------+---------+-- 插入一个超出范围的值 insert into my_int (tiny_int, small_int, medium_int, int_, big_int) values (255, 255, 255, 255, 255); ERROR 1264 (22003): Out of range value for column tiny_int at row 1 -- 原因是tinyint实际取值范围：-128~127，包含了负数insert into my_int (tiny_int, small_int, medium_int, int_, big_int) values (-128, 255, 255, 255, 255); select * from my_int; +----------+-----------+------------+----------+---------+ | tiny_int | small_int | medium_int | int_| big_int | +----------+-----------+------------+----------+---------+ |10 |10000 |100000 | 10000000 | 1000000 | |-128 |255 |255 |255 |255 | +----------+-----------+------------+----------+---------+

无符号标识（unsigned）只要正数(0-255)

alter table my_int add unsigned_tiny_int tinyint unsigned first; -- 插入255 insert into my_int (unsigned_tiny_int, tiny_int, small_int, medium_int, int_, big_int) values (255, 127, 255, 255, 255, 255); mysql> select * from my_int; +-------------------+----------+-----------+------------+----------+---------+ | unsigned_tiny_int | tiny_int | small_int | medium_int | int_| big_int | +-------------------+----------+-----------+------------+----------+---------+ |NULL |10 |10000 |100000 | 10000000 | 1000000 | |NULL |-128 |255 |255 |255 |255 | |255 |127 |255 |255 |255 |255 | +-------------------+----------+-----------+------------+----------+---------+mysql> desc my_int; +-------------------+---------------------+------+-----+---------+-------+ | Field| Type| Null | Key | Default | Extra | +-------------------+---------------------+------+-----+---------+-------+ | unsigned_tiny_int | tinyint(3) unsigned | YES|| NULL|| | tiny_int| tinyint(4)| YES|| NULL|| | small_int| smallint(6)| YES|| NULL|| | medium_int| mediumint(9)| YES|| NULL|| | int_| int(11)| YES|| NULL|| | big_int| bigint(20)| YES|| NULL|| +-------------------+---------------------+------+-----+---------+-------+

显示长度zerofill整型数据在显示的时候，最多可以显示的位数

tinyint(3) 表示最长显示3位，unsigned是正数，0-255不会超过3个长度
tinyint(4) 表示最长可以显示4位 -128~127

显示长度，只是代表了数据是否可以达到指定的长度，但是不会自动满足到指定长度，如果想要数据显示的时候，保持最高位（显示长度），需要增加zerofill属性
zerofill：从左侧开始填充0到指定位数，自动设置为unsigned
示例

alter table my_int add zerofill_tiny_int tinyint zerofill first; desc my_int; +-------------------+------------------------------+------+-----+---------+-------+ | Field| Type| Null | Key | Default | Extra | +-------------------+------------------------------+------+-----+---------+-------+ | zerofill_tiny_int | tinyint(3) unsigned zerofill | YES|| NULL|| | unsigned_tiny_int | tinyint(3) unsigned| YES|| NULL|| | tiny_int| tinyint(4)| YES|| NULL|| | small_int| smallint(6)| YES|| NULL|| | medium_int| mediumint(9)| YES|| NULL|| | int_| int(11)| YES|| NULL|| | big_int| bigint(20)| YES|| NULL|| +-------------------+------------------------------+------+-----+---------+-------+-- 插入数据 insert into my_int (zerofill_tiny_int, unsigned_tiny_int, tiny_int, small_int, medium_int, int_, big_int) values (1, 1, 1, 1, 1, 1, 1); select * from my_int; +-------------------+-------------------+----------+-----------+------------+----------+---------+ | zerofill_tiny_int | unsigned_tiny_int | tiny_int | small_int | medium_int | int_| big_int | +-------------------+-------------------+----------+-----------+------------+----------+---------+ |NULL |NULL |10 |10000 |100000 | 10000000 | 1000000 | |NULL |NULL |-128 |255 |255 |255 |255 | |NULL |255 |127 |255 |255 |255 |255 | |001 |1 |1 |1 |1 |1 |1 | +-------------------+-------------------+----------+-----------+------------+----------+---------+

自定义显示长度，不会改变字段所能表示的数据长度，超出长度不受影响，长度不足会补0
【MySQL数据库（数据类型-整数）】示例

alter table my_int add zerofill_tiny_int_2 tinyint(2) zerofill first; desc my_int; +---------------------+------------------------------+------+-----+---------+-------+ | Field| Type| Null | Key | Default | Extra | +---------------------+------------------------------+------+-----+---------+-------+ | zerofill_tiny_int_2 | tinyint(2) unsigned zerofill | YES|| NULL|| | zerofill_tiny_int| tinyint(3) unsigned zerofill | YES|| NULL|| | unsigned_tiny_int| tinyint(3) unsigned| YES|| NULL|| | tiny_int| tinyint(4)| YES|| NULL|| | small_int| smallint(6)| YES|| NULL|| | medium_int| mediumint(9)| YES|| NULL|| | int_| int(11)| YES|| NULL|| | big_int| bigint(20)| YES|| NULL|| +---------------------+------------------------------+------+-----+---------+-------+insert into my_int (zerofill_tiny_int_2, zerofill_tiny_int, unsigned_tiny_int, tiny_int, small_int, medium_int, int_, big_int) values (100, 100, 100, 100, 100, 100, 100, 100), (1, 1, 1, 1, 1, 1, 1, 1); select * from my_int; +---------------------+-------------------+-------------------+----------+-----------+------------+----------+---------+ | zerofill_tiny_int_2 | zerofill_tiny_int | unsigned_tiny_int | tiny_int | small_int | medium_int | int_| big_int | +---------------------+-------------------+-------------------+----------+-----------+------------+----------+---------+ |NULL |NULL |NULL |10 |10000 |100000 | 10000000 | 1000000 | |NULL |NULL |NULL |-128 |255 |255 |255 |255 | |NULL |NULL |255 |127 |255 |255 |255 |255 | |NULL |001 |1 |1 |1 |1 |1 |1 | |100 |100 |100 |100 |100 |100 |100 |100 | |01 |001 |1 |1 |1 |1 |1 |1 | +---------------------+-------------------+-------------------+----------+-----------+------------+----------+---------+