mysql 时差查了13小时_Mysql 时间差了 14 或 13 小时 com.mysql.cj.jdbc.Driver

解决办法

serverTimezone=CTT

spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver

spring.datasource.url=jdbc:mysql://xxx.xxx.xxx.xxx:3306/shys?serverTimezone=CTT&useUnicode=true&characterEncoding=utf8&autoReconnect=true&failOverReadOnly=false

spring.datasource.username=xxxxxx

spring.datasource.password=xxxxxx

spring.datasource.initialize=false

排错过程

mysql-connector-java-8.0.13.jar

当 JDBC 与 MySQL 开始建立连接时

com.mysql.cj.jdbc.ConnectionImpl.initializePropsFromServer() throws SQLException {

...

this.session.getProtocol().initServerSession();

...

}

com.mysql.cj.protocol.a.NativeProtocol.initServerSession() {

configureTimezone();

...

}

com.mysql.cj.protocol.a.NativeProtocol.configureTimezone() {

String configuredTimeZoneOnServer = this.serverSession.getServerVariable("time_zone");

if ("SYSTEM".equalsIgnoreCase(configuredTimeZoneOnServer)) {

configuredTimeZoneOnServer = this.serverSession.getServerVariable("system_time_zone");

}

String canonicalTimezone = getPropertySet().getStringProperty(PropertyKey.serverTimezone).getValue();

if (configuredTimeZoneOnServer != null) {

// user can override this with driver properties, so don't detect if that's the case

if (canonicalTimezone == null || StringUtils.isEmptyOrWhitespaceOnly(canonicalTimezone)) {

try {

canonicalTimezone = TimeUtil.getCanonicalTimezone(configuredTimeZoneOnServer, getExceptionInterceptor());

} catch (IllegalArgumentException iae) {

throw ExceptionFactory.createException(WrongArgumentException.class, iae.getMessage(), getExceptionInterceptor());

}

}

}

if (canonicalTimezone != null && canonicalTimezone.length() > 0) {

this.serverSession.setServerTimeZone(TimeZone.getTimeZone(canonicalTimezone));

//

// The Calendar class has the behavior of mapping unknown timezones to 'GMT' instead of throwing an exception, so we must check for this...

//

if (!canonicalTimezone.equalsIgnoreCase("GMT") && this.serverSession.getServerTimeZone().getID().equals("GMT")) {

throw ExceptionFactory.createException(WrongArgumentException.class, Messages.getString("Connection.9", new Object[] { canonicalTimezone }),

getExceptionInterceptor());

}

}

this.serverSession.setDefaultTimeZone(this.serverSession.getServerTimeZone());

}

追踪代码可知，当 MySQL 的 time_zone 值为 SYSTEM 时，会取 system_time_zone 值作为协调时区。

重点在这里！若 String configuredTimeZoneOnServer 得到的是 CST 那么 Java 会误以为这是 CST -0500 ，因此 TimeZone.getTimeZone(canonicalTimezone) 会给出错误的时区信息。

如图所示，本机默认时区是 Asia/Shanghai +0800 ，误认为服务器时区为 CST -0500 ，实际上服务器是 CST +0800 。

com.mysql.cj.ClientPreparedQueryBindings.setTimestamp(int parameterIndex, Timestamp x, Calendar targetCalendar, int fractionalLength) {

if (x == null) {

setNull(parameterIndex);

} else {

x = (Timestamp) x.clone();

if (!this.session.getServerSession().getCapabilities().serverSupportsFracSecs()

|| !this.sendFractionalSeconds.getValue() && fractionalLength == 0) {

x = TimeUtil.truncateFractionalSeconds(x);

}

if (fractionalLength < 0) {

// default to 6 fractional positions

fractionalLength = 6;

}

x = TimeUtil.adjustTimestampNanosPrecision(x, fractionalLength, !this.session.getServerSession().isServerTruncatesFracSecs());

this.tsdf = TimeUtil.getSimpleDateFormat(this.tsdf, "''yyyy-MM-dd HH:mm:ss", targetCalendar,

targetCalendar != null ? null : this.session.getServerSession().getDefaultTimeZone());

StringBuffer buf = new StringBuffer();

buf.append(this.tsdf.format(x));

if (this.session.getServerSession().getCapabilities().serverSupportsFracSecs()) {

buf.append('.');

buf.append(TimeUtil.formatNanos(x.getNanos(), 6));

}

buf.append('\'');

setValue(parameterIndex, buf.toString(), MysqlType.TIMESTAMP);

}

}

原因

Timestamp 被转换为会话时区的时间字符串了。问题到此已然明晰：

JDBC 误认为会话时区在 CST-5

JBDC 把 Timestamp+0 转为 CST-5 的 String-5

MySQL 认为会话时区在 CST+8，将 String-5 转为 Timestamp-13

最终结果相差 13 个小时！如果处在冬令时还会相差 14 个小时！