MySQL 时间字段用 int 、 datetime 还是 timestamp？

社会最底层

好久没写博客了，当初停止写博客，是想着先把技术再往上提升一点，再自己搞一个高大上的网站，最后输出全网独一无二的文章。三年过去了，技术上是比以前强了很多，只是回过头来看，什么也没有留下。当初的想法，现在看来确实不太现实。
现在我意识到，应该从现在立刻做起，不要想着一下子就做到最好，而应该是越做越好。以后每周都会不定时输出一篇博文，质量不敢说最好，但一定是让人有所收获的文章。另外准备做一个专栏，绝对是干货，这里先保密，尽情期待。
刚要提笔，我就开始尴尬了。
“写啥好呢？”，“卧槽，不会写了”。
找到以前准备的“待写文章”列表，就 MySQL 这个容易写点，就先写这个了。
也不知道是不是巧合，亦或是自己从来就没改变。翻阅以前写过的文章，发现类似的标题自己以前写过，感觉还不错。

现在，经历了职场的风风雨雨，我有了新的感悟，相信大家看了也会有新的收获。

MySQL 存储日期的数据类型有 DATE、TIME、YEAR、DATETIME 和 TIMESTAMP。
我们这里主要讨论的是 YYYY-MM-DD HH:MM:SS 格式的时间，因为它用得最多。对于其它格式所要采用的数据类型的讨论，也可以差不多套用的，这里就不对其进行说明。

存储 YYYY-MM-DD HH:MM:SS 格式的数据类型有 DATETIME 和 TIMESTAMP，而 INT 类型严格来说只是存储数字，和时间没什么关系，只是我们人为地将其视为时间戳而已，因此在实际的操作中需要使用一些方法将其转化为真正的日期格式。还有 BIGINT 类型，这个和 INT 类型差不多，只是占用空间更大，可表示的时间范围也更广。不过我在实际开发中倒是没遇到过，可能是我资历尚浅吧。这里，由于空间的限制，就不讨论 BIGINT 了。

网上关于该问题的讨论其实有很多，我加上自己的一些经验把它们总结成以下表格。


可以看到，不同数据类型都是有一些各自的特点的。下面对每个特点具体分析一下。

存储空间

在存储空间上，DATETIME 比其它人类型多了一倍的存储空间，但其实也就多了 4 个字节。现如今的系统越来越复杂，许多表的字段动辄十几行甚至几十行，多这 4 个字节其实没有什么影响，完全可以忽略不计，除非你的表字段全是 DATETIME 类型，才需要衡量一下。

显示格式

INT 类型需要通过 MySQL 提供的函数或者程序本身额外进行处理才能显示为我们想要的日期格式，这个其实也不需要多少工作量。当然可能在直接使用工具连接数据库查看时，INT 数据阅读起来会很困难。实际在工作中，生产环境的数据库一般都是禁止使用图形化工具直接连接的。我们要么编写脚本，要么通过后台查看，这个完全可以让在程序处理时将数据转化为我们想要的格式，所以 INT 数据无法直接阅读这个缺点在现实中影响不大。

操作效率

INT 在排序和查询时的效率是最高的，但也仅仅是高一丁点。当存在大量的查询和排序情况时，加个索引可能就完全解决了，再不然就分库分表，甚至是加缓存。而如果仅仅通过选择 INT ，只因为其本身的处理性能比别的高，就想获得巨大性能提升是不现实的。

跨时区

DATETIME 虽然和时区无关，但可以通过将其转化为时间戳，再将时间戳转化为其它任何时区时间。方法总比困难多。

特殊功能

TIMESTAMP 可以自动管理时间的更新，确实很方便。
现在的许多框架，都有自动管理时间的功能，也不一定非得用 TIMESTAMP。

时间范围

这个我觉得是区别最大的，也是最需要我们去做抉择的地方。
在这四种数据类型中，对于时间范围，TIMESTAMP 和 INT（无符号）最多只支持到 2038 年。换句话说，到了 2038 年，我们可能会遇到像 千年虫 那样的问题。面对这个问题，有些人可能想都不用想就直接选择 DATETIME 或者 INT（有符号）了。如果你选择 INT（有符号），问题其实并没有解决。MySQL 所提供的将时间戳转化为日期格式的函数 FROM_UNIXTIME 仅支持32的数据，超出将溢出，返回的结果将是 NULL。
那我直接用一些编程语言自带的时间处理函数转不就不会有这个问题了？
这个当然是没问题的，只是这样你可能就需要在结果输出前遍历转化一下结果，并且也不是所有的语言都能完全正确的处理这个问题，这个得要实际测试一下才知道，但我相信很多人是没有做这个测试的，而且你在设置表的时候，可能会忘记勾选“无符号”的选项。
这样看来，只有 DATETIME 最保险了。是的，这个也是我所推荐的。

