MySQL基础（3）—— MySQL数据类型

MySQL说到底就是一个关系型数据库，底层存储数据不论是怎样，但是我们可以将数据库中的每一个表都看成是一张表格。一行就一个样本，每一列都是这个样本的某一个属性。所以每一列都有其数据类型。

数值类型

1、整数类型

类型【(有符号)/无符号】	占用空间	无符号取值范围	有符号取值范围	表示
TINYINT (SIGNED)/UNSIGNED	1字节	$0\sim 2^8-1(0\sim 255)$	$-2^7\sim 2^7-1(-128\sim 127)$	非常小的整数
SMALLINT (SIGNED)/UNSIGNED	2字节	$0\sim 2^{16}-1(0\sim 65535)$	$-2^{15}\sim 2^{15}-1(-32768\sim 32767)$	小的整数
MEDIUMINT (SIGNED)/UNSIGNED	3字节	$0\sim 2^{24}-1(0\sim 1677215)$	$-2^{23}\sim 2^{23}-1(-8388608\sim 8388607)$	中等大小的整数
INT(INTEGER) (SIGNED)/UNSIGNED	4字节	$0\sim 2^{32}-1(0\sim 4294967295)$	$-2^{31}\sim 2^{31}-1(-2147483648\sim 2147483647)$	标准的整数
BIGINT (SIGNED)/UNSIGNED	8字节	$0\sim 2^{64}-1(0\sim 18446744073709551615)$	$-2^{63}\sim 2^{63}-1(-9223372036854775808\sim 9223372036854775807)$	大整数

无符号类型必须添加 UNSIGNED，而有符号加不加 SIGNED 都可以。此外，现在不推荐使用 INT 而鼓励使用 INTEGER，其实两者是一样的。

2、浮点数类型

类型	占用空间	绝对值最小非零值	绝对值最大值	表示
FLOAT	4字节	$\pm 1.175494351E-38$	$\pm 3.402823466E+38$	单精度浮点数
DOUBLE	8字节	$\pm 2.2250738585072014E-308$	$\pm 1.7976931348623157E+308$	双精度浮点数

由于本身设计的原因，浮点数不仅无法表示某些小数，而且对一些小数的表示是不精确的。所以想保证所存储的小数是精确的，需要其他类型的数据类型，例如等下要介绍的定点数类型。

浮点数类型除了直接使用之外，还可以通过指定参数来限制表示的范围：

FLOAT(M, D)
DOUBLE(M, D)

其中 M 表示有效数字的个数，D 表示小数点后面需要保留的数字个数。例如 FLOAT(4, 1) 的取值范围为 -999.9 ~ 999.9。

当然，从MySQL8.0.17开始，MySQL已经不再推荐使用指定参数的方式了。

3、定点数类型

由于浮点数类型不能保证存储数据的精确度，所以MySQL又提出了定点数类型，它也是存储小数的一种方式：

类型	占用空间	取值范围
DECIMAL(M, D)	取决于 M 和 D	取决于 M 和 D

这里的 M 和 D 的含义与浮点数的相同，不过浮点数占用的存储空间不会随 M 和 D 的变化而变化，而定点数会。

另外，为了解决浮点数因为舍入操作而不精确的问题，定点数将十进制小数用小数点分开，将整数部分和小数部分分别存储起来。而为了尽量少的使用空间，定点数会从小数点开始，分别从左和从右，以9个位数为一组（不足9个位数也分为一组），把整个小数分成多个组，每个组以位数分配空间。

每个组的十进制位数	占用空间大小
1 或 2	1字节
3 或 4	2字节
5 或 6	3字节
7 或 8 或 9	4字节

例如现在有数据类型 DECIMAL(16, 4)，它会被分为3组：

第一组包含3个十进制位，所以占2个字节；

第二组包含9个十进制位，所以占4个字节；

第三组包含4个十进制位，所以占2个字节；

综合所有情况，DECIMAL(16, 4) 会占用8个字节的空间大小。可以看出，当 M 和 D 确定的时候，这个数据类型所占用的空间也就确定了。

注意：这里指的 M 和 D 并不是实际存储数据的位数大小，而是最大值。例如我们可以使用 DECIMAL(16, 4) 存储拥有12个有效位数的数据 1234567890.12，而不一定必须存储16个有效位数，其中小数位数拥有4个的数据。

注意：当存储实际数据的时候，按照规则，我们还需要将转换完成的二进制位的最高位设置为1【这一步主要考虑到别的用途，暂且知道即可】。

以 DECIMAL(16, 4) 为定点数类型，其占用的空间是8个字节，让我们存储数据 1234567890.12。

