不指定时区会踩坑：MySQL Java 驱动升级遇到的 Bug 分析

    <dependencies>        <dependency>            <groupId>org.springframework.boot</groupId>            <artifactId>spring-boot-starter-web</artifactId>            <version>2.5.4</version>        </dependency>        <dependency>            <groupId>org.mybatis.spring.boot</groupId>            <artifactId>mybatis-spring-boot-starter</artifactId>            <version>2.2.0</version>            <exclusions>                <exclusion>                    <artifactId>slf4j-api</artifactId>                    <groupId>org.slf4j</groupId>                </exclusion>            </exclusions>        </dependency>        <dependency>            <groupId>mysql</groupId>            <artifactId>mysql-connector-java</artifactId>            <version>8.0.22</version>            <scope>runtime</scope>        </dependency>    </dependencies>

@SpringBootApplication@MapperScan("com.feng.mysql.rep")public class MySQLDateMain {    public static void main(String[] args) {        SpringApplication.run(MySQLDateMain.class, args);    }} @RestControllerpublic class UserController {     @Autowired    private UserRepository userRepository;     @RequestMapping(value = "/Users/User", method = RequestMethod.POST)    public String addUser(){        UserEntity userEntity = new UserEntity();        userEntity.setAge(12);        userEntity.setName("tom");        userEntity.setCreateDate(new Date(System.currentTimeMillis()));        userEntity.setUpdateDate(new Timestamp(System.currentTimeMillis()));        userRepository.insertUser(userEntity);        return "ok";    }} @Mapperpublic interface UserRepository {     @Insert("insert into User (name, age, createDate, updateDate) values (#{name}, #{age}, #{createDate}, #{updateDate})")    int insertUser(UserEntity userEntity);}

CREATE TABLE `work`.`User`  (  `id` int(10) UNSIGNED ZEROFILL NOT NULL AUTO_INCREMENT,  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,  `age` int NULL DEFAULT NULL,  `createDate` timestamp NULL DEFAULT NULL,  `updateDate` datetime NULL DEFAULT NULL,  PRIMARY KEY (`id`) USING BTREE) ENGINE = InnoDB AUTO_INCREMENT = 29 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

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

