数据类型

数值

整数类型

MySQL 支持的整型类型：

• SQL 标准类型：INT、SMALLINT。
• TINYINT、MEDIUMINT、BIGINT。

避免使用整数的显示宽度。也就是说，不要用 INT(10) 类似的方法指定字段显示宽度，直接用 INT。

显示宽度

创建数据表时整数类型可以指定一个长度，如下：

CREATE TABLE `user`(
  `id` TINYINT(2) UNSIGNED
);

这里表示 user 表的 id 字段的类型是 TINYINT，可以存储的最大数值是 255。这里的 TINYINT(2) 中的 2 并不是类型存储的最大长度，而是显示的最大长度。比如，存入 200，虽然超过 2 位，但是没有超出 TINYINT 类型的最大数值 255，所以可以正常保存；如果存入值大于 255，如 500，那么 MySQL 会自动保存为 TINYINT 类型的最大值 255。

显示宽度的作用：

当需要在查询结果前填充 0 时，定义类型时加上 ZEROFILL 就可以实现，如：

`id` TINYINT(2) UNSIGNED ZEROFILL

这样，查询结果如果是 5，那输出就是 05。如果指定 TINYINT(5)，那输出就是 00005，其实实际存储的值还是 5，而且存储的数据不会超过 255，只是 MySQL 输出数据时在前面填充了 0。

浮点数

• Float、Double：这两个类型不是高精度，也不是 SQL 标准的类型，并且在 8.0 之后的版本中将会废弃。所以在真实的生产环境中不推荐使用。

使用：

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

• M 表示该小数最多需要的十进制有效数字个数。注意这个有效数字个数，例如小数 -2.3 来说有效数字个数就是 2，对于小数 0.9 来说有效数字个数就是 1。
• D 表示该小数的小数点后的十进制数字个数。

示例：

• M 的取值范围是 1~255，D 的取值范围是 0~30，D 不能大于 M。
• Float 占用 4 字节，DOUBLE 占用 8 字节。它们占用的存储空间大小并不随着 M 和 D 的值的变动而变动。

为什么用浮点数表示小数可能会有不精确的情况？

这是由浮点数的二进制存储机制和 IEEE 754 标准的特性决定的。

浮点数是用来表示小数的，十进制小数也可以被转换成二进制后被计算机存储。例如 9.875 的二进制就是：9.875 = 8 + 1 + 0.5 + 0.25 + 0.125 = 1 × 2³ + 1 × 2⁰ + 1 × 2⁻¹ + 1 × 2⁻² + 1 × 2⁻³ = 1001.111。

但是更多的小数是无法直接转换成二进制的，比如说 0.3，它转换成的二进制小数就是一个无限小数（二进制表示 0.0100110011001...，其中 1001 是循环），但是现在只能用 4 个字节或者 8 个字节来表示这个小数，所以只能进行一些舍入来近似的表示，所以说计算机的浮点数表示有时是不精确的。

高精度类型

为了保证小数是精确的，MySQL 提供了高精度类型：

• Decimal

使用：

DECIMAL(M, D)

• M, D 的含义和浮点数类型的 M, D 含义一样。
• M, D 都是可选的，M 的默认值是 10，D 的默认值是 0。
• M 的范围是 1~65，D 的范围是 0~30，D 的值不能超过 M。
• DECIMAL 类型占用的存储空间大小就和 M、D 的取值有关。

DECIMAL 的存储方式和浮点数不一样，它是把小数点左右的两个十进制整数给存储起来，然后将它们组合起来。

DECIMAL 的存储不是简单的分为左右两个部分，而是从小数点位置出发，每 9 个十进制数字划分为 1 组，将每个组中的十进制数字，将其转换为二进制数字进行存储。根据组中包含的十进制数字位数不同，所需的存储空间大小也不同（变长类型）：

例如：

• DECIMAL(18,9) 表示的整数部分是 9 个，小数部分是 9 个，那么就可以划分为 2 组，每组 9 个占 4 字节，4+4=8 字节。
• DECIMAL(20,6) 表示的整数部分是 14 个，小数部分是 6 个，那么就可以划分为 3 组，每组分别是 5、9、6 个数字，分别占用 3、4、3 个字节，3+4+3=10 字节。

