MySQL 中 DATE、DATETIME 和 TIMESTAMP 的区别

在 MySQL 中，DATE、DATETIME 和 TIMESTAMP 都是用于存储日期和时间的数据类型，但它们在格式、范围、存储大小、时区处理和功能上存在显著差异。以下将逐步对比这些区别，帮助您根据实际需求选择合适的数据类型。

1. 定义和基本用途

DATE：仅存储日期部分（年、月、日），不包括时间信息。适用于只需要记录日期的场景，如生日、事件日期。
DATETIME：存储日期和时间（年、月、日、时、分、秒）。适用于需要精确时间点的场景，如订单创建时间、日志记录。
TIMESTAMP：也存储日期和时间，但与时区相关。它基于 Unix 时间戳（从 ‘1970-01-01 00:00:00’ UTC 开始的秒数），并自动进行时区转换。适用于需要自动更新或国际化处理的场景，如用户登录时间。

2. 格式和有效范围

DATE：
- 格式：YYYY-MM-DD。
- 范围：‘1000-01-01’ 到 ‘9999-12-31’。
- 示例：2023-10-05
DATETIME：
- 格式：YYYY-MM-DD HH:MM:SS。
- 范围：‘1000-01-01 00:00:00’ 到 ‘9999-12-31 23:59:59’。
- 示例：2023-10-05 14:30:00'
TIMESTAMP：
- 格式：YYYY-MM-DD HH:MM:SS，但在存储时转换为 UTC 时间。
- 范围：‘1970-01-01 00:00:01’ UTC 到 ‘2038-01-19 03:14:07’ UTC（由于 4 字节存储限制，称为“2038 年问题”）。
- 示例：2023-10-05 14:30:00'

3. 存储大小

DATE：占用 3 字节。
DATETIME：占用 8 字节（在 MySQL 5.6.4 及之后版本中占用5字节）。
TIMESTAMP：占用 4 字节。

存储大小影响性能和空间效率：TIMESTAMP 更紧凑，但范围较小；DATETIME 范围更大但占用更多空间；DATE 适用于纯日期场景。

4. 时区处理

DATE 和 DATETIME：存储为字面值，不进行时区转换。插入和检索时值保持不变（例如，插入 ‘2023-10-05 14:30:00’ 会原样存储）。
TIMESTAMP：存储时转换为 UTC 时间，检索时根据 MySQL 服务器的时区设置或会话时区转换回本地时间。例如，如果服务器时区为 UTC+8，插入 ‘2023-10-05 14:30:00’ 会存储为 UTC 时间 ‘2023-10-05 06:30:00’，检索时再转换回 ‘2023-10-05 14:30:00’。

5. 自动更新功能

TIMESTAMP：支持自动更新特性。可以在表定义中设置 DEFAULT CURRENT_TIMESTAMP 或 ON UPDATE CURRENT_TIMESTAMP，使其在插入或更新时自动设置为当前时间（例如，用于记录最后修改时间）。
DATETIME：在 MySQL 5.6.5 及之后版本也支持自动更新（如 DEFAULT CURRENT_TIMESTAMP），但 TIMESTAMP 更常用且兼容性好。
DATE：不支持自动更新。

6. 使用场景建议

使用 DATE 当：只关心日期，如员工入职日期、活动日期。
使用 DATETIME 当：需要完整日期和时间，且范围较大（超过 2038 年）或不需要时区转换，如历史数据记录。
使用 TIMESTAMP 当：需要自动更新时间、处理时区敏感数据或节省存储空间，如用户会话跟踪、国际化应用。

总结关键区别

特性	DATE	DATETIME	TIMESTAMP
存储内容	仅日期 (YYYY-MM-DD)	日期和时间 (YYYY-MM-DD HH:MM:SS)	日期和时间 (YYYY-MM-DD HH:MM:SS)
范围	1000-01-01 到 9999-12-31	1000-01-01 00:00:00 到 9999-12-31 23:59:59	1970-01-01 00:00:01 UTC 到 2038-01-19 03:14:07 UTC
存储大小	3 字节	8 字节（5字节）	4 字节
时区处理	无转换	无转换	存储为 UTC，检索时转换
自动更新	不支持	支持（5.6.5+）	支持

在实际应用中，选择数据类型时需考虑数据范围、时区需求和存储效率。例如，对于用户注册时间，优先使用 TIMESTAMP 以利用自动更新；对于长期历史数据，DATETIME 更可靠。建议参考 MySQL
官方文档测试具体版本行为。

其他时间字段

TIME
- 存储内容：时间（时分秒）
- 格式：HH:MM:SS
- 范围：-838:59:59 到 838:59:59（可表示时间间隔）
- 示例：14:30:45 或 -02:30:00（表示负时间间隔）
YEAR
- 存储内容：年份
- 格式：YYYY（4 位）或 YY（2 位，已弃用）
- 范围：1901 到 2155
- 示例：2023
微秒支持（MySQL 5.6.4+）
- 通过附加精度参数实现（如 DATETIME(3) 存储毫秒）
- 格式：YYYY-MM-DD HH:MM:SS.ffffff
- 示例：2023-10-05 14:30:45.123456

选择建议

只需日期 → DATE
需完整时间且不涉及时区 → DATETIME
需自动时区转换或记录更新时间 → TIMESTAMP
仅需时间或时间间隔 → TIME
仅需年份 → YEAR

示例：

-- 创建包含三种类型的表
CREATE TABLE example (date_col DATE comment '创建日期',time_col TIME comment '时间',year_col YEAR comment '年份',datetime_col DATETIME DEFAULT CURRENT_TIMESTAMP comment '创建时间',timestamp_col TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP comment '时间戳'
);