mysql datetime 精度问题

记录一下mysql datetime类型精度的坑

业务中需要用到两个字段，开始时间start_time,截止时间end_time，根据实际业务场景，start_time取当天最早的那个时间点，end_time取当天时间最后的那个时间点，java代码里面的处理方式分别是

start_time 取当天最早的时间点，时分秒是 00:00:00.000

  /**
 * 把指定Date的时间部分设置为0:0:0.000
 * 
 * @param date
 * @return
 */
public static Date getMinTimeByDate(Date date) {
    Calendar c = Calendar.getInstance();
    c.setTime(date);
    c.set(Calendar.HOUR_OF_DAY, 0);
    c.set(Calendar.MINUTE, 0);
    c.set(Calendar.SECOND, 0);
    c.set(Calendar.MILLISECOND, 000);
    return c.getTime();
}

end_time 取当天最晚的时间点，时分秒是23:59:59.999

/**
 * 把指定Date的时间部分设置为23:59:59.999
 * 
 * @param date
 * @return
 */
public static Date getMaxTimeByDate(Date date) {
    Calendar c = Calendar.getInstance();
    c.setTime(date);
    c.set(Calendar.HOUR_OF_DAY, 23);
    c.set(Calendar.MINUTE, 59);
    c.set(Calendar.SECOND, 59);
    c.set(Calendar.MILLISECOND, 999);
    return c.getTime();
}

java代码里面两个字段的类型都是java.util.Date，mybatis xml里面映射的类型是timestamp，

mysql里面字段类型设置：

可以看到mysql里面未设置精度

mysql版本号是 5.6.43-log

之前end_time插入数据库之后，后面的999没了，比如插入的时候是 2022-11-03 23:59:59.999，插入到数据库后变成了2022-11-03 23:59:59。因为一直是这么用，所以也没关注精度丢失的问题。直到最近要将数据库切换成TIDB。

数据库切换成tidb后，代码还是原来的代码，jdbc驱动还是原来的驱动，但是end_time插入的时候是2022-11-03 23:59:59.999，插入到tidb之后就变成了2022-11-04 00:00:00，所谓差之毫厘谬以千里，虽然只是多了1毫秒，但是因为前面日期不一样了，对业务影响很大。

于是开始找原因，首先看应用打印的db日志，发现sql日志里面end_time插入的值都是2022-11-03 23:59:59.999，于是找到DBA询问原因，得到的回复是，datetime类型字段，未设置精度，插入数据库时会使用四舍五入的进位方式处理掉秒后面的值，所以2022-11-03 23:59:59.999四舍五入之后就变成了2022-11-04 00:00:00。

网上找资料，mysql 5.6.4 之前数据库是会把datetime类型秒后面的精度丢掉，5.6.4之后的版本是会保留这个精度，但是如果字段未设置精度，跟tidb一样也会做四舍五入的进位处理。但是我们mysql的版本是5.6.43，那为什么原来在mysql的时候没有做这个进位处理呢？于是又找DBA要了数据库层的日志，发现tidb中db日志end_time插入的值就是2022-11-03 23:59:59.999，而mysql里面的db日志显示end_time插入的值却是2022-11-03 23:59:59。

这个时候就需要怀疑jdbc驱动了，我们项目中使用的mysql-connector-java版本号是5.1.43，于是直奔主题，找到com.mysql.jdbc.PreparedStatement类，在处理日期类型的方法上打上断点

/**
 * Set a parameter to a java.sql.Timestamp value. The driver converts this
 * to a SQL TIMESTAMP value when it sends it to the database.
 * 
 * @param parameterIndex
 *            the first parameter is 1, the second is 2, ...
 * @param x
 *            the parameter value
 * @param tz
 *            the timezone to use
 * 
 * @throws SQLException
 *             if a database-access error occurs.
 */
