一文搞懂MySQL的数据类型中长度的含义

我们在数据库建表时,经常会困扰某个字段应该选择什么数据类型,以及填写什么长度。选择数据类型方面一般不会有什么大问题,但是在填写对应的长度的时候,很多人就会困扰,对应长度填写的数字到底是什么含义,以及会影响到哪些东西。笔者在翻阅网上的相关文章时,发现一大半文章写的都是错的,主要的问题在于搞混了“字符”和“字节”这两者的含义,甚至有的人觉得这就是一回事。如果对字符和字节不理解的读者,可以先阅读《一文搞懂字符和字节的含义》。本文我们通过实例来介绍MySQL的数据类型中长度的含义,读完本文能够让你在数据库建表的时候不再困惑。

字符串类型

常用的字符串类型的数据类型有 CHARVARCHAR 两种,两者后面都需要跟上一个数字表示长度,例如

CHAR(10) 
VARCHAR(10)

CHAR(n) 和 VARCHAR(n) 两者中的 n 含义均为该字段最大可容纳的字符数。(注意早期的版本中,n指的是字节数,你也不需要关注是哪些版本,因为是十多年前的版本了,估计一般人也用不到)。
占用空间

  • CHAR(n) 和 VARCHAR(n) 字段值的占用空间不是固定的,而是由实际存入的内容决定的,但在细节上两者有一些不同。我们均以 n=4 为例。

对于 CHAR(4) 表示固定容纳4个字符,当少于4个字符时,会使用空格填充空缺的部分,使其达到4个字符。如果超过4个字符,会自动截断超出部分。例如你存入数据为 'ab' ,实际会存入 'ab ' (ab后有2个空格),因此占用4个字节。以下以几个案例作为演示:

  1. 'a啊b' —— 字符数为3,少1个用空格补齐,因此实际存入 'a啊b ' ,字符数:4,字节数:1+3+1+1=6
  2. 'a啊b哈ccccccccc' —— 字符数超出4,仅保留前4个字符,因此实际存入 'a啊b哈' ,字符数:4,字节数:1+3+1+3=8
  3. 'a啊和哈' —— 字符数刚好为4,不需要截断和补齐,因此实际存入 'a啊和哈' ,字符数:4,字节数:1+3+3+3=10

对于 CHAR 字段,你在使用 CHAR_LENGTH() 和 LENGTH() 函数查询时,会发现和以上描述的情况不一致,我们放上代码演示:

(备注: CHAR_LENGTH() 函数返回字符串的字符数, LENGTH() 函数返回字符串的字节数)

-- 假定已存在表 tb ,其中包含字段 s_char 的数据类型定义为 CHAR(4) ,我们先进行插入操作,获取插入行id=1 
INSERT INTO `tb`(`s_char`) VALUES ('啊a'); 
-- 接下去查询该行 SELECT s_char, CHAR_LENGTH(s_char), LENGTH(s_char) FROM `tb` WHERE id=1; 
-- 结果为:s_char=>'啊a',CHAR_LENGTH(s_char)=>2,LENGTH(s_char)=>4

你会发现以上结果跟预想中的不一致,按照一般理解预期存入 '啊a' ,仅为2个字符,需补充2个空格,实际存入为 '啊a ' ,因此字符数为4,字节数为 3+1+1+1=6 。

这里造成偏差的原因并不是错误,而是 CHAR 字段在检索输出时,自动省略了右侧的空格。我们来演示一遍完整的流程:

预期存入 '啊a' ,少于4个字符,补充2个空格,因此实际存入的值为 '啊a ' ,该值字符数为4,字节数为6。在检索时,原值为 '啊a ' ,输出时自动省略右侧空格,实际输出为 '啊a' ,该字符串字符数为2,字节数为4。

下面再来说说 VARCHAR 类型,依然以 n=4 为例。区别于 CHAR 类型的补空, VARCHAR 类型对于未达到 n 字符的情况不会补空。

关于计算 VARCHAR 类型字符串的占用空间,有一点需要说明的是, VARCHAR 类型字符串的占用空间实际上包含2部分,一是存储数据本身占用的空间,二是描述数据的元数据占用的空间,例如 VARCHAR 类型会使用1个字节记录存入数据实际的字符数。下述描述的“占用空间”特指前者,即存储数据本身占用的空间,不包含描述数据的元数据占用的空间。(其他数据类型等同)

以下以几个案例作为演示:

(1) 'a啊b' —— 字符数为3,不补空,实际存入为 'a啊b' ,字符数为3,字节数为 1+3+1=5 。
(2)'a啊b哈ccccccccc' —— 字符数超出4,仅保留前4个字符,因此实际存入 'a啊b哈' ,字符数:4,字节数:1+3+1+3=8 。这种情况和 CHAR 类型处理一致。
(3)'a啊和哈' —— 字符数刚好为4,不需要截断和补齐,因此实际存入 'a啊和哈' ,字符数:4,字节数:1+3+3+3=10

整数类型