综上，你可能觉得 DATETIME 是唯一的选择。其实不是，在现实生活中，我们面对的问题实在是太多了，这个问题的重要性在所有问题中的占比约等于零。不管是选择哪一种，短期来看，区别不大。至于2038年可能要面临的 千年虫 问题，也并非我们想的那么恐怖。你想想看，解决一个 千年虫 可能要花费几十亿，但如果现在解决，花费的可能远不止这几十亿（思考成本，测试成本等）。而等到2038年，可能很多公司都倒闭了，那岂不是白忙活了，所以完全不用去纠结这个问题。
对于一家公司，最重要的是如何在激烈的竞争中生存下去，MySQL 时间字段用 int 、 datetime 还是 timestamp？这个问题对于竞争毫无帮助。有些人可能会说:“细节决定成败”。我以前也很信奉这句话，并且事事以此为标准。结果就是做事比别人慢，把简单的问题想得过于复杂。
这个世界其实很复杂，许多道理并非一句话就能概括，凡事还是要根据不同场景对症下药。细节用在刀刃上，将事半功倍，要是用在可有可无的地方，那不过是瞎忙活，白费力气。

当然最后还是要做出选择的，咱也不能直接丢骰子随便乱选吧，那就是真的是不负责任了。
下面我根据自己的一些经验给出一些自己的观点。
1、我们到一个公司上班，都不是一开始就从零构建一个项目，一般都是先从维护旧项目开始。在对旧项目添加或者修改功能时，我们可以直接按照该公司的习惯来选择存储时间字段的数据类型。前人用 DATETIME 我们就用 DATETIME，用 INT 我们就用 INT，除非公司有特别指定某种类型。而新项目我也是建议按照之前的设计，减少交接成本。
2、对于自己的个人作品，比如说开源项目，我是建议用 DATETIME，因为我们可以直接连接数据库，格式化的时间看起来多舒服呀。另外，咱们自己维护的项目，自然是想着永远存在的。你想想，十几年后，等孩子长大了，某一天来了兴致要给孩子炫耀一下老爹当年的项目，一打开网站竟然报错，原因是数据溢出，那多尴尬呀！然后你只能说稍等改一下。都一把年纪了，代码也不知道改不改得动。

许多时候，我都会在一些细节问题上纠结。
编程是一门艺术 真是说得太对了。
本文说这么多，更多的是想表达解决问题的思考方法，还有就是在细节的思考上，要先想想有没有那个必要，当然如果只是个人兴趣的话其实没什么所谓。

总算是写完了，太久没写了都生疏了，花了差不多半天时间。
写作不易，如果您喜欢或者觉得有收获，请点个赞，不胜感激。
另外如果您觉得有什么不对的地方，也欢迎评论留言。

汤和后裔

根据官方文档：

The DATETIME type is used for values that contain both date and time parts. MySQL retrieves and displays DATETIME values in 'YYYY-MM-DD hh:mm:ss' format. The supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'.

The TIMESTAMP data type is used for values that contain both date and time parts. TIMESTAMP has a range of '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC.

https://dev.mysql.com/doc/refman/8.0/en/datetime.html

所以博主的表貌似有点问题。。

于航

感谢指正，图片已修改

枫落吴江

千年虫需要几十亿 来解决,,,,,,学到了

国梅

管那么多呢，到2038年mysql已经帮你解决timestamp千年虫问题了

毅然

想请教一下，如果用int存时间戳，分区的时候 怎么写less than的值呀

倪骏

不考虑性能的话，可以扫描所有分区。
考虑性能的话，可以再冗余一张表，专门针对优化，比如只存储关联 ID 和需要比较的字段，获取结果后再去对应的分区表拿详细数据，可以的话分区表的数据也加个缓存。
或者用其它工具，比如 ElasticSearch，像后台这种数据统计类的业务，MySQL 能力有限，建不了太多索引。

麦田里的老鼠

无符号的int 存储的时间应该比有符号的存储的时间长才对吧

南山饮水

看到作者对于细节思考的取舍心得，很有共鸣，想起自己此前也是很纠结细节，如果能及时跳出来看待问题，可能现在思考的就不是问题了。
对于某个细节是否有必要，我觉得主要还是看个人性格，有时候我想到有个细节是有问题的，但并不是常见的问题，甚至有点极端，用户不太可能遇到，但又想，如果遇到了，我该怎么引导他去解决而不是让他自己想办法，因为这个问题比较极端，我其实也可以不管它，让它存在，但其实心里会惦记，会持续几天思考要不要出手解决，最后大概率还是解决了，然后就比原计划多花费了一两天甚至更多，还有可能造成了别的 bug，有时候也是很打击自信的。
但正是由于太抠细节，能让我想到一些别人想不到的主意，预测用户的行为并及时给予引导，有可能就一两个细节就把用户留住了。当然，是哪个细节打动了用户，用户或许不会告诉你。

孤鸿俊

我现在的觉悟是，有类似性格的人，不适合打工，因为这种人对于领导来讲不好掌控，就不会看重你，要尽早想办法自己单干。