TIMESTAMP vs DATETIME 核心区别

TIMESTAMP vs DATETIME 核心区别

TIMESTAMP (时间戳)

• 本质：世界时间 (UTC)
• 存储方式： 存储为 1970-01-01 UTC 以来的秒数 (4字节整数)
• 时区敏感性：强敏感。存入时：Session时区 -取出时：UTC Session时区。
• 时间范围：1970年 ～ 2038年 (受32位整数限制)。
• 使用场景：记录数据变更时刻、跨国业务（需要全球统一时间）。
• 存储空间：4 字节 (+小数秒部分)。

DATETIME (日期时间)

• 本质：日历时间 (墙上时间)

• 存储方式： 存储为 YYYYMMDDHHMMSS 打包后的整数 (5字节)。

• 时区敏感性：不敏感。你存什么，它就原封不动地存什么，取出来也是什么。
• 时间范围：000年 ～ 9999年。
• 使用场景：记录将来时间（如会员过期）、生日、历史事件（不受时区影响）。
• 存储空间：5 字节 (+小数秒部分)。

FAQ

连接串带时区后，是谁在做转换？

答案是：是协作完成的，但最终改变存储值的“UTC 转换”是由 MySQL Server 端完成的，而驱动（Driver）负责“上下文环境”的协商。 假设 JDBC 连接串是： jdbc:mysql://host:3306/db?serverTimezone=Asia/Shanghai，且 Java 代码运行在 America/New_York 时区。

驱动 (Driver) 做了什么？

当驱动（如 MySQL Connector/J）建立连接时，它会读取 serverTimezone 参数。配置 Session: 驱动会在连接握手阶段，或者连接建立后的第一条指令，告知 MySQL Server：“当前这个 Session 的 time_zone 应该是 Asia/Shanghai”。

预处理 (PreparedStatement): 当使用 setTimestamp() 发送数据时，驱动会将 JVM 时间对象（Java Instant 或 Date）转换成符合 MySQL 协议的格式。如果是文本协议，它会发送 ‘2023-12-25 10:00:00’ 这样的字符串。

MySQL Server 端做了什么？

这是关键步骤。Server 端根据当前 Session 的 time_zone 设置（由驱动刚才设定）来处理数据。

场景 A：写入 TIMESTAMP

接收： Server 收到 ‘2023-12-25 10:00:00’。

定位： Server 认为这个时间是 Asia/Shanghai 的时间（因为 Session time_zone 设为此）。

转换 (核心动作)： Server 内部计算：Asia/Shanghai 的 10:00 等于 UTC 的 02:00。

存储： Server 将 UTC 02:00 对应的秒数存入磁盘（即你提到的 mi_int4store 操作）。

场景 B：写入 DATETIME

接收： Server 收到 ‘2023-12-25 10:00:00’。

存储： Server 直接将 2023, 12, 25, 10… 这些数字打包（Bit Packing），存入磁盘。不进行任何时区转换。

结论

驱动 (Driver): 负责“对齐口径”。它确保 Server 知道客户端希望以什么时区来解析传入的时间字符串。

服务端 (Server): 负责“物理转换”。对于 TIMESTAMP，是 Server 拿着 Session 时区把时间转成 UTC 秒数落盘的。

注意： 如果你的 JDBC 驱动版本很老，或者配置不当（例如没有传 serverTimezone 且 Server 端默认为 SYSTEM），Server 就会用它操作系统的时区来解析，这往往是时间差错乱的根源。

TIMESTAMP vs DATETIME 内部存储细节

TIMESTAMP (4字节):

确实是标准的 Unix Timestamp（大端序存储）。mi_int4store(ptr, tm->tv_sec) 是 MyISAM/Aria 引擎层的代码，InnoDB 也有类似的机制。它直接把 UTC 秒数写成二进制。

DATETIME (5字节打包):

在 5.6.4 之前，DATETIME 占用 8 字节（存为 YYYYMMDDHHMMSS 的 long long 形式）。

在 5.6.4 之后，为了支持微秒并节省空间，改成了描述的 5 字节打包格式。

计算验证：

年(14位) + 月(4位) + 日(5位) = 23位。

时(5位) + 分(6位) + 秒(6位) = 17位。

23 + 17 = 40位 = 5 字节。

这种设计极其精妙，它允许直接通过位运算提取年月日，且保持了字典序（可以直接用于索引排序），而无需转换为字符串。

如果业务需要国际化（用户在不同时区看同一条数据，希望看到的是各自的本地时间），优先选 TIMESTAMP。如果你的业务只在一个固定时区，或者你需要保存“未来的某个绝对时间点”（例如：2050年的某个合同到期日，超过了2038限制），必须选 DATETIME。

业务场景

TIMESTAMP 做国际化？

核心理由：因为“懒”（便利性）。TIMESTAMP 的最大优势在于 MySQL 帮你把时区转换的工作给做了。场景： 一个在美国的用户存入时间，一个在中国的用户读取时间。使用 TIMESTAMP：美国用户传 2023-12-25 10:00 (美国时间) ，MySQL 自动转成 UTC 存入。中国用户查询 MySQL 自动把 UTC 转成 2023-12-25 23:00 (中国时间) 返回。优势： 后端开发人员代码写得很爽，不用自己在代码里根据用户的时区加加减减，数据库全自动处理。但是，这种便利是有代价的，就是 2038 年溢出问题。

