MySQL :: MySQL 9.0 參考手冊 :: 7.1.15 MySQL 伺服器時區支援

版本 9.0

8.4 目前
8.0
5.7

8.0 日文

MySQL 9.0 參考手冊 / ... / MySQL 伺服器時區支援

7.1.15 MySQL 伺服器時區支援

本節說明 MySQL 維護的時區設定、如何載入具名時間支援所需的系統表格、如何隨時掌握時區變更，以及如何啟用閏秒支援。

插入的日期時間值也支援時區偏移；如需更多資訊，請參閱第 13.2.2 節「日期、日期時間和時間戳記類型」。

如需關於複寫設定中時區設定的資訊，請參閱第 19.5.1.14 節「複寫和系統函數」和第 19.5.1.34 節「複寫和時區」。

時區變數

MySQL 伺服器維護數個時區設定

伺服器系統時區。當伺服器啟動時，它會嘗試判斷主機的時區，並使用它來設定system_time_zone 系統變數。
若要在啟動時明確指定 MySQL 伺服器的系統時區，請在啟動mysqld之前設定 TZ 環境變數。如果您使用mysqld_safe啟動伺服器，其--timezone 選項提供了另一種設定系統時區的方法。 TZ 和 --timezone 的允許值與系統相關。請查閱您的作業系統文件，以了解可接受的值。
伺服器目前時區。全域 time_zone 系統變數指示伺服器目前正在執行的時區。初始的 time_zone 值為 'SYSTEM'，這表示伺服器時區與系統時區相同。

注意

如果設定為 SYSTEM，則每個需要進行時區計算的 MySQL 函式呼叫都會進行系統函式庫呼叫，以判斷目前的系統時區。此呼叫可能會受到全域互斥鎖的保護，導致競爭。

可以使用命令列中的 --default-time-zone 選項，在啟動時明確指定初始的全域伺服器時區值，或者您可以在選項檔案中使用以下行
```
default-time-zone='timezone'
```
如果您擁有 SYSTEM_VARIABLES_ADMIN 權限 (或已棄用的 SUPER 權限)，您可以使用以下陳述式在執行階段設定全域伺服器時區值
```
SET GLOBAL time_zone = timezone;
```
每個連線的用戶端都有自己的會話時區設定，由會話 time_zone 變數指定。最初，會話變數的值取自全域 time_zone 變數，但用戶端可以使用以下陳述式變更自己的時區
```
SET time_zone = timezone;
```

會話時區設定會影響對時區敏感的時間值的顯示和儲存。這包括諸如 NOW() 或 CURTIME() 等函式所顯示的值，以及儲存在 TIMESTAMP 欄位中並從中檢索的值。TIMESTAMP 欄位的值會從會話時區轉換為 UTC 進行儲存，並從 UTC 轉換為會話時區進行檢索。

會話時區設定不會影響諸如 UTC_TIMESTAMP() 等函式所顯示的值，也不會影響 DATE、TIME 或 DATETIME 欄位中的值。這些資料類型的值也不會儲存為 UTC；時區僅在從 TIMESTAMP 值轉換時套用。如果想要對 DATE、TIME 或 DATETIME 值進行與地區相關的運算，請將它們轉換為 UTC，執行運算，然後再轉換回來。

可以使用以下方式檢索目前的全域和會話時區值

SELECT @@GLOBAL.time_zone, @@SESSION.time_zone;

timezone 值可以使用多種格式給定，所有格式都不區分大小寫

值為 'SYSTEM'，表示伺服器時區與系統時區相同。
以字串形式表示與 UTC 的偏移量，格式為 [H]H:MM，帶有 + 或 - 前綴，例如 '+10:00'、'-6:00' 或 '+05:30'。小時值小於 10 時，可以選擇性使用前導零；MySQL 在儲存和檢索此類情況下的值時會加上前導零。MySQL 會將 '-00:00' 或 '-0:00' 轉換為 '+00:00'。
此值必須在 '-13:59' 到 '+14:00' 的範圍內（含）。
以具名時區的形式，例如 'Europe/Helsinki'、'US/Eastern'、'MET' 或 'UTC'。
注意

只有在 mysql 資料庫中的時區資訊表已建立並填入資料時，才能使用具名時區。否則，使用具名時區會導致錯誤
```
mysql> SET time_zone = 'UTC';
ERROR 1298 (HY000): Unknown or incorrect time zone: 'UTC'
```

填入時區表

mysql 系統結構描述中存在數個表，用於儲存時區資訊（請參閱第 7.3 節「mysql 系統結構描述」）。MySQL 安裝程序會建立時區表，但不會載入它們。若要手動執行此操作，請使用以下說明。

