查看mysql当前使用时区
show variables like '%time_zone%';
解决办法
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 个小时!