选择合适的 MySQL 日期时间类型来存储你的时间

构建数据库写程序避免不了使用日期和时间,对于数据库来说,有多种日期时间字段可供选择,如 timestamp 和 datetime 以及使用 int 来存储 unix timestamp。

不仅新手,包括一些有经验的程序员还是比较迷茫,究竟我该用哪种类型来存储日期时间呢?

那我们就一步一步来分析他们的特点,这样我们根据自己的需求选择合适的字段类型来存储 (优点和缺点是比较出来的 :smile: , 跟父母从小喜欢拿邻居小孩子跟自己比一样的)

datetime 和 timestamp

  1. datetime 更像日历上面的时间和你手表的时间的结合,就是指具体某个时间。
  2. timestamp 更适合来记录时间,比如我在东八区时间现在是 2016-08-02 10:35:52, 你在日本(东九区此时时间为 2016-08-02 11:35:52),我和你在聊天,数据库记录了时间,取出来之后,对于我来说时间是 2016-08-02 10:35:52,对于日本的你来说就是 2016-08-02 11:35:52。所以就不用考虑时区的计算了。
  3. 时间范围是 timestamp 硬伤(1970-2038),当然 datetime (1000-9999)也记录不了刘备什么时候出生(161年)。

timestamp 和 UNIX timestamp

  1. 显示直观,出问题了便于排错,比好多很长的 int 数字好看多了
  2. int 是从1970年开始累加的,但是 int 支持的范围是 1901-12-13 到 2038-01-19 03:14:07,如果需要更大的范围需要设置为 bigInt。但是这个时间不包含毫秒,如果需要毫秒,还需要定义为浮点数。datetime 和 timestamp 原生自带6位的微秒。
  3. timestamp 是自带时区转换的,同上面的第2项。
  4. 用户前端输入的时间一般都是日期类型,如果存储 int 还需要存前取后处理

总结:

  1. timestamp 记录经常变化的更新/创建/发布/日志时间/购买时间/登录时间/注册时间等,并且是近来的时间,够用,时区自动处理,比如说做海外购或者业务可能拓展到海外
  2. datetime 记录固定时间如服务器执行计划任务时间/健身锻炼计划时间等,在任何时区都是需要一个固定的时间要做某个事情。超出 timestamp 的时间,如果需要时区必须记得时区处理
  3. UNIX timestamps 使用起来并不是很方便,至于说比较取范围什么的,timestamp 和 datetime 都能干。
  4. 如果你不考虑时区,或者有自己一套的时区方案,随意了,喜欢哪个上哪个了
  5. laravel 是国际化设计的框架,为了程序员方便、符合数据库设计标准,所以 created_at updated_at 使用了 timestamp 是无可厚非的。
  6. 有没有一个时间类型即解决了范围、时区的问题?这是不可能的,不是还有 tinyInt BigInt 吗?取自己所需,并且 MySQL 是允许数据库字段变更的。
  7. 生日可以使用多个字段来存储,比如 year/month/day,这样就可以很方便的找到某天过生日的用户(User::where(['month' => 8, 'day' => 12])->get())

构建项目的时候需要认真思考一下,自己的业务场景究竟用哪种更适合。选哪个?需求来定。

欢迎大家补充和指正。

本帖已被设为精华帖!
本帖由 Summer 于 7年前 加精
《L04 微信小程序从零到发布》
从小程序个人账户申请开始,带你一步步进行开发一个微信小程序,直到提交微信控制台上线发布。
《L03 构架 API 服务器》
你将学到如 RESTFul 设计风格、PostMan 的使用、OAuth 流程,JWT 概念及使用 和 API 开发相关的进阶知识。
讨论数量: 29

:thumbsup:博主好文章,收藏了!!!

每次建表总会小纠结,到底是用timestamp、datetime还是int,或者其他格式。

之前在一个跨境的项目中碰到过时间差的问题,因为相差了几个小时导致本地数据库和线上数据库中时间的差异。最后因为时间不准确引起了一些非常麻烦的其他并发问题。当然这些问题最后也已都经解决了。

