MySQL数据类型之字符串、数字、日期详解

2026-03-29 15:03:02发布 2次浏览

详情描述

这是一个非常核心的MySQL主题。下面我将详细解析字符串、数字和日期这三种最常用的数据类型，包括它们的特性、使用场景和选择建议。

一、字符串类型

字符串类型用于存储文本数据。选择的关键在于了解字符集（决定如何编码，如utf8mb4）和排序规则，以及长度和存储方式的差异。

1. CHAR 与 VARCHAR

这是最常用的两种字符串类型。

特性	CHAR(M)	VARCHAR(M)
定义	定长字符串。	变长字符串。
M 的含义	字符数（0-255）。即使存储内容小于M，也会占用M个字符定义的空间。	最大字符数（0-65,535，实际受行大小限制，约65,532字节）。按实际内容+长度标识（1-2字节）存储。
存储空间	固定为 M × 字符集单个字符最大字节数。例如`utf8mb4`下`CHAR(10)`最多占40字节。	实际字符数 × 字符集字节数 + 长度标识（1或2字节）。
性能	对于长度固定或几乎固定的字段（如MD5哈希值、国家代码），存取速度更快。	对于长度变化大的字段，能节省大量存储空间，是现代开发中的默认选择。
尾部空格	存储时会用空格填充到指定长度，检索时会自动删除尾部空格。	不会填充，存储原样内容（包括尾部空格），检索时也保留尾部空格。
适用场景	身份证号、手机号（特定国家）、UUID（不带`-`）、枚举代码等长度绝对固定的字段。	绝大多数文本字段：用户名、标题、描述、地址等。

选择建议：优先使用 VARCHAR。仅在明确知道长度固定且非常短时，才考虑 CHAR。

2. TEXT 类型

用于存储大文本数据，不能有默认值（BLOB/TEXT列除外）。

类型	最大长度（字符）	特性与用途
TINYTEXT	255 (2^8 - 1)	小段文本。
TEXT	65,535 (2^16 - 1)	标准大文本，如文章内容、产品描述。
MEDIUMTEXT	16,777,215 (2^24 - 1)	较大的文本，如书籍章节。
LONGTEXT	4,294,967,295 (2^32 - 1)	极大文本，如完整的电子书、日志文件。

与VARCHAR的区别：

TEXT类型数据存储在行外（溢出页），而VARCHAR存储在行内。这使得对TEXT的查询效率通常低于VARCHAR。
不能为TEXT类型创建前缀索引（VARCHAR可以），必须指定索引长度。

选择建议：如果文本可能超过VARCHAR的最大长度（约16KB），或者平均长度很长且不常被查询，使用TEXT。否则，用VARCHAR。

3. BINARY 与 VARBINARY

类似于CHAR和VARCHAR，但存储的是二进制字节串，而不是字符串。

没有字符集概念，按字节比较和排序。
适合存储加密数据、压缩数据或真正的二进制信息（如图片、文件的二进制，但通常建议存路径，文件本身放对象存储）。

4. BLOB 类型

TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB。与TEXT系列对应，但存储的是二进制大对象。

现代实践中，很少直接将图片/文件存储在BLOB中，因为：
数据库体积膨胀极快。
读写效率低。
不利于CDN加速和前端直接访问。
通常用于存储加密后的、或压缩处理过的小段二进制数据。

二、数字类型

数字类型分为精确数字和近似数字两类。

1. 精确数字类型

类型	存储空间（字节）	有符号范围（Signed）	无符号范围（Unsigned）	用途
整数类型
`TINYINT`	1	-128 ~ 127	0 ~ 255	状态码、性别（0/1）、年龄范围等。
`SMALLINT`	2	-32,768 ~ 32,767	0 ~ 65,535	端口号、小规模计数。
`MEDIUMINT`	3	-8.3M ~ 8.3M	0 ~ 16.7M	城市ID、中等范围计数。
`INT` (常用)	4	-2.14B ~ 2.14B	0 ~ 4.29B	主键ID、外键、数量、时间戳等，最通用。
`BIGINT`	8	-9.22E18 ~ 9.22E18	0 ~ 1.84E19	极大数据，如全球用户ID、交易流水号。
定点数类型
`DECIMAL(M, D)`	变长	取决于M和D	不支持	精确小数，如金融金额。`M`是总位数（1~65），`D`是小数位数。例如 `DECIMAL(10, 2)` 表示总共10位，其中2位小数。

整数选择建议：

