MySQL的数据类型中长度的含义

前言

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

本文我们通过实例来介绍MySQL的数据类型中长度的含义，读完本文能够让你在数据库建表的时候不再困惑。

字符串类型

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

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 字符的情况不会补空。

<u style="text-decoration: none; border-bottom: 1px dashed grey;">关于计算 VARCHAR 类型字符串的占用空间，有一点需要说明的是， VARCHAR 类型字符串的占用空间实际上包含2部分，一是存储数据本身占用的空间，二是描述数据的元数据占用的空间，例如 VARCHAR 类型会使用1个字节记录存入数据实际的字符数。下述描述的“占用空间”特指前者，即存储数据本身占用的空间，不包含描述数据的元数据占用的空间。（其他数据类型等同）</u>

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

（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?from_voters_page=true