1. 从小数点往左数9个位数，即 234567890 占用4个字节，为了方便，这里以十六进制表示为 0x0DFB38D2；
2. 另外的 1 占用2个字节，用十六进制表示为 0x0001；
3. 小数点右边的 12 占用2个字节，用十六进制表示为 0x000C；
4. 于是得到了一个存储好的数 0x0001 0DFB38D2 000C（空格人为添加，请忽略）；
5. 最后别忘记，还需要将第1个二进制位数的数置为1，于是最终结果为 0x8001 0DFB38D2 000C【注意是二进制的第一个数，转换为十六进制是8】；

如果我们需要存储负数，只需要将对应正数的二进制中的所有数值反转即可。例如 -1234567890.12，只需要将结果 0x8001 0DFB38D2 000C 的每个二进制位数值反转即可，为 0x7FFE F204C72D FFF3。

另外，M 的最大值65，D 的最大值为30，且 D 的值不能超过 M 的值。从MySQL8.0.17开始，MySQL也不建议在浮点数和定点数后面添加 UNSIGNED 来将它们设置为无符号类型。

虽然定点数类型的精度贼高，但是占用的空间也是不小，所以当不要求存储高精度的小数时，使用浮点数类型即可。

日期和时间类型

MySQL在5.6.4版本之前，TIME、DATETIME、TIMESTAMP 仅支持到秒，但是在这个版本之后，MySQL添加了对微秒的支持。$1秒=1000毫秒=1000000微秒$。

类型	占用空间	取值范围	表示
YEAR	1字节	$1901\sim 2155$	年份值
DATE	3字节	${}^{\prime }1000-01-01^{\prime }{\sim }^{\prime }9999-12-31^{\prime }$	日期值
TIME	3字节+小数秒的存储空间	${}^{\prime }-838:59:59[.000000{]}^{\prime }{\sim }^{\prime }838:59:59[.000000{]}^{\prime }$	时间值
DATETIME	5字节+小数秒的存储空间	${}^{\prime }1000-01-0100:00:00[.000000{]}^{\prime }{\sim }^{\prime }9999-12-3123:59:59[.999999{]}^{\prime }$	日期加时间值
TIMESTAMP	4字节+小数秒的存储空间	${}^{\prime }1970-01-0100:00:01[.000000{]}^{\prime }{\sim }^{\prime }2038-01-1903:14:07[.999999{]}^{\prime }$	时间戳

可以看到，对于微秒的支持，其占用的空间也是变化的，它会随着小数秒的位数变化而占据不同的空间大小：

保留的小数秒的位数	额外占用的空间
0	0字节
1 或 2	1字节
3 或 4	2字节
5 或 6	3字节

例如 DATETIME(3) 表示支持3位小数秒，额外要占用2个字节，于是它占用的空间就是 5字节 + 2字节 = 7字节。

1、YEAR

它就是简单的年份值，如果想存储范围之外的年份，可以使用 SMALLINT 或者字符串类型。

2、DATE、TIME、DATETIME

接下来为了方便说明，使用 YYYY、MM、DD、hh、mm、ss、uuuuuu 分别表示年份值、月份值、天数、时、分、秒、小数秒。

DATE 表示日期，格式为 YYYY-MM-DD；

TIME 表示时间，格式为 hh:mm:ss[.uuuuuu]。它可以表示一天中的某个时间，也可以表示两个时间点之间的时间间隔，所以 TIME 表示的小时数可能非常大，并且可能为负值；

DATETIME 表示日期时间，格式为 YYYY-MM-DD hh:mm:ss[.uuuuuu]。其存储的时间必须是一天中的某一个时间点。

3、TIMESTAMP

全世界分为24个时区，我们中国则是以北京所在的东八区进行计时。而从0号时区的 1970-01-01 00:00:00 开始（也就是东八区的 1970-01-01 08:00:00）到现在所经历的秒数就被称为时间戳。

使用时间戳存储时间之后，可以让数据随着时区的变化而变化。而其他方式存储的时间数据都是固定的。

字符串类型

字符分为可见字符【例如敲键盘后能看见】和不可见字符【例如回车、空格】，多个字符连接起来就形成了字符串。

因为计算机只能存储二进制数，为了存储字符串，就需要一种方式，将字符映射为二进制数，这种映射过程就被称为字符编码。可以采用定长编码【固定转换后占用的空间】和变长编码【根据不同情况让字符占用不同的空间】。

类型	最大长度	占用空间	表示
CHAR(M)	M个字符	$M\times 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 代表该数据类型最多能存储的字符数量，L 代表实际存储的字符串在特定字符集中占用的字节数，W 代表在特定字符集中编码一个字符最多需要的字节数。

