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

版本 8.4

9.0
8.0
5.7

8.0 日文

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

7.1.15 MySQL 伺服器時區支援

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

插入的 datetime 值也支援時區偏移；如需更多資訊，請參閱第 13.2.2 節，「DATE、DATETIME 和 TIMESTAMP 類型」。

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

時區變數

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 命令假設您使用具有修改 mysql 系統綱要中資料表權限的帳戶 (例如 root) 連線到伺服器。請根據需要調整連線參數。

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 開發人員專區下載的包含 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 開發人員專區是否有更新。當有新的更新可用時，請下載並使用它來取代您目前時區表的內容。
如需這兩種方法的指示，請參閱「填入時區表」。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)

上一步首頁上一層下一步