主键默认用 INT UNSIGNED AUTO_INCREMENT 或 BIGINT UNSIGNED（取决于数据量预期）。根据数据范围选择够用且最小的类型，以节省空间和内存。谨慎使用 UNSIGNED，它可能导致一些减法运算出现意外结果。 2. 近似数字（浮点数）类型

类型	存储空间（字节）	特点	用途
`FLOAT(M, D)`	4	单精度浮点数，近似值，有精度损失。	科学计算、对精度要求不高的测量数据（如温度、经纬度）。
`DOUBLE(M, D)`	8	双精度浮点数，比`FLOAT`精度更高，范围更大，但仍是近似值。	需要更高精度的科学或工程计算。

重要警告：由于浮点数是近似存储，FLOAT/DOUBLE 不能用于存储需要精确计算的数值，特别是货币金额。金额必须使用 DECIMAL。

对比示例：

CREATE TABLE test_numbers (
    price_decimal DECIMAL(10, 2), -- 精确存储
    price_float FLOAT(10, 2)       -- 近似存储
);
INSERT INTO test_numbers VALUES (1234567.89, 1234567.89);
-- 查询时，price_float 可能会出现 1234567.88 或 1234567.91 这样的微小误差。

三、日期与时间类型

核心类型概览

类型	格式	范围	存储空间	用途
`DATE`	`YYYY-MM-DD`	'1000-01-01' ~ '9999-12-31'	3字节	仅存储日期，如生日、签约日。
`TIME`	`HHH:MM:SS` 或 `HH:MM:SS`	'-838:59:59' ~ '838:59:59'	3字节	存储时间或时间间隔（可超过24小时）。
`DATETIME`	`YYYY-MM-DD HH:MM:SS`	'1000-01-01 00:00:00' ~ '9999-12-31 23:59:59'	5-8字节	存储日期和时间，与时区无关（存入什么，读出什么）。
`TIMESTAMP`	`YYYY-MM-DD HH:MM:SS`	'1970-01-01 00:00:01' UTC ~ '2038-01-19 03:14:07' UTC	4字节	存储时间戳，与时区相关（存入时转为UTC，读出时转为当前时区）。注意2038年问题。
`YEAR`	`YYYY`	1901 ~ 2155	1字节	存储年份。

DATETIME vs TIMESTAMP 深度对比

这是最重要的选择点。

维度	DATETIME	TIMESTAMP
时区	与时区无关。客户端存入什么值，就存储什么值，读取时也原样返回。	与时区相关。存入时从当前会话时区转换为UTC存储；读取时从UTC转换回当前会话时区。适合国际化应用。
范围	范围很大（1000-9999年）。	范围有限（1970-2038年），有著名的“2038年问题”。
存储	5-8字节（MySQL 5.6.4+）。	4字节，更节省空间。
自动更新	不支持自动初始化/更新（但可以设置 `DEFAULT CURRENT_TIMESTAMP` 和 `ON UPDATE CURRENT_TIMESTAMP`，需要MySQL 5.6.5+）。	支持自动初始化（`DEFAULT CURRENT_TIMESTAMP`）和自动更新（`ON UPDATE CURRENT_TIMESTAMP`），非常适合记录“记录创建时间”和“最后修改时间”。
性能	由于范围大，存储空间稍大，索引占用也稍大。	范围小，存储紧凑，索引性能通常略优。

选择建议：

TIMESTAMP：用于需要自动记录“创建/更新时间”、并且数据范围在1970-2038年之间的字段。例如 created_at, updated_at。 DATETIME：

需要存储历史或未来日期（如出生日期、合同截止日）。
明确希望值与时区无关。
需要避免2038年问题。
在MySQL 8.0+中，也可以设置 DEFAULT 和 ON UPDATE 自动更新。

最佳实践示例

CREATE TABLE `orders` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `order_no` VARCHAR(32) NOT NULL UNIQUE COMMENT '订单号',
    `amount` DECIMAL(12, 2) NOT NULL COMMENT '订单金额',
    `status` TINYINT UNSIGNED DEFAULT 0 COMMENT '订单状态',
    `customer_name` VARCHAR(100) NOT NULL COMMENT '客户姓名',
    `remark` TEXT COMMENT '订单备注',
    `order_date` DATE NOT NULL COMMENT '订单日期',
    -- 使用 DATETIME 记录业务时间，与时区无关
    `paid_at` DATETIME NULL COMMENT '支付时间',
    -- 使用 TIMESTAMP 自动记录系统时间
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后更新时间'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;