MySQL中存储时间的最佳实践指南

 更新时间:2021年7月1日 15:00  
这篇文章主要给大家介绍了关于MySQL中存储时间的最佳实践,文中详细介绍了哪种存储时间的方式更好,对大家学习或者使用mysql具有一定的参考学习价值,需要的朋友可以参考下

前言

平时开发中经常需要记录时间,比如用于记录某条记录的创建时间以及修改时间。在数据库中存储时间的方式有很多种,比如 MySQL 本身就提供了日期类型,比如 DATETIME,TIMESTAMEP 等,我们也可以直接存储时间戳为 INT 类型,也有人直接将时间存储为字符串类型。

那么到底哪种存储时间的方式更好呢?

不要使用字符串存储时间类型

这是初学者很容易犯的错误,容易直接将字段设置为 VARCHAR 类型,存储"2021-01-01 00:00:00"这样的字符串。当然这样做的优点是比较简单,上手快。

但是极力不推荐这样做,因为这样做有两个比较大的问题:

  • 字符串占用的空间大
  • 这样存储的字段比较效率太低,只能逐个字符比较,无法使用 MySQL 提供的日期API

MySQL 中的日期类型

MySQL 数据库中常见的日期类型有 YEAR、DATE、TIME、DATETIME、TIMESTAMEP。因为一般都需要将日期精确到秒,其中比较合适的有DATETIME,TIMESTAMEP。

DATETIME

DATETIME 在数据库中存储的形式为:YYYY-MM-DD HH:MM:SS,固定占用 8 个字节。

从 MySQL 5.6 版本开始,DATETIME 类型支持毫秒,DATETIME(N) 中的 N 表示毫秒的精度。例如,DATETIME(6) 表示可以存储 6 位的毫秒值。

TIMESTAMEP

TIMESTAMP 实际存储的内容为‘1970-01-01 00:00:00'到现在的毫秒数。在 MySQL 中,由于类型 TIMESTAMP 占用 4 个字节,因此其存储的时间上限只能到‘2038-01-19 03:14:07'。

从 MySQL 5.6 版本开始,类型 TIMESTAMP 也能支持毫秒。与 DATETIME 不同的是,若带有毫秒时,类型 TIMESTAMP 占用 7 个字节,而 DATETIME 无论是否存储毫秒信息,都占用 8 个字节。

类型 TIMESTAMP 最大的优点是可以带有时区属性,因为它本质上是从毫秒转化而来。如果你的业务需要对应不同的国家时区,那么类型 TIMESTAMP 是一种不错的选择。比如新闻类的业务,通常用户想知道这篇新闻发布时对应的自己国家时间,那么 TIMESTAMP 是一种选择。Timestamp 类型字段的值会随着服务器时区的变化而变化,自动换算成相应的时间,说简单点就是在不同时区,查询到同一个条记录此字段的值会不一样。

TIMESTAMP 的性能问题

TIMESTAMP 还存在潜在的性能问题。

虽然从毫秒数转换到类型 TIMESTAMP 本身需要的 CPU 指令并不多,这并不会带来直接的性能问题。但是如果使用默认的操作系统时区,则每次通过时区计算时间时,要调用操作系统底层系统函数 __tz_convert(),而这个函数需要额外的加锁操作,以确保这时操作系统时区没有修改。所以,当大规模并发访问时,由于热点资源竞争,会产生两个问题:

  • 性能不如 DATETIME:DATETIME 不存在时区转化问题。
  • 性能抖动:海量并发时,存在性能抖动问题。

为了优化 TIMESTAMP 的使用,建议使用显式的时区,而不是操作系统时区。比如在配置文件中显示地设置时区,而不要使用系统时区:

[mysqld]

time_zone = "+08:00"

简单总结一下这两种数据类型的优缺点:

  • DATETIME 没有存储的时间上限,而TIMESTAMP存储的时间上限只能到‘2038-01-19 03:14:07'
  • DATETIME 不带时区属性,需要前端或者服务端处理,但是仅从数据库保存数据和读取数据而言,性能更好
  • TIMESTAMP 带有时区属性,但是每次需要通过时区计算时间,并发访问时会有性能问题
  • 存储 DATETIME 比 TIMESTAMEP 多占用一部分空间