2038 年溢出风险是致命的吗？

是的，对于现代企业级应用，几乎是致命的。

TIMESTAMP 范围： 1970 ~ 2038-01-19 03:14:07 UTC。

现实问题：

如果是记录“创建时间”、“更新时间”，可能还能撑几年。

但是，如果你的业务涉及“会员到期日”、“保险终身保单”、“房屋贷款结束日”，现在就已经不能用 TIMESTAMP 了，因为这些时间点很容易就超过 2038 年。

注： 即使 MySQL 8.0 目前的标准版本中，TIMESTAMP 依然是 32 位 int 存储，受 2038 年限制（虽然 MariaDB 已经通过修改底层解决了这个问题，但 Oracle MySQL 官方版尚未彻底改变这一行为）。

用 DATETIME 做国际化？

既然 TIMESTAMP 有坑，DATETIME 又不带时区，那现在的通用解决方案是什么？

答案：DATETIME + 应用层控制 + 统一存储 UTC。

具体做法：

• 数据库设计：字段类型选 DATETIME（5字节或8字节），范围可达 9999 年。约定： 数据库里存的一定是 UTC 时间（世界标准时间），绝对不要存北京时间或纽约时间。

• 写入流程（应用层做）：用户在界面选择 2025-12-25 10:00 (假设用户在东京，GMT+9)。Java/Go/Python 代码接收到请求，根据用户的时区，将其转化为 UTC 时间 2025-12-25 01:00。生成的 SQL 语句是：INSERT INTO … VALUES (‘2025-12-25 01:00:00’)。MySQL 把它当成普通数字存进去（DATETIME 不做转换）。

• 读取流程（应用层做）：应用从数据库读出 2025-12-25 01:00:00。应用判断当前查看页面的用户是“纽约用户(GMT-5)”。应用代码将 UTC 时间减去 5 小时，渲染给前端 2025-12-24 20:00。

这套方案的优缺点：

• 优点：彻底解决 2038 问题：DATETIME 可以用到人类文明毁灭。性能更好：MySQL 不需要消耗 CPU 去做时区计算（CONVERT_TZ 是有开销的）。
• 数据迁移方便：数据本身就是原样的，不会因为数据库服务器的时区配置变了（比如从中国机房迁到美国机房）导致导出的数据时间错乱。

• 语义明确：代码里显式控制时区，比依赖数据库隐式转换更不容易出错。

• 缺点：开发稍微麻烦一点点，所有的时间处理必须经过一个统一的 Utils 类来进行 UTC 转换。

还有一种极端方案：BIGINT

有些极客团队（比如早期的阿里某些部门、很多游戏公司）喜欢直接用 BIGINT 存储毫秒级时间戳。

原理： 直接存 1672531200000 这样的长整数。

优点：跨平台神技：Java 的 System.currentTimeMillis() 存进去，拿出来直接用，完全没有格式转换开销。

效率最高：排序、比较就是整数运算，速度最快。

范围超大（64位整数，大约能用到几亿年后）。

缺点：可读性极差：DBA 也就是运维人员去数据库查数据时，看到一堆数字 1672531200000，完全不知道是哪年哪月，必须手动敲命令转一下才能看懂，排查问题很不方便。

FAQ

BIGINT 的流程：纯粹的搬运工

假设 Java 中 long t = 1672531200000L;

• Java 端 (JVM):t 在内存里就是一个 64位的二进制数 (0x000001856E3B3300)。JDBC 驱动在发送数据时，直接把这 8 个字节写入网络 buffer。开销： 几乎为 0（内存拷贝）。

• 网络传输:传输的就是这 8 个字节的二进制流。

• MySQL Server 端:收到这 8 个字节。储引擎（InnoDB）直接把这 8 个字节写到磁盘页（Page）中对应的位置。开销： 几乎为 0（内存拷贝）。

总结： 全程没有涉及任何“日期”、“时间”、“闰年”、“时区”的概念，计算机只把它当成一个普通的数字。

DATETIME 的流程：复杂的翻译官

假设 Java 中存入 2023-01-01 00:00:00。即使用了预处理（PreparedStatement），开销依然存在。

• Java 端 (JVM):需要把 Java 的 Date 对象或者 LocalDateTime 对象解析出来。

• JDBC 驱动通常需要将其转换成符合 MySQL 协议的结构（可能是字符串形式 ‘2023-01-01…‘，也可能是分段的数字）。

• MySQL Server 端 (最耗时的地方):解析（Parsing）： MySQL 收到数据后，如果是字符串，必须进行文本解析（这是很慢的）。
• 合法性检查（Validation）： MySQL 必须检查：月份是不是 1-12？日期是不是 1-31？今年是闰年吗？2月有29天吗？这个时间存在吗（比如夏令时切换时的空洞时间）？

• 位打包（Packing）： 检查通过后，MySQL 需要执行位运算逻辑，把 年、月、日、时、分、秒 压缩成之前提到的 5字节 MY_PACKED_TIME 格式。比如：((year * 13 + month) « 5)

  day … 这种数学计算。

• 落盘:最后才把算出来的 5 个字节写入磁盘。

总结： DATETIME 需要 CPU 介入进行大量的逻辑判断和数学运算（Calendar Logic），这就是所谓的“格式转换开销”。