MySQL：数据类型

数据类型：不仅仅是“存个数”和“存个字”

1. 本文简述

在学习 MySQL 的过程中，当我们开始设计表结构时，遇到的第一个问题往往就是：“这个字段我应该用什么类型？”

本文梳理了 MySQL 中纷繁复杂的数据类型，希望能做到：

• 对 MySQL 的数据类型有一个整体的分类认知。
• 掌握最核心、最常用的数据类型，并深刻理解它们各自的最佳应用场景。

2. 为什么需要数据类型？

每当我们设计一张数据库表，比如一张学生信息表（students），就需要为学号、姓名、年龄、入学日期这些字段，规定好它们各自应该存储什么样的数据。

这就引出了我们本文的主角——数据类型。

它就像一个契约，告诉数据库如何去理解、存储和处理我们存入的信息。这和我们在编程语言里为变量声明类型的道理是完全一样的。为每个字段选择一个恰当的数据类型，是数据库设计的基石。

MySQL 提供了非常丰富的数据类型，通常我们可以将它们归为几大类：

• 数值类型 (Numeric Types)
• 字符串类型 (String Types)
• 日期和时间类型 (Date and Time Types)
• 二进制类型 (Binary Types)

3. 数值类型

数值类型，顾名思义，就是用来跟数字打交道的，比如用户的年龄、商品的价格、库存数量等。

3.1 类型列表

类型	大小	说明
BIT[(M)]	默认bit	位值类型。M表示每个值的位数，范围为1~64，默认为1。
TINYINT[(M)]	1 byte	小整数。范围-128 ~ 127，无符号则为 0 ~ 255。
BOOL	1 byte	TINYINT(1)的同义词。0被视为false，非0值被视为true。
SMALLINT[(M)]	2 bytes	普通整数。范围-32768 ~ 32767，无符号则为 0 ~ 65535。
MEDIUMINT[(M)]	3 bytes	中等整数。
INT[(M)]	4 bytes	大整数。这是我们最常用的整数类型。
INTEGER[(M)]	4 bytes	INT[(M)]的同义词。
BIGINT[(M)]	8 bytes	极大整数。当INT可能不够用时（如海量订单ID），就该它出场了。
FLOAT[(M,D)]	4 bytes	单精度浮点数。M是总位数，D是小数位数。存在精度丢失风险。
DOUBLE[(M,D)]	8 bytes	双精度浮点数。M是总位数，D是小数位数。比FLOAT更精确，但同样有精度风险。
DECIMAL[(M[,D])]	动态	高精度定点数。不存在精度损失，M是总位数，D是小数位数。特别适合存储金额这类要求绝对精确的数据。

关于DECIMAL的一个小提示：
DECIMAL(M,D) 中的 M 指的是总的有效数字位数，D 是小数部分的位数。M 和 D 都不包含小数点或负号。例如 DECIMAL(5, 2) 可以存储从 -999.99 到 999.99 的数值。

在处理金额时，一个常见的技巧：为了完全避免浮点数带来的精度问题，有些系统会将金额（如元）乘以100或10000转换为分（或厘），然后用 BIGINT 类型来存储。这样所有的运算都变成了整数运算，既高效又绝对安全。

3.2 数据类型取值范围

类型	大小	有符号最小值	有符号最大值	无符号最小值	无符号最大值
TINYINT	1 Byte	-128	127	0	255
SMALLINT	2 Bytes	-32768	32767	0	65535
MEDIUMINT	3 Bytes	-8388608	8388607	0	16777215
INT	4 Bytes	-2147483648	2147483647	0	4294967295
BIGINT	8 Bytes	-2^63	2^63-1	0	2^64-1

小建议：关于 unsigned 的思考

我们可以用 unsigned 关键字为数值类型指定为无符号，这样它的正数表示范围就能扩大一倍。

计算范围的公式：

• 有符号: -2^(字节数*8-1) 到 2^(字节数*8-1) - 1
• 无符号: 0 到 2^(字节数*8) - 1

不过，在实际开发中，我们建议尽量不使用 unsigned。原因在于，如果一个 INT 型存不下的数据，那么 INT UNSIGNED 大概率也存不下。与其在这里纠结，不如在设计阶段就预见到数据增长的可能性，直接选用 BIGINT 类型。这样的设计更具扩展性，也能避免后期因数据溢出而被迫修改表结构的痛苦。

4. 字符串类型

字符串类型用来存储文本数据，比如姓名、地址、文章内容等。

4.1 类型列表

类型	说明
CHAR[(M)]	固定长度字符串，M为字符长度 (0-255)。
VARCHAR(M)	可变长度字符串，M为最大字符长度 (0-65535)。这是最常用的字符串类型。
TINYTEXT	小文本，最大长度 255 字符。
TEXT[(M)]	文本，最大长度 65,535 字符。用于存储较长的文章或描述。
MEDIUMTEXT	中等文本，最大长度约 16MB。
LONGTEXT	大文本，最大长度 4GB。
BINARY[(M)]	固定长度的二进制字节串，类似于CHAR，但用于存储字节。
VARBINARY(M)	可变长度的二进制字节串，类似于VARCHAR。
TINYBLOB	小二进制大对象 (BLOB)，最大 255 字节。
BLOB[(M)]	二进制大对象，最大 64KB。常用于存储图片、音频等二进制文件。
MEDIUMBLOB	中等二进制大对象，最大 16MB。
LONGBLOB	大二进制大对象，最大 4GB。
ENUM(‘v1’,‘v2’,…)	枚举类型。值只能从预设的列表中选择一个。
SET(‘v1’,‘v2’,…)	集合类型。值可以从预设的列表中选择零个或多个。

