MySQL里那些时间类型到底是怎么存储的，物理层面其实挺有意思的细节

关于MySQL中时间类型在物理层面是如何存储的，这个话题确实很有意思，我们平时在命令行或者客户端里看到的日期时间格式，‘2023-10-26 15:30:00’，并不是它们在硬盘上的真实模样，为了高效地存储和计算，MySQL在底层使用了更紧凑、更直接的二进制格式。

我们需要理解一个基本概念：MySQL的服务端负责将我们写入的人类可读的时间值，转换成一个紧凑的二进制格式，然后写入硬盘（比如ibd文件）；当需要读取时，再从二进制格式解码成我们熟悉的字符串形式显示出来。 这个过程对于用户是完全透明的。

我们来分别看看几种主要的时间类型是怎么“变身”的。

DATE类型 - 只存储年月日

DATE类型只关心日期，不关心时间，你可能会想，那存成 ‘YYYY-MM-DD’ 的字符串不就行了？但那样需要10个字节，MySQL用了更聪明的方法，根据MySQL官方文档的描述，DATE类型实际上是用一个3字节的整数来存储的。

这个整数是怎么来的呢？它是将一个日期距离一个基准点（通常是‘0000-01-01’）的天数计算出来，然后存储这个天数。‘0000-01-01’就是0天，‘0000-01-02’就是1天，以此类推，这样，无论日期多大，都只需要3个字节（因为3字节无符号整数最大可以表示16777215天，约等于4万多年，完全够用了），这种“存储偏移量”而不是原始字符串的思路，在数据库系统中非常常见,极大地节省了空间。

TIME类型 - 只存储时间

TIME类型存储时间，可以精确到微秒，它的存储方式有点特别，在MySQL内部，TIME类型被理解为一天中的时间加上可能的时间偏移，但它实际上可以表示一个时间跨度，甚至可以是负值（用于表示两个时间点的差值）。

根据源码和文档，TIME类型的存储结构是：它将自己表示的微秒数打包存储，它使用3个字节的基础部分来存储时间，格式是：HHMMSS 的打包格式，但这不是字符串，而是类似BCD码的格式，或者直接是二进制数，更重要的是，为了支持微秒精度，如果定义了小数秒（比如TIME(3)表示毫秒精度），MySQL会额外分配存储空间：1字节可以存储到百分之一秒和千分之一秒，2字节存储万分之一秒和十万分之一秒，3字节存储微秒，一个TIME(6)类型最多会占用 3 + 3 = 6个字节，这种按需分配额外空间的方式,也是在空间和精度之间做的权衡。

DATETIME和TIMESTAMP - 最常用的日期时间类型

这两个类型看起来很像,但在存储机制和语义上有着根本的不同。

• DATETIME： 这个类型存储的是一个“绝对”的时间值，它与时区无关，你存入的是什么时间，读出来的就是什么时间，不受数据库服务器时区设置变化的影响，它的存储方式非常直观，就是将年月日时分秒（以及小数秒）打包成一个连续的整数。

  

  在没有微秒精度的情况下（MySQL 5.6.4之前），DATETIME固定使用8个字节，它的格式是：YYYYMMDD HHMMSS 的数字被组合在一起。‘2023-10-26 15:30:00’ 在硬盘上可能被存为类似 20231026153000 这样一个大整数的二进制形式，从MySQL 5.6.4开始支持小数秒后，存储结构发生了变化，它使用5个字节的基础部分（前4个字节存储 YYYYMMDD HHMMSS 的打包整数，但格式更优化），再加上最多3个字节用于存储微秒部分,这种设计使得DATETIME的存储非常紧凑和高效。

• TIMESTAMP： 这是最有意思的一个类型，它存储的不是一个直观的日期时间，而是一个时间戳——即从‘1970-01-01 00:00:00’ UTC（世界协调时） 开始到当前时刻所经过的秒数，这通常是一个4字节的整数（如果在5.6.4之前定义，或不带小数秒），正因为存储的是UTC时间的秒数，所以TIMESTAMP是与时区相关的。

  它的工作流程是这样的：当你插入一个时间值（‘2023-10-26 15:30:00’）时，MySQL会假设这个时间是你当前连接会话时区的时间，然后将其转换为对应的UTC时间戳（秒数）并存储，当你查询时，MySQL再根据你当前会话的时区设置，将这个UTC时间戳转换回对应的本地时间显示，这就解释了为什么改变 time_zone 系统变量后，查出来的TIMESTAMP值会变，从MySQL 5.6.4开始，如果需要微秒精度，TIMESTAMP会占用最多7个字节（4字节秒数 + 3字节微秒数）。

总结一下物理层面的核心思想：

MySQL的时间类型存储，核心就是空间效率和计算效率，它尽量避免存储冗长的字符串，而是将时间概念转化为数字（天数、秒数、打包的整数），然后用最少的字节数来存储这些数字，DATETIME像是一个封装好的日历和钟表，直接记录数字，而TIMESTAMP则像是一个秒表，记录着一个基准点之后的秒数，需要二次转换，DATE和TIME则是将这个思想应用在更具体的场景下，只取所需，进一步节省空间，这些精妙的设计，使得MySQL在处理海量时间数据时，既能保证性能,又能有效地控制存储成本。