为了账号安全,请及时绑定邮箱和手机立即绑定
慕课专栏

目录

索引目录

MySQL 开发高频面试题精选

原价 ¥ 48.00

立即订阅
03 为什么会有程序员使用INT(20)或INT(1)这样的设计,这样是否合理?
更新时间:2020-09-22 10:16:38
低头要有勇气,抬头要有底气。——韩寒

在上一节中我们了解了 VARCHAR 这个使用频率很高的数据类型的用法,并介绍了 VARCHAR 如何优化的一些知识点,比如要避免行溢出,避免设计了太多的 VARCHAR 字段导致无法扩展的尴尬等等,希望你能从中得到一些收获。

这一节我们来学习整形数据类型,那么整形这个数据类型又该如何使用呢?

1. INT 1、INT 20 与显示宽度

在我的日常工作当中,经常遇到一些程序员朋友发来一些建库建表语句,其中的部分字段是这样子的:

status int(1) not null default '1'
type int(1) not null default '1'

那么 INT (1) 就真的是 1 个字节了吗,这个括号中间的数字究竟有什么意义?

我们还是做一个实验来看一下。

下面我们创建一个数据类型都是 INT 的新表,这个表里面有 3 个字段,分别定义为 INT、INT (1)、和 INT (20),并且插入一些数据:

mysql> create table int_test(id int auto_increment, col_1 int(1), col_2 int(20), primary key (id));
Query OK, 0 rows affected (0.02 sec)

mysql> insert into int_test values(1,1,1),(10,10,10);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

查询一下结果,看下输出的值:

mysql> select * from int_test;
+----+-------+-------+
| id | col_1 | col_2 |
+----+-------+-------+
|  1 |     1 |     1 |
| 10 |    10 |    10 |
+----+-------+-------+
2 rows in set (0.01 sec)

在这个实验中你可以看到,不管你定义 INT (1) 还是 INT (20),MySQL 在存储的时候都不会做出超出 INT 的限制范围。

那这个括号里面的值是做什么用的呢?

其实它定义的是显示宽度

当你在程序中定义显示宽度之后,如果你插入的值不够这个宽度,在查询时会在左边用相应宽度的空格填充。

这个空格在通常情况下,我们是看不到的,因为在客户端输出时(MySQL Workbench、mysql-connector-java 等客户端)默认会去掉左侧的空格。

所以为什么说它是显示宽度,是因为它并不限制存储的值的范围,不管是 INT (1) 还是 INT (20),存储范围都是 INT

  • 有符号:-2147483648 ~ 2147483647
  • 无符号:0 ~ 4294967295

这里再提及一点,整形数据类型中还有一个 ZEROFILL 的属性,如果建表时指定了这个属性,刚刚的空格就会变成 0,并且自动给这个列加上 UNSIGNED 属性。

我们再来做实验看下:

mysql> show create table int_test \G
*************************** 1. row ***************************
       Table: int_test
Create Table: CREATE TABLE `int_test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `col_1` int(1) DEFAULT NULL,
  `col_2` int(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8
1 row in set (0.01 sec)

mysql> alter table int_test modify col_2 int(20) zerofill;
Query OK, 2 rows affected (0.07 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> show create table int_test \G
*************************** 1. row ***************************
       Table: int_test
Create Table: CREATE TABLE `int_test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `col_1` int(1) DEFAULT NULL,
  `col_2` int(20) unsigned zerofill DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> select * from int_test;
+----+-------+----------------------+
| id | col_1 | col_2                |
+----+-------+----------------------+
|  1 |     1 | 00000000000000000001 |
| 10 |    10 | 00000000000000000010 |
+----+-------+----------------------+
2 rows in set (0.01 sec)

在上面的实验中我们可以看到,col_2 这一列已经在左侧填充了一串无意义的 0。但是在一些客户端仍然会自动去掉左侧这个无意义的 0。