private void setTimestampInternal(int parameterIndex, Timestamp x, Calendar targetCalendar, TimeZone tz, boolean rollForward) throws SQLException {
    if (x == null) {
        setNull(parameterIndex, java.sql.Types.TIMESTAMP);
    } else {
        checkClosed();
 
        if (!this.sendFractionalSeconds) {
            x = TimeUtil.truncateFractionalSeconds(x);
        }
 
        if (!this.useLegacyDatetimeCode) {
            newSetTimestampInternal(parameterIndex, x, targetCalendar);
        } else {
            Calendar sessionCalendar = this.connection.getUseJDBCCompliantTimezoneShift() ? this.connection.getUtcCalendar()
                    : getCalendarInstanceForSessionOrNew();
 
            x = TimeUtil.changeTimezone(this.connection, sessionCalendar, targetCalendar, x, tz, this.connection.getServerTimezoneTZ(), rollForward);
 
            if (this.connection.getUseSSPSCompatibleTimezoneShift()) {
                doSSPSCompatibleTimezoneShift(parameterIndex, x);
            } else {
                synchronized (this) {
                    if (this.tsdf == null) {
                        this.tsdf = new SimpleDateFormat("''yyyy-MM-dd HH:mm:ss", Locale.US);
                    }
 
                    StringBuffer buf = new StringBuffer();
                    buf.append(this.tsdf.format(x));
 
                    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;
    }
}　

可以看到处理日期的方法中有个重要的变量this.serverSupportsFracSecs，通过字面意思我们可以猜到，这个参数应该是用来控制秒后面的精度的。打上断点，调试进去看，果然如此，贴上com.mysql.jdbc.TimeUtil#formatNanos的代码看看

public static String formatNanos(int nanos, boolean serverSupportsFracSecs, boolean usingMicros) {
 
        // get only last 9 digits
        if (nanos > 999999999) {
            nanos %= 100000000;
        }
 
        if (usingMicros) {
            nanos /= 1000;
        }
 
        if (!serverSupportsFracSecs || nanos == 0) {
            return "0";
        }
 
        final int digitCount = usingMicros ? 6 : 9;
 
        String nanosString = Integer.toString(nanos);
        final String zeroPadding = usingMicros ? "000000" : "000000000";
 
        nanosString = zeroPadding.substring(0, (digitCount - nanosString.length())) + nanosString;
 
        int pos = digitCount - 1; // the end, we're padded to the end by the code above
 
        while (nanosString.charAt(pos) == '0') {
            pos--;
        }
 
        nanosString = nanosString.substring(0, pos + 1);
 
        return nanosString;
    }

　在formatNanos方法里面可以看到如果serverSupportsFracSecs为false就会直接返回0，如果为true，就会对精度进行处理。

那现在关键的点是serverSupportsFracSecs的值依据什么而定。在PreparedStatement类中找到serverSupportsFracSecs设值的代码段，发现里面有几个固定的入参5，6，4，对应的参数名是major, minor, subminor，对应的应该是主版本，次版本，子次版本号

protected void detectFractionalSecondsSupport() throws SQLException {
    this.serverSupportsFracSecs = this.connection != null && this.connection.versionMeetsMinimum(5, 6, 4);
}

在类com.mysql.jdbc.ConnectionImpl中进方法this.connection.versionMeetsMinimum中看看

public boolean versionMeetsMinimum(int major, int minor, int subminor) throws SQLException {
    checkClosed();
 
    return this.io.versionMeetsMinimum(major, minor, subminor);
}

再跳进this.io.versionMeetsMinimum(major, minor, subminor)里面看看

在类com.mysql.jdbc.MysqlIO里面

boolean versionMeetsMinimum(int major, int minor, int subminor) {
    if (getServerMajorVersion() >= major) {
        if (getServerMajorVersion() == major) {
            if (getServerMinorVersion() >= minor) {
                if (getServerMinorVersion() == minor) {
                    return (getServerSubMinorVersion() >= subminor);
                }
 
                // newer than major.minor
                return true;
            }
 
            // older than major.minor
            return false;
        }
 
        // newer than major
        return true;
    }
 
    return false;
}　