MySQL 时区与 serverTimezone详解_Mysql

tl;dr

手动为 mysql 指定非偏移量的时区，以避免 timestamp 类型夏令时问题和时区转化性能瓶颈
timestamp 范围：'1970-01-01 00:00:01' utc to '2038-01-19 03:14:07'
连接 mysql 数据库时，servertimezone 参数用于指定数据库服务器的时区，需要设置为与 mysql 服务端相同的时区

mysql 时区设置影响 timestamp 类型数据和部分时间函数

mysql 会话时区设置会影响 timestamp 和时间函数（now()、curdate()、curtime()、current_timestamp()）

存储 timestamp 类型数据时，mysql 会根据当前会话的时区将时间转换为 utc 时间，mysql 实际存储的是 utc 时间。检索时 mysql 根据会话的时区将存储的 utc 时间转换为会话对应时区的时间。而 datetime 类型的字段存储的时间值是原始值，不受时区影响

mysql 默认使用 system 时区（即操作系统的时区），每个需要时区计算的 mysql 函数调用都会调用系统库来确定当前系统时区。此调用可能受到全局互斥体的保护，从而导致争用，建议显式设置时区

查询当前时区

# time_zone：mysql 使用 system 的时区
# system_time_zone：system 为 cst 时区
show variables like "%time_zone%";
+------------------+--------+
| variable_name    | value  |
+------------------+--------+
| system_time_zone | cst    |
| time_zone        | system |
+------------------+--------+

不同会话时区对时间函数的影响

# 当前时区 
# 查看当前的全球和会话时区值
select @@global.time_zone, @@session.time_zone;
select now(), curdate(), curtime(), current_timestamp();
set time_zone = 'america/new_york';
select now(), curdate(), curtime(), current_timestamp();

不同会话时区对 timestamp 类型的影响

# utc +8
set time_zone = 'asia/shanghai';
create table events (
    id int auto_increment primary key,
    event_name varchar(255) not null,
    event_timestamp timestamp default current_timestamp,
    event_datetime datetime default current_timestamp
);
insert into events (event_name, event_timestamp, event_datetime) values ('10.24 15:45:00', '2022-10-24 15:45:00', '2022-10-24 15:45:00');
insert into events (event_name, event_timestamp, event_datetime) values ('12.24 15:45:00', '2022-12-24 15:45:00', '2022-12-24 15:45:00');

select * from events;
+----+----------------+---------------------+---------------------+
| id | event_name     | event_timestamp     | event_datetime      |
+----+----------------+---------------------+---------------------+
|  1 | 10.24 15:45:00 | 2022-10-24 15:45:00 | 2022-10-24 15:45:00 |
|  2 | 12.24 15:45:00 | 2022-12-24 15:45:00 | 2022-12-24 15:45:00 |
+----+----------------+---------------------+---------------------+
2 rows in set (0.00 sec)

# 仅修改当前会话的时区
set time_zone = 'america/new_york';

select * from events;
+----+----------------+---------------------+---------------------+
| id | event_name     | event_timestamp     | event_datetime      |
+----+----------------+---------------------+---------------------+
|  1 | 10.24 15:45:00 | 2022-10-24 03:45:00 | 2022-10-24 15:45:00 |    <- 夏令时，相差 12 小时
|  2 | 12.24 15:45:00 | 2022-12-24 02:45:00 | 2022-12-24 15:45:00 |    <- 平时相差 13 小时
+----+----------------+---------------------+---------------------+
2 rows in set (0.00 sec)

纽约 utc 时差通常为 utc-5（est），夏令时为 utc-4（edt），所以将原本的会话从上海(utc+8) 转到纽约时，timestamp 相差了 13 或 12(夏令时) 小时，所以为了自动转换夏令时，指定时区最好使用时区名词 asia/shanghai，避免使用偏移量：'+08:00'

jdbc 连接 mysql 时 servertimezone 对于 timestamp 类型的影响

连接 mysql 时我们使用 url：jdbc:mysql://192.168.1.2:3306/mydb?usessl=false&servertimezone=asia/shanghai

这里的 servertimezone 参数用于指定连接到 mysql 数据库时所使用的时区，不显示指定使用 jvm 默认时区

mysql 服务端处理 timestamp ：写入时根据会话时区转为 utc 时间戳存储，读取时将 utc 还原为会话时区的时间，保证了写入和读取数据的一致。数据库会话时区与 jvm 时区相同时，jvm 读写的 timestamp 一致，如果不一致就会出现问题，servertimezone 就是为了告诉 jdbc 从 mysql 服务端获取到的 timestamp 是什么时区，知道了它所使用的时区，jdbc 就可以进行预处理

mybatis 在处理 timestamp 类型的数据时会有一些差异，实体映射为 timestamp 或 date 在读写时会进行上面提到的预处理，而 localdatetime 则不会