数值型时间戳(INT)

很多时候,我们也会使用 int 或者 bigint 类型的数值也就是时间戳来表示时间。

这种存储方式的具有 Timestamp 类型的所具有一些优点,并且使用它的进行日期排序以及对比等操作的效率会更高,跨系统也很方便,毕竟只是存放的数值。缺点也很明显,就是数据的可读性太差了,你无法直观的看到具体时间。

如果需要查看某个时间段内的数据

select * from t where created_at > UNIX_TIMESTAMP('2021-01-01 00:00:00');

DATETIME vs TIMESTAMP vs INT,怎么选?

每种方式都有各自的优势,下面再对这三种方式做一个简单的对比:


日期类型 占用空间 日期格式 日期范围 是否存在时区问题
DATETIME 8 字节 YYYY-MM-DD HH:MM:SS 1000-01-01 00:00:00 ~9999-12-31 23:59:59
TIMESTAMP 4 字节 YYYY-MM-DD HH:MM:SS 1970-01-01 00:00:00 ~2038-01-19 03:14:07
INT 4 字节 全数字时间戳 1000-01-01 00:00:01 之后的时间

TIMESTAMP 与 INT 本质一样,但是相比而言虽然 INT 对开发友好,但是对 DBA 以及数据分析人员不友好,可读性差。所以《高性能 MySQL 》的作者推荐  TIMESTAMP 的原因就是它的数值表示时间更加直观。下面是原文:

至于时区问题,可以由前端或者服务这里做一次转化,不一定非要在数据库中解决。

总结

本文比较了几种最常使用的存储时间的方式,我最推荐的还是 DATETIME。理由如下:

  • TIMESTAMP 比数值型时间戳可读性更好
  • DATETIME 的存储上限为 9999-12-31 23:59:59,如果使用 TIMESTAMP,则 2038 年需要考虑解决方案
  • DATETIME 由于不需要时区转换,所以性能比 TIMESTAMP 好
  • 如果需要将时间存储到毫秒,TIMESTAMP 要 7 个字节,和 DATETIME 8 字节差不太多

到此这篇关于MySQL中存储时间的文章就介绍到这了,更多相关MySQL存储时间内容请搜索猪先飞以前的文章或继续浏览下面的相关文章希望大家以后多多支持猪先飞!

