乐趣区

INT类型知多少

jiezi

前言：
整型是 MySQL 中最常用的字段类型之一，通常用于存储整数，其中 int 是整型中最常用的，对于 int 类型你是否真正了解呢？本文会带你熟悉 int 类型相关知识，也会介绍其他整型字段的使用。

1. 整型分类及存储范围

整数类型 字节 有符号范围 无符号范围
TINYINT 1 -128 ~ 127 0 ~ 255
SMALLINT 2 -32768 ~ 32767 0 ~ 65535
MEDIUMINT 3 -8388608 ~ 8388607 0 ~ 16777215
INT/INTEGER 4 -2147483648 ~ 2147483647 0 ~ 4294967295
BIGINT 8 -9223372036854775808 ~ 9223372036854775807 0 ~ 18446744073709551615

表格一共有四列分别表示: 字段类型, 占用字节数, 有符号范围, 无符号范围。
我们拿 int 类型为例:
int 类型, 占用字节数为 4byte, 学过计算机原理的同学应该知道, 字节 (byte) 并非是计算机存储的最小单位, 还有比字节 (byte) 更小的单位, 也就是位(bit), 一个位就代表一个 0 或 1; 8 个位组成一个字节; 一般字节用大写 B 来表示 byte, 位用小写 b 来表示 bit.

计算机存储单位的换算:
1B=8b
1KB=1024B
1MB=1024KB

那么根据 int 类型允许存储的字节数是 4 个字节, 我们就能换算出 int UNSIGNED(无符号)类型的能存储的最小值为 0, 最大值为 4294967295(即 4B=32b, 最大值即为 32 个 1 组成，即 4294967295 换算成二进制则是 32 个 1)。

2. 存储范围测试

mysql> CREATE TABLE test_int (
    -> col1 TINYINT,
    -> col2 SMALLINT,
    -> col3 MEDIUMINT,
    -> col4 INT,
    -> col5 BIGINT
    -> ) ENGINE = INNODB DEFAULT CHARSET = utf8;
Query OK, 0 rows affected (0.01 sec)

mysql> show create table test_int\G
*************************** 1. row ***************************
       Table: test_int
Create Table: CREATE TABLE `test_int` (`col1` tinyint(4) DEFAULT NULL,
  `col2` smallint(6) DEFAULT NULL,
  `col3` mediumint(9) DEFAULT NULL,
  `col4` int(11) DEFAULT NULL,
  `col5` bigint(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> insert into test_int values (1234,123456,12345678,12345678901,12345678901234567890);
Query OK, 1 row affected, 5 warnings (0.00 sec)

mysql> insert into test_int values (-1234,-123456,-12345678,-12345678901,-12345678901234567890);
Query OK, 1 row affected, 5 warnings (0.01 sec)

mysql> show warnings;
+---------+------+-----------------------------------------------+
| Level   | Code | Message                                       |
+---------+------+-----------------------------------------------+
| Warning | 1264 | Out of range value for column 'col1' at row 1 |
| Warning | 1264 | Out of range value for column 'col2' at row 1 |
| Warning | 1264 | Out of range value for column 'col3' at row 1 |
| Warning | 1264 | Out of range value for column 'col4' at row 1 |
| Warning | 1264 | Out of range value for column 'col5' at row 1 |
+---------+------+-----------------------------------------------+
5 rows in set (0.01 sec)

mysql> select * from test_int;
+------+--------+----------+-------------+----------------------+
| col1 | col2   | col3     | col4        | col5                 |
+------+--------+----------+-------------+----------------------+
|  127 |  32767 |  8388607 |  2147483647 |  9223372036854775807 |
| -128 | -32768 | -8388608 | -2147483648 | -9223372036854775808 |
+------+--------+----------+-------------+----------------------+

从上述测试中我们可以看出：有符号时，各种整型类型最大的存储范围，当存储数字大小不在存储范围时，MySQL 会产生告警，但数字可以插入，默认截取为可存储的最大值或最小值。

3.int(M)中 M 的含义与 zerofill 的使用

我们经常听到这句话：int(M)中的 M 代表 最大显示宽度 ，” 最大显示宽度 ” 我们第一反应是该字段的值最大能允许存放的值的宽度，以为我们建了 int(1), 就不能存放数据 10 了, 其实不是这个意思。
整数列的显示宽度与 mysql 需要用多少个字符来显示该列数值，与该整数需要的存储空间的大小都没有关系，比如，不管设定了显示宽度是多少个字符，int 都是占用 4 个字节，bigint 都要占用 8 个字节。即 int(5)和 int(10)可存储的范围一样。
整型字段有个 ZEROFILL 属性(0 填充)，在数字长度不够的数据前面填充 0，以达到设定的长度。加上 ZEROFILL 后 M 才表现出不同，当使用 ZEROFILL 时，默认会自动加 unsigned（无符号）属性。比如 INT(3) ZEROFILL, 你插入到数据库里的是 10, 则实际插入为 010, 也就是在前面补充加了一个 0，下面我们来测试下：

mysql> CREATE TABLE test_int_zerofill (-> col1 INT(5) ZEROFILL,
    -> col2 INT ZEROFILL,
    -> col3 INT(5)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.01 sec)

mysql> show create table test_int_zerofill\G
*************************** 1. row ***************************
       Table: test_int_zerofill
Create Table: CREATE TABLE `test_int_zerofill` (`col1` int(5) unsigned zerofill DEFAULT NULL,
  `col2` int(10) unsigned zerofill DEFAULT NULL,
  `col3` int(5) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> insert into test_int_zerofill values (12,12,12);
Query OK, 1 row affected (0.00 sec)

mysql> select * from test_int_zerofill;
+-------+------------+------+
| col1  | col2       | col3 |
+-------+------------+------+
| 00012 | 0000000012 |   12 |
+-------+------------+------+
1 row in set (0.00 sec)

那么有同学可能会问 zerofill 有什么应用场景呢，比较常用的应该是月份或日期前补 0，这样显示的会规范些

CREATE TABLE `t_zerofill` (`year` year(4) DEFAULT NULL,  
  `month` int(2) unsigned zerofill DEFAULT NULL,  
  `day` int(2) unsigned zerofill DEFAULT NULL  
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

mysql> insert into t_zerofill values (2019,6,5);
Query OK, 1 row affected (0.01 sec)

mysql> insert into t_zerofill values (2019,6,18);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t_zerofill values (2019,10,1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t_zerofill values (2019,11,11);
Query OK, 1 row affected (0.01 sec)

mysql> select * from t_zerofill;
+------+-------+------+
| year | month | day  |
+------+-------+------+
| 2019 |    06 |   05 |
| 2019 |    06 |   18 |
| 2019 |    10 |   01 |
| 2019 |    11 |   11 |
+------+-------+------+
4 rows in set (0.00 sec)

4. 类型选取

经过上面的介绍，关于不同整型字段的选取变得容易很多。本着最小化存储的原则，当然是能选 TINYINT 不选 SMALLINT，能选 MEDIUMINT 不选 INT 了，不过一切都要满足业务的前提下尽量选取占用字节更少的类型。对于确定只存储正整数的字段，可以加上 unsigned 属性，这样会使存储范围更大，比如当字段有 AUTO_INCREMENT 属性时，我们可以为 int 类型加上 unsigned 属性。

退出移动版