MySQL?時區(qū)與?serverTimezone詳解

更新時間：2024年12月20日 09:01:19 作者：AaronLin

存儲?TIMESTAMP?類型數(shù)據(jù)時,MySQL 會根據(jù)當前會話的時區(qū)將時間轉(zhuǎn)換為 UTC 時間,MySQL 實際存儲的是 UTC 時間,這篇文章主要介紹了MySQL?時區(qū)與?serverTimezone,需要的朋友可以參考下

TL;DR

手動為 MySQL 指定非偏移量的時區(qū)，以避免 TIMESTAMP 類型夏令時問題和時區(qū)轉(zhuǎn)化性能瓶頸
TIMESTAMP 范圍：'1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07'
連接 MySQL 數(shù)據(jù)庫時，serverTimezone 參數(shù)用于指定數(shù)據(jù)庫服務器的時區(qū)，需要設置為與 MySQL 服務端相同的時區(qū)

MySQL 時區(qū)設置影響 TIMESTAMP 類型數(shù)據(jù)和部分時間函數(shù)

MySQL 會話時區(qū)設置會影響 TIMESTAMP 和時間函數(shù)（NOW()、CURDATE()、CURTIME()、CURRENT_TIMESTAMP()）

存儲 TIMESTAMP 類型數(shù)據(jù)時，MySQL 會根據(jù)當前會話的時區(qū)將時間轉(zhuǎn)換為 UTC 時間，MySQL 實際存儲的是 UTC 時間。檢索時 MySQL 根據(jù)會話的時區(qū)將存儲的 UTC 時間轉(zhuǎn)換為會話對應時區(qū)的時間。而 DATETIME 類型的字段存儲的時間值是原始值，不受時區(qū)影響

MySQL 默認使用 SYSTEM 時區(qū)（即操作系統(tǒng)的時區(qū)），每個需要時區(qū)計算的 MySQL 函數(shù)調(diào)用都會調(diào)用系統(tǒng)庫來確定當前系統(tǒng)時區(qū)。此調(diào)用可能受到全局互斥體的保護，從而導致爭用，建議顯式設置時區(qū)

查詢當前時區(qū)

# time_zone：MySQL 使用 SYSTEM 的時區(qū)
# system_time_zone：SYSTEM 為 CST 時區(qū)
show variables like "%time_zone%";
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone | CST    |
| time_zone        | SYSTEM |
+------------------+--------+

不同會話時區(qū)對時間函數(shù) 的影響

# 當前時區(qū) 
# 查看當前的全球和會話時區(qū)值
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();

不同會話時區(qū)對 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)

# 僅修改當前會話的時區(qū)
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) 轉(zhuǎn)到紐約時，TIMESTAMP 相差了 13 或 12(夏令時) 小時，所以為了自動轉(zhuǎn)換夏令時，指定時區(qū)最好使用時區(qū)名詞 Asia/Shanghai，避免使用偏移量：'+08:00'

JDBC 連接 MySQL 時 serverTimezone 對于 TIMESTAMP 類型的影響

連接 MySQL 時我們使用 URL：jdbc:mysql://192.168.1.2:3306/mydb?useSSL=false&serverTimezone=Asia/Shanghai

這里的 serverTimezone 參數(shù)用于指定連接到 MySQL 數(shù)據(jù)庫時所使用的時區(qū)，不顯示指定使用 JVM 默認時區(qū)

MySQL 服務端處理 TIMESTAMP ：寫入時根據(jù)會話時區(qū)轉(zhuǎn)為 UTC 時間戳存儲，讀取時將 UTC 還原為會話時區(qū)的時間，保證了寫入和讀取數(shù)據(jù)的一致。數(shù)據(jù)庫會話時區(qū)與 JVM 時區(qū)相同時，JVM 讀寫的 TIMESTAMP 一致，如果不一致就會出現(xiàn)問題，serverTimezone 就是為了告訴 JDBC 從 MySQL 服務端獲取到的 TIMESTAMP 是什么時區(qū)，知道了它所使用的時區(qū)，JDBC 就可以進行預處理

MyBatis 在處理 TIMESTAMP 類型的數(shù)據(jù)時會有一些差異，實體映射為 Timestamp 或 Date 在讀寫時會進行上面提到的預處理，而 LocalDateTime 則不會

JDBC 讀取 TIMESTAMP 類型數(shù)據(jù)時

JDBC 執(zhí)行命令時，調(diào)用不同的 ResultSet 方法會有不同結(jié)果：

ResultSet 的 getString 方法：直接讀取時間，即直接返回 數(shù)據(jù)庫根據(jù)會話時區(qū)轉(zhuǎn)化后的時間
ResultSet 的 getTimestamp 方法：將 數(shù)據(jù)庫根據(jù)會話時區(qū)轉(zhuǎn)化后的時間 根據(jù) serverTimezone 設置的時區(qū)進行轉(zhuǎn)化，得到 數(shù)據(jù)庫根據(jù)會話時區(qū)轉(zhuǎn)化后的時間 對應的 UTC 時間毫秒戳，然后將這個 UTC 毫秒時間戳轉(zhuǎn)換為 Timestamp 類型（它本身不包含時區(qū)信息），打印時會根據(jù) JVM 的時區(qū)轉(zhuǎn)化為對應的時區(qū)時間

getTimestamp 轉(zhuǎn)化 Timestamp 的源碼在：com.mysql.cj.result.SqlTimestampValueFactory

這里的 this.connectionTimeZone 就是連接 url 中指定的 serverTimezone

假設 MySQL 默認設置的會話時區(qū)為 Asia/Shanghai，通過默認會話讀取該 TIMESTAMP 的值為：2022-10-24 15:45:00。而 MySQL 實際存儲的 TIMESTAMP 為 UTC 時間：2022-10-24 07:45:00。MySQL JDBC 驅(qū)動通過默認會話獲取該值時，MySQL 會自動根據(jù)默認時區(qū)提供轉(zhuǎn)化好時間：2022-10-24 15:45:00，驅(qū)動則會根據(jù) serverTimezone 配置的時區(qū)，將 MySQL 的時間轉(zhuǎn)化為 Calendar 對象，通過 c.getTimeInMillis() 獲取對應的 UTC 時間戳，用于創(chuàng)建 Timestamp 對象

JDBC 寫入 TIMESTAMP 類型：

now()寫入，數(shù)據(jù)庫 server 端會獲取數(shù)據(jù)庫當前時區(qū)
按照字符串寫入：MySQL 服務端根據(jù)會話時區(qū)轉(zhuǎn)成對應的 UTC 毫秒數(shù)存儲
通過變量綁定寫入：傳入 Timestamp 對象，JDBC 將其編碼為 serverTimezone 所代表的時間字符串，類似：2022-06-22 03:29:29，然后發(fā)送給 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();
            // 根據(jù) serverTimezone 將 Timestamp 預處理為 UTC 時間：2024-12-07 02:50:00
            // 相當于執(zhí)行下列 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
                // 根據(jù) serverTimezone，認定數(shù)據(jù)庫時區(qū)為 UTC，轉(zhuǎn)化為 本地 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();
        }
    }
}

實驗環(huán)境：
MySQL 8.0.40
mysql-connector-j 9.1.0
mybatis-spring-boot-starter 3.0.4