由于这个显示宽度实在鸡肋,MySQL 官方也表示会在未来的版本中去掉 ZEROFILL 和显示宽度这两个属性。

在现在的版本中,我们只需要了解它,并且不要用错数据类型即可。存储的值的范围不超过 128/256 的话就用 TINYINT,存储值超出 INT 的范围的话就用 BIGINT。

下图是各种整形数据类型的数值范围表:

图片描述

2. 两个 UNSIGNED 的值无法相减?

UNSIGNED 属性就是无符号的数字类型,如果用做自增主键的话,相比有符号的整形数据类型能扩展 1 倍的空间。

有的大型互联网公司会在内部的开发规范中要求使用 UNSIGNED 的自增值。

看起来还蛮不错,但是在使用时有一个小问题你需要注意,那就是两个数值相减得到负数的情形。

我们再来做一个实验,建一个两列都带有 UNSIGNED 属性的表,然后再插入一行数据:

mysql> create table int_test_2 (col_1 int unsigned, col_2 int unsigned);
Query OK, 0 rows affected (0.03 sec)

mysql> insert into int_test_2 values(1,2);
Query OK, 1 row affected (0.01 sec)

mysql> select col_1 - col_2 from int_test_2;
ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(`imooc_mysql_interview`.`int_test_2`.`col_1` - `imooc_mysql_interview`.`int_test_2`.`col_2`)'

在这里你可以看到,本来该输出 - 1 的结果却报错了。并且错误看起来还有点奇怪,提示 BIGINT UNSIGNED 超出了范围。

那为什么会发生这样的问题呢?

其实和编程语言中的问题类似,对于有符号的整形数来说,-1 的十六进制值是 0xFFFFFFFF;而对于无符号的整形数来说,4294967295 的十六进制值也是 0xFFFFFFFF。

在 MySQL 数据库中,对于 UNSIGNED 数的操作,它的返回值都是 UNSIGNED 的,不能是负值,所以就导致了上面的错误产生。

那么如果非要获得负值呢?只需要改一下 SQL_MODE 即可:

mysql> SET sql_mode = 'NO_UNSIGNED_SUBTRACTION';
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> show warnings;
+---------+------+------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                        |
+---------+------+------------------------------------------------------------------------------------------------+
| Warning | 3090 | Changing sql mode 'NO_AUTO_CREATE_USER' is deprecated. It will be removed in a future release. |
+---------+------+------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

但是不太建议这么做,如果需要避免这个情况还是老老实实的使用有符号的整形数据类型,如果担心 INT 的数值范围不够用的话换成 BIGINT 基本也够用了。

3. 选 TINYINT 还是 ENUM?

你在设计数据库表结构的时候,可能会设计一些枚举的列来存储一些属性值,比如用 (1,2,3) 代表苹果、安卓、其他操作系统。

在数据类型的选择上,可以存储枚举值的数据类型除了 TINYINT 和 ENUM,其实 VARCHAR (N) 也有很多人在用。那么这 3 种数据类型哪个最合适呢?

先放下 TINYINT 不说,我们来看 ENUM 这个数据类型,和前面一样,我们先做个实验,下面是一个建表语句:

mysql> CREATE TABLE smartphone (
    ->     vendor VARCHAR(40),
    ->     os_name ENUM('ios', 'android', 'other')
    -> );
Query OK, 0 rows affected (0.03 sec)

MySQL 官方在实现 ENUM 这个数据类型时,实际上是用 TINYINT 来存储的。

可读性也比较好,不需要映射成类似 1,2,3 这样的数值。

但是 ENUM 这个数据类型在 MySQL 数据库中有一点不好的地方是,字符串列表是预定义的,以后如果要添加或删除属性必须使用 ALTER TABLE

即便现在 MySQL 数据库有了 online DDL,ALTER TABLE 时不会锁表,在大表上这也是一个很重的操作。

另外 ENUM 的数据类型还有两个问题需要注意:

  1. 如果给 ENUM 列插入了一个无效值,则会转换为空字符串( ’ ’ )存储(SQL_MODE 设置在严格模式下会直接抛出错误)。

