mysql time类型精度_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=2DEFAULT 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 aroundStringBuffer 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第三条