一起因MySQL时间戳精度引发的血案分析

 更新时间:2019-09-09 13:01:11   作者:佚名   我要评论(0)

写在前面
最近工作中遇到两例mysql时间戳相关的问题,一个是mysql-connector-java和msyql的精度不一致导致数据查不到;另一例是应用服务器时区错误导致数据查

写在前面

最近工作中遇到两例mysql时间戳相关的问题,一个是mysql-connector-java和msyql的精度不一致导致数据查不到;另一例是应用服务器时区错误导致数据查询不到。通过这篇文章,希望能够解答关于mysql中时间戳的几个问题:

  • mysql中的DATETIME精度为什么只支持到秒?
  • mysql中的DATETIME类型跟时区有关吗?
  • mysql设计表的时候,表示时间的字段改如何选择?

案例分析 DATETIME的精度问题

前段时间,将负责的应用的mysql-connector-java的版本从5.1.16升级到5.1.30,在做功能回归的时候发现,使用了类似上面的SQL的用例的运行时数据会有遗漏,导致功能有问题。

考虑到我负责的应用中,有个功能需要用到类似下面这种SQL,即使用时间戳作为查询的条件,查询在某个时间戳之后的所有数据。

经过排查发现:mysql-connector-java在5.1.23之前会将秒后面的精度丢弃再传给MySQL服务端,正好我们使用的mysql版本中DATETIME的精度是秒;在我将mysql-connector-java升级到5.1.30后,从java应用通过mysql-connector-java将时间戳传到MySQL服务端的时候,就不会将毫秒数丢弃了,从mysql-connector-java的角度看是修复了一个BUG,但是对于我的应用来说却是触发了一个BUG。

如果你面对这个问题,你会怎么修复呢?

我们当时想了三种方案:

  • 将mybatis的Mapper接口中的时间戳参数的类型,从java.util.Date改成java.sql.Date;
  • 在传入Mapper接口之前,将传入的时间戳按秒取正,代码如下

  • 在查询之前,将传入的时间戳减1秒;

经过验证,方案1会,java.util.Date转过去的java.sql.Date对象会将日期之后的精度全部丢掉,从而导致查询出更多不必要的数据;方案3是可以的,就是可能会查出多一两条数据;方案2也是可以的,相当于从代码上对mysql-connector-java的特性做了补偿。最终我选择的是方案2。

案例复现

利用homebrew安装MySQL,版本是8.0.15,装好后建一个表,用来存放用户信息,SQL如下:

使用spirngboot + mybatis作为开发框架,定义一个用户实体,代码如下所示:

定义该实体对应的Mapper,代码如下:

设置连接mysql相关的配置,代码如下:

编写测试代码,先插入一条数据,然后用时间戳作为查询条件去查询,代码如下:

运行单测,如我们的设想,确实是没有查询出数据来,结果如下:

然后修改代码,利用上面的代码将查询的时间戳按秒取正,代码如下:

再次运行单测,如我们的设想,这次可以查询出数据来了。

不过,这里有个小插曲,我在最开始设计表的时候,使用的SQL语句是下面这样的,

聪明如你一定发现了,这里的datetime已经支持小数点后更小的时间精度了,最多支持6位即最多可以支持到微妙级别。这个特性是什么时候引入的呢,我去查阅了[MySQL的官方文档][9],发现这个特性是在mysql 5.6.4之后开始支持的。

知识点总结

经过了前面的实际案例分析和案例复现,想必读者已经对mysql中DATETIME这个类型有了一定的认识,接下来跟我一起看下,我们从这个案例中可以总结出哪些经验。

  • mysql-connector-java的版本和mysql的版本需要配套使用,例如5.6.4之前的版本,就最好不要使用mysql-connector-java的5.1.23之后的版本,否则就可能会遇到我们这次遇到的问题。
  • MySQL中用来表示时间的字段类型有:DATE、DATETIME、TIMESTAMP,它们之间有相同点,各自也有自己的特性,我总结了一个表格,如下所示:

DATETIME

  • 类型在MySQL中是以“YYYYMMDDHHMMSS”格式的整数存放的,与时区无关,使用8个字节的空间;
  • TIMESTAMP类型可以保存的时间范围要小很多,显示的值依赖时区,MySQL的服务器、操作系统以及客户端连接都有时区的设置。
  • 一般情况下推荐使用DATETIME作为时间戳字段,不推荐使用bigint类型来存储时间。
  • 在开发中,应该尽量避免使用时间戳作为查询条件,如果必须要用,则需要充分考虑MySQL的精度和查询参数的精度等问题。

