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;
}
代码意思是判断当前获取到的服务器版本号是否 大于等于5.6.4，如果大于serverSupportsFracSecs就是true那日期类型就要保留精度，否则就是false，直接丢掉精度。
在com.mysql.jdbc.MysqlIO#doHandshake可以看到获取服务端版本号的代码。
然后继续调试，发现连tidb数据库获取到的版本号5.7.25；切换成mysql数据库后获取到的服务端版本号是5.5.8
之前我们得知的信息是，我们的mysql版本号是5.6.43，怎么代码里面获取到的版本号是5.5.8呢，再次请教DBA，原因是我们通过了Mycat连接的mysql，这里获取到的版本号是mycat的版本号（真是惊天大坑，欲哭无泪。。。。）
至此，一切真相大白！
浙公网安备 33010602011771号