注意

載入時區資訊不一定是單次操作，因為資訊偶爾會變更。當發生此類變更時，使用舊規則的應用程式會過期，您可能會發現有必要重新載入時區表，以保持 MySQL 伺服器使用的資訊為最新狀態。請參閱與時區變更保持同步。

如果您的系統有自己的 zoneinfo 資料庫（一組描述時區的檔案），請使用 mysql_tzinfo_to_sql 程式來載入時區表。此類系統的範例包括 Linux、macOS、FreeBSD 和 Solaris。這些檔案的一個可能位置是 /usr/share/zoneinfo 目錄。如果您的系統沒有 zoneinfo 資料庫，您可以使用可下載的套件，如本節稍後所述。

若要從命令列載入時區表，請將 zoneinfo 目錄路徑名稱傳遞給 mysql_tzinfo_to_sql，並將輸出傳送到 mysql 程式。例如

mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -p mysql

這裡顯示的 mysql 命令假設您使用諸如 root 等具有修改 mysql 系統結構描述中表格的權限的帳戶連線到伺服器。請根據需要調整連線參數。

mysql_tzinfo_to_sql 會讀取您系統的時區檔案，並從這些檔案產生 SQL 陳述式。mysql 會處理這些陳述式以載入時區表。

mysql_tzinfo_to_sql 也可以用於載入單個時區檔案或產生閏秒資訊

若要載入對應於時區名稱 tz_name 的單個時區檔案 tz_file，請像這樣叫用 mysql_tzinfo_to_sql
```
mysql_tzinfo_to_sql tz_file tz_name | mysql -u root -p mysql
```
使用此方法，您必須執行單獨的命令來載入伺服器需要知道的每個具名時區的時區檔案。
如果您的時區必須考慮閏秒，請像這樣初始化閏秒資訊，其中 tz_file 是您的時區檔案的名稱
```
mysql_tzinfo_to_sql --leap tz_file | mysql -u root -p mysql
```

執行 mysql_tzinfo_to_sql 後，請重新啟動伺服器，使其不會繼續使用任何先前快取的時區資料。

如果您的系統沒有 zoneinfo 資料庫（例如，Windows），您可以使用 MySQL Developer Zone 上可下載的包含 SQL 陳述式的套件

https://mysqldev.dev.org.tw/downloads/timezones.html

警告

如果您的系統有 zoneinfo 資料庫，請勿使用可下載的時區套件。請改用 mysql_tzinfo_to_sql 公用程式。否則，您可能會導致 MySQL 與系統上的其他應用程式之間出現日期時間處理差異。

若要使用您已下載的 SQL 陳述式時區套件，請將其解壓縮，然後將解壓縮的檔案內容載入到時區表中

mysql -u root -p mysql < file_name

然後重新啟動伺服器。

警告

請勿使用包含 MyISAM 表格的可下載時區套件。這是為舊版 MySQL 設計的。MySQL 現在對時區表使用 InnoDB。嘗試將它們替換為 MyISAM 表格會導致問題。

與時區變更保持同步

當時區規則變更時，使用舊規則的應用程式會過期。若要保持最新狀態，必須確保您的系統使用最新的時區資訊。對於 MySQL，在保持最新狀態方面有多個因素需要考慮

如果 MySQL 伺服器的時區設定為 SYSTEM，則作業系統時間會影響 MySQL 伺服器用於時間的值。請確保您的作業系統使用最新的時區資訊。對於大多數作業系統，最新的更新或服務包會讓您的系統為時間變更做好準備。請查看您的作業系統供應商的網站，尋找解決時間變更的更新。
如果您將系統的 /etc/localtime 時區檔案替換為在 mysqld 啟動時生效的規則不同的版本，請重新啟動 mysqld，使其使用更新後的規則。否則，mysqld 可能不會注意到系統何時變更其時間。
如果您將具名時區與 MySQL 搭配使用，請確保 mysql 資料庫中的時區表為最新狀態
- 如果您的系統有自己的 zoneinfo 資料庫，請在每次更新 zoneinfo 資料庫時重新載入 MySQL 時區表。
- 對於沒有自己的 zoneinfo 資料庫的系統，請檢查 MySQL Developer Zone 以尋找更新。當有新的更新可用時，請下載它並使用它來替換您目前時區表的內容。
如需兩種方法的說明，請參閱填入時區表。mysqld 會快取其查閱的時區資訊，因此在更新時區表後，請重新啟動 mysqld，以確保它不會繼續提供過期的時區資料。

如果您不確定具名時區是否可用，以用作伺服器的時區設定或由設定自己時區的用戶端使用，請檢查您的時區表是否為空。以下查詢會判斷包含時區名稱的表格是否有任何列