参考资料

  • https://dev.mysql.com/doc/refman/8.0/en/datetime.html
  • 《高性能MySQL》

总结

以上就是这篇文章的全部内容了,希望本文的内容对大家的学习或者工作具有一定的参考学习价值,谢谢大家对脚本之家的支持。

您可能感兴趣的文章:

  • mysql之TIMESTAMP(时间戳)用法详解
  • FROM_UNIXTIME 格式化MYSQL时间戳函数
  • MySQL中日期和时间戳互相转换的函数和方法
  • 详解MySQL日期 字符串 时间戳互转
  • mysql时间戳转成常用可读时间格式的两种方法
  • mysql 获取今天、昨天0点时间戳的实例
  • MySQL表中添加时间戳的几种方法
  • MySQL版本低了不支持两个时间戳类型的值解决方法

相关文章

  • 一起因MySQL时间戳精度引发的血案分析

    一起因MySQL时间戳精度引发的血案分析

    写在前面 最近工作中遇到两例mysql时间戳相关的问题,一个是mysql-connector-java和msyql的精度不一致导致数据查不到;另一例是应用服务器时区错误导致数据查
    2019-09-09
  • php常用经典函数集锦【数组、字符串、栈、队列、排序等】

    php常用经典函数集锦【数组、字符串、栈、队列、排序等】

    本文实例总结了php常用经典函数。分享给大家供大家参考,具体如下: 数组函数 数组操作 range(start,end) 创建一个数组,值从start开始,一直到end结束
    2019-09-09
  • Spring事务管理方法步骤解析

    Spring事务管理方法步骤解析

    1、Spring的事务管理主要包括3个接口 TransactionDefinition:封装事务的隔离级别,超时时间,是否为只读事务和事务的传播规则等事务属性,可通过XML配置具体信
    2019-09-09
  • Nginx实现负载均衡的方法总结

    Nginx实现负载均衡的方法总结

    要理解负载均衡,必须先搞清楚正向代理和反向代理。 注: 正向代理,代理的是用户。 反向代理,代理的是服务器 什么是负载均衡 当一台服务器的单位时
    2019-09-09
  • PyCharm2018 安装及破解方法实现步骤

    PyCharm2018 安装及破解方法实现步骤

    PyCharm就是Python语言开发中一个很受欢迎的IDE,界面类似于visual studio,android studio,集成的功能也很多。 1>. 安装 首先要下载Pycharm这个软件,可以在
    2019-09-09
  • Spring 面向切面编程AOP实现详解

    Spring 面向切面编程AOP实现详解

    简介 1、什么叫做面向切面编程? 概念:把一个个的横切关注点(某种业务的实现代码)放到某个模块中去,称之为切面。每个切面影响业务的一种功能,切面的
    2019-09-09
  • 微信小程序HTTP请求从0到1封装

    微信小程序HTTP请求从0到1封装

    前言 作为一个前端开发者,从最开始的js、jQuery一把梭,后来的vue、react、angular等MVVM、MVC框架,我们在开发工程中都离不开HTTP库的使用。 HTTP库
    2019-09-09
  • Yii框架日志操作图文与实例详解

    Yii框架日志操作图文与实例详解

    本文实例讲述了Yii框架日志操作。分享给大家供大家参考,具体如下: 将日志记录到文本中. Yii::log("test"); //写入测试日志 //找到配置文件
    2019-09-09
  • 在pycharm中配置Anaconda以及pip源配置详解

    在pycharm中配置Anaconda以及pip源配置详解

    在学习推荐系统、机器学习、数据挖掘时,python是非常强大的工具,也有很多很强大的模块,但是模块的安装却是一件令人头疼的事情。 现在有个工具——anaconda
    2019-09-09
  • SpringMVC KindEditor在线编辑器之文件上传代码实例

    SpringMVC KindEditor在线编辑器之文件上传代码实例

    最近几个项目都要用到在线编辑器,由于之前做在线编辑器都只在php上,对于用java尤其是springmvc框架时,似乎并不如PHP那么简单,搜集了很多博文和资料,全部
    2019-09-09

最新评论