MySQL 数据类型-白红宇

MySQL 数据类型

阅读量：5226 次

发布时间：2019-06-14

本文共 7997 字，大约阅读时间需要 26 分钟。

前言

　　如果我们只顾着往前学习，不会总结，那一段时间之后，你将一无所有。

　　我们学习一门语言，无非从两个方面入手：

　　　　基本语法，有了语法，我们才可以组织逻辑

　　　　数据类型，即在特定场景下选择合适的数据类型，到底是用整型还是浮点型还是字符串，每种数据机构占多少字节，最大值是多少。这点只针对强类型的语言，像 js 这种弱类型的语言，是不需要考虑这一点的

希望通过一篇文章的梳理，可以把 MySQL 的基本数据类型总结总结。

整型

先从最基本的数据类型整型说起，首先用一张表格归纳一下：

数据类型	字节数	带符号最小值	带符号最大值	不带符号最小值	不带符号最大值
TINYINT	1	-128	127	0	255
SMALLINT	2	-32768	32767	0	65535
MEDIUMINT	3	-8388608	8388607	0	16777215
INT	4	-2147483648	2147483647	0	4294967295
BIGINT	8	-9223372036854775808	9223372036854775807	0	18446744073709551616

即使是带符号的 BIGINT，其实也已经是一个天文数字了，什么概念，9223372036854775807 我们随便举下例子：

以 byte 为例可以表示 8589934592GB-->8388608TB-->8192PB

以毫秒为例可以表示 292471208 年

所以从实际开发的角度，我们一定要为合适的列选取合适的数据类型，即到底用不用得到这种数据类型？举个例子：

一个枚举字段明明只有 0 和 1 两个枚举值，选用 TINYINT 就足够了，但在开发场景下却使用了 BIGINT，这就造成了资源浪费

简单计算一下，假使该数据表中有 100W 数据，那么总共浪费了 700W 字节也就是 6.7M 左右，如果更多的表这么做了，那么浪费的更多

要知道，MySQL本质上是一个存储，以Java为例，可以使用 byte 类型的地方使用了 long 类型问题不大，因为绝大多数的对象在程序中都是短命对象，方法执行完毕这块内存区域就被释放了，7 个字节实际上不存在浪不浪费一说。但是 MySQL 作为一个存储，8 字节的 BIGINT 放那儿就放那儿了，占据的空间是实实在在的。

举个例子：

1 create table if not exists test_tinyint (2     num tinyint3 ) engine=innodb charset=utf8;4 5 insert into test_tinyint values(-100);6 insert into test_tinyint values(255);

执行第 6 行的代码时候报错 "Out of range value for column 'num' at row 1"，即很清楚的我们可以看到插入的数字范围越界了，这也同样反映出MySQL中整型默认是带符号的。

把第 2 行的 num 字段定义改为 "num tinyint unsigned" 第 6 的插入就不会报错了，但是第 5 行的插入 -100 又报错了，因为无符号整型是无法表示负数的。

整型（N）形式

在开发中，我们会碰到有些定义整型的写法是 int(11)，这种写法从我个人开发的角度看我认为是没有多大用，不过作为一个知识点做一下讲解吧。

int(N) 我们只需要记住两点：

无论 N 等于多少，int 永远占 4 个字节

N表示的是显示宽度，不足的用0补足，超过的无视长度而直接显示整个数字，但这要整型设置了 unsigned zerofill 才有效

下面举个例子，写一段 SQL：

1 drop table if exists test_int_width; 2 create table test_int_width ( 3     a int(5), 4     b int(5) unsigned, 5     c int(5) unsigned zerofill, 6     d int(8) unsigned zerofill 7 ) engine=innodb charset=utf8; 8  9 insert into test_int_width values(1, 1, 1, 1111111111);10 11 select * from test_int_width;

从上面的两点，我们应该预期结果应该是 1,1,00001,1111111111

我们看一下结果：

不符合预期是吧，因为这个问题我也有过困扰，后来查了一下貌似是 Navicat 工具本身的问题，我们使用控制台就不会有这个问题了：

不过实际工作场景中反正我是没有碰到过指定 zerofill 的，也不知道具体应用场景，如果有使用这种写法的朋友可以留言告知具体在哪种场景下用到了这种写法。

浮点型

整型之后，下面是浮点型，在 MySQL 中浮点型有两种，分别为 float、double，它们三者用一张表格总结一下：

数据类型	字节数	备注
float	4	单精度浮点型
double	8	双精度浮点型

下面还是用 SQL 来简单看一下 float 和 double 型数据，以 float 为例，double 同理：

drop table if exists test_float;create table test_float (    num float(5, 2)) engine=innodb charset=utf8;insert into test_float values(1.233);insert into test_float values(1.237);insert into test_float values(10.233);insert into test_float values(100.233);insert into test_float values(1000.233);insert into test_float values(10000.233);insert into test_float values(100000.233);select * from test_float;

显示结果为：

从这个结果我们总结一下 float(M,D) 、double(M、D) 的用法规则：

D 表示浮点型数据小数点之后的精度，假如超过 D 位则四舍五入，即 1.233 四舍五入为 1.23，1.237四舍五入为 1.24

M 表示浮点型数据总共的位数，M = 5 则表示总共支持五位，即小数点前只支持三位数，所以我们并没有看到 1000.23、10000.233、100000.233 这三条数据的插入，因为插入都报错了

当我们不指定 M、D 的时候，会按照实际的精度来处理。

定点型