无符号数值类型

无符号数就是非负数。就是在原数值类型后加一个 UNSIGNED。

字符串

其中最常使用的是 CHAR、VARCHAR。

CHAR(M)

CHAR(M) 中的 M 代表该类型最多可以存储的字符数量，注意，表示的是字符的个数，而不是字节的个数。其中 M 的取值范围是 0~255。默认值就是 1。也就是说 CHAR 就是 CHAR(1)。

CHAR(0) 是一种特别的类型，它只能存储空字符串 '' 或者 NULL 值。

CHAR(M) 在不同的字符集下需要的存储空间也是不一样的。当列采用的是定长字符集时，该列占用的字节数不会被加到变长字段长度列表，而如果采用变长字符集时，该列占用的字节数也会被加到变长字段长度列表。

例如：

• ascii 字符集的 CHAR(5)，一个字符最多需要 1 个字节，也就是 M=5、W=1，所以该类型占用的存储空间大小就是 5×1 = 5 个字节。
• utf8mb3 字符集的 CHAR(5)，一个字符需要 1~3 个字节，所以该类型占用的存储空间大小至少需要就是 5×1 = 5 个字节，最多 5×3 = 15 个字节。即使向该列中存储一个空字符串也会占用 5 个字节。

如果实际存储的字符串在特定字符集编码下占用的字节数不足 M×W，那么剩余的那些存储空间用空格字符（也就是：' '）补齐。

例如：

• ascii 字符集的 CHAR(5) 类型，存入字符串 'abc'，需要 3 个字节存储，而采用 ascii 字符集的 CHAR(5) 类型又需要 5 个字节的存储空间，那么剩下的那两个字节的存储空间就会存储空格字符 ' ' 的编码。

VARCHAR(M)

低于长短不一的字符串，使用 CHAR(M) 可能会浪费很多存储空间，VARCHAR(M) 正是为了解决这个问题而生的。

VARCHAR(M) 中的 M 也是代表是字符的个数，而不是字节的个数，理论上的取值范围是 1~65535。但是 MySQL 中还有一个规定，表中某一行包含的所有列中存储的数据大小总共不得超过 65535 个字节（注意是字节），也就是说 VARCHAR(M) 类型实际能够容纳的字符数量肯定是小于 65535 的。

一个 VARCHAR(M) 类型表示的数据由两部分组成：

• 真正的字符串内容，长度是 L 个字节。
• 字符串内容占用字节数，长度是 1~2 个字节。
  • 假设 VARCHAR(M) 类型采用的字符集编码一个字符最多需要 W 个字节，那么：
    • 当 M×W < 256 时，只需要一个字节来表示占用的字节数。
    • 当 M×W >= 256 且 M×W < 65536 时，需要两个字节来表示占用的字节数。

所以，VARCHAR(M) 类型占用的存储空间大小就是 L+1~2 个字节。

例如：

• utf8mb3 字符集的 VARCHAR(5)，也就是说 M = 5、W = 3，所以 M × W= 5×3 = 15，而 15 < 256，所以只需要一个字节来表示真实数据占用的字节长度就好了。
• utf8mb3 字符集的 VARCHAR(100)，也就是说 M = 100、W = 3，所以 M × W= 100×3 = 300，而 300 > 256，所以需要 2 个字节来表示真实数据占用的字节长度。

如图：

大字符串的类型

对于很长的字符串，如果 VARCHAR(M) 如果还是不够用，可以使用 TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT 这四种可以存储大型的字符串的类型。

它们也都是变长类型，这些类型占用的存储空间由实际内容和内容占用的字节长度两部分构成。

• TINYTEXT 最多可以存储 2⁸-1 个字节，所以内容占用的字节长度用 1 个字节（L+1）就可以表示。
• TEXT 最多可以存储 2¹⁶-1 个字节，所以内容占用的字节长度用 2 个字节（L+2）就可以表示。
• MEDIUMTEXT 最多可以存储 2²⁴-1 个字节，所以内容占用的字节长度用 3 个字节（L+3）就可以表示。
• LONGTEXT 最多可以存储 2³²-1 个字节，所以内容占用的字节长度用 4 个字节（L+4）就可以表示。

