百木园概述
要想学好mysql，了解其支持的基本数据类型以及内部原理是极为重要的，只有这样，我们才能根据不同的业务要求来选择不同的数据类型，实现最佳的存储效果和查询性能，因而本文就着重总结一下mysql支持的数据类型以及内部的存储原理。
总体来说，mysql一共分成了四类：数值类型、日期和时间类型、字符串类型、二进制类型等。
数值类型
数值类型是最为基础的类型，在业务开发中存储递增主键ID、金额、数量等属性时，都会经常选择数值类型来进行存储。整体上将数值类型分为整形、浮点型和定点数类型三类，其中整型对应我们现实生活中常用的整数类型；而浮点型则是对应现实生活中的小数类型；定点数类型则是为了存储精确的小数而被设计出来的。
整型
整型之中，根据是否有符号又被分为无符号数和有符号数，同时不同存储空间以及整数表示范围的考量，整数类型又被分成了TINYINT、SMALLINT、MEDIUMINT、INT以及BIGINT五类，各个类别的类型以及所占空间和含义如下表所示：
| 类型 | 占用的存储空间 | 无符号数的取值范围 | 有符号数的取值范围 | 含义 |
|---|---|---|---|---|
| TINYINT | 1字节 | 0~$2^8 $ | \\(-2^{7}\\)~\\(2^{7}-1\\) | 非常小的整数 |
| SMALLINT | 2字节 | 0~\\(2^{16}-1\\) | \\(-2^{15}\\)~\\(2^{15}-1\\) | 小的整数 |
| MEDIUMINT | 3字节 | 0~\\(2^{24}-1\\) | \\(-2^{23}\\)~\\(2^{23}-1\\) | 中等大小的整数 |
| INT（别名INTEGER） | 4字节 | 0~\\(2^{32}-1\\) | \\(-2^{31}\\)~\\(2^{31}-1\\) | 标准的整数，跟Java中的int类型相同 |
| BIGINT | 8字节 | 0~\\(2^{64}-1\\) | \\(-2^{64}\\)~\\(2^{64}-1\\) | 大整数 |
具体使用时，为某个变量设定变量类型时加上对应的类型关键字即可；在区分无符号数以及有符号数时，则需要加上UNSIGINED关键字来加以区分，加上该关键字后，则表示使用的无符号数，比如TINYINT UNSIGNED表示的就是无符号TINYINT类型的数。
浮点型
浮点型主要用来存储小数，其实现和存储范围和我们在其他编程语言中学习的类型，分为单精度浮点型（FLOAT）和双精度浮点型（DOUBLE）两类，两者的存储空间和表示范围如下表所示：
| 类型 | 占用的存储空间 | 绝对值最小的非0值 | 绝对值最大值 | 含义 |
|---|---|---|---|---|
| FLOAT | 4个字节 | ±1.175494351E-38 | ±4.402823466E+38 | 单精度浮点型 |
| DOUBEL | 8字节 | ±2.22507385072014E-308 | ±1.7976931348623157E+3008 | 双精度浮点型 |
具体浮点数内部的存储原理以及存储范围的确定，感兴趣的同学可以参考之前写的一篇文章《\"从内存角度分析浮点数大小比较方法\"》此处不再赘述。
从表中可以看到浮点型数据可以将大量十进制小数转成二进制进行存储，但实际存储过程中许多小数存在误差，即存在精度损失。为了解决该问题，在MYSQL中引入了\"定点数类型\"来进行实现对小数的精确存储。
定点数类型
定点类型作为精确存储小数的方式，它的设计思路和存储原理和浮点数有较大不同，其具体的结构如下表所示：
| 类型 | 占用的存储空间 | 取值范围 |
|---|---|---|
| DECIMAL(M,D) | 取决于M和D的值 | 取决于M和D的值 |
从表中可以看出一个定点数类型的数有两部分构成：
- M：表示该小数最多包含的有效数字个数。比如2.3有效数字个数为2；0.2,有效数字个数为1
- D：表示该小数保留小数点后十进制数字的个数，简单来讲就是小数的位数。比如2.3中D的值为1；8.321中D的值为3。
在存储时，为了保证定点数不损失小数精度，因而采用如下存储策略：
将十进制小数用小数点分隔开，分别把小数点左右的两个十进制整数存储起来
比如存储8.32时，分别将8和32分开存储，这样就相当于保存了8.32这一精确的小数。
具体使用时，不同的M和D值会影响到存储的小数范围，本着“能少用存储空间就少用存储空间”的原则，mysql在设计时，采用如下策略来对DECIMAL(M,D)数据类型分配存储空间，存储数据。我们以DECIMAL(16,4)为例：
- 第一步 划分位数：首先按照M和D的大小来划分，整数位和小数位，在本例中，总的有效数字位数为16，可存储的小数位数为4，可存储的整数位数为12。划分的示意图如下图所示：（注意在本图中，每一个方格代表的是十进制位而不是二进制位。）
- 第二步 分组：从小数点位置开始，分别向两边进行分组操作，将每个整数每隔9个十进制位划分成一组，划分结果如下图所示：
从图中可以看到，在分组时，如果不够9个十进制位，那么最终也会被单独划分成一组，比如第一组和第三组。
- 第三步 转换二进制：针对每个组中的十进制数字，分别将其转成二进制数字进行存储。为了有效利用存储空间，在存储时组中包含的十进制数字位数同步，则所占用的存储空间也不同，具体对应关系表如下：
| 组中包含的十进制位数 | 占用存储空间 | 备注 |
|---|---|---|
| 1或2 | 1字节 | 最多需要存储十进制数99，一个字节可存储范围为（-128,127）满足要求 |
| 3或4 | 2字节 | 最多需要存储十进制数9999，两个字节可存储范围为（-32,768，32,767）满足要求 |
| 5或6 | 3字节 | 最多需要存储十进制数999,999，三个字节可存储范围为（-8,388,608,8，388,607）满足要求 |
| 7或8或9 | 4字节 | 最多需要存储十进制数999,999,999，4个字节可存储范围（-2,147,483,648，2,147,483,647）满足要求 |
从表中可以看出，在选择存储位数时，可表示的存储范围实际上是大于需要表示的数字范围的，因此此种存储方案是可行的的。当然有的小伙伴会问了，似乎这样存储是有空间浪费的？这也是没办法的事情，只能通过牺牲空间来缓存存储精度了，在工程实践中经常也会有这种tradeoff 🐶~~
- 第四步 最高位置为1：按照上述方式转换完成后将二进制序列的最高位设置成1（这一步有其他用途，具体用途此处先挖个坑~），然后将对应数据存储。
此处以存储小数1234567890.1234为例：
- 首先将该十进制数进行划分，分成如下三组：
1 234567890 1234
其中前两组是整数部分，最后一组是小数部分。第一组存储整数1；第二组存储整数2345678890；第三组存储1234；
- 分别将每一组的十进制数转成二进制，为了便于表示此处均用16进制来表示，最终三组数的结果如下：
0x0001 0x0DFB38D2 0x04D2
将三组16进制数连接起来之后的结果为0x00010DFB38D204D2。
- 将最高位设置为1，获取到的最终的待存储的16进制数为
0x80010DFB38D204D2。 - 最终将该数据按照对应关系表来进行存储，最终的存储情况如下图所示：
当然这时候可能就有小伙伴问了，如果存储一个负数该咋办？？比如不是存储1234567890.1234而是要存储-1234567890.1234
此时只需要将1234567890.1234对应的二进制存储数0x80010DFB38D204D2每一位都对应执行取反操作即可，即得到0x7FFEF204C72DFB2D即可。
从上述内容中，我们可以得知，DECIMAL数据类型在存储数据时，其存储精度和范围是可选的根据D和M值的变化而变化，而且不同的D值和M值最终实际占用的空间也是不同的。
在默认情况下，M的值为10，D的值为0；另外DECIMAL数据类型能够表示的数据范围也不是无限大的，其中允许M的最大值为65，D的最大值为30，而且D的值是小于等于M的值。
日期和时间类型
在许多业务场景下，我们需要在数据库中存储日期和时间等信息，这时候就需要日期和时间格式来进行存储。
MySQL提供了多种日期和时间类型，各种类型的表示范围和占用空间情况如下表所示：
| 类型 | 占用的存储空间（字节） | 取值范围 | 含义 |
|---|---|---|---|
| YEAR | 1 | 1901~2155 | 年份值 |
| DATE | 3 | ‘1000-01-01’~‘9999-12-31 | 日期值 |
| TIME | 3 | ‘-838:59:59’~‘838:59:59’ | 时间值 |
| DATETIME | 8 | ‘1000-01-01 00:00:00’~ ‘9999-12-31 23:59:59’ | 日期和时间值 |
| TIMESTAMP | 4 | ‘1970-01-01 00:00:01’~ ‘2038-01-19 03:14:07’ | 时间戳 |
字符串类型
在使用数据库过程中，我们最为常用的类型应该就是字符串类型。在MySQL中为了应对不同的应用场景，在设计字符串时，提供了多种类型可供选择：
| 类型 | 最大长度 | 存储空间要求 | 含义 |
|---|---|---|---|
| CHAR(M) | M个字符 | M × W字节 | 固定长度的字符串 |
| VARCHAR(M) | M个字符 | L+1或者L+2字节 | 可变长度的字符串 |
| TINYTEXT | \\(2^8-1\\)字节 | L+1字节 | 非常小型的字符串 |
| TEXT | \\(2^{16}-1\\)字节 | L+2字节 | 小型的字符串 |
| MEDIUMTEXT | \\(2^{24}-1\\)字节 | L+3字节 | 中等大小的字符串 |
| LONGTEXT | \\(2^{32}-1\\)字节 | L+4字节 | 大型的字符串 |
其中M表示该数据类型最多能存储的字符数量，M表示在特定字符集下编码一个字符需要的字节数；L代表实际向该类型的列中存储字符串在特定字符集下所占的字节数。
CHAR(M)类型
CHAR(M)中的M表示最多可以存储的字符数量。其取值范围为0~255，默认值是1。其中CHAR(0)是一种比较特殊的类型只能存储空字符串\'\'或者NULL值。
在不同的字符集编码情况下，CHAR(M)的所占用的存储空间是不同的。如果在某个字符集编码类型下，存储一个字符需要W个字节则针对该编码情况下CHAR(M)类型所占有的存储空间便为M×W字节。比如在ASCII字符集下的CHAR(5)类型，ASCII字符集编码一个字符需要1个字节，因此在该编码集下，CHAR(5)类型占用的存储空间为5×1=5字节。
如果实际存储的字符串在特定编码集下占用的存储空间小于M×W字节，剩余空间则会用空格字符（也就是\' \'）来进行填充。比如表中某一列的值使用ASCII字符集下面的CHAR(5)类型，当我们想要将字符串\'abc\'存储到该列时需要实际占用的存储空间由3字节小于该类型下的5字节存储空间，因而会把剩余的2个字节用空格字符进行填充。
很明显，这种存储方式下，当M非常大，而实际存储的字符却比较短时，会对存储空间有较大浪费。
VARCHAR(M)
在实际业务开发过程中，某个列存储的字符串它的大小是长短不一的，如果直接使用CHAR(M)类型存储会有较大的空间开销，因而MySQL中提供了VARCHAR类型来解决该问题。
VARCHAR(M)中的M也表示最多可以存储的字符数量，理论上能够存储的范围为1~65535。但MySQL中规定表的一行数据占用的存储空间总共不得超过65535个字节，也就是说VARCAHR(M)类型实际能够容纳的字符串数量是小于65535的。
在实际存储时VARCHAR(M)类型的数据实际占用的存储空间是不确定的，需要针对不同情况进行分析。为什么这样说呢？因为VARCHAR(M)类型所对应的存储空间实际上是由如下两部分构建成的：
-
第一部分：真正的字符串内容。具体大小跟存储的长度和所用编码方式有关，此处假定采用特定字符编码后所占用的字节数为L。
-
第二部分：额外占用的字节数。这部分字节主要用来记录VARCHAR(M)类型字符串在存储时所占用的字节长度，即时L的2进制表示数。
这部分额外占用的字节数跟字符串实际存储的字符串长度以及编码方式有关。此处采用的编码方式下，存储一个字符所用字节数为W则有L=M×W。那么额外字节数为：
- 当L < 256，1个字节便可以存储L的二进制数，需要1个字节来表示所占用的字节数量。此时，整个VARCHAR(M)类型所占用的字节数量为L+1个字节。
- 当L >=256时，需要2个字节来表示所占用的字节数量。此时，整个VARCHAR(M)类型所占用的字节数量为L+2个字节。
1个字节占用8bit，能表示的最大无符号数为\\(2^8-1\\)即255。
其他TEXT类型
除了VARCHAR(M)类型外，MySQL提供了TINYTEXT、TEXT、MEDIUMTEXT以及LONGTEXT这4种可以存储可变长度的字符串。这些TEXT类型规定最大的存储长度，可以让我们根据实际的业务需要进行选择。
- TINYTEXT：最多可以存储\\(2^8-1\\)字节，其中额外需要占用1个字节来存储长度。
- TEXT：最多存储\\(2^{16}-1\\)字节，其中额外需要2个字节来存储长度。
- MEDIUMTEXT：最多存储\\(2^{24}-1\\)字节，其中额外需要3个字节来存储长度。
- LONGTEXT：最多存储\\(2^{32}-1\\)字节，其中额外需要4个字节来存储长度。
在\"VARCHAR(M)\"章节中说到，“MySQL中规定表的一行数据占用的存储空间总共不得超过65535个字节”，但这一规定对TEXT类型的数据不起作用，因而当表中的数据较长时，可以考虑选择TEXT类型来进行存储。
枚举和集合类型
枚举(ENUM)类型和集合(SET)类型是我们在其他编程语言中常见的类型，比如当我们定义性别字段的类型时便可以采用枚举类型，保证这一列只能填“男”或者“女”。其使用方式为ENUM(\'str1\',\'str2\',...)。
SET类型则标出该列中的值可以从给定字符串列表中选择一个或者多个。比如针对兴趣列可以定义为(\'吃饭\',\'睡觉\',\'打豆豆\')，这样兴趣列中的值则只能在这三个字符串中选择一个或者多个。
总的来说ENUM和SET类型都是一种特殊的字符串类型，从字符串列表中选择单个值或者多个值时可能会用到，此处不再详述。
二进制类型
在某些特殊的应用场景下，我们需要存储二进制位，就用到了二进制类型，总的来讲二进制类型分成四种：
- 二进制类型
- BIT类型
- BINAR(M)
- VARBINARY(M)
- BLOB类型
BIT类型
BIT类型主要是用来存储单个或者多个二进制位所用到的类型：
| 类型 | 占用的存储空间 | 含义 |
|---|---|---|
| BIT(M) | 近似为(M+7)/8 | 存储M个二进制位的值 |
其中M的取值范围为1~64，而且M可以省略，它的默认值为1。
由于计算机在存储数据的时候都是按照字节来进行存储，因而当使用BIT类型时，如果存储的比特数不够整数个字节，则MySQL会对其进行填充，保证是整数个字节，比如：
- BIT(1):仅仅存储一个字节，但实际占用空间为(1+7)/8=1字节，填充了7bit。
- BIT(2):仅仅存储了一个字节，但实际占用空间(2+6)/8=1字节，填充了6bit。
- BIT(9):仅仅存储了9个字节，但实际占用空间(9+7)/2=2字节，填充了7bit。
BINARY(M)与VARBINARY(M)
这两种类型与上一章节中的CHAR(M)和VARCHAR(M)类型对应关系相同，不同点在于CHAR(M)和VARCHAR(M)使用来存储字符而BINARY(M)与VARBINARY(M)则是用来存储二进制数。
BLOB类型
与TEXT类型类似，BLOB类型也分成了TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOB类型四种。主要用来存储字节。常用来存储不是非常大的图片、音频和视频数据。如果比较大的二进制类型数据，一般不直接存储到数据库中，而是存储到文件系统中，在数据库中仅仅存放其文件路径。
总结
本文总结了在MySQL数据库中常用的数据类型及其相关原理，在MySQL中总体上支持的数据类型有数值类型、字符串类型、二进制类型三大类，分别用于存储三类常见的数据。
来源：https://www.cnblogs.com/goWithHappy/p/mysql_data_type.html
本站部分图文来源于网络，如有侵权请联系删除。