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

2022-04-15 11:01 浏览：124

日期算是我们在日常开发中经常用到的数据类型，一般来说一张表都有 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:00 到 9999-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：

现在有一个 user 表，数据如下：

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

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

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