MySQL客户端与服务端时区不一致问题解决方案

满足如下三点即可：

mysql的jdbc链接确保有配置connectionTimeZone及forceConnectionTimeZoneToSession=true；
springboot启动前配置默认时区及local；
mysql-connector-j驱动使用8.0.33 注：mysql-connector-j驱动8.0.23之前版本有时区bug。

CREATE TABLE `date_time_ts` (
	`long1` BIGINT(20) NULL DEFAULT NULL,
	`string1` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8mb4_bin',
	`util_date1` DATETIME NULL DEFAULT NULL,
	`sql_date1` DATETIME NULL DEFAULT NULL,
	`date_timestamp1` TIMESTAMP NULL DEFAULT NULL,
	`local_date_time1` DATETIME NULL DEFAULT NULL,
	`zoned_date_time1` DATETIME NULL DEFAULT NULL,
	`timestamp1` TIMESTAMP NULL DEFAULT NULL,
	`timestamp_date1` DATETIME NULL DEFAULT NULL
)
COLLATE='utf8mb4_bin'
ENGINE=InnoDB
;

@SpringBootApplication
@MapperScan("com.sf.test.demo.mapper")
public class DemoApplication {

    public static void main(String[] args) {
        Locale.setDefault(Locale.JAPAN);
        TimeZone.setDefault(TimeZone.getTimeZone("Asia/Tokyo"));

        SpringApplication.run(DemoApplication.class, args);

        System.err.println(Locale.getDefault());
        System.err.println(TimeZone.getDefault());
        System.err.println(new Date());
        System.err.println(LocalDateTime.now());
    }
}

spring.application.name=demo

spring.jackson.date-format=yyyy-MM-dd HH:mm:ss
spring.jackson.time-zone=GMT+9

spring.datasource.url=jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf-8&useSSL=false&connectionTimeZone=GMT%2B9&forceConnectionTimeZoneToSession=true
spring.datasource.username=root
spring.datasource.password=root
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver

MariaDB [test]> select @@global.time_zone;
+--------------------+
| @@global.time_zone |
+--------------------+
| +08:00             |
+--------------------+
1 row in set (0.000 sec)

MariaDB [test]> set @@session.time_zone="+8:00";
Query OK, 0 rows affected (0.000 sec)

MariaDB [test]> select @@session.time_zone;
+---------------------+
| @@session.time_zone |
+---------------------+
| +08:00              |
+---------------------+
1 row in set (0.000 sec)

MariaDB [test]> select * from date_time_ts \G;
*************************** 1. row ***************************
           long1: 1729741937912
         string1: 2024/10/24 12:52:17
      util_date1: 2024-10-24 12:52:17
       sql_date1: 2024-10-24 00:00:00
 date_timestamp1: 2024-10-24 11:52:17
local_date_time1: 2024-10-24 12:52:17
zoned_date_time1: 2024-10-24 12:52:17
      timestamp1: 2024-10-24 11:52:17
 timestamp_date1: 2024-10-24 12:52:17
1 row in set (0.000 sec)

ERROR: No query specified

MariaDB [test]> set @@session.time_zone="+9:00";
Query OK, 0 rows affected (0.000 sec)

MariaDB [test]> select @@session.time_zone;
+---------------------+
| @@session.time_zone |
+---------------------+
| +09:00              |
+---------------------+
1 row in set (0.000 sec)

MariaDB [test]>  select * from date_time_ts \G;
*************************** 1. row ***************************
           long1: 1729741937912
         string1: 2024/10/24 12:52:17
      util_date1: 2024-10-24 12:52:17
       sql_date1: 2024-10-24 00:00:00
 date_timestamp1: 2024-10-24 12:52:17
local_date_time1: 2024-10-24 12:52:17
zoned_date_time1: 2024-10-24 12:52:17
      timestamp1: 2024-10-24 12:52:17
 timestamp_date1: 2024-10-24 12:52:17
1 row in set (0.000 sec)

ERROR: No query specified

mysql jdbc驱动NativeProtocol类的configureTimeZone方法：

public void configureTimeZone() {
        String connectionTimeZone = getPropertySet().getStringProperty(PropertyKey.connectionTimeZone).getValue();

        TimeZone selectedTz = null;

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

        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(getCommandBuilder().buildComQuery(null, query.toString()), false, 0);
        }
    }

yangl/mysql_client_server_diff_timezone.md