ENUM 和 SET 类型

ENUM 枚举类型，是一种字符串类型。

使用：

ENUM('str1', 'str2', 'str3' ⋯)

它表示在给定的字符串列表里选择一个。例如性别一列可以定义成 ENUM('男', '女') 类型。这个的意思就是性别一列只能在 ‘男’ 或者 ‘女’ 这两个字符串之间选择一个。相当于一个单选框。

ENUM 只能在给定的字符串列表里选择一个，并且只能选择一个，不能选择多个。如果要选择多个，可以使用 SET 类型。

SET 集合类型，也是一种字符串类型。

使用：

SET('str1','str2','str3' ⋯)

它表示在给定的字符串列表里选择多个。例如兴趣一列就可以定义成 SET('打球', '画画', '扯犊子', '玩游戏') 类型。这个的意思就是兴趣一列可以在给定的这几个字符串中选择一个或多个，相当于一个多选框。

日期和时间

MySQL 支持的日期和时间类型：

最常使用的日期类型为 DATETIME 和 TIMESTAMP，因为大部分业务场景都需要将日期精确到秒。

5.6.4 版本以后 TIME、DATETIME、TIMESTAMP 这几种类型添加了对毫秒、微秒的支持。由于毫秒、微秒都不到 1 秒，所以也被称为小数秒，MySQL 最多支持 6 位小数秒的精度。

使用：

# 小数秒位数可以在 0、1、2、3、4、5、6 中选择
类型(小数秒位数)

例如：

-- 表示精确到秒
DATETIME(0)
-- 表示精确到毫秒
DATETIME(3)
-- 表示精确到 10 微秒
DATETIME(5)

保留小数秒，需要额外的存储空间。不同位数的小数秒，占用的存储空间大小是不一样的。

也就是说如果选择使用 DATETIME(1)，那么需要的存储空间就是在 DATETIME 的空间上再加上小数秒需要的空间，就是 8 + 1 = 9 个字节。

二进制类型

二进制类型存储二进制字符串，与字符集无关。

BIT(M)

BIT(M) 是用来存放位的类型，M 表示该类型最多可以存放的位的个数，取值范围是 1~64。默认值为 1，也就是说 BIT(1) 和 BIT 是一样的。

MySQL 是以字节为单位存储数据的，一个字节拥有 8 个比特位。如果存储的比特位个数不足整数个字节，那么 MySQL 会将它填充满，例如：

• BIT(1) 类型仅仅需要存储 1 个比特位的数据，但是 MySQL 会为其申请 (1+7)/8 = 1 个字节。
• BIT(5) 类型仅仅需要存储 5 个比特位的数据，但是 MySQL 会为其申请 (5+7)/8 = 1 个字节。
• BIT(9) 类型仅仅需要存储 9 个比特位的数据，但是 MySQL 会为其申请 (9+7)/8 = 2 个字节。

BINARY(M) 与 VARBINARY(M)

BINARY(M) 和 VARBINARY(M) 对应于 CHAR(M) 和 VARCHAR(M)，都是前者是固定长度的类型，后者是可变长度的类型，只不过 BINARY(M) 和 VARBINARY(M) 是用来存放字节的，M 表示最多能存放的字节数。

其他的二进制类型

TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOB 是针对数据量很大的二进制数据提出的，比如图片、音频、压缩文件。很像 TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT，不过各种 BLOB 类型是用来存储字节的，而各种 TEXT 类型是用来存储字符的。

JSON 数据类型

JSON（JavaScript Object Notation）主要用于互联网应用服务之间的数据交换。JSON 类型是从 MySQL 5.7 版本开始支持的功能，而 8.0 版本解决了更新 JSON 的日志性能瓶颈。如果要在生产环境中使用 JSON 数据类型，推荐使用 MySQL 8.0 版本。