是使用 DATETIME
还是 TIMESTAMP
就需要知道他们的区别,两者之间的区别如下:
- 范围
DATETIME
可以表示的时间范围是 1000-01-01 00:00:00
到 9999-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、性能不如 DATETIME
:DATETIME
不存在时区转化问题。
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"这样的字符串。这样做的优点是比较简单,上手快。但是,大明哥极力反对,因为它有如下几个很大的缺陷:
- 缺乏数据校验:
VARCHAR
是不会对内容进行日期验证的,你可以存入任意类型的字符串而不会出错。 - 占用空间大:
VARCHAR
内部存储空间占用更大。 - 性能:由于日期类型数据大小固定,因此在进行查询、排序和索引操作时,它们通常比
VARCHAR
类型更快。尤其是涉及到日期比较的时候。 - 日期函数:MySQL 内置的日期函数,怕是比较难使用了,因为需要进行额外的转换,而且还有可能会报错。
使用 INT/BIGINT
这里,大明哥依然不推荐使用 INT
或 BIGINT
来存储日期格式。这种方式的优点是跨数据库兼容性比较好,毕竟只是存放的数值,同时涉及到对比和排序时可能效率会高一点,但是它的缺点也比较明显:
- 可读性:可读性非常差,一般都无法直观看到一个具体的时间。
- 存储:
INT
4 个字节,BIGINT
8 个字节,TIMESTAMP
4 个字节,DATETIME
5 个字节。 - 日期函数:与
VARCHAR
一样,如果要使用 MySQL 的内置日期函数需要进行额外的转换操作。