现在看了博主这篇文章,方才扫除了之前因为时间差的郁闷心情啊!恨不能早点看到!:grin:

7年前 评论
fangmuke 2年前
Summer

总结非常棒的文章 :100: 来自那天讨论的扩展知识 :smile_cat:

7年前 评论

@Summer 查了很多的文档,包括MySQL 官方文档(官方只是简单介绍每个的存储值范围和格式,并没有给什么具体的建议)和 stackoverflow 的各种答案(不少启发),还有一些是自己的经验。 :smile:

7年前 评论

非常实用,社区需要这样优质的内容 fav了 :+1:

7年前 评论

@zhuzhichao NB 的总结,非常感谢!

7年前 评论
monkey

:+1: :+1: :+1:

7年前 评论

用string存时间戳的怎么破

7年前 评论

@Payne 具体一点。是用 varchar 存储时间了吗?

7年前 评论

@zhuzhichao 对,之前有不少项目是的,这样是不是很不好?

7年前 评论

@Payne 是很不规范的,写个迁移文件,尝试把类型转换一下,只要时间格式规范,应该没问题的。

7年前 评论

好清晰,感谢分享

7年前 评论
Destiny

:punch:

7年前 评论

这个必须点赞!!纠结很久的问题,收藏了,怕忘记

7年前 评论

@zhuzhichao 有个观点是服务器最好不要使用 timestamp 字段,这样在高并发场景下回出现 数据一致性 的问题,建议考虑一下

7年前 评论

@czl1252409767 请举个例子分享一下。 :smile:

7年前 评论

@zhuzhichao timestamp字段一般都会使用 mysql 的 default current_timestamp / on update current_timestamp 来自动更新时间,而需要保持数据同步到不同源的时候,这个时间应该由业务方生成,然后多点同步(比如 一部分数据落地到 mysql,一部分落地到 redis,2部分要保持一致)

7年前 评论
xcaptain

@czl1252409767 不至于吧,要维持多个数据源一致可以通过生成一致的Id来解决,分布式系统肯定不会依赖时间来排序,到了那个程度每台机器的时钟,网络延迟都会比timestamp的一点点误差产生更大的影响。

7年前 评论

@xcaptain 如果mysql设置2个这样的字段 a timestamp not null default '0000-00-00 00:00:00 ,会报错,是 mysql 版本的原因么?

7年前 评论

时区那的描述有些误导~

timestamp的时区自动转换是mysql根据它自己的time_zone设置自动完成的,对于用户来说是透明的。

7年前 评论

@czl1252409767 默认只能开启一个字段的default current_timestamp / on update current_timestamp

7年前 评论

@czl1252409767 我本地测试了一下 Laravel Framework version 5.2.45 mysql: stable 5.7.16,$table->timestamp('locked_at'); 数据库是默认 0000-00-00 00:00:00。不过我也发现了前段时间同样的命令创建的数据库字段添加了 default CURRENT_TIMESTAMP 和 on update CURRENT_TIMESTAMP,给我们带来了不少困扰。

有一点我要说的,一般不使用数据库生成的时间,都是使用 Carbon::now() ,到缓存还是数据库值哪里都一样的。

7年前 评论

@eddy8 我原文主要指的是 mysql 数据库方面的时区,只有不带时区的我提到了需要程序去处理。 如果有歧义,麻烦指出具体的内容,我修正一下。:smile:

7年前 评论

@zhuzhichao 意思其实还是使用 timestamp 这个数据类型,但是时间都由程序这边控制,而不是交给mysql?

7年前 评论

@czl1252409767 是的,时间都是程序赋值到模型然后保存的。

7年前 评论

你好,我有个疑问:为什么用int或bigint不是更好的选择呢?
点击参考下这个文章

5年前 评论

@Summer 你好,我有个疑问:为什么用int或bigint不是更好的选择呢?
点击参考下这个文章

5年前 评论

生日分开存储很棒哦~

4年前 评论

讨论应以学习和精进为目的。请勿发布不友善或者负能量的内容,与人为善,比聪明更重要!