jdbc 读取 timestamp 类型数据时

jdbc 执行命令时，调用不同的 resultset 方法会有不同结果：

resultset 的 getstring 方法：直接读取时间，即直接返回 数据库根据会话时区转化后的时间
resultset 的 gettimestamp 方法：将 数据库根据会话时区转化后的时间 根据 servertimezone 设置的时区进行转化，得到 数据库根据会话时区转化后的时间 对应的 utc 时间毫秒戳，然后将这个 utc 毫秒时间戳转换为 timestamp 类型（它本身不包含时区信息），打印时会根据 jvm 的时区转化为对应的时区时间

gettimestamp 转化 timestamp 的源码在：com.mysql.cj.result.sqltimestampvaluefactory

这里的 this.connectiontimezone 就是连接 url 中指定的 servertimezone

假设 mysql 默认设置的会话时区为 asia/shanghai，通过默认会话读取该 timestamp 的值为：2022-10-24 15:45:00。而 mysql 实际存储的 timestamp 为 utc 时间：2022-10-24 07:45:00。mysql jdbc 驱动通过默认会话获取该值时，mysql 会自动根据默认时区提供转化好时间：2022-10-24 15:45:00，驱动则会根据 servertimezone 配置的时区，将 mysql 的时间转化为 calendar 对象，通过 c.gettimeinmillis() 获取对应的 utc 时间戳，用于创建 timestamp 对象

jdbc 写入 timestamp 类型：

now()写入，数据库 server 端会获取数据库当前时区
按照字符串写入：mysql 服务端根据会话时区转成对应的 utc 毫秒数存储
通过变量绑定写入：传入 timestamp 对象，jdbc 将其编码为 servertimezone 所代表的时间字符串，类似：2022-06-22 03:29:29，然后发送给 mysql 服务端

验证

import org.junit.jupiter.api.test;
import java.sql.*;
import java.util.timezone;
public class jdbctest {
    private static string url = "jdbc:mysql://host:3306/mydb?usessl=false&servertimezone=utc";
    private static string username = "root";
    private static string password = "";
    @test
    void testinserttimestamp() {
        timezone.setdefault(timezone.gettimezone("asia/shanghai"));
        try (connection connection = drivermanager.getconnection(url, username, password); preparedstatement ps = connection
                .preparestatement("insert into events(id,event_name,event_timestamp,event_datetime) values (1,'now()',now(),now())");) {
            ps.execute();
        }catch (exception e){
            e.printstacktrace();
        }
        try (connection connection = drivermanager.getconnection(url, username, password); preparedstatement ps = connection
                .preparestatement("insert into events(id,event_name,event_timestamp,event_datetime) values (2,'2022-06-22 03:29:29','2022-06-22 03:29:29', '2022-06-22 03:29:29')");) {
            ps.execute();
        }catch (exception e){
            e.printstacktrace();
        }
        try (connection connection = drivermanager.getconnection(url, username, password); preparedstatement ps = connection
                .preparestatement("insert into events(id,event_name,event_timestamp,event_datetime) values (3,'1733539800000l',?,?)")) {
            // sat dec 07 2024 02:50:00 gmt+0000
            // sat dec 07 2024 10:50:00 gmt+0800 (中国标准时间)
            long timestamp = 1733539800000l;
            timestamp ts1 = new timestamp(timestamp);
            timestamp ts2 = new timestamp(timestamp);
            ps.settimestamp(1, ts1);
            ps.settimestamp(2, ts2);
            ps.execute();
            // 根据 servertimezone 将 timestamp 预处理为 utc 时间：2024-12-07 02:50:00
            // 相当于执行下列 sql
            // insert into events(id,event_name,event_timestamp,event_datetime) values (3,'1733539800000l','2024-12-07 02:50:00','2024-12-07 02:50:00')
        }catch (exception e){
            e.printstacktrace();
        }
    }
    @test
    void testgettimestamp() {
        timezone.setdefault(timezone.gettimezone("asia/shanghai"));
        try (connection connection = drivermanager.getconnection(url, username, password); preparedstatement ps = connection
                .preparestatement("select * from events where id=3"); resultset rs = ps.executequery();) {
            while (rs.next()) {
                // gettimestamp is 2024-12-07 10:50:00.0
                // 根据 servertimezone，认定数据库时区为 utc，转化为 本地 asia/shanghai 需要 +8，则预处理为：2024-12-07 10:50:00.0
                system.out.println("gettimestamp is " + rs.gettimestamp("event_timestamp"));
                // getstring is 2024-12-07 02:50:00
                system.out.println("getstring is " + rs.getstring("event_datetime"));
            }
        }catch (exception e){
            e.printstacktrace();
        }
    }
}

实验环境：
mysql 8.0.40
mysql-connector-j 9.1.0
mybatis-spring-boot-starter 3.0.4