Loading...
墨滴

楼仔

2021/06/30  阅读:41  主题:橙心

【MySql系列1】数值类型长度问题

你知道tinyint(1)、tinyint(11)、int(1)和int(5)的区别么?不懂?那你就要好好看看这篇文章了。

前言

上周和荣哥讨论DB字段的创建时,有个字段只存储整型状态值,因为状态值只能取值0和1,所以我用的是tinyint(1),然后荣哥说“你用这个1和用11,其实是一样的”,我心里顿时嘀咕“这个1不就是长度1么?”。后来网上查了一下相关资料,发现我和他的理解都错了。

整型类型

基础知识

整数类型又称数值型数据,数值型数据类型主要用来存储数字。MySQL 提供了多种数值型数据类型,不同的数据类型提供不同的取值范围,可以存储的值范围越大,所需的存储空间也会越大。

MySQL 主要提供的整数类型有 TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT,其属性字段可以添加 AUTO_INCREMENT 自增约束条件。下表中列出了 MySQL 中的数值类型。

从上表中可以看到,不同类型的整数存储所需的字节数不相同,占用字节数最小的是 TINYINT 类型,占用字节最大的是 BIGINT 类型,占用的字节越多的类型所能表示的数值范围越大。

根据占用字节数可以求出每一种数据类型的取值范围。例如,TINYINT 需要 1 个字节(8bit)来存储,那么 TINYINT 无符号数的最大值为 28-1,即 255;TINYINT 有符号数的最大值为 27-1,即 127。其他类型的整数的取值范围计算方法相同,如下表所示:

最大显示宽度

比如int(5),这个5就是M值,表示的是最大显示宽度,这个值有什么含义呢?

细心的朋友应该有注意到过MySQL手册上有这么一句话:M指示最大显示宽度。最大有效显示宽度是255。显示宽度与存储大小或类型包含的值的范围无关;

这句话看上去不太容易理解,因为这里有个关键词容易让我们混淆,"最大显示宽度"我们第一反应是该字段的值最大能允许存放的值的宽度。 以为我们建了int(1),就不能存放数据10了,其实不是这个意思。

这个M=5我们可以简单的理解成为,我们建立这个长度是为了告诉MySQL数据库我们这个字段的存储的数据的宽度为5位数, 当然如果你不是5位数(只要在该类型的存储范围之内)MySQL也能正常存储。

那问题又来了,既然“显示宽度与存储大小或类型包含的值的范围无关”,也就是实际使用时,int(1)和int(5)没有任何区别,那为啥还要指定这个“最大显示宽度”呢?

有网友给出如下解释:我推测当字段类型为数值时,设置M其实是在告诉数据库,我们预设该字段宽度是M,用来方便数据库做优化之类的东西,因为数值型都有其数值范围,所以在我们想存入超过M宽度的数值时,数据库会扩展字段空间来存储。

下面是整型的默认显示宽度:

TINYINT[(M)] [UNSIGNED] [ZEROFILL]  M默认为4
SMALLINT[(M)] [UNSIGNED] [ZEROFILL] M默认为6
MEDIUMINT[(M)] [UNSIGNED] [ZEROFILL] M默认为9
INT[(M)] [UNSIGNED] [ZEROFILL]   M默认为11
BIGINT[(M)] [UNSIGNED] [ZEROFILL] M默认为20

问题讨论

tinyint(1) vs tinyint(11)

再回到“前言”中提到的问题,就很好解决了,先看看荣哥提的那个问题“tinyint(1)和tinyint(11)是一样的”,这里存在2个问题,首先tinyint的M值默认是4,所以你指定11是没有意义的,因为tinyint无符号的最大范围是[0,255]。

所以对于tinyint,你可以指定M=1,也可以不指定M值,即采用默认值,指定M=11是不符合规范的,当然你如果偏要这么做,系统也不会报错,比如:

但是插入大于225的数据时,提示越界,只有插入小于225的数据时,才能插入成功:

这里有个有趣的点,我没有指定id_1为无符号整型tinyint,但是依然可以插入255的值,所以整型插入时,最大值可插入的是无符号最大范围值255,不是有符号最大范围值127。

其它的整型类型同理。

int(1) vs int(5)

对于int(1)和int(5),我们也可以演示一下,我们先创建表:

int的无符号整型最大取值为4294967295:

我们发现无论是int(1)还是int(5),在int范围外的数据插入失败,在int范围内的数据插入成功。

我之前想插入一个微秒的时间戳,之前用的是int(11),发现插入越界,后来我改成int(20),发现仍然越界,最后改成bigint(20),就可以了,现在终于知道原因了,原来存储数据的场景,和M根本没有关系,只和具体的类型有关系!

不过还是建议M的取值,在该类型的范围内,填写你认为最合理的值,这样可能有利于MySQL做一些优化操作,具体啥优化,我现在也不知道,只是猜测M对MySQL会起到一定的优化作用。

后记

一个简单的表字段整型类型创建,就可以牵扯出这么多有趣的知识,换做之前的我,可能就随便用用,估计是现在年纪大了,对很多事情喜欢较真,希望这种“较真”的精神能一直保持下去。

欢迎大家多多点赞,更多文章,请关注微信公众号“楼仔进阶之路”,点关注,不迷路~~

楼仔

2021/06/30  阅读:41  主题:橙心

作者介绍

楼仔