介绍完 float、double 两种浮点型，我们介绍一下定点型的数据类型 decimal 类型，有了浮点型为什么我们还需要定点型？写一段SQL看一下就明白了：

drop table if exists test_decimal;create table test_decimal (    float_num float(10, 2),    double_num double(20, 2),    decimal_num decimal(20, 2)) engine=innodb charset=utf8;insert into test_decimal values(1234567.66, 1234567899000000.66, 1234567899000000.66);insert into test_decimal values(1234567.66, 12345678990000000.66, 12345678990000000.66);

运行结果为：

看到 float、double 类型存在精度丢失问题，即写入数据库的数据未必是插入数据库的数据，而 decimal 无论写入数据中的数据是多少，都不会存在精度丢失问题，这就是我们要引入 decimal 类型的原因，decimal 类型常见于银行系统、互联网金融系统等对小数点后的数字比较敏感的系统中。

最后讲一下 decimal 和 float/double 的区别，个人总结主要体现在两点上：

float/double 在db中存储的是近似值，而 decimal 则是以字符串形式进行保存的

decimal(M,D) 的规则和float/double相同，但区别在 float/double 在不指定M、D时默认按照实际精度来处理而 decimal 在不指定 M、D 时默认为 decimal(10, 0)

日期类型

接着我们看一下 MySQL 中的日期类型，MySQL 支持五种形式的日期类型：date、time、year、datetime、timestamp，用一张表格总结一下这五种日期类型：

数据类型	字节数	格式	备注
date	3	yyyy-MM-dd	存储日期值
time	3	HH:mm:ss	存储时分秒
year	1	yyyy	存储年
datetime	8	yyyy-MM-dd HH:mm:ss	存储日期+时间
timestamp	4	yyyy-MM-dd HH:mm:ss	存储日期+时间，可作时间戳

下面我们还是用 SQL 来验证一下：

drop table if exists test_time;create table test_time (    date_value date,    time_value time,    year_value year,    datetime_value datetime,    timestamp_value timestamp) engine=innodb charset=utf8;insert into test_time values(now(), now(), now(), now(), now());

看一下插入后的结果：

MySQL 的时间类型的知识点比较简单，这里重点关注一下 datetime 与 timestamp 两种类型的区别：

上面列了，datetime 占 8 个字节，timestamp 占 4 个字节

由于大小的区别，datetime 与 timestamp 能存储的时间范围也不同，datetime 的存储范围为 1000-01-01 00:00:00 —— 9999-12-31 23:59:59，timestamp 存储的时间范围为 19700101080001——20380119111407

datetime 默认值为空，当插入的值为 null 时，该列的值就是 null；timestamp 默认值不为空，当插入的值为 null 的时候，mysql 会取当前时间

datetime 存储的时间与时区无关，timestamp 存储的时间及显示的时间都依赖于当前时区

在实际工作中，一张表往往我们会有两个默认字段，一个记录创建时间而另一个记录最新一次的更新时间，这种时候可以使用 timestamp 类型来实现：

create_time timestamp default current_timestamp comment "创建时间",update_time timestamp default current_timestamp on update current_timestamp comment "修改时间",

char和varchar类型

最后看一下常用到的字符型，说到 MySQL 字符型，我们最熟悉的应该就是 char 和 varchar 了，关于 char 和 varchar 的对比，我总结一下：

char 是固定长度字符串，其长度范围为 0~255 且与编码方式无关，无论字符实际长度是多少，都会按照指定长度存储，不够的用空格补足；varchar 为可变长度字符串，在utf8 编码的数据库中其长度范围为 0~21844

char 实际占用的字节数即存储的字符所占用的字节数，varchar 实际占用的字节数为存储的字符 +1 或 +2 或 +3

MySQL 处理 char 类型数据时会将结尾的所有空格处理掉而 varchar 类型数据则不会

关于第一点、第二点，稍后专门开一个篇幅解释，关于第三点，写一下SQL验证一下：

drop table if exists test_string;create table test_string (    char_value char(5),    varchar_value varchar(5)) engine=innodb charset=utf8;insert into test_string values('a', 'a');insert into test_string values(' a', ' a');insert into test_string values('a ', 'a ');insert into test_string values(' a ', ' a ');

使用 length 函数来看一下结果：

验证了我们的结论，char 类型数据并不会取最后的空格。

varchar 型数据占用空间大小及可容纳最大字符串限制探究

接上一部分，我们这部分来探究一下 varchar 型数据实际占用空间大小是如何计算的以及最大可容纳的字符串为多少，首先要给出一个结论：这部分和具体编码方式有关，且MySQL版本我现在使用的是5.7，当然5.0之后的都是可以的。

先写一段 SQL 创建表，utf8 的编码格式：

drop table if exists test_varchar;create table test_varchar (    varchar_value varchar(100000)) engine=innodb charset=utf8;

执行报错：

Column length too big for column 'varchar_value' (max = 21845); use BLOB or TEXT instead

按照提示，我们把大小改为21845，执行依然报错

Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs

改为 21844 就不会有问题，因此在 utf8 编码下我们可以知道 varchar(M)，M 最大=21844。那么 gbk 呢：

drop table if exists test_varchar; create table test_varchar ( varchar_value varchar(100000) ) engine=innodb charset=gbk;

同样的报错：

Column length too big for column 'varchar_value' (max = 32767); use BLOB or TEXT instead

把大小改为 32766，也是和 utf8 编码格式一样的报错：

Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs

可见 gbk 的编码格式下，varchar(M) 最大的 M=32765，那么为什么会有这样的区别呢，分点详细解释一下：