1、CHAR(M)

CHAR(M)类型中的 M 取值范围为 $0\sim 255$，如果省略掉 M，那它的默认值就是1，也就是 CHAR 与 CHAR(1)是一个意思。比较特别的一种 CHAR 类型是 CHAR(0)，它只能存储空字符串 '' 或者 NULL 值。

接下来看一下如何计算存储空间大小：

• 一个 CHAR(5) 类型采用 ASCII 码字符集。因为 ASCII 码字符集中一个字符最多占1个字节，所以 $M=5$，$W=1$，最终占用的字节数为 $5\times 1=5$ 字节。

• 如果实际存储的字符串小于5字节，剩余的那些空间会用空格字符（也就是 ' '）补齐。

以此可知，如果 M 非常大，而我们向列中存储的字符串非常短时，就会浪费大量的空间。

2、VARCHAR(M)

VARCHAR(M) 跟 CHAR(M) 很像，不过 VARCHAR 的 M 范围更广，为 $1\sim 65535$ 字符数，而且 VARCHAR 占用的字节数是以实际存储的字符串大小为基础，为可变长度。

于是，VARCHAR(M) 在存储时共有两个部分。一个部分为实际存储的字符串占用的字节数，另一个部分则用来存储字节数这个数值。

下面看一下如何计算其存储空间大小：

• 真正的字符串内容：实际存储的字符串在特定字符集条件下占用的空间 L；
• 占用的字节数：
  • 假如 VARCHAR(M) 采用的字符集编码一个字符需要 W 字节，于是有：
  • 当 $M\times W<256$ 时，只需要一个字节来表示占用的字节数【$2^8-1=255$】。最终得到存储空间为 L+1 字节；
  • 当 $M\times W\ge 256$ 时，需要两个字节来表示占用的字节数【$2^{16}-1=65535$】。最终得到存储空间为 L+2 字节；

例如，如果采用 utf8mb3 字符集存储字符串“狗蛋”，数据类型为 VARCHAR(5)。其中，实际存储的“狗蛋”占用 $3\times 2=6$ 字节；又因为 $M\times W=3\times 5=15$，$15<256$，所以存储6这个数值的空间仅占用一个字节。所以“狗蛋”总共占用7个字节。

3、各种 TEXT 类型

4种 TEXT 类型可以存储可变长度的字符串类型，开始的表格已经详细介绍，其中使用的多余字节数也是用来存储字节长度这个数值。

MySQL有一个规定，表的一行数据（包含多个列）占用的存储空间总共不得超过 65535 字节，但是这个规定对这些 TEXT 类型是不起作用的。如果一个表中有属性需要存储特别长的文本，就可以考虑这几个类型。

4、ENUM 类型和 SET 类型

ENUM 类型也被称为枚举类型，格式为 ENUM('str1', 'str2', 'str3', ...)。对于使用 ENUM 类型的列，它的值只能在给定的字符串列表中选择一个，相当于一个单选按钮。

那如果我们想像多选题一样选择多个，应该怎么做呢？MySQL提供了 SET 类型，它的格式为 SET('str1', 'str2', 'str3', ...)。使用 SET 类型的列可以在给定的字符串列表中选择一个或者多个。

二进制类型

1、BIT 类型

类型	占用的空间	表示
BIT(M)	近似为 $(M+7)/8$	存储 M 个二进制位的值

有时候我们需要存储一个或者多个二进制位，此时就需要用到 BIT 类型。其中，M 的取值范围为 $1\sim 64$，而且 M 可以省略，它的默认值为1。

它的存储方式跟上面的类型有些不同：

• BIT(1) 类型仅仅需要存储1比特数据，但是MySQL会申请 $(1+7)/8=1$ 字节（8比特）的空间；
• BIT(9) 类型仅需要存储7比特数据，但是MySQL会申请 $(9+7)/8=2$ 字节（16比特）的空间；

2、BINARY(M) 与 VARBINARY(M)

BINARY(M) 与 VARBINARY(M) 对应于前面提到的 CHAR(M) 与 VARCHAR(M)。不同点在于前者用来存放字节，后者用来存放字符。所以 M 在前者表示为最大字节数，后者表示为最大字符数。

3、BLOB 类型

有4种BLOB类型：TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOB，它们用来存储可变长度的二进制数据，如图像、音频等。

它们很像 TEXT 类型，不过 BLOB 类型用来存储字节，而 TEXT 类型用来存储字符。

一般情况下，较大的图像、音频等数据不会直接存储到数据库种，而是存储在文件系统中，然后将文件路径存储到数据库中。