MySQL 数据类型

发表于 更新于 分类于 ， ， 本文字数： 5.1k 阅读时长 ≈ 5 分钟

MySQL 数据类型

::: info 概述

数据类型在 MySQL 中扮演着至关重要的角色，它定义了表中每个字段可以存储的数据种类和格式。

MySQL 支持多种类型，大致可以分为三类：数值、时间和字符串类型。

:::

数值类型

MySQL 支持所有标准 SQL 数值数据类型。这些类型包括严格数值数据类型 (INTEGER、SMALLINT、DECIMAL 和 NUMERIC)，以及近似数值数据类型 (FLOAT、REAL 和 DOUBLE PRECISION)。

作为 SQL 标准的扩展，MySQL 也支持整数类型 TINYINT、MEDIUMINT 和 BIGINT。下面的表显示了需要的每个整数类型的存储和范围。

类型 大小 （字节） 范围（有符号） 范围（无符号） 用途
TINYINT 1 (-128，127) (0，255) 小整数值
SMALLINT 2 (-32768，32767) (0，65535) 大整数值
MEDIUMINT 3 (-8388608，8388607) (0，16777215) 大整数值
INT 或 INTEGER 4 (-2147483648，2147483647) (0，4294967295) 大整数值
BIGINT 8 (-9223372036854775808，9223372036854775 807) (0，18446744073709551615) 极大整数值
FLOAT 4 (-3.402823466E+38，-1.175494351E-38)，0，(1.175494351E-38，3.402823466351E+38) 0，(1.175494351E-38，3.402823466E+38) 单精度 浮点数值
DOUBLE 8 (-1.7976931348623157E+308，-2.2250738585072014E-308)，0，(2.2250738585072014E-308，1.7976931348623157E+308) 0，(2.2250738585072014E-308，1.7976931348623157E+308) 双精度 浮点数值
DECIMAL 对 DECIMAL(M,D) ，如果 M>D，为 M+2 否则为 D+2 依赖于 M 和 D 的值 依赖于 M 和 D 的值 小数值

日期和时间类型

表示时间值的日期和时间类型为 DATETIME、DATE、TIMESTAMP、TIME 和 YEAR。

每个时间类型有一个有效值范围和一个”零”值，当指定不合法的 MySQL 不能表示的值时使用”零”值。

类型 大小 （字节） 范围 格式 用途
DATE 3 1000-01-01/9999-12-31 YYYY-MM-DD 日期值
TIME 3 ‘-838:59:59’/‘838:59:59’ HH:MM:SS 时间值或持续时间
YEAR 1 1901/2155 YYYY 年份值
DATETIME 8 ‘1000-01-01 00:00:00’ 到 ‘9999-12-31 23:59:59’ YYYY-MM-DD hh:mm:ss 混合日期和时间值
TIMESTAMP 4 ‘1970-01-01 00:00:01’ UTC 到 ‘2038-01-19 03:14:07’ UTC YYYY-MM-DD hh:mm:ss 混合日期和时间值，时间戳

DATETIME 类型最终展现的形式为：YYYY-MM-DD HH:MM:SS，固定占用 8 个字节。从 MySQL 5.6 版本开始，DATETIME 类型支持毫秒，DATETIME(N) 中的 N 表示毫秒的精度。例如，DATETIME(6) 表示可以存储 6 位的毫秒值。同时，一些日期函数也支持精确到毫秒，例如常见的函数 NOW、SYSDATE。

TIMESTAMP 类型存储的内容为‘1970-01-01 00:00:00’到现在的毫秒数。TIMESTAMP 占用 4 个字节，因此其存储的时间上限只能到 “2038-01-19 03:14:07”。同类型 DATETIME 一样，从 MySQL 5.6 版本开始，类型 TIMESTAMP 也能支持毫秒。与 DATETIME 不同的是，若带有毫秒时，类型 TIMESTAMP 占用 7 个字节，而 DATETIME 无论是否存储毫秒信息，都占用 8 个字节。

字符串类型

字符串类型指 CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM 和 SET。

