一、背景
不同版本的mysql-connector执行同一条sql时，得到的结果有差别。
时间戳：1641439248850(2022-01-06 11:20:48.85) 。
- 5.1.8版本执行后结果为11:20:48
- 5.1.47版本执行后结果为11:20:49
即5.1.8版本是向下取整，5.1.47是四舍五入。
二、原因
1.测试代码
- 连接数据库
- 构建PreparedStatement对象
- 调用PreparedStatement.executeUpdate()执行sql语句
- 最后关闭连接
DruidDataSource ds = new DruidDataSource();
ds.setName("localhoset_3306_mytest");
ds.setUrl("jdbc:mysql://localhost:3306/mytest?useUnicode=true&characterEncoding=UTF-8");
ds.setUsername("root");
ds.setPassword("");
ds.setFilters("slf4j,stat,wall");
String replaceSql = "insert into Haige(name,create_time) value(?,?)";
Connection conn = ds.getConnection();// 获取Connection对象
PreparedStatement pstmt = conn.prepareStatement(replaceSql);// 构建PreparedStatement对象
Object[] paramValues = new Object[] { "test1", new Date() };
for (int j = 0; j < paramValues.length; j++)
{
pstmt.setObject(j + 1, paramValues[j]);// 注入参数
}
pstmt.executeUpdate();// 执行sql
pstmt.close();
conn.close();
2.setObject方法
- 所在类：com.mysql.jdbc.PreparedStatement
- 入参的parameterObj判断类型
- 类型如果是java.util.Date，调用setTimestamp方法
public void setObject(int parameterIndex, Object parameterObj) throws SQLException {
if (parameterObj == null) {
this.setNull(parameterIndex, 1111);
} else if (parameterObj instanceof Byte) {
this.setInt(parameterIndex, ((Byte)parameterObj).intValue());
.................
} else if (parameterObj instanceof Clob) {
this.setClob(parameterIndex, (Clob)parameterObj);
} else if (this.connection.getTreatUtilDateAsTimestamp() && parameterObj instanceof java.util.Date) { // 类型如果是Date类型，进入setTimestamp方法
this.setTimestamp(parameterIndex, new Timestamp(((java.util.Date)parameterObj).getTime()));// 核心方法
} else if (parameterObj instanceof BigInteger) {
this.setString(parameterIndex, parameterObj.toString());
} else {
this.setSerializableObject(parameterIndex, parameterObj);
}
}
3.setTimestamp方法
- 所在类：com.mysql.jdbc.PreparedStatement
- 进入setTimestampInternal方法
public void setTimestamp(int parameterIndex, Timestamp x) throws SQLException {
this.setTimestampInternal(parameterIndex, x, (Calendar)null, Util.getDefaultTimeZone(), false);// 核心方法
}
4.setTimestampInternal方法
- 所在类：com.mysql.jdbc.PreparedStatement
- 处理时间戳后三位： formatNanos方法
4.1 5.1.8版本
- 处理时间戳后三位的方法：this.formatNanos(x.getNanos())
- 拼接结果：2022-01-06 11:20:48.0
private void setTimestampInternal(int parameterIndex, Timestamp x, Calendar targetCalendar, TimeZone tz, boolean rollForward) throws SQLException {
if (x == null) {
this.setNull(parameterIndex, 93);
} else {
this.checkClosed();//检查数据库连接是否关闭
if (!this.useLegacyDatetimeCode) { //useLegacyDatetimeCode 默认为true，开启服务端和客户端的时区转换
this.newSetTimestampInternal(parameterIndex, x, targetCalendar);
} else {
String timestampString = null;
//useJDBCCompliantTimezoneShift 使用JDBC是否符合时区转换，为true获取UTC时间，false获取jdbc连接中session时间
Calendar sessionCalendar = this.connection.getUseJDBCCompliantTimezoneShift() ? this.connection.getUtcCalendar() : this.getCalendarInstanceForSessionOrNew();
synchronized(sessionCalendar) {
x = TimeUtil.changeTimezone(this.connection, sessionCalendar, targetCalendar, x, tz, this.connection.getServerTimezoneTZ(), rollForward);
}
if (this.connection.getUseSSPSCompatibleTimezoneShift()) {
this.doSSPSCompatibleTimezoneShift(parameterIndex, x, sessionCalendar);
} else {
synchronized(this) {
if (this.tsdf == null) {
this.tsdf = new SimpleDateFormat("''yyyy-MM-dd HH:mm:ss", Locale.US);
}
timestampString = this.tsdf.format(x);
StringBuffer buf = new StringBuffer();
buf.append(timestampString);
buf.append('.');
buf.append(this.formatNanos(x.getNanos()));// 核心方法
buf.append(''');
this.setInternal(parameterIndex, buf.toString());//拼接结果：2022-01-06 11:20:48.0
}
}
}
this.parameterTypes[parameterIndex - 1 + this.getParameterIndexOffset()] = 93;
}
}
4.2 5.1.47版本
- 处理时间戳后三位核心方法：TimeUtil.formatNanos
- 拼接结果： 2022-01-06 11:20:48.85
protected void setTimestampInternal(int parameterIndex, Timestamp x, Calendar targetCalendar, TimeZone tz, boolean rollForward, int fractionalLength, boolean useSSPSCompatibleTimezoneShift) throws SQLException {
if (x == null) {
this.setNull(parameterIndex, 93);
} else {
this.checkClosed();
x = (Timestamp)x.clone();
if (!this.serverSupportsFracSecs || !this.sendFractionalSeconds && fractionalLength == 0) {
x = TimeUtil.truncateFractionalSeconds(x);
}
if (fractionalLength < 0) {
fractionalLength = 6;
}
x = TimeUtil.adjustTimestampNanosPrecision(x, fractionalLength, !this.connection.isServerTruncatesFracSecs());
if (!this.useLegacyDatetimeCode) {
this.newSetTimestampInternal(parameterIndex, x, targetCalendar);
} else {
Calendar sessionCalendar = this.connection.getUseJDBCCompliantTimezoneShift() ? this.connection.getUtcCalendar() : this.getCalendarInstanceForSessionOrNew();
sessionCalendar = TimeUtil.setProlepticIfNeeded(sessionCalendar, targetCalendar);
x = TimeUtil.changeTimezone(this.connection, sessionCalendar, targetCalendar, x, tz, this.connection.getServerTimezoneTZ(), rollForward);
if (useSSPSCompatibleTimezoneShift) {
this.doSSPSCompatibleTimezoneShift(parameterIndex, x, fractionalLength, targetCalendar);
} else {
synchronized(this) {
this.tsdf = TimeUtil.getSimpleDateFormat(this.tsdf, "''yyyy-MM-dd HH:mm:ss", (Calendar)null, (TimeZone)null);
Calendar adjCal = TimeUtil.setProlepticIfNeeded(this.tsdf.getCalendar(), targetCalendar);
if (this.tsdf.getCalendar() != adjCal) {
this.tsdf.setCalendar(adjCal);
}
StringBuffer buf = new StringBuffer();
buf.append(this.tsdf.format(x));
if (fractionalLength > 0) {
int nanos = x.getNanos();
if (nanos != 0) {
buf.append('.');
buf.append(TimeUtil.formatNanos(nanos, this.serverSupportsFracSecs, fractionalLength));// 核心方法
}
}
buf.append(''');
this.setInternal(parameterIndex, buf.toString());// 拼接结果：2022-01-06 11:20:48.85
}
}
}
this.parameterTypes[parameterIndex - 1 + this.getParameterIndexOffset()] = 93;
}
5.formatNanos
- 所在类：com.mysql.jdbc.PreparedStatement
- 可以看到5.1.47是取了具体的值，而5.1.8版本是强制填写了0
5.1.47
public static String formatNanos(int nanos, boolean serverSupportsFracSecs, int fsp) throws SQLException {
if (nanos >= 0 && nanos <= 999999999) {
if (fsp >= 0 && fsp <= 6) {
if (serverSupportsFracSecs && fsp != 0 && nanos != 0) {
nanos = (int)((double)nanos / Math.pow(10.0D, (double)(9 - fsp)));
if (nanos == 0) {
return "0";
} else {
String nanosString = Integer.toString(nanos);
String zeroPadding = "000000000";
nanosString = "000000000".substring(0, fsp - nanosString.length()) + nanosString;
int pos;
for(pos = fsp - 1; nanosString.charAt(pos) == '0'; --pos) {
}
nanosString = nanosString.substring(0, pos + 1);
return nanosString;
}
} else {
return "0";
}
} else {
throw SQLError.createSQLException("fsp value must be in 0 to 6 range but was " + fsp, "S1009", (ExceptionInterceptor)null);
}
} else {
throw SQLError.createSQLException("nanos value must be in 0 to 999999999 range but was " + nanos, "S1009", (ExceptionInterceptor)null);
}
}
5.1.8
private String formatNanos(int nanos) {
return "0";
}
三、总结
- 虽然我们System.out.println(new Date());打印出来的Date类型只精确到秒级别，但这只是toString()方法的实现，真正Date的精度还是毫秒级别的
- mysql-connector的5.1.8版本 会强制让时间精确到秒级别，向下取整；而5.1.47版本，会保留时间的原有毫秒级别精度
- 之后mysql在执行时，会对日期做四舍五入处理，因此导致最后结果有1秒的差别
\