mysql> SELECT COUNT(*) FROM mysql.time_zone_name;
+----------+
| COUNT(*) |
+----------+
|        0 |
+----------+

計數為零表示表格為空。在這種情況下，目前沒有任何應用程式使用具名時區，您無需更新表格（除非您想要啟用具名時區支援）。計數大於零表示表格不為空，並且其內容可用於具名時區支援。在這種情況下，請務必重新載入您的時區表，以便使用具名時區的應用程式可以取得正確的查詢結果。

為了檢查您的 MySQL 安裝是否已正確更新以應對日光節約時間規則的變更，請使用如下的測試。此範例使用的值適用於 2007 年美國在 3 月 11 日凌晨 2 點發生的 1 小時日光節約時間變更。

此測試使用以下查詢

SELECT
  CONVERT_TZ('2007-03-11 2:00:00','US/Eastern','US/Central') AS time1,
  CONVERT_TZ('2007-03-11 3:00:00','US/Eastern','US/Central') AS time2;

這兩個時間值表示日光節約時間變更發生的時間，並且使用具名的時區需要使用時區表。預期的結果是兩個查詢都返回相同的結果（輸入時間，轉換為 'US/Central' 時區中的等效值）。

在更新時區表之前，您會看到像這樣的不正確結果

+---------------------+---------------------+
| time1               | time2               |
+---------------------+---------------------+
| 2007-03-11 01:00:00 | 2007-03-11 02:00:00 |
+---------------------+---------------------+

更新表格後，您應該會看到正確的結果

+---------------------+---------------------+
| time1               | time2               |
+---------------------+---------------------+
| 2007-03-11 01:00:00 | 2007-03-11 01:00:00 |
+---------------------+---------------------+

時區閏秒支援

閏秒值會以結尾為 :59:59 的時間部分返回。這表示像是 NOW() 的函數在閏秒期間可能會在連續的兩到三秒返回相同的值。但時間部分結尾為 :59:60 或 :59:61 的時間文字值仍被視為無效。

如果需要搜尋早於閏秒一秒的 TIMESTAMP 值，如果您使用與 'YYYY-MM-DD hh:mm:ss' 值的比較，可能會得到異常的結果。以下範例說明了這一點。它將會話時區變更為 UTC，因此內部 TIMESTAMP 值（以 UTC 表示）與顯示的值（已套用時區校正）之間沒有差異。

mysql> CREATE TABLE t1 (
         a INT,
         ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
         PRIMARY KEY (ts)
       );
Query OK, 0 rows affected (0.01 sec)

mysql> -- change to UTC
mysql> SET time_zone = '+00:00';
Query OK, 0 rows affected (0.00 sec)

mysql> -- Simulate NOW() = '2008-12-31 23:59:59'
mysql> SET timestamp = 1230767999;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO t1 (a) VALUES (1);
Query OK, 1 row affected (0.00 sec)

mysql> -- Simulate NOW() = '2008-12-31 23:59:60'
mysql> SET timestamp = 1230768000;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO t1 (a) VALUES (2);
Query OK, 1 row affected (0.00 sec)

mysql> -- values differ internally but display the same
mysql> SELECT a, ts, UNIX_TIMESTAMP(ts) FROM t1;
+------+---------------------+--------------------+
| a    | ts                  | UNIX_TIMESTAMP(ts) |
+------+---------------------+--------------------+
|    1 | 2008-12-31 23:59:59 |         1230767999 |
|    2 | 2008-12-31 23:59:59 |         1230768000 |
+------+---------------------+--------------------+
2 rows in set (0.00 sec)

mysql> -- only the non-leap value matches
mysql> SELECT * FROM t1 WHERE ts = '2008-12-31 23:59:59';
+------+---------------------+
| a    | ts                  |
+------+---------------------+
|    1 | 2008-12-31 23:59:59 |
+------+---------------------+
1 row in set (0.00 sec)

mysql> -- the leap value with seconds=60 is invalid
mysql> SELECT * FROM t1 WHERE ts = '2008-12-31 23:59:60';
Empty set, 2 warnings (0.00 sec)

為了解決這個問題，您可以使用基於實際儲存在欄位中的 UTC 值的比較，該值已套用閏秒校正

mysql> -- selecting using UNIX_TIMESTAMP value return leap value
mysql> SELECT * FROM t1 WHERE UNIX_TIMESTAMP(ts) = 1230768000;
+------+---------------------+
| a    | ts                  |
+------+---------------------+
|    2 | 2008-12-31 23:59:59 |
+------+---------------------+
1 row in set (0.00 sec)