相关文章

  • MySQL带你秒懂索引下推

    如果你在面试中,听到MySQL5.6”、“索引优化” 之类的词语,你就要立马get到,这个问的是“索引下推”。本文就来分分享这个小知识点索引下推...2021-09-16
  • mysql IS NULL使用索引案例讲解

    这篇文章主要介绍了mysql IS NULL使用索引案例讲解,本篇文章通过简要的案例,讲解了该项技术的了解与使用,以下就是详细内容,需要的朋友可以参考下...2021-08-14
  • MySQL中常见的六个约束类型详解

    数据类型是为了节约内存,提高计算, 数据约束是为了完整性(存储关系),下面这篇文章主要给大家介绍了关于MySQL中常见的六个约束类型的相关资料,文中通过图文介绍的非常详细,需要的朋友可以参考下...2021-09-08
  • MySQL深度分页(千万级数据量如何快速分页)

    后端开发中经常需要分页展示,个时候就需要用到MySQL的LIMIT关键字。LIMIT在数据量大的时候极可能造成的一个问题就是深度分页。本文就介绍一下解决方法,感兴趣的可以了解一下...2021-07-25
  • 利用redis实现聊天记录转存功能的全过程

    社交类软件聊天功能必不可少,聊天记录存储的方式也比较多,比如文本,数据库,云等等,但是最好的选择还是redis进行存储,这篇文章主要给大家介绍了关于如何利用redis实现聊天记录转存功能的相关资料,需要的朋友可以参考下...2021-08-03
  • MySQL如何解决幻读问题

    在高并发数据库系统中,需要保证事务与事务之间的隔离性,还有事务本身的一致性。所以需要解决幻读问题,本文就来介绍一下,感兴趣的可以了解一下...2021-08-07
  • 微信小程序实现根据日期和时间排序功能

    这篇文章主要为大家详细介绍了微信小程序实现根据日期和时间排序功能,文中示例代码介绍的非常详细,具有一定的参考价值,感兴趣的小伙伴们可以参考一下...2021-08-26
  • MySQL 5.7常见数据类型

    这篇文章主要介绍了MySQL 5.7数据类型详解,本文通过实例代码给大家介绍的非常详细,对大家的学习或工作具有一定的参考借鉴价值,需要的朋友可以参考下...2021-07-15
  • Android实现日期时间选择对话框

    这篇文章主要为大家详细介绍了Android实现日期以及时间选择对话框,文中示例代码介绍的非常详细,具有一定的参考价值,感兴趣的小伙伴们可以参考一下...2021-09-12
  • MySQL去除重叠时间求时间差和的实现

    在生产中常常出现计算两个时间差的业务,比如总宕机时间、总开通会员时间等,本文就详细的来介绍一下如何计算,感兴趣的可以了解一下...2021-08-23
  • 详解MySQL自增主键的实现

    现在大部分的软件开发都离不开数据库。而mysql也是经常会用到的一个数据库。mysql数据库中有一个主键生成规则,就是自增。也是我们经常会用到的。本文就来介绍一下...2021-09-06
  • java最新版本连接mysql失败的解决过程

    这篇文章主要给大家介绍了关于java最新版本连接mysql失败的解决过程,文中通过图文以及示例代码将解决的过程介绍的非常详细,对遇到这个问题的同学具有一定的参考学习价值,需要的朋友可以参考下...2021-09-09
  • MySQL之权限以及设计数据库案例讲解

    这篇文章主要介绍了MySQL之权限以及设计数据库案例讲解,本篇文章通过简要的案例,讲解了该项技术的了解与使用,以下就是详细内容,需要的朋友可以参考下...2021-08-09
  • MySQL中的redo log和undo log日志详解

    MySQL日志系统中最重要的日志为重做日志redo log和归档日志bin log,后者为MySQL Server层的日志,前者为InnoDB存储引擎层的日志。今天通过本文给大家介绍MySQL中的redo log和undo log日志,感兴趣的朋友一起看看吧...2021-07-27
  • mysql主键id的生成方式(自增、唯一不规则)

    本文主要介绍了mysql主键id的生成方式,主要包括两种生成方式,文中通过代码示例介绍的非常详细,感兴趣的可以了解一下...2021-09-06
  • 浅谈订单重构之 MySQL 分库分表实战篇

    这篇文章主要介绍了 MySQL 分库分表方法的相关资料,需要的朋友可以参考下面文章内容,希望能帮助到你...2021-09-08
  • MySQL数据库体系架构详情

    这篇文章主要介绍了MySQL体系架构,根据大部分的面试内容所描述,需要的朋友可以参考下面文章内容...2021-09-07
  • MySQL中的回表和索引覆盖示例详解

    索引覆盖是一种避免回表查询的优化策略,具体的做法就是将要查询的数据作为索引列建立普通索,下面这篇文章主要给大家介绍了关于MySQL中回表和索引覆盖的相关资料,需要的朋友可以参考下...2021-09-08
  • mysql回表致索引失效案例讲解

    这篇文章主要介绍了mysql回表致索引失效案例讲解,本篇文章通过简要的案例,讲解了该项技术的了解与使用,以下就是详细内容,需要的朋友可以参考下...2021-08-14
  • mysql判断当前时间是否在开始与结束时间之间且开始与结束时间允许为空

    这篇文章主要介绍了mysql判断当前时间是否在开始与结束时间之间且开始与结束时间允许为空,文中通过示例代码介绍的非常详细,具有一定的参考价值,感兴趣的小伙伴们可以参考一下...2021-09-05