MYSQL时区导致时间差了14或13小时的解决方法

public void configureTimezone() {
    String configuredTimeZoneOnServer = getServerVariable("time_zone");
 
    if ("SYSTEM".equalsIgnoreCase(configuredTimeZoneOnServer)) {
        configuredTimeZoneOnServer = getServerVariable("system_time_zone");
    }
 
    String canonicalTimezone = getPropertySet().getStringReadableProperty(PropertyDefinitions.PNAME_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.serverTimezoneTZ = 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.serverTimezoneTZ.getID().equals("GMT")) {
            throw ...
        }
    }
 
    this.defaultTimeZone = this.serverTimezoneTZ;
}复制代码

mysql> show variables like '%time_zone%';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone | CST    |
| time_zone        | SYSTEM |
+------------------+--------+
2 rows in set (0.00 sec)复制代码

public void setTimestamp(int parameterIndex, Timestamp x) throws java.sql.SQLException {
    synchronized (checkClosed().getConnectionMutex()) {
        setTimestampInternal(parameterIndex, x, this.session.getDefaultTimeZone());
    }
}

private void setTimestampInternal(int parameterIndex, Timestamp x, TimeZone tz) throws SQLException {
    if (x == null) {
        setNull(parameterIndex, MysqlType.TIMESTAMP);
    } else {
        if (!this.sendFractionalSeconds.getValue()) {
            x = TimeUtil.truncateFractionalSeconds(x);
        }
 
        this.parameterTypes[parameterIndex - 1 + getParameterIndexOffset()] = MysqlType.TIMESTAMP;
 
        if (this.tsdf == null) {
            this.tsdf = new SimpleDateFORMat("''yyyy-MM-dd HH:mm:ss", Locale.US);
        }
 
        this.tsdf.setTimeZone(tz);
 
        StringBuffer buf = new StringBuffer();
        buf.append(this.tsdf.format(x));
        if (this.session.serverSupportsFracSecs()) {
            buf.append('.');
            buf.append(TimeUtil.formatNanos(x.getNanos(), true));
        }
        buf.append('\'');
 
        setInternal(parameterIndex, buf.toString());
    }
}

mysql> set global time_zone = '+08:00';
Query OK, 0 rows affected (0.00 sec)
 
mysql> set time_zone = '+08:00';
Query OK, 0 rows affected (0.00 sec)复制代码

public class User {
    private LocalDateTime createTime;
    // setter ... getter ... 省略了哈
}
 
public interface UserDao {
    @Insert("INSERT INTO user(create_time) values(#{createTime})")
    int insertUser(User user);
}
 
// 在set时间时，一般直接用now方法就好
user.setCreateTime(LocalDateTime.now());

System.out.println(LocalDateTime.now());
// 输出的是北京时间
2019-03-15T16:51:37.121

System.out.println(LocalDateTime.now(ZoneId.of("+08:00")));

mysql> select curtime();
mysql> show variables like '%time_zone%';

mysql> set global time_zone='+08:00';
mysql> set time_zone='+08:00';
mysql> flush privileges;

[mysqld]
default-time-zone = '+08:00'