数据类型-整数

字段类型 名称 字节数 表示范围
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

示例

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 |
+---------------------+-------------------+-------------------+----------+-----------+------------+----------+---------+