常用的整数数据类型有 tinyint ,smallint ,mediumint , int ,bigint 共计5种,在声明列时,后面也可以跟上 n ,例如 int(n) 。实际上这里的 n 非常鸡肋,几乎没有任何使用场景。它的含义是“显示位宽”,这个 n 无论填任何数,不影响存储环节,仅影响在检索时的输出格式,而且在非常严格的情况下才成立。我们描述一种应用场景:我们声明某列(列名取int_5)为 int(5) ,在声明列的时候,要使用到该特性,必须加上 zerofill (填充0)属性,即语句为

`int_5` int(5) unsigned zerofill DEFAULT NULL
     -- 备注:加zerofill必须同时加unsigned    

当插入的数字小于5位时,在特定客户端检索输出时,会在数字前“补0”,凑足5位数字。(大于5位则原数字原样显示)例如存储的数字是123,那么输出00123 。说它鸡肋,主要有以下几个原因:
(1)对存储环节没有任何帮助,仅改变输出显示环节。而“格式化显示”一般在前端或者后端的应用层操作就可以了,无需在数据库中输出时操作。
(2)格式化方式仅仅只有“补0”一种方式。
(3)仅针对特定客户端输出时才有显示效果,目前仅发现使用MySQL Shell才有显示效果,其他客户端连接时均无。

由于以上原因,所以几乎没有开发者会使用这个特性。

占用空间这5种整型的占用空间是固定的,均与其后设置的 n 无关,例如设置字段类型为 int ,则无论 n 设置什么,它占用的空间就是4个字节。这5种整型的占用空间分别是: tinyint :1个字节,smallint :2个字节,mediumint :3个字节,int :4个字节,bigint :8个字节

很多人说经常记不住他们的取值范围,实际上很好算,例如 tinyint 占用1个字节,也就是8位,每1位都包含0和1两种情况,因此共2的8次方为256种情况,如果是无符号(unsigned),取值范围就是0至255。如果是有符号情况,由于第1位要用来表示符号,因此可用7位表示数字,2的7次方为128,再加上符号,取值范围为 -128至127 。其它几种数据类型也可以按照这个方法计算。

怕有的人还是难以理解,这里再重复一遍,以 int 为例,无论 int(n) 中的 n 设置什么值,无论插入的这个值或大或小,只要在取值范围内,那这个字段就是占用4个字节

另外再补充一点,当插入的值,超出取值范围的时候,MySQL并不会报错,而是自动变成成在取值范围内最接近该值的边界值。例如字段为 tinyint ,有符号型时取值范围 -128至127 ,当你输入-222时,不会报错,会自动存入最接近-222的-128,当你输入222时,会自动存入127。这一点需要尤其注意,否则很容易造成巨大的bug。

浮点型

FLOAT 类型固定占用4个字节, DOUBLE 类型固定占用8个字节,逻辑和上述的整型类似,不再赘述。

下面我们来说说 DECIMAL 类型,它的定义方式是 DECIMAL(M,D) ,其中 M 表示最大位数,D 表示小数点右侧的位数。这里的“位”不是二进制的比特位,而是指十进制的数字的位数。

例如我们定义 DECIMAL(5,2) ,则表示最大位数为5位,小数点后2位,因此小数点前还剩下3位,于是取值范围为 -999.99至999.99 。可以这样理解:M-D 的值为小数点前的位数,D 的值为小数点后的位数,要算取值范围则各个位置填充9,取正负范围。那么容易计算 DECIMAL(5,1) 的取值范围是 -9999.9至9999.9 ; DECIMAL(4,2) 的取值范围是 -99.99至99.99 。

占用空间

DECIMAL(M,D) 的存储方式和其他数字类型都完全不同,它是以字符串形式进行存储的。这可能有点不好理解,以整型 tinyint 为例,它存储的值是直接为十进制到二进制的转换,以无符号型为例,当需要存入的值为100值,将100转化为二进制为1100100 ,使用1个字节即8位记录,实际存入的是 01100100 。但是用 DECIMAL 类型存储时,比如定义 DECIMAL(3,0) ,存入100时,实际存入的是由字符“1”,“0”,“0”拼接而成的字符串“100”的二进制值,存入时占用3个字节,分别是31,30,30(注意这是十六进制)。
1个数字字符占用1个字节,因此定义为 DECIMAL(M,D) 占用 M 个字节。

(同上所述,M个字节为数据本身的占用空间,另外描述该数据的元数据还固定占用2个字节的空间)。需要注意的是, DECIMAL 类型在存储时有补0操作。小数点前不足,向更高位补0,小数点后不足,向更低位补0。以 DECIMAL(5,2) 为例,如果准备存入9.5,小数点前应为3位,缺2位,小数点后应为2位,缺1位,各补0后,实际存入 '009.50' ,转化为十六进制为30 30 39 2E 35 30 。但是在检索输出时,小数点前的0一般会省略,而小数点后的0会保留,这一点也需要注意。以上就是最长使用的3种数据类型的长度含义以及其占用空间,理解了以上概念,在使用MySQL时,将会更得心应手。

转载于https://zhuanlan.zhihu.com/p/111028232

posted @ 2021-04-27 17:46  杰的博客#  阅读(207)  评论(0编辑  收藏  举报