摘要：从一次"时间莫名其妙少了8小时"的线上故障出发，深度剖析DATETIME和TIMESTAMP的5大核心差异。通过时区转换的真实案例、存储范围对比、以及性能测试数据，揭秘为什么TIMESTAMP会自动转换时区、DATETIME不会，以及2038年问题的解决方案。配合时序图展示时区转换流程，给出不同场景下的选型建议。
💥 翻车现场
周四上午，运营同学在群里炸了。
运营同学：@哈吉米 数据有问题！用户的订单时间都少了8小时！
哈吉米：？？？发个截图我看看
数据库记录：
order_id | create_time
---------|--------------------
1001 | 2024-10-07 10:30:00
后台显示：
订单ID：1001
创建时间：2024-10-07 02:30:00 ← 少了8小时！
哈吉米："卧槽，怎么会这样？"
查看表结构：
SHOW CREATE TABLE `order`\G
CREATE TABLE `order` (
`order_id` bigint PRIMARY KEY,
`user_id` bigint NOT NULL,
`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, ← TIMESTAMP类型
...
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
哈吉米："TIMESTAMP有问题吗？"
查看MySQL时区设置：
SHOW VARIABLES LIKE '%time_zone%';
+------------------+--------+
| Variable_name | Value |
+------------------+--------+
| system_time_zone | CST | -- 中国标准时间 UTC+8
| time_zone | +00:00 | ← MySQL时区设置成了UTC（0时区）
+------------------+--------+
哈吉米："原来MySQL时区是UTC，但我本地是东八区，所以差了8小时！"
下午，南北绿豆和阿西噶阿西来了。
南北绿豆："TIMESTAMP会自动根据时区转换，DATETIME不会！"
哈吉米："？？？"
阿西噶阿西："这是DATETIME和TIMESTAMP的核心区别之一，我给你讲讲。"
🤔 核心区别1：时区处理
TIMESTAMP的时区转换
原理：TIMESTAMP存储的是UTC时间，查询时自动转换成当前会话的时区。
时序图：
sequenceDiagram
participant Client as 客户端(东八区)
participant MySQL as MySQL(time_zone=+00:00)
participant Storage as 磁盘存储
Note over Client,Storage: 插入数据
Client->>MySQL: INSERT ... VALUES ('2024-10-07 10:30:00')
Note over MySQL: 当前会话时区：+08:00
MySQL->>MySQL: 转换：10:30:00(+08) → 02:30:00(UTC)
MySQL->>Storage: 存储：02:30:00 (UTC)
Note over Client,Storage: 查询数据（会话时区=+08:00）
Client->>MySQL: SELECT create_time
MySQL->>Storage: 读取：02:30:00 (UTC)
MySQL->>MySQL: 转换：02:30:00(UTC) → 10:30:00(+08)
MySQL->>Client: 返回：10:30:00
Note over Client,Storage: 查询数据（会话时区=+00:00）
Client->>MySQL: SET time_zone='+00:00'<br/>SELECT create_time
MySQL->>Storage: 读取：02:30:00 (UTC)
MySQL->>MySQL: 不转换（已经是UTC）
MySQL->>Client: 返回：02:30:00
测试验证：
-- 创建测试表
CREATE TABLE test_timestamp (
id INT PRIMARY KEY,
event_time TIMESTAMP
);
-- 设置时区为东八区
SET time_zone = '+08:00';
-- 插入数据
INSERT INTO test_timestamp VALUES (1, '2024-10-07 10:30:00');
-- 查询（东八区）
SELECT * FROM test_timestamp;
+----+---------------------+
| id | event_time |
+----+---------------------+
| 1 | 2024-10-07 10:30:00 | ← 显示10:30
+----+---------------------+
-- 切换到UTC时区
SET time_zone = '+00:00';
-- 再查询
SELECT * FROM test_timestamp;
+----+---------------------+
| id | event_time |
+----+---------------------+
| 1 | 2024-10-07 02:30:00 | ← 显示02:30（少了8小时）
+----+---------------------+
DATETIME不受时区影响
原理：DATETIME存储的是字面值，查询时不做任何转换。
-- 创建测试表
CREATE TABLE test_datetime (
id INT PRIMARY KEY,
event_time DATETIME
);
-- 设置时区为东八区
SET time_zone = '+08:00';
-- 插入数据
INSERT INTO test_datetime VALUES (1, '2024-10-07 10:30:00');
-- 查询（东八区）
SELECT * FROM test_datetime;
+----+---------------------+
| id | event_time |
+----+---------------------+
| 1 | 2024-10-07 10:30:00 | ← 显示10:30
+----+---------------------+
-- 切换到UTC时区
SET time_zone = '+00:00';
-- 再查询
SELECT * FROM test_datetime;
+----+---------------------+
| id | event_time |
+----+---------------------+
| 1 | 2024-10-07 10:30:00 | ← 还是10:30（不变）
+----+---------------------+
南北绿豆："看到了吗？DATETIME存什么就是什么，不会因为时区改变。"
对比总结
| 特性 | TIMESTAMP | DATETIME |
|---|---|---|
| 存储 | UTC时间戳（4字节整数） | 字面值（8字节） |
| 时区转换 | ✅ 自动转换 | ❌ 不转换 |
| 会话时区影响 | ✅ 受影响 | ❌ 不受影响 |
🤔 核心区别2：存储范围
TIMESTAMP的2038年问题
存储原理：TIMESTAMP用4字节整数存储从1970-01-01 00:00:00 UTC开始的秒数。
4字节有符号整数：
最大值：2^31 - 1 = 2147483647
2147483647秒 = 2038-01-19 03:14:07 UTC
所以TIMESTAMP只能存储到2038年！
测试：
-- 尝试插入2038年以后的时间
INSERT INTO test_timestamp VALUES (2, '2038-01-19 03:14:07');
-- Query OK ✅
INSERT INTO test_timestamp VALUES (3, '2038-01-19 03:14:08');
-- ERROR 1292 (22007): Incorrect datetime value: '2038-01-19 03:14:08'
-- ❌ 超出范围
阿西噶阿西："所以TIMESTAMP有2038年问题，超过这个时间就存不了！"
DATETIME的范围更大
-- DATETIME范围
'1000-01-01 00:00:00' 到 '9999-12-31 23:59:59'
-- 测试
CREATE TABLE test_datetime (
id INT PRIMARY KEY,
event_time DATETIME
);
INSERT INTO test_datetime VALUES (1, '1000-01-01 00:00:00'); -- ✅
INSERT INTO test_datetime VALUES (2, '9999-12-31 23:59:59'); -- ✅
INSERT INTO test_datetime VALUES (3, '2050-01-01 00:00:00'); -- ✅
范围对比：
| 类型 | 范围 | 是否有2038年问题 |
|---|---|---|
| TIMESTAMP | 1970-01-01 00:00:01 ~ 2038-01-19 03:14:07 | ✅ 有 |
| DATETIME | 1000-01-01 00:00:00 ~ 9999-12-31 23:59:59 | ❌ 没有 |
哈吉米："所以如果要存储很久以后的时间（比如用户生日、预约时间），必须用DATETIME？"
南北绿豆："对！"
🤔 核心区别3：存储空间
存储大小对比
| 类型 | MySQL 5.6- | MySQL 5.6+ | 说明 |
|---|---|---|---|
| TIMESTAMP | 4字节 | 4字节 | 固定4字节 |
| DATETIME | 8字节 | 5字节 | MySQL 5.6优化了 |
MySQL 5.6+的优化：
DATETIME(0)：5字节（不存储小数秒）
DATETIME(1)~DATETIME(2)：6字节（1-2位小数秒）
DATETIME(3)~DATETIME(4)：7字节（3-4位小数秒）
DATETIME(5)~DATETIME(6)：8字节（5-6位小数秒）
TIMESTAMP同理：
TIMESTAMP(0)：4字节
TIMESTAMP(1)~TIMESTAMP(2)：5字节
TIMESTAMP(3)~TIMESTAMP(4)：6字节
TIMESTAMP(5)~TIMESTAMP(6)：7字节
测试：
CREATE TABLE test_storage (
ts0 TIMESTAMP,
ts3 TIMESTAMP(3), -- 精确到毫秒
dt0 DATETIME,
dt3 DATETIME(3)
);
-- 查看表大小
SELECT
column_name,
data_type,
datetime_precision,
character_octet_length
FROM information_schema.columns
WHERE table_name = 'test_storage';
+-------------+-----------+---------------------+-------------------------+
| column_name | data_type | datetime_precision | character_octet_length |
+-------------+-----------+---------------------+-------------------------+
| ts0 | timestamp | 0 | NULL |
| ts3 | timestamp | 3 | NULL |
| dt0 | datetime | 0 | NULL |
| dt3 | datetime | 3 | NULL |
+-------------+-----------+---------------------+-------------------------+
空间对比：
| 场景 | TIMESTAMP | DATETIME | 差距 |
|---|---|---|---|
| 无小数秒 | 4字节 | 5字节 | TIMESTAMP省1字节 |
| 3位小数秒 | 6字节 | 7字节 | TIMESTAMP省1字节 |
| 存储1亿行 | 400MB | 500MB | TIMESTAMP省100MB |
南北绿豆："虽然TIMESTAMP省空间，但差距不大，不是主要选型依据。"
🤔 核心区别4：默认值和更新
TIMESTAMP的自动更新
-- TIMESTAMP的默认行为
CREATE TABLE test_auto_update (
id INT PRIMARY KEY,
content VARCHAR(100),
create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP, -- 插入时自动设置
update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP -- 更新时自动更新
);
-- 插入数据
INSERT INTO test_auto_update (id, content) VALUES (1, 'hello');
SELECT * FROM test_auto_update;
+----+---------+---------------------+---------------------+
| id | content | create_time | update_time |
+----+---------+---------------------+---------------------+
| 1 | hello | 2024-10-07 10:30:00 | 2024-10-07 10:30:00 |
+----+---------+---------------------+---------------------+
-- 等待5秒，更新数据
UPDATE test_auto_update SET content = 'world' WHERE id = 1;
SELECT * FROM test_auto_update;
+----+---------+---------------------+---------------------+
| id | content | create_time | update_time |
+----+---------+---------------------+---------------------+
| 1 | world | 2024-10-07 10:30:00 | 2024-10-07 10:30:05 |
+----+---------+---------------------+---------------------+
-- update_time自动更新了 ✅
DATETIME也支持（MySQL 5.6+）
CREATE TABLE test_datetime_auto (
id INT PRIMARY KEY,
content VARCHAR(100),
create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
-- 功能和TIMESTAMP一样 ✅
对比：
| 特性 | TIMESTAMP | DATETIME |
|---|---|---|
| DEFAULT CURRENT_TIMESTAMP | ✅ 所有版本 | ✅ MySQL 5.6+ |
| ON UPDATE CURRENT_TIMESTAMP | ✅ 所有版本 | ✅ MySQL 5.6+ |
🤔 核心区别5：NULL值处理
TIMESTAMP的特殊规则
-- TIMESTAMP的第一个列默认NOT NULL
CREATE TABLE test_ts_null (
id INT PRIMARY KEY,
ts1 TIMESTAMP, -- 默认NOT NULL + DEFAULT CURRENT_TIMESTAMP
ts2 TIMESTAMP -- 可以为NULL
);
SHOW CREATE TABLE test_ts_null\G
CREATE TABLE `test_ts_null` (
`id` int PRIMARY KEY,
`ts1` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, ← 自动加了NOT NULL
`ts2` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB;
南北绿豆："TIMESTAMP的第一个列会自动加NOT NULL，这是MySQL的特殊规则。"
DATETIME默认允许NULL
CREATE TABLE test_dt_null (
id INT PRIMARY KEY,
dt1 DATETIME,
dt2 DATETIME
);
SHOW CREATE TABLE test_dt_null\G
CREATE TABLE `test_dt_null` (
`id` int PRIMARY KEY,
`dt1` datetime DEFAULT NULL, ← 默认允许NULL
`dt2` datetime DEFAULT NULL
) ENGINE=InnoDB;
📊 性能对比测试
测试环境：
- MySQL 8.0
- 1000万行数据
- 查询、插入、排序性能对比
测试1：插入性能
-- TIMESTAMP
INSERT INTO test_timestamp (id, event_time) VALUES (1, NOW());
-- 100万次插入：12.3秒
-- DATETIME
INSERT INTO test_datetime (id, event_time) VALUES (1, NOW());
-- 100万次插入：12.5秒
结论：插入性能基本一样。
测试2：查询性能
-- TIMESTAMP
SELECT * FROM test_timestamp WHERE event_time > '2024-01-01';
-- 耗时：1.2秒
-- DATETIME
SELECT * FROM test_datetime WHERE event_time > '2024-01-01';
-- 耗时：1.1秒
结论：查询性能基本一样。
测试3：时区转换的性能影响
-- TIMESTAMP（需要时区转换）
SET time_zone = '+08:00';
SELECT * FROM test_timestamp LIMIT 1000000;
-- 耗时：2.3秒
-- DATETIME（不需要时区转换）
SELECT * FROM test_datetime LIMIT 1000000;
-- 耗时：2.1秒
结论：TIMESTAMP有微弱的时区转换开销，但差距不大（约10%）。
🎯 选型建议
场景1：需要时区转换 → TIMESTAMP
适用场景：
- 全球化应用（不同时区的用户）
- 需要显示"本地时间"
- 服务器在不同时区
示例：
- 用户发帖时间（显示用户本地时间）
- 订单创建时间（全球订单系统）
场景2：不需要时区转换 → DATETIME
适用场景：
- 单时区应用
- 需要存储"绝对时间"（不随时区变化）
- 需要存储很久以后的时间（2038年后）
示例：
- 用户生日（固定的日期）
- 预约时间（北京时间2024-10-10 14:00，不管在哪查都是这个时间）
- 历史事件时间
场景3：超过2038年 → 必须DATETIME
-- 存储用户生日
CREATE TABLE user (
id BIGINT PRIMARY KEY,
username VARCHAR(50),
birthday DATETIME -- 可能是1950年出生的用户
);
-- 存储预约时间
CREATE TABLE appointment (
id BIGINT PRIMARY KEY,
user_id BIGINT,
appointment_time DATETIME -- 可能预约到2050年
);
推荐方案
| 字段类型 | 推荐类型 | 原因 |
|---|---|---|
| 创建时间 | DATETIME | 不需要时区转换，记录客观时间 |
| 更新时间 | DATETIME | 不需要时区转换 |
| 用户操作时间 | TIMESTAMP | 需要显示用户本地时间 |
| 生日 | DATE | 只需要日期，不需要时间 |
| 预约时间 | DATETIME | 可能超过2038年 |
阿西噶阿西："一般情况下，推荐用DATETIME，除非有明确的时区转换需求。"
🎓 面试标准答案
题目：DATETIME和TIMESTAMP的区别？
答案：
5大核心区别：
-
时区处理
- TIMESTAMP：存储UTC时间，查询时自动转换到当前时区
- DATETIME：存储字面值，不做时区转换
-
存储范围
- TIMESTAMP：1970-2038年（有2038年问题）
- DATETIME：1000-9999年
-
存储空间
- TIMESTAMP：4字节
- DATETIME：5字节（MySQL 5.6+）
-
默认行为
- TIMESTAMP第一个列自动NOT NULL + DEFAULT CURRENT_TIMESTAMP
- DATETIME默认允许NULL
-
性能
- 基本一样，TIMESTAMP有微弱的时区转换开销
选型建议：
- 需要时区转换：用TIMESTAMP
- 不需要时区转换：用DATETIME
- 超过2038年：必须用DATETIME
- 一般推荐：DATETIME
🎉 结束语
晚上7点，哈吉米终于搞清楚DATETIME和TIMESTAMP的区别了。
哈吉米："原来TIMESTAMP会自动时区转换，怪不得我本地查询少了8小时！"
南北绿豆："对，这是TIMESTAMP的特性，有好有坏。"
阿西噶阿西："一般情况下，推荐用DATETIME，除非你的应用需要时区转换。"
哈吉米："还有2038年问题，以后设计表都用DATETIME了！"
南北绿豆："对，DATETIME更通用，范围更大，而且MySQL 5.6之后存储空间也优化了。"
记忆口诀：
TIMESTAMP存时间戳，自动转换看时区
DATETIME存字面值，存啥查啥不变化
2038年是坎，TIMESTAMP存不了
一般推荐DATETIME，除非需要时区转
希望这篇文章能帮你彻底搞懂DATETIME和TIMESTAMP的区别！下次设计表结构，就知道该选哪个了！💪
