是由一则生产环境问题引出的MySQL对于datetime、timestamp等时间类型字段中毫秒的处理的深究。

　　【问题描述】

　　现有的生产应用升级mysql-connector-java驱动后，datetime字段值xxx入库后，按照该xxx值却查不到刚刚写入的记录。即：select * from table where insert_time=#xxx#返回为空。

　　【问题原因】

　　1、断点debug发现：datetime字段值入库前为”2015-01-01 00:00:00″，入库后却是”2015-01-01 00:00:01″

　　2、开启debug日志，输出执行sql，发现实际提交至mysql server的参数是”2015-01-01 00:00:00.560″，判断是毫秒部分发生四舍五入所致

　　3、追查mysql-connector-java驱动，发现驱动由5.1.6升级至5.1.30时，datetime字段的毫秒处理不同。”com.mysql.jdbc.PreparedStatement“中的

　　(1)5.1.6版本，直接丢弃毫秒部分(代码飘黄部分)

private void setTimestampInternal(int parameterIndex, Timestamp x, Calendar targetCalendar, TimeZone tz, boolean rollForward)
                throws SQLException
{
    if(x == null)
    {
        setNull(parameterIndex, 93);
    } 
    else
    {
        checkClosed();
        if(!useLegacyDatetimeCode)
        {
            newSetTimestampInternal(parameterIndex, x, targetCalendar);
        } else
        {
            String timestampString = null;
            Calendar sessionCalendar = connection.getUseJDBCCompliantTimezoneShift() ? connection.getUtcCalendar() : getCalendarInstanceForSessionOrNew();
            synchronized(sessionCalendar)
            {
                x = TimeUtil.changeTimezone(connection, sessionCalendar, targetCalendar, x, tz, connection.getServerTimezoneTZ(), rollForward);
            }
            if(connection.getUseSSPSCompatibleTimezoneShift())
                doSSPSCompatibleTimezoneShift(parameterIndex, x, sessionCalendar);
            else
                synchronized(this)
                {
                    if(tsdf == null)
                        tsdf = new SimpleDateFormat("''yyyy-MM-dd HH:mm:ss''", Locale.US);
                    timestampString = tsdf.format(x);
                    setInternal(parameterIndex, timestampString);
                }
        }
        parameterTypes[(parameterIndex - 1) + getParameterIndexOffset()] = 93;
    }
}

(2)5.1.30版本，判断若server端版本为5.6.4及以上时，则将毫秒部分一同提交至server

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

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));

                        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;
        }
    }
}

3、经查，生产mysql server版本是5.6.16，按照推理，server会存储client提交过来的毫秒部分，不应发生四舍五入的截断。

4、查看MySQL 5.6 manual发现，5.6.4及以上版本的mysql server端确实支持fractional second part(fsp)，但如果client提交过来的小数位数超过server端建表时指定的小数位数，mysql server会自动进行四舍五入的截断，没有任何警告或异常。

看不懂的代码

谢谢楼主