Java与MySQL时间不一致问题解决

public static void main(String[] args) {
    // 完整时区ID与时区描述：一共628个
    String[] ids = TimeZone.getAvailableIDs();
    for (String id : ids) {
        // System.out.println(id+"\t"+TimeZone.getTimeZone(id).getDisplayName());
    }
    // 系统默认时区
    TimeZone defaultTimeZone = TimeZone.getDefault();
    System.out.println("系统默认时区："+defaultTimeZone.getID()+"\t"+defaultTimeZone.getDisplayName());
    // 北京时区
    TimeZone bjTimeZone = TimeZone.getTimeZone("Asia/Shanghai");
    System.out.println("北京时区："+bjTimeZone.getID()+"\t"+bjTimeZone.getDisplayName());
    // 东京时区
    TimeZone djTimeZone = TimeZone.getTimeZone("Asia/Tokyo");
    System.out.println("东京时区："+djTimeZone.getID()+"\t"+djTimeZone.getDisplayName());
    // CST时区
    TimeZone cstTimeZone = ZoneInfo.getTimeZone("CST");
    System.out.println("CST时区："+cstTimeZone.getID()+"\t"+cstTimeZone.getDisplayName());

    Date date = new Date(0L);
    System.out.println("时间戳=0对应系统时间："+date.toString());
    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
    sdf.setTimeZone(bjTimeZone);// 设置北京时区
    System.out.println("时间戳0对应北京时间：" + sdf.format(date));
    sdf.setTimeZone(djTimeZone);// 设置东京时区
    System.out.println("时间戳0对应东京时间：" + sdf.format(date));
    sdf.setTimeZone(cstTimeZone);// 设置CST时区
    System.out.println("时间戳0对应CST时间：" + sdf.format(date));
}

mysql> show global variables like "%time_zone%";
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone | CST    |
| time_zone        | SYSTEM |
+------------------+--------+
2 rows in set (0.00 sec)

mysql> set global time_zone = '+9:00';
Query OK, 0 rows affected (0.00 sec)

mysql> show global variables like "%time_zone%";
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone | CST    |
| time_zone        | +09:00 |
+------------------+--------+
2 rows in set (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

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

mysql> set time_zone = '+9:00';
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like "%time_zone%";
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone | CST    |
| time_zone        | +09:00 |
+------------------+--------+
2 rows in set (0.00 sec)

    public static void main(String[] args) {
        Date date = new Date(0L);
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        sdf.setTimeZone(TimeZone.getTimeZone("UTC"));
        System.out.println("时间戳0对应时间(UTC):"+sdf.format(date));
        sdf.setTimeZone(TimeZone.getTimeZone("Asia/Shanghai"));
        System.out.println("时间戳0对应时间(UTC+8):"+sdf.format(date));
        sdf.setTimeZone(TimeZone.getTimeZone("Asia/Tokyo"));
        System.out.println("时间戳0对应时间(UTC+9):"+sdf.format(date));
    }

   public void configureTimezone() {
       String configuredTimeZoneOnServer = this.serverSession.getServerVariable("time_zone");

       if ("SYSTEM".equalsIgnoreCase(configuredTimeZoneOnServer)) {
           configuredTimeZoneOnServer = this.serverSession.getServerVariable("system_time_zone");
       }
	   // 获取serverTimezone配置的时区（PropertyKey.serverTimezone=serverTimezone）
       String canonicalTimezone = getPropertySet().getStringProperty(PropertyKey.serverTimezone).getValue();

       if (configuredTimeZoneOnServer != null) {
           // 如果没配置serverTimezone，获取数据库中time_zone变量的时区
           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));

           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());
   }

    public SqlTimestampValueFactory(PropertySet pset, Calendar calendar, TimeZone tz) {
        super(pset);
        if (calendar != null) {
            this.cal = (Calendar) calendar.clone();
        } else {
            this.cal = Calendar.getInstance(tz, Locale.US);
            this.cal.setLenient(false);
        }
    }

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

    public void 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) {
                fractionalLength = 6;
            }

            x = TimeUtil.adjustTimestampNanosPrecision(x, fractionalLength, !this.session.getServerSession().isServerTruncatesFracSecs());
			// 将时间戳格式化为字符串时间
			// this.session.getServerSession().getDefaultTimeZone() 时区（未配置serverTimezone，且数据库中time_zone变量的时区为CST时，这里就是CST时区）
            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);
        }
    }

    public Timestamp getTimestamp(String columnName) throws SQLException {
        return getTimestamp(findColumn(columnName));
    }

    public Timestamp getTimestamp(int columnIndex) throws SQLException {
        checkRowPos();
        checkColumnBounds(columnIndex);
        return this.thisRow.getValue(columnIndex - 1, this.defaultTimestampValueFactory);
    }

    public Timestamp localCreateFromTimestamp(InternalTimestamp its) {
        if (its.getYear() == 0 && its.getMonth() == 0 && its.getDay() == 0) {
            throw new DataReadException(Messages.getString("ResultSet.InvalidZeroDate"));
        }

        synchronized (this.cal) {
            try {
                // 这里就是关键环节，this.cal是一个Calendar类，里面有时区信息（未配置serverTimezone，且数据库中time_zone变量的时区为CST时，这里就是CST时区）
                this.cal.set(its.getYear(), its.getMonth() - 1, its.getDay(), its.getHours(), its.getMinutes(), its.getSeconds());
                Timestamp ts = new Timestamp(this.cal.getTimeInMillis());
                ts.setNanos(its.getNanos());
                return ts;
            } catch (IllegalArgumentException e) {
                throw ExceptionFactory.createException(WrongArgumentException.class, e.getMessage(), e);
            }
        }
    }