1. 前言

MySQL 中支持的数据类型从整体上可以分为数值类型和日期时间类型,其中数值类型可以分为整数类型、浮点数类型、定点数类型和位类型。整数类型包含常见的 SMALLINT、MEDIUMINT、INT、BIGINT,浮点数类型主要是 FLOAT 单精度浮点数类型和 DOUBLE 双精度浮点数类型。日期类型也有 DATE、TIME、YEAR、DATETIME、TIMESTAMP 类型。关于整数类型和浮点数类型存在一些比较常见的误区,经常被面试官考察。

2. int (3) 和 int (11)

面试官提问: MySQL 中 int (3) 和 int (11) 这两种用法有什么区别呢?

题目解析:

这道题非常常见,但是没有仔细了解过 MySQL 中 int 数据类型用法的同学,很容易掉进误区。

我们知道 varchar(m) 用于修饰变长字符,其中 m 表示能够存储的字符上限。

例如 username varchar(2) 在 MySQL 5.0 之后的版本表示最多接受 2 个汉字的字符作为用户名存储,如果长度超限会报错:ERROR 1406 (22001): Data too long for column 'username' at row 1 。所以候选人可能会想当然的认为 int(m) 中的 m 表示存储数字的长度,int(3)int(11) 分别表示最多存储 3 位数和 11 位数,这种观点是完全错误的!

2.1 int (3) 和 int (11) 占用的硬件存储空间完全相同

首先,我们在申明某个字段数据类型为 int 的时候,不管是 int(3) 还是 int(11),在 MySQL 中存储时都占用 4 个字节的长度。

1 个字节(Byte) = 8 个二进制位(bit),所以 1 个 int = 4 Byte = 4 * 8 bit = 32 bit,计算机中使用首个比特位存储数字符号(参考补码的定义),所以可以算出 int(m) 的存储范围在 [-2147483648,2147483647] 之间。

2.2 int (3) 和 int (11) 在 zerofill 关键词修饰时展示不同

我们在之前创建的 mooc_demo 数据库中创建一张测试表:

DROP TABLE IF EXISTS `test_int`;

CREATE TABLE `test_int` (
  `id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '数据库主键',
  `num1` int(3)  zerofill,
  `num2` int(11)  zerofill
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

再插入一条测试数据:

insert into test_int (num1, num2) values (1,1);

现在执行 select * from test_int; 查询语句,查询结果如图:

图片描述

查询结果

如上图所示,存储相同的数字 1,num1 前补全了 2 个 0,num2 前补全了 10 个 0,

所以可以得出结论:int(m) 中的 m 表示在 zerofill 修饰时,数字长度不足 m 时前缀补充的 0 的个数,除此之外,两者使用时没有任何区别。

3. double(m,n)

面试官: MySQL 中 double (m,n) 中的 m 和 n 有什么含义?

题目解析: 这道题容易和上题一起出现,混淆视听,但是难度相对就简单多了。

double(m,n)float(m,n) 以及 decimal(m,n) 中的 m 和 n 定义均相同,而且比较清晰:

  • m:数据精度,即数据的总长度;
  • n:小数点精度,即浮点数小数点后的长度。
  • 举例说明float(6,2) 表示最多能存储 6 位长度的浮点数,并且小数点精度为 2。

实战验证下上述结论, 还是在之前创建的 mooc_demo 数据库中创建一张测试表:

DROP TABLE IF EXISTS `test_float`;

CREATE TABLE `test_float` (
  `id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '数据库主键',
  `num1` float(6,2)  zerofill,
  `num2` double(6,2)  zerofill
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

继续插入测试数据:

insert into test_float (num1, num2) values (1234.5678,1234.5678); 

执行 select * from test_float; 查询语句,查询结果如图:

图片描述

查询结果

如上图所示,小数点 2 位之后的数据被截断,符合 SQL 定义时的预期。

4. 小结

MySQL 基础数据类型的知识学习可以从两个方面入手,一点是基本语法,学习基础语法的目的是能够上手使用这些数据类型,另一点是如何选择在合适的场景使用合适的数据类型,需要明确这种数据类型会占用多少的字节空间,数据类型的最小值和最大值是什么,选择不同数据类型可能会存在什么样的潜在问题。