2024-11-09
版权声明:本文为博主原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。 本文链接:https://www.skjava.com/mianshi/baodian/detail/2066579061

是使用 DATETIME 还是 TIMESTAMP 就需要知道他们的区别,两者之间的区别如下:

  • 范围

DATETIME 可以表示的时间范围是 1000-01-01 00:00:009999-12-31 23:59:59。而 TIMESTAMP可以表示的时间范围是 1970-01-01 00:00:01 UTC 到 2038-01-19 03:14:07 UTC。

所以,TIMESTAMP 最大日期只支持到 2038,到了 2038 年,我们可能会遇到像千年虫那样的问题。所以,如果我们需要存储的时间比较长,大明哥推荐选择 DATETIME,可能有人觉得,真到了 2038 难道就没有解决方案吗?谁知道呢,就算有解决方案,那成本呢?

  • 时区

TIMESTAMP 存储时区信息,在读取和存储时,它会自动转换为当前时区的时间。而 DATETIME 不存储时区信息。所以,如果我们的应用程序要考虑时区的话,可以考虑 TIMESTAMP。但是不一定要是它,因为我们可以通过查询 DATETIME 后再在程序里面转换。

  • 性能

由于 TIMESTAMP 要支持时区,所以会涉及时区转换的问题,使得 TIMESTAMP 会存在一些性能方面的问题。如果我们使用使用默认的操作系统时区,每次通过时区计算时间时,都是需要调用操作系统底层系统函数 __tz_convert(),系统函数为了保证操作系统时区的一致性,需要进行加锁操作,这就降低了效率。

所以,当大规模并发访问时,由于热点资源竞争,会产生两个问题。

1、性能不如 DATETIMEDATETIME 不存在时区转化问题。

2、性能抖动:海量并发时,存在性能抖动问题。

  • 存储空间

下图是 MySQL 日期类型所占的存储空间(官方文档传送门:https://dev.mysql.com/doc/refman/8.0/en/storage-requirements.htmlopen in new window):

v5.6.4 版本之前:

TIMESTAMP 内部是以一个正整数来存储的,占用 4 字节,最小是 0,最大值为 2^31 – 1,转换为 UTC 时间就是2038-01-19 03:14:07,所以 TIMESTAMP 最大只能支持到 2038-01-19 03:14:07

DATETIME 内部占用 8 个字节,以两个 4 个字节整数组合而成。假设有一个YYYY-MM-DD hh:mm:ss格式的日期,前 4 个字节表示日期部分,等于YYYY*10000 + MM*100 + DD*,*后四个字节表示时间部分,等于 hh*10000 + mm*100 + ss

但是,从 v5.6.4 版本开始,DATETIME 的存储发生了本质变化,内部仅占用 5 个字节,如下:

 ---------------------------
 1 bit  sign           (1 = non-negative, 0= negative)
17 bits year*13+month  (year 0-9999, month 0-12)
 5 bits day            (0-31)
 5 bits hour           (0-23)
 6 bits minute         (0-59)
 6 bits second         (0-59)
 ---------------------------
 40 bits = 5 bytes
  • 建议

日期推荐使用 DATETIME,无论是从范围、性能方面考虑,DATETIME 都是优于 TIMESTAMP,可能有小伙伴反对说,TIMESTAMP 底层存储空间比 DATETIME 小,哥们都啥年代了,还在乎这 1 个字节?就算使用版本低于 v5.6.4,也就只多几个字节,现在业务开发,哪个表的列不是10+ ,而且大部分还都是 varchar 类型。

扩展

使用字符串存储时间

很多初学者很喜欢使用 VARCHAR 来存储日期,例如 "2024-01-17 14:14:26"这样的字符串。这样做的优点是比较简单,上手快。但是,大明哥极力反对,因为它有如下几个很大的缺陷:

  1. 缺乏数据校验VARCHAR 是不会对内容进行日期验证的,你可以存入任意类型的字符串而不会出错。
  2. 占用空间大VARCHAR 内部存储空间占用更大。
  3. 性能:由于日期类型数据大小固定,因此在进行查询、排序和索引操作时,它们通常比 VARCHAR 类型更快。尤其是涉及到日期比较的时候。
  4. 日期函数:MySQL 内置的日期函数,怕是比较难使用了,因为需要进行额外的转换,而且还有可能会报错。

使用 INT/BIGINT

这里,大明哥依然不推荐使用 INTBIGINT 来存储日期格式。这种方式的优点是跨数据库兼容性比较好,毕竟只是存放的数值,同时涉及到对比和排序时可能效率会高一点,但是它的缺点也比较明显:

  1. 可读性:可读性非常差,一般都无法直观看到一个具体的时间。
  2. 存储INT 4 个字节,BIGINT 8 个字节,TIMESTAMP 4 个字节,DATETIME 5 个字节。
  3. 日期函数:与 VARCHAR 一样,如果要使用 MySQL 的内置日期函数需要进行额外的转换操作。
阅读全文