示例如下:

 mysql> insert into smartphone values('MicroSoft','WindowsPhone');
 Query OK, 1 row affected, 1 warning (0.01 sec)
 
 mysql> show warnings;
 +---------+------+----------------------------------------------+
 | Level   | Code | Message                                      |
 +---------+------+----------------------------------------------+
 | Warning | 1265 | Data truncated for column 'os_name' at row 1 |
 +---------+------+----------------------------------------------+
 1 row in set (0.00 sec)
 
 mysql> select * from smartphone;
 +-----------+---------+
 | vendor    | os_name |
 +-----------+---------+
 | MicroSoft |         |
 +-----------+---------+
 1 row in set (0.00 sec)
  1. 给 ENUM 的列进行排序操作时,是按照表定义中指定的顺序,而不是根据字母表的顺序。

示例如下:

 mysql> insert into smartphone values('HUAWEI','android'),('Apple','ios'),('NOKIA','other'),('XIAOMI','android');
 Query OK, 4 rows affected (0.01 sec)
 Records: 4  Duplicates: 0  Warnings: 0
 
 mysql> select * from smartphone order by os_name asc;
 +-----------+---------+
 | vendor    | os_name |
 +-----------+---------+
 | MicroSoft |         |
 | Apple     | ios     |
 | HUAWEI    | android |
 | XIAOMI    | android |
 | NOKIA     | other   |
 +-----------+---------+
 5 rows in set (0.00 sec)

由于 ENUM 实际存储是用的 TINYINT,因此在存储较小的数字时,建议直接使用 TINYINT 即可。

只有一些比较特殊的场景,例如星期、季节、颜色、性别等固定不变的枚举值,才建议你选用 ENUM 这个数据类型。

那 VARCHAR (N) 能用吗?

选择 VARCHAR (N) 这个数据类型的好处是比较直观。比如 “canceled”,“finished”,“delivering” 的可读性显然比 1,2,3 要好,但是占用的存储空间也相应的变大。

但是话说回来,从性能角度考虑 VARCHAR (N) 会比 TINYINT 略差,主要表现在 JOIN 和 ORDER BY 的操作上。

你可以根据具体情况选用。

最后再来说说 TINYINT 这个数据类型。

首先 TINYINT 的字节数很小,只占 1 个字节;其次它的查询和 DML 的性能表现都不错。

因此除了一些比较特殊的应用场景,再综合上面的一些问题来看,在大多数的场景下,还是建议优先选用 TINYINT

4. 小结

今天,我给你介绍了 MySQL 里面最频繁使用的整形数据类型,包括 INT 和 TINYINT。

INT 定义中的数字只是表示显示宽度,它并不限制 MySQL 的实际存储空间。因此在开发工作中不要把 INT (1) 当初 TINYINT 来用,也千万不要把 INT (20) 当成 BIGINT 用。

对于 UNSIGNED 这个属性,最适合的场景就是主键自增值,或者一些不允许出现负数的应用中。在使用时要注意带有 UNSIGNED 的字段,在做计算时如果出现负数可能会报错。

最后我还给你介绍了 TINYINT、ENUM、VARCHAR (N) 在存储枚举值的情况。鉴于前面描述的 ENUM 和 VARCHAR (N) 的一些情况,因此我的建议是首选 TINYINT,特殊场景适当选用其他类型。

参考文献:

  1. MySQL 官方文档:整形数据类型,https://dev.mysql.com/doc/refman/8.0/en/integer-types.html
  2. 《MySQL 技术内幕:SQL 编程》
}
立即订阅 ¥ 48.00

你正在阅读课程试读内容,订阅后解锁课程全部内容

千学不如一看,千看不如一练

手机
阅读

扫一扫 手机阅读

MySQL 开发高频面试题精选
立即订阅 ¥ 48.00

举报

0/150
提交
取消