类型 大小 （字节） 用途
CHAR 0-255 定长字符串
VARCHAR 0-65535 变长字符串
TINYBLOB 0-255 不超过 255 个字符的二进制字符串
TINYTEXT 0-255 短文本字符串
BLOB 0-65535 二进制形式的长文本数据
TEXT 0-65535 长文本数据
MEDIUMBLOB 0-16777215 二进制形式的中等长度文本数据
MEDIUMTEXT 0-16777215 中等长度文本数据
LONGBLOB 0-4294967295 二进制形式的极大文本数据
LONGTEXT 0-4294967295 极大文本数据

说明：

BLOB 是一个二进制大对象，可以容纳可变数量的数据。有 4 种 BLOB 类型：TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。它们区别在于可容纳存储范围不同。

有 4 种 TEXT 类型：TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。对应的这 4 种 BLOB 类型，可存储的最大长度不同，可根据实际情况选择。

数据类型常见问题

CHAR 和 VARCHAR 的区别是什么？

CHAR 和 VARCHAR 的主要区别在于：CHAR 是定长字符串，VARCHAR 是变长字符串。

  • CHAR 在存储时会在右边填充空格以达到指定的长度，检索时会去掉空格；VARCHAR 在存储时需要使用 1 或 2 个额外字节记录字符串的长度，检索时不需要处理。
  • CHAR 更适合存储长度较短或者长度都差不多的字符串，例如 Bcrypt 算法、MD5 算法加密后的密码、身份证号码；VARCHAR 类型适合存储长度不确定或者差异较大的字符串，例如用户昵称、文章标题等。
  • CHAR(M) 和 VARCHAR(M) 的 M 都代表能够保存的字符数的最大值，无论是字母、数字还是中文，每个都只占用一个字符。

BINARY 和 VARBINARY 类似于 CHAR 和 VARCHAR，不同的是它们包含二进制字符串而不要非二进制字符串。也就是说，它们包含字节字符串而不是字符字符串。这说明它们没有字符集，并且排序和比较基于列值字节的数值值。

金钱相关的数据用什么类型存储？

MySQL 中有 3 种类型可以表示浮点数，分别是 FLOAT、DOUBLE 和 DECIMAL。

采用 FLOAT 和 DOUBLE 类型会丢失精度。数据的精确度取决于分配给每种数据类型的存储长度。由于计算机只能存储二进制，所以浮点型数据在存储的时候，必须转化成二进制。

  • 单精度类型 FLOAT 存储空间为 4 字节，即 32 位。
  • 双精度类型 DOUBLE 存储空间为 8 字节，即 64 位。

如果存储的数据转为二进制后，超过存储的位数，数据就被截断，因此存在丢失精度的可能。

更重要的是，从 MySQL 8.0.17 版本开始，当创建表用到类型 Float 或 Double 时，会抛出下面的警告：MySQL 提醒用户不该用上述浮点类型，甚至提醒将在之后版本中废弃浮点类型。

1
Specifying number of digits for floating point data types is deprecated and will be removed in a future release

【示例】丢失精度案例

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- 创建表
CREATE TABLE `test` (
  `value` FLOAT(10,2) DEFAULT NULL
);

mysql> insert into test value (131072.32);
Query OK, 1 row affected (0.01 sec)

mysql> select * from test;
+-----------+
| value     |
+-----------+
| 131072.31 |
+-----------+
1 row in set (0.02 sec)

说明：示例中，使用 FLOAT 类型，明明保留了两位小数。但是写入的数据却从 131072.32 变成了 131072.31 。

DECIMAL 类型是 MySQL 官方唯一指定能精确存储的类型。因此，对于不允许丢失精度的场景（如金钱相关的业务），可以使用 DECIMAL 类型。

然而，在海量并发的互联网业务中使用，金额字段的设计并不推荐使用 DECIMAL 类型，而更推荐使用 BIGINT 整型类型。这里会用到一个巧思：将资金类型的数据用分为单位存储，而不是用元为单位存储。如 1 元在数据库中用整型类型 100 存储。