小建议：数据库应该存文件吗？

尽管 BLOB 类型可以存储文件，但在实际项目中，我们强烈不建议直接将文件存入数据库。这样做会使数据库迅速膨胀，备份和恢复变得异常困难，且严重影响性能。

最佳实践是：将文件存储在专门的文件服务器或云存储（如阿里云OSS、腾讯云COS）上，然后在数据库中只保存文件的访问路径（URL）或唯一标识ID。这样既能保证数据库的轻量和高效，又能灵活地管理文件资源。

4.2 关于排序

• 字符串类型 (如 VARCHAR) 的排序基于我们指定的字符集和排序规则（Collation）。例如，utf8mb4_general_ci 规则在排序时会不区分大小写。
• 二进制类型 (如 VARBINARY) 的排序则直接比较字节的数值，因此是严格区分大小写的。

4.3 CHAR与VARCHAR的区别

CHAR 和 VARCHAR 是最容易混淆的两个字符串类型，它们的核心区别在于长度是否固定。

• CHAR(M): 定长字符串。无论存入的实际内容多短，它都会在磁盘上占据 M 个字符的固定空间。如果存入的字符不足 M，数据库会自动在右侧用空格补齐。在检索数据时，这些尾部空格又会被自动删除。
• VARCHAR(M): 变长字符串。它只会根据存入的实际内容来分配空间，同时会额外使用1到2个字节来记录内容的实际长度。这使得它在存储长度不一的数据时更加节省空间。

我们来看一个直观的例子：

-- 创建一个包含 VARCHAR 和 CHAR 的表
CREATE TABLE vc (v VARCHAR(4), c CHAR(4)
);-- 插入带有尾部空格的字符串
INSERT INTO vc VALUES ('ab  ', 'ab  ');-- 使用CONCAT函数连接括号，以清晰地观察空格的保留情况
SELECT CONCAT('(', v, ')'), CONCAT('(', c, ')') FROM vc;

从结果中我们可以清晰地看到：

• VARCHAR 精确地保存了原始的 'ab '，包括尾部的两个空格。
• CHAR 在存储时虽然也用空格补齐了长度，但在检索时，尾部的填充空格被去掉了，最终只剩下 'ab'。

4.4 如何选择CHAR与VARCHAR

• 用 CHAR 的场景：当数据长度非常确定且固定时。例如：存储MD5哈希值（32位）、身份证号（18位）、邮政编码（6位）。在这些场景下，CHAR 的处理效率会略高于 VARCHAR。
• 用 VARCHAR 的场景：绝大多数情况。当数据长度不固定时，如用户名、地址、文章标题等，使用 VARCHAR 能有效节省存储空间。

简单总结：追求极致效率且长度固定，选 CHAR；追求空间效率且长度可变，选 VARCHAR。

4.5 VARCHAR与TEXT的区别

当 VARCHAR 的64KB上限也无法满足需求时，就需要 TEXT 类型们。

• 容量大小：VARCHAR 最大约64KB，而 TEXT （TEXT, MEDIUMTEXT, LONGTEXT）可以存储更大的数据。
• 存储方式：VARCHAR 的数据通常和其它列一起存储在数据行内（如果内容过长也会存到行外），而 TEXT 的数据几乎总是存储在行外的“溢出页”中，行内只保留一个指向它的地址。
• 查询性能：由于存储方式的不同，直接对 VARCHAR 列创建索引和查询的性能通常优于 TEXT 列。TEXT 列不能创建常规索引，但可以创建全文索引（FULLTEXT）。
• 适用场景：需要频繁作为查询条件、长度可控的短文本（如姓名、邮箱），用 VARCHAR；需要存储大段文本、不常作为主要查询条件的（如文章正文、日志内容），用 TEXT。

5. 日期类型

用于存储日期和时间信息，如生日、创建时间、更新时间等。

5.1 类型列表

类型	大小	说明	零值
TIMESTAMP[(fsp)]	4 bytes	时间戳。范围从 1970-01-01 到 2038-01-19。它与时区相关，存储的是UTC时间，显示时会根据当前会话时区转换。常用于记录数据的创建和修改时间。	0000-00-00 00:00:00
DATETIME[(fsp)]	8 bytes	日期时间。范围从 1000-01-01 到 9999-12-31。与时区无关，存储的是字面值，存什么就显示什么。	0000-00-00 00:00:00
DATE	3 bytes	日期。只存储年、月、日。	0000-00-00
TIME[(fsp)]	3 bytes	时间。只存储时、分、秒。	00:00:00
YEAR[(4)]	1 byte	年份。存储4位格式的年份。	0

5.2 其他

• fsp 是一个可选参数，用于指定秒的小数部分的精度（0到6位），例如 DATETIME(3) 可以精确到毫秒。
• MySQL提供了一些方便的函数来获取当前时间：
  • CURDATE(): 获取当前日期 (YYYY-MM-DD)
  • CURTIME(): 获取当前时间 (hh:mm:ss)
  • NOW(): 获取当前日期和时间 (YYYY-MM-DD hh:mm:ss)
• MySQL提供了一些方便的函数来获取当前时间：
  • CURDATE(): 获取当前日期 (YYYY-MM-DD)
  • CURTIME(): 获取当前时间 (hh:mm:ss)
  • NOW(): 获取当前日期和时间 (YYYY-MM-DD hh:mm:ss)