Mysql 时间类型精度截取的bug

mysql-connector-java版本升级出现的一次问题。涉及到了时间精度的截取和四舍五入。

首先了解一点,timestamp,datetime如果不指定精度,默认的精度是秒。

当mysql-connector-java版本<=5.1.22时,db的客户端会将Datetime,Timestamp秒以下的精度丢弃。版本>5.1.22后,秒以下的值将不会截断

db的server端会对超出精度位数的数据进行四舍五入!!

举个例子:在db建表时没指定精度时,插入精确到毫秒级别的日期

如果使用mysql-connector-java版本<=5.1.22,在客户端用'2018-04-02 23:59:59.999'插入日期,精度会在客户端被截取到秒,插入db里是'2018-04-02 23:59:59'

如果升级版本,在db的客户端用'2018-04-02 23:59:59.999'插入日期,精度在客户端不会被截断,db的server端会对超出精度位数的数据进行四舍五入,即插入db里是'2018-04-03 00:00:00 '

所以说mysql-connector-java版本升级就带了时间与原本不一致的问题,结合具体业务逻辑上的使用,可能会造成不同大小的影响。

要想证实这个观点,可以分两步:

  • server端是否会四舍五入
  • 客户端代码不同版本对精度是否有不同的处理方式

来实际测一下server会不会四舍五入:

CREATE TABLE `time_test` (  
  `id` int(11) NOT NULL AUTO_INCREMENT ,  
  `create_time` timestamp NOT NULL DEFAULT '1971-01-01 00:00:00' ,  
  `end_time` timestamp NOT NULL DEFAULT '1971-01-01 00:00:00' ,  
  PRIMARY KEY (`id`)  
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4;  
   
   
insert into time_test (create_time,end_time) values('2018-04-02 23:59:59','2018-04-02 23:59:59.999');  
   
   
select * from time_test;

看一下记录:

+----+---------------------+---------------------+

| id | create_time         | end_time            |

+----+---------------------+---------------------+

|  2 | 2018-04-02 23:59:59 | 2018-04-03 00:00:00 |

+----+---------------------+---------------------+

可以看出db的server端果然会进行四舍五入。

再看一下mysql驱动里是怎么写的,是否真的是截断精度了。

Mysql对于时间精度的处理在com.mysql.jdbc.PreparedStatement#setTimestampInternal这个方法中

翻一下5.1.21的源码看一下:

private void setTimestampInternal(int parameterIndex,  
      Timestamp x, Calendar targetCalendar,  
      TimeZone tz, boolean rollForward) throws SQLException {  
   synchronized (checkClosed()) {  
      if (x == null) {  
         setNull(parameterIndex, java.sql.Types.TIMESTAMP);  
      } else {  
         checkClosed();  
            
         if (!this.useLegacyDatetimeCode) {  
            newSetTimestampInternal(parameterIndex, x, targetCalendar);  
         } else {  
            String timestampString = null;  
   
            Calendar sessionCalendar = this.connection.getUseJDBCCompliantTimezoneShift() ?  
                  this.connection.getUtcCalendar() :  
                     getCalendarInstanceForSessionOrNew();  
                  
            synchronized (sessionCalendar) {  
               x = TimeUtil.changeTimezone(this.connection,  
                     sessionCalendar,  
                     targetCalendar,  
                     x, tz, this.connection  
                  .getServerTimezoneTZ(), rollForward);  
            }  
      
            if (this.connection.getUseSSPSCompatibleTimezoneShift()) {  
               doSSPSCompatibleTimezoneShift(parameterIndex, x, sessionCalendar);  
            } else {  
               synchronized (this) {  
                  if (this.tsdf == null) {  
                     //这里,截断秒以下的精度  
                     this.tsdf = new SimpleDateFormat("''yyyy-MM-dd HH:mm:ss''", Locale.US); //$NON-NLS-1$  
                  }  
                     
                  timestampString = this.tsdf.format(x);  
                     
                  //这里永远不会执行添加秒以下的精度  
                  if (false) { // not so long as Bug#50774 is around  
                     StringBuffer buf = new StringBuffer();  
                     buf.append(timestampString);  
                     int nanos = x.getNanos();  
                        
                     if (nanos != 0) {  
                        buf.append('.');  
                        buf.append(formatNanos(nanos));  
                     }  
                        
                     buf.append('\'');  
                  }  
                     
                  setInternal(parameterIndex, timestampString); // SimpleDateFormat is not  
                                                     // thread-safe  
               }  
            }  
         }  
            
         this.parameterTypes[parameterIndex - 1 + getParameterIndexOffset()] = Types.TIMESTAMP;  
      }  
   }  
} 

再看下5.1.32的实现:

private void setTimestampInternal(int parameterIndex,  
      Timestamp x, Calendar targetCalendar,  
      TimeZone tz, boolean rollForward) throws SQLException {  
   synchronized (checkClosed().getConnectionMutex()) {  
      if (x == null) {  
         setNull(parameterIndex, java.sql.Types.TIMESTAMP);  
      } else {  
         checkClosed();  
            
         if (!this.useLegacyDatetimeCode) {  
            newSetTimestampInternal(parameterIndex, x, targetCalendar);  
         } else {  
            Calendar sessionCalendar = this.connection.getUseJDBCCompliantTimezoneShift() ?  
                  this.connection.getUtcCalendar() :  
                     getCalendarInstanceForSessionOrNew();  
                  
            synchronized (sessionCalendar) {  
               x = TimeUtil.changeTimezone(this.connection,  
                     sessionCalendar,  
                     targetCalendar,  
                     x, tz, this.connection  
                  .getServerTimezoneTZ(), rollForward);  
            }  
      
            if (this.connection.getUseSSPSCompatibleTimezoneShift()) {  
               doSSPSCompatibleTimezoneShift(parameterIndex, x, sessionCalendar);  
            } else {  
               synchronized (this) {  
                  //同样截断精度  
                  if (this.tsdf == null) {  
                     this.tsdf = new SimpleDateFormat("''yyyy-MM-dd HH:mm:ss", Locale.US); //$NON-NLS-1$  
                  }  
                     
                  StringBuffer buf = new StringBuffer();  
                  buf.append(this.tsdf.format(x));  
   
                  //这里,如果server支持fractional seconds的话,就加上毫秒的精度  
                  if (this.serverSupportsFracSecs) {  
                     int nanos = x.getNanos();  
                        
                     if (nanos != 0) {  
                        buf.append('.');  
                        buf.append(TimeUtil.formatNanos(nanos, this.serverSupportsFracSecs, true));  
                     }  
                  }  
   
                  buf.append('\'');  
   
                  setInternal(parameterIndex, buf.toString()); // SimpleDateFormat is not  
                                                     // thread-safe  
               }  
            }  
         }  
            
         this.parameterTypes[parameterIndex - 1 + getParameterIndexOffset()] = Types.TIMESTAMP;  
      }  
   }  
}  

看来果然是个bug...看一下mysql官网的描述:参见bugFix第三条

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏JMCui

MongoDB系列四(索引).

一、索引简介     再来老生常谈一番,什么是索引呢?数据库索引与书籍的索引类似。有了索引就不需要翻整本书,数据库可以直接在索引中查找,在索引中找到条目以后,就...

3205
来自专栏乐沙弥的世界

SQL基础--> 序列(SEQUENCE)、同义词(SYNONYM)

--=============================================

902
来自专栏乐沙弥的世界

SQL Tuning Advisor(STA) 到底做了什么?

      SQL Tuing Advisor(STA) 是Automatic Tuning Optimizer(自动优化调整器)的一部分。在前面的文章使用SQ...

1184
来自专栏眯眯眼猫头鹰的小树杈

树状结构存储与读取之Modified Preorder Tree

一直以来存储树状结构都采用经典的结构<id,pid>的组合,即每一个节点持有其父节点的ID,并由此构成完整的树状结构。但是这样的结构在遇到大量的查询时会成为严重...

2101
来自专栏逸鹏说道

SQL Server 索引和表体系结构(包含列索引)

包含列索引 概述 包含列索引也是非聚集索引,索引结构跟聚集索引结构是一样,有一点不同的地方就是包含列索引的非键列只存储在叶子节点;包含列索引的列分为键列和非键列...

3308
来自专栏云计算教程系列

如何管理SQL数据库

安装SQL数据库时,需要添加,修改,删除和查询数据所需的所有命令。这个备忘单样式指南提供了一些最常用的SQL命令的快速参考。

2622
来自专栏北京马哥教育

SQL的几种连接:内连接、左联接、右连接、全连接、交叉连接

SQL连接可以分为内连接、外连接、交叉连接。 数据库数据: ? ? book表 ...

3464
来自专栏Java3y

Oracle总结【SQL细节、多表查询、分组查询、分页】

前言 在之前已经大概了解过Mysql数据库和学过相关的Oracle知识点,但是太久没用过Oracle了,就基本忘了…印象中就只有基本的SQL语句和相关一些概念…...

39610
来自专栏云霄雨霁

数据库完整性总结

1914
来自专栏乐沙弥的世界

使用 Toad 实现 SQL 优化

       It is very easy for us to implement sql tuning by toad.  We need to do is...

932

扫码关注云+社区

领取腾讯云代金券