vlambda博客
学习文章列表

MySQL 保存日期,用哪种数据类型合适?datetime?timestamp?还是 int?

日期算是我们在日常开发中经常用到的数据类型,一般来说一张表都有 createTime 和 updateTime 字段,MySQL 中针对日期也提供了很多种不同的数据类型,如:

  • datetime
  • timestamp
  • int

等等。甚至也有人直接将日期存为字符串的。

那么到底该用哪种类型来保存日期呢?

1. 字符串

在这些类型中,首先应该排除掉的就是字符串了,很多新手小伙伴爱用字符串存储日期,但实际上这并不是一个很好的方案。

使用字符串存储日期,第一个显而易见的问题就是无法使用 MySQL 中提供的日期函数,这会为很多查询带来不便。

例如用户表中有一个字段 birthday,表示用户的生日,现在想要查询 2001 出生的所有用户,如果 birthday 是 日期类型,就可以使用 YEAR 函数,但是如果 birthday 是字符串类型,这个问题就不太好处理了。

使用字符串存储日期的第二个问题就是占用空间较大,例如存储如下时间:

2021-01-01 00:00:00

  • 如果使用字符串,需要 19 个字节。
  • 如果使用 datetime 需要 8 个字节。
  • 如果使用 timestamp 需要 4 个字节。

所以首先排除掉字符串。

2. DATETIME VS TIMESTAMEP

2.1 占用空间

DATETIME 在数据库中存储的形式为:YYYY-MM-DD hh:mm:ss,至于占用的字节数,则看情况,我们来看一段来自 MySQL 官网的内容:

可以看到,MySQL5.6.4 是一个分水岭:

  • 在 MySQL5.6.4 之前,DATETIME 固定占用 8 个字节。
  • 从 MySQL5.6.4 开始,DATETIME 类型开始支持毫秒,DATETIME(N) 中的 N 表示毫秒的精度,例如,DATETIME(6) 表示可以存储 6 位的毫秒值,那么此时,DATETIME 占用的字节数,就跟后面的毫秒数有关了,如果 DATETIME 没有详细到毫秒,那么占用 5 个字节,如果详细到毫秒了,那就看情况,根据毫秒的精度,占用不同的空间,毫秒精度小于等于 2 时,总共占用 6 个字节;毫秒精度小于等于 4 时,总共占用 7 个字节;毫秒精度小于等于 6 时,总共占用 8 个字节。

同样,由上图我们也可以看出,在 MySQL5.6.4 之前,TIMESTAMEP 固定占用 4 个字节,从 MySQL5.6.4 开始,依据毫秒的精度,TIMESTAMEP 占用的字节数介于 4 到 7 之间。

所以无论是 TIMESTAMEP,还是 DATETIME,都是比字符串节省空间的。

2.2 存储范围

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 之间。

很明显 DATETIME 的存储范围要更大一些。

2.3 底层存储

TIMESTAMP 类型最大的优势在于自带时区属性,因为它本质上是从毫秒转化而来。如果你的业务需要对应不同的国家时区,那么类型 TIMESTAMP 是一种不错的选择,TIMESTAMP 类型字段的值会随着服务器时区的变化而变化,自动换算成相应的时间,说简单点就是在不同时区,查询到同一个条记录此字段的值会不一样。

举个 TIMESTAMP 的使用场景例子:

新闻类的业务,通常用户想知道这篇新闻发布时对应的自己国家时间,那么 TIMESTAMP 是一种不错的选择。

TIMESTAMP 会随着时区的变化而自动调整,而 DATETIME 不会。

我举个例子:假设我数据库目前的时区是 Asia/Shanghai

MySQL 保存日期,用哪种数据类型合适?datetime?timestamp?还是 int?

现在有一个 user 表,数据如下:

其中,createTime 字段是 DATETIME,而 updateTime 是 TIMESTAMP,现在我修改一下数据库时区,我们再来查看:

小伙伴们可以看到,我把时区设置为东京,东京比我们快一个小时,此时 updateTime 自动变了,而 DATETIME 不变。

时区的问题一定要谨慎,不过时区问题也并非一定要在数据库中解决,也可以在前端或者服务端用代码处理下。

2.4 性能比较

从毫秒数转换到 TIMESTAMP 并不费事,但是当要进行时区转换的时候,需要调用操作系统底层系统函数,而这个函数需要额外的加锁操作,以确保这时操作系统时区没有修改,一加锁,效率就低了。

对于这个问题,只存在于 TIMESTAMP 中,因为 DATETIME 不存在时区转化问题。

对于 TIMESTAMP,建议使用显式的时区,而不是操作系统时区。

3. int

字符串费空间,TIMESTAMP 和 DATETIME 如果没有吃透则总感觉乱乱的,所以也有人存时间戳,存一个 int 类型的数值,用一个时间戳来表示时间。

用 int 保存时间的话,当我们需要进行日期排序以及按照日期范围查询的时候,就变成了普通的数字比较了,那么效率肯定是杠杠滴。

不过 int 有一个致命的问题就是可读性太差,所以用不用 int 就要仔细斟酌看情况了。

好啦,小伙伴们留言说说你日常开发日期用的哪种类型呢?出于什么样的考虑用了该类型?