public void configureTimezone() {    //获取MySQL server端的时区    String configuredTimeZoneOnServer = this.serverSession.getServerVariable("time_zone");
    //如果是SYSTEM，则获取系统时区    if ("SYSTEM".equalsIgnoreCase(configuredTimeZoneOnServer)) {        configuredTimeZoneOnServer = this.serverSession.getServerVariable("system_time_zone");    }
    //配置文件获取时区serverTimezone配置，即可以手动配置，这是一个解决问题的手段    String canonicalTimezone = getPropertySet().getStringProperty(PropertyKey.serverTimezone).getValue();
    //未指定时区，且读取到MySQL时区，就    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...        //时区不规范，比如不是GMT，然而ID标识GMT        if (!canonicalTimezone.equalsIgnoreCase("GMT") && this.serverSession.getServerTimeZone().getID().equals("GMT")) {            throw ExceptionFactory.createException(WrongArgumentException.class, Messages.getString("Connection.9", new Object[] {                    canonicalTimezone                }),                getExceptionInterceptor());        }    }}

/*** Returns the 'official' Java timezone name for the given timezone* * @param timezoneStr*            the 'common' timezone name* @param exceptionInterceptor*            exception interceptor* * @return the Java timezone name for the given timezone*/public static String getCanonicalTimezone(String timezoneStr, ExceptionInterceptor exceptionInterceptor) {   if (timezoneStr == null) {       return null;   }
   timezoneStr = timezoneStr.trim();
   // handle '+/-hh:mm' form ...   //顾名思义   if (timezoneStr.length() > 2) {       if ((timezoneStr.charAt(0) == '+' || timezoneStr.charAt(0) == '-') && Character.isDigit(timezoneStr.charAt(1))) {           return "GMT" + timezoneStr;       }   }
   synchronized(TimeUtil.class) {       if (timeZoneMappings == null) {           loadTimeZoneMappings(exceptionInterceptor);       }   }
   String canonicalTz;   //时区缓存去找关键字   if ((canonicalTz = timeZoneMappings.getProperty(timezoneStr)) != null) {       return canonicalTz;   }
   throw ExceptionFactory.createException(InvalidConnectionAttributeException.class,       Messages.getString("TimeUtil.UnrecognizedTimezoneId", new Object[] {           timezoneStr       }), exceptionInterceptor);}

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

public static TimeZone getTimeZone(String var0) {    return ZoneInfoFile.getZoneInfo(var0);}

private static void addOldMapping() {    String[][] var0 = oldMappings;    int var1 = var0.length;
    for (int var2 = 0; var2 < var1; ++var2) {        String[] var3 = var0[var2];        //这里就把CST时区设置为芝加哥时区        aliases.put(var3[0], var3[1]);    }
    if (USE_OLDMAPPING) {        aliases.put("EST", "America/New_York");        aliases.put("MST", "America/Denver");        aliases.put("HST", "Pacific/Honolulu");    } else {        zones.put("EST", new ZoneInfo("EST", -18000000));        zones.put("MST", new ZoneInfo("MST", -25200000));        zones.put("HST", new ZoneInfo("HST", -36000000));    }}

private static String[][] oldMappings = new String[][] {    {        "ACT",        "Australia/Darwin"    }, {        "AET",        "Australia/Sydney"    }, {        "AGT",        "America/Argentina/Buenos_Aires"    }, {        "ART",        "Africa/Cairo"    }, {        "AST",        "America/Anchorage"    }, {        "BET",        "America/Sao_Paulo"    }, {        "BST",        "Asia/Dhaka"    }, {        "CAT",        "Africa/Harare"    }, {        "CNT",        "America/St_Johns"    }, {        "CST",        "America/Chicago"    }, {        "CTT",        "Asia/Shanghai"    }, {        "EAT",        "Africa/Addis_Ababa"    }, {        "ECT",        "Europe/Paris"    }, {        "IET",        "America/Indiana/Indianapolis"    }, {        "IST",        "Asia/Kolkata"    }, {        "JST",        "Asia/Tokyo"    }, {        "MIT",        "Pacific/Apia"    }, {        "NET",        "Asia/Yerevan"    }, {        "NST",        "Pacific/Auckland"    }, {        "PLT",        "Asia/Karachi"    }, {        "PNT",        "America/Phoenix"    }, {        "PRT",        "America/Puerto_Rico"    }, {        "PST",        "America/Los_Angeles"    }, {        "SST",        "Pacific/Guadalcanal"    }, {        "VST",        "Asia/Ho_Chi_Minh"    }};

private static ZoneInfo getZoneInfo0(String var0) {    try {        //缓存获取        ZoneInfo var1 = (ZoneInfo) zones.get(var0);        if (var1 != null) {            return var1;        } else {            String var2 = var0;            if (aliases.containsKey(var0)) {                var2 = (String) aliases.get(var0);            }
            int var3 = Arrays.binarySearch(regions, var2);            if (var3 < 0) {                return null;            } else {                byte[] var4 = ruleArray[indices[var3]];                DataInputStream var5 = new DataInputStream(new ByteArrayInputStream(var4));                var1 = getZoneInfo(var5, var2);                //首次获取，存缓存                zones.put(var0, var1);                return var1;            }        }    } catch (Exception var6) {        throw new RuntimeException("Invalid binary time-zone data: TZDB:" + var0 + ", version: " + versionId, var6);    }}

 set global time_zone = '+08:00';

default-time-zone = '+08:00'

timedatectl set-timezone Asia/Shanghai

jdbc:mysql://localhost:3306/work?useUnicode=true&characterEncoding=utf-8&useSSL=true&serverTimezone=Asia/Shanghai

public void configureTimeZone() {    //先读配置connectionTimeZone    String connectionTimeZone = getPropertySet().getStringProperty(PropertyKey.connectionTimeZone).getValue();
    TimeZone selectedTz = null;    //如果没配参数，或者参数配LOCAL，就取客户端时区    //配置其他选择，基本上参数决定了时区，不再MySQL server去获取时区了    if (connectionTimeZone == null || StringUtils.isEmptyOrWhitespaceOnly(connectionTimeZone) || "LOCAL".equals(connectionTimeZone)) {        selectedTz = TimeZone.getDefault();
    } else if ("SERVER".equals(connectionTimeZone)) {        // Session time zone will be detected after the first ServerSession.getSessionTimeZone() call.        return;
    } else {        selectedTz = TimeZone.getTimeZone(ZoneId.of(connectionTimeZone)); // TODO use ZoneId.of(String zoneId, Map<String, String> aliasMap) for custom abbreviations support    }
    //设置时区    this.serverSession.setSessionTimeZone(selectedTz);
    //默认不再强制把时区塞进session 的 Variables中    if (getPropertySet().getBooleanProperty(PropertyKey.forceConnectionTimeZoneToSession).getValue()) {        // TODO don't send 'SET SESSION time_zone' if time_zone is already equal to the selectedTz (but it requires time zone detection)
        StringBuilder query = new StringBuilder("SET SESSION time_zone='");
        ZoneId zid = selectedTz.toZoneId().normalized();        if (zid instanceof ZoneOffset) {            String offsetStr = ((ZoneOffset) zid).getId().replace("Z", "+00:00");            query.append(offsetStr);            this.serverSession.getServerVariables().put("time_zone", offsetStr);        } else {            query.append(selectedTz.getID());            this.serverSession.getServerVariables().put("time_zone", selectedTz.getID());        }
        query.append("'");        sendCommand(this.commandBuilder.buildComQuery(null, query.toString()), false, 0);    }}

public void setTimestamp(int parameterIndex, Timestamp x) throws java.sql.SQLException {    synchronized(checkClosed().getConnectionMutex()) {        ((PreparedQuery << ? > ) this.query).getQueryBindings().setTimestamp(getCoreParameterIndex(parameterIndex), x, MysqlType.TIMESTAMP);    }}

public void bindTimestamp(int parameterIndex, Timestamp x, Calendar targetCalendar, int fractionalLength, MysqlType targetMysqlType) {   if (fractionalLength < 0) {       // default to 6 fractional positions       fractionalLength = 6;   }
   x = TimeUtil.adjustNanosPrecision(x, fractionalLength, !this.session.getServerSession().isServerTruncatesFracSecs());
   StringBuffer buf = new StringBuffer();
   if (targetCalendar != null) {       buf.append(TimeUtil.getSimpleDateFormat("''yyyy-MM-dd HH:mm:ss", targetCalendar).format(x));   } else {       this.tsdf = TimeUtil.getSimpleDateFormat(this.tsdf, "''yyyy-MM-dd HH:mm:ss",           targetMysqlType == MysqlType.TIMESTAMP && this.preserveInstants.getValue() ? this.session.getServerSession().getSessionTimeZone() :           this.session.getServerSession().getDefaultTimeZone());       buf.append(this.tsdf.format(x));   }
   if (this.session.getServerSession().getCapabilities().serverSupportsFracSecs() && x.getNanos() > 0) {       buf.append('.');       buf.append(TimeUtil.formatNanos(x.getNanos(), 6));   }   buf.append('\'');
   setValue(parameterIndex, buf.toString(), targetMysqlType);}