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

写在前面

最近工作中遇到两例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》

总结

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

赞 (0)

相关推荐

  • mysql之TIMESTAMP（时间戳）用法详解

    一.TIMESTAMP的变体 TIMESTAMP时间戳在创建的时候可以有多重不同的特性,如: 1.在创建新记录和修改现有记录的时候都对这个数据列刷新: TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP 2.在创建新记录的时候把这个字段设置为当前时间,但以后修改时,不再刷新它: TIMESTAMP DEFAULT CURRENT_TIMESTAMP 3.在创建新记录的时候把这个字段设置为0,以后修改时刷新它: TIMES

  • MySQL版本低了不支持两个时间戳类型的值解决方法

    MySQL报错:错误代码: 1293 Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP 原因是:两台服务器的mysql版本不一致. 低版本不支持在一个表里面 有2个TIMESTAMP 类型 的列. 项目中版本是: 配置环境(推荐jdk8,mysql5.7,maven3,想法) 我的是5.52 解决方法? 是采用把2台服务器都使用最新的版本这个办法 以上方法很简单,感谢

  • mysql时间戳转成常用可读时间格式的两种方法

    使用EXECL转换时间戳的公式为: 代码:=(xxxxxxxxxx+8*3600)/86400+70*365+19 使用MYSQL语句解释时间戳语法举例: 代码: SELECT FROM_UNIXTIME(1234567890, '%Y-%m-%d %H:%i:%S') 附:在mysql中,一个时间字段的存储类型是int(11),怎么转化成字符类型,比方存储为13270655222,需要转化为yyyy -mm-dd的形式 使用 FROM_UNIXTIME函数,具体如下: 代码:FROM_UNIX

  • mysql 获取今天、昨天0点时间戳的实例

    如下所示: 昨天:UNIX_TIMESTAMP(CAST(SYSDATE()AS DATE) - INTERVAL 1 DAY) 今天:UNIX_TIMESTAMP(CAST(SYSDATE()AS DATE)) 要查询今天内的记录只要创建时间大于等于今天0点的时间戳就可以(仅限没有创建时间大于今天的情况),昨天内的记录就是大于等于昨天0点小于今天0点. 以上这篇mysql 获取今天.昨天0点时间戳的实例就是小编分享给大家的全部内容了,希望能给大家一个参考,也希望大家多多支持我们.

  • MySQL中日期和时间戳互相转换的函数和方法

    MySQL中日期和时间戳互相转换的函数和方法

    ① 时间戳转换成日期 复制代码 代码如下: FROM_UNIXTIME 例如: 数据表中 invest_time 存储的是时间戳,如 1429063399 使用 FROM_UNIXTIME 可以把时间戳转换为日期: 复制代码 代码如下: select FROM_UNIXTIME(invest_time,'%Y年%m月%d') from crm_invest_apply 执行结果: ② 把日期转换为时间戳,和 FROM_UNIXTIME 正好相反 复制代码 代码如下: UNIX_TIMESTAMP

  • MySQL表中添加时间戳的几种方法

    MySQL表中添加时间戳的几种方法

    场景: 有张表的数据需要用同步工具同步至其他库,需要 update_time 时间戳字段 来做增量同步. 解决方法: alter table quant_stk_calc_d_wxcp add update_time timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP; 以上就是本文的全部内容,希望对大家的学习有所帮助,也希望大家多多支持我们.

  • 详解MySQL日期 字符串 时间戳互转

    平时比较常用的时间.字符串.时间戳之间的互相转换,虽然常用但是几乎每次使用时候都喜欢去搜索一下用法:本文将作为一个笔记,整理一下三者之间的 转换(即:date转字符串.date转时间戳.字符串转date.字符串转时间戳.时间戳转date,时间戳转字符串)用法,方便日后查看: 涉及的函数 date_format(date, format) 函数,MySQL日期格式化函数date_format() unix_timestamp() 函数 str_to_date(str, format) 函数 fro

  • FROM_UNIXTIME 格式化MYSQL时间戳函数

    函数:FROM_UNIXTIME作用:将MYSQL中以INT(11)存储的时间以"YYYY-MM-DD"格式来显示.语法:FROM_UNIXTIME(unix_timestamp,format) 返回表示 Unix 时间标记的一个字符串,根据format字符串格式化.format可以包含与DATE_FORMAT()函数列出的条目同样的修饰符. 根据format字符串格式化date值.下列修饰符可以被用在format字符串中: %M 月名字(January--December)%W 星期

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

    写在前面 最近工作中遇到两例mysql时间戳相关的问题,一个是mysql-connector-java和msyql的精度不一致导致数据查不到:另一例是应用服务器时区错误导致数据查询不到.通过这篇文章,希望能够解答关于mysql中时间戳的几个问题: mysql中的DATETIME精度为什么只支持到秒? mysql中的DATETIME类型跟时区有关吗? mysql设计表的时候,表示时间的字段改如何选择? 案例分析 DATETIME的精度问题 前段时间,将负责的应用的mysql-connector-j

  • MySQL学习记录之KEY分区引发的血案

    需求背景 业务表tb_image部分数据如下所示,其中id唯一,image_no不唯一.image_no表示每个文件的编号,每个文件在业务系统中会生成若干个文件,每个文件的唯一ID就是字段id: 业务表tb_image的一些情况如下: 根据image_no查询和根据id查询: 存量数据2kw: 日增长4w左右: 日查询量20w左右: 非ToC系统,所以并发的天花板可见: 方案选择 根据上面对业务的分析,分库分表完全没有必要.单库分表的话,由于要根据image_no和id查询,所以,一种方案是冗余

  • mysql 时间戳的用法

    前言: 时间戳字段在MySQL中经常使用到,比如需要记录一行数据创建的时间或修改的时间时,我们通常会使用时间戳即timestamp字段.本篇文章主要介绍timestamp字段的使用方法及相关参数,希望大家读完能对timestamp有更深的认识. 1.TIMESTAMP字段类型简介 timestamp字段类型可存储时间类型数据,timestamp所能存储的时间范围为:'1970-01-01 00:00:01.000000' 到 '2038-01-19 03:14:07.999999',timest

  • 一个等号引发的血案（谈Nginx正确的404配置）

    一个等号引发的血案（谈Nginx正确的404配置）

    这是一个血淋淋的教训,这么说一点也不过分.因为最近发生了一个重大问题,网站流量大幅下跌,跌了近80%了.由于事件发生之前做过一些工作,加了大量友链,而且外站权重都相当高,在那天还发生了一次挂马事件,当然也即时解决了.还做了其它一些关键字内.外链优化等等.这样使得查找问题的原因就变的难上加难.偶然的原因发现,百度收录的链接开始出现错误,由于网站URL方式采用的目录式结构,最后一个字符都是/,然而百度收录的页面却无缘无故把这个线去掉了,而这种访问方式,我并没有做兼容.当时也查看了网站页面上的重写结果

  • thinkphp5.1框架实现格式化mysql时间戳为日期的方式小结

    thinkphp5.1框架实现格式化mysql时间戳为日期的方式小结

    本文实例讲述了thinkphp5.1框架实现格式化mysql时间戳为日期的方式.分享给大家供大家参考,具体如下: 方式一 使用mysql函数FROM_UNIXTIME(unix_timestamp,format)直接转换 select FROM_UNIXTIME(o.create_time,'%Y-%m-%d') create_time from table 方式二 使用模型获取器 withAttr, 在该方法中用date函数格式化 ->field('*') ->withAttr('creat

  • 详解Nginx proxy_pass的一个/斜杠引发的血案

    背景 一个nginx的server模块下需要proxy到两个server,所以就通过location的不同路径来区分转发到不同的服务器上. 一开始是这么写的 location / { proxy_pass http://server1/; } location /index { proxy_pass http://server2/; } 但是忘记了server1上有个服务路径是/indexNew,结果就被proxy到了server1,出现404问题,然后紧急修改配置如下: location /i

  • mysql时间戳格式化函数from_unixtime使用的简单说明

    我们一般使用字段类型int(11)时间戳来保存时间,这样方便查询时提高效率.但这样有个缺点,显示的时间戳,很难知道真实日期时间. mysql提供了一个时间戳格式化函数from_unixtime来转换格式 from_unxitime语法说明: from_unixtime(unix_timestamp, format) 返回Unix时间标记的一个字符串,根据format格式化.如果format为空默认会使用%Y-%m-%d %H:%i:%s的格式 例如: mysql> select from_uni

  • MySQL 出现错误1418 的原因分析及解决方法

    MySQL 出现错误1418 的原因分析及解决方法 具体错误: 使用mysql创建.调用存储过程,函数以及触发器的时候会有错误符号为1418错误. ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL,or READS SQL DATA in its declaration and binary logging is enabled(you *might* want to use the less safe log

  • MySQL锁等待与死锁问题分析

    前言: 在 MySQL 运维过程中,锁等待和死锁问题是令各位 DBA 及开发同学非常头痛的事.出现此类问题会造成业务回滚.卡顿等故障,特别是业务繁忙的系统,出现死锁问题后影响会更严重.本篇文章我们一起来学习下什么是锁等待及死锁,出现此类问题又应该如何分析处理呢? 1.了解锁等待与死锁 出现锁等待或死锁的原因是访问数据库需要加锁,那你可能要问了,为啥要加锁呢?原因是为了确保并发更新场景下的数据正确性,保证数据库事务的隔离性. 试想一个场景,如果你要去图书馆借一本<高性能MySQL>,为了防止有人

  • loadavg数据异常引发问题起源分析

    loadavg数据异常引发问题起源分析

    目录 proc loadavg 1: 问题起源 2: 数据来源 2.1:scheduler_tick 2.2: calc_global_load_tick 2.3: calc_load_fold_active 3: 数据计算 3.1: tick_sched_timer 3.2: calc_global_load 3.3:计算规则 calc_load 问题解析 简述结果 收获和总结 proc NAME (名称解释): proc - process information pseudo-filesy

随机推荐