为什么更推荐用 BIGINT 存储金钱数据？因为 DECIMAL 是个变长字段，若要定义金额字段，则定义为 DECIMAL(8,2) 是远远不够的。这样只能表示存储最大值为 999999.99，百万级的资金存储。用户的金额至少要存储百亿的字段，而统计局的 GDP 金额字段则可能达到数十万亿级别。用类型 DECIMAL 定义，不好统一。另外重要的是，类型 DECIMAL 是通过二进制实现的一种编码方式，计算效率远不如整型来的高效。因此，推荐使用 BIGINT 来存储金额相关的字段。

扩展阅读：MySQL 如何选择 float, double, decimal

如何存储 emoji 😃？

在表结构设计中，除了将列定义为 CHAR 和 VARCHAR 用以存储字符以外，还需要额外定义字符对应的字符集，因为每种字符在不同字符集编码下，对应着不同的二进制值。常见的字符集有 gbk、utf8，通常推荐把默认字符集设置为 utf8。

随着移动互联网的飞速发展，**推荐把 MySQL 的默认字符集设置为 utf8mb4**，否则，某些 emoji 表情字符无法在 UTF8 字符集下存储。

【示例】设置表的字符集为 utf8mb4

1
ALTER TABLE test CHARSET utf8mb4;

注意：上述修改只是将表的字符集修改为 utf8mb4，下次新增列时，若不显式地指定字符集，新列的字符集会变更为 utf8mb4，但对于已经存在的列，其默认字符集并不做修改。

【示例】设置表的默认字符集为 utf8mb4

正确设置 utf8mb4 字符集方法如下：

1
ALTER TABLE test CONVERT TO CHARSET utf8mb4;

时间数据选择 DATETIME 还是 TIMESTAMP？

表结构设计时，对时间字段的存储，通常会有 3 种选择：DATETIME、TIMESTAMP、INT。

INT 类型就是直接存储 ‘1970-01-01 00:00:00’ 到现在的毫秒数，本质和 TIMESTAMP 一样，因此用 INT 不如直接使用 TIMESTAMP。

TIMESTAMP 类型存储的内容为‘1970-01-01 00:00:00’到现在的毫秒数。**TIMESTAMP 占用 4 个字节，因此其存储的时间上限只能到 “2038-01-19 03:14:07”**。

此外，TIMESTAMP 还存在潜在的性能问题。虽然从毫秒数转换到类型 TIMESTAMP 本身需要的 CPU 指令并不多，这并不会带来直接的性能问题。但是如果使用默认的操作系统时区，则每次通过时区计算时间时，要调用操作系统底层系统函数 __tz_convert()，而这个函数需要额外的加锁操作，以确保这时操作系统时区没有修改。所以，当大规模并发访问时，由于热点资源竞争，会产生两个问题。

  • 性能不如 DATETIME： DATETIME 不存在时区转化问题。
  • 性能抖动： 海量并发时，存在性能抖动问题。

为了优化 TIMESTAMP 的使用，强烈建议使用显式的时区，而不是操作系统时区。比如在配置文件中显示地设置时区，而不要使用系统时区

综上，由于 TIMESTAMP 存在时间上限和潜在性能问题，所以推荐使用 DATETIME 类型来存储时间字段。

小结

  • 自增整型主键，务必使用 BIGINT，而非 INT，以防后期超过数据范围，会很麻烦。

  • 不要再使用浮点类型 Float、Double，MySQL 后续版本将不再支持上述两种类型。

  • 金钱数据字段，可以使用 DECIMAL 类型。但是更推荐使用 BIGINT，长度固定，存储更紧凑，性能更好。

  • 业务隐私信息，如密码、手机、信用卡等信息，需要加密。切记简单的 MD5 算法是可以进行暴力破解，并不安全，推荐使用动态盐+动态加密算法进行隐私数据的存储。

  • 由于 TIMESTAMP 存在时间上限和潜在性能问题，所以推荐使用 DATETIME 类型来存储时间字段。

参考资料