時區資訊記錄表|全方位認識 mysql 系統庫

# 使用系統自帶的時區資料集檔案來填充MySQL 時區表（一次載入作業系統支援的所有時區），mysql_tzinfo_to_sql命令會讀取您系統的時區檔案並生成SQL語句來插入到MySQL的時區表中。注意：這種方式不會匯入跳秒資訊到time_zone_leap_second表中，需要單獨操作
[root@localhost ~]# mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql -pletsg0
mysql: [Warning] Using a password on the command line interface can be insecure.
Warning: Unable to load '/usr/share/zoneinfo/iso3166.tab' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/zone.tab' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/zone1970.tab' as time zone. Skipping it.
# mysql_tzinfo_to_sql也可用於載入單個時區檔案或生成閏秒資訊：
## 載入單個時區檔案，格式為：mysql_tzinfo_to_sql tz_file tz_name | mysql -u root mysql ，如下
[root@localhost ~]# mysql_tzinfo_to_sql /usr/share/zoneinfo/Asia/Shanghai Asia/Shanghai | mysql -u root mysql -pletsg0 
## 如果您的時區需要考慮閏秒（跳秒），命令如下，其中tz_file是您的時區檔案的名稱（絕對路徑，要注意：匯入跳秒資訊的時區必須要是使用了跳秒的時區，即，在time_zone表中的use_leap_second欄位為Y才有跳秒資訊可匯入，否則該表中的資訊為空）：
shell> mysql_tzinfo_to_sql --leap tz_file | mysql -u root mysql ，例如：
[root@localhost ~]# mysql_tzinfo_to_sql --leap /usr/share/zoneinfo/right/US/Arizona | mysql -u root mysql -pletsg0 
## 注意：執行mysql_tzinfo_to_sql之後，最好重新啟動伺服器，以便使得Server使用新的時區資料，以免它不會繼續使用任何先前快取的時區資料。
# 如果您的系統沒有zoneinfo資料庫（例如Windows），那麼您可以使用可在MySQL Developer Zone上下載的軟體包，連結如下：http://dev.mysql.com/downloads/timezones.html
## 下載完成之後匯入該檔案到資料庫中，然後重啟Server即可
shell> mysql -u root mysql <file_name
## 注意：不要使用包含MyISAM表的可下載軟體包。MySQL 5.7及更高版本使用InnoDB作為時區表。嘗試用MyISAM表替換它們會導致問題。如果您的系統有zoneinfo資料庫，請不要自行下載軟體包。改用mysql_tzinfo_to_sql工具直接匯入系統中的時區資料集。否則，您可能會在MySQL和系統上的其他應用程式之間的日期時間處理方面產生差異。

2.1. time_zone

該表提供查詢時區ID和跳秒之間的對映關係資料。如何填充時區表（mysql 系統字典庫下有時區相關的表time_zone、time_zone_leap_second、time_zone_name、time_zone_transition、time_zone_transition_type，這些表是在MySQL初始化時建立，但不會載入資料到這些表中）：

下面是該表中儲存的資訊內容（需要手工匯入時區資料資訊到資料庫才有資料）。

root@localhost : mysql 04:51:37> select * from time_zone limit 1186,2;
+--------------+------------------+
| Time_zone_id | Use_leap_seconds |
+--------------+------------------+
| 2373 | N |
| 2375 | Y |
+--------------+------------------+
2 rows in set (0.01 sec)

表欄位含義。

• Time_zone_id： 時區ID。

• Use_leap_seconds： 表示該時區是否使用了跳秒(GPS原子鐘時間和UTC時間之差，因為兩個時間系統的秒長不一樣，也就是我們所說的時間尺度不一樣，最終隨著時間的累積，兩者之間就會差。而世界協調時為了協調人們生活中的時間，就採用了跳秒的辦法來處理這種差異。目前兩者之差為15秒)。

2.2. time_zone_leap_second

該表提供查詢跳秒機器修正值資訊，該表中的資訊與time_zone_transition表中的類似，但time_zone_transition表中還記錄了時區ID等資訊。

下面是該表中儲存的資訊內容。

root@localhost : mysql 04:51:44> select * from time_zone_leap_second;
+-----------------+------------+
| Transition_time | Correction |
+-----------------+------------+
| 78796800 | 1 |
| 94694401 | 2 |
......

表欄位含義。

• Transition_time： 跳秒的瞬變時間(表示UTC和GPS時間的差異 ？

• Correction： 跳秒的修正值。

2.3. time_zone_name

該表提供查詢時區的名稱列表和時區ID的對映關係。

下面是該表 中儲存的資訊內容。

root@localhost : mysql 04:53:13> select * from time_zone_name limit 2;
+----------------+--------------+
| Name | Time_zone_id |
+----------------+--------------+
| Africa/Abidjan | 1 |
| Africa/Accra | 3 |
+----------------+--------------+
2 rows in set (0.00 sec)

表欄位含義。

• Name： 時區名稱，該值為time_zone系統變數的有效值之一。

• Time_zone_id： 時區ID，該ID和表time_zone中的ID相對應。

2.4. time_zone_transition

該表提供查詢時區的跳秒資料。

下面是該表中儲存的資訊內容。

root@localhost : mysql 04:59:54> select * from time_zone_transition limit 2;
+--------------+-----------------+--------------------+
| Time_zone_id | Transition_time | Transition_type_id |
+--------------+-----------------+--------------------+
| 1 | -1830383032 | 1 |
| 3 | -1640995148 | 2 |
+--------------+-----------------+--------------------+
2 rows in set (0.00 sec)

表欄位含義。

• Time_zone_id： 時區ID。

• Transition_time： 與time_zone_leap_second表中的Transition_time欄位含義相同。

• Transition_type_id： 與time_zone_transition_type表中的Transition_type_id相對應。

2.5. time_zone_transition_type

該表提供查詢具體的跳秒資訊以及與時區的對應資料。

下面是該表中儲存的資訊內容。

root@localhost : mysql 05:14:59> select * from time_zone_transition_type limit 2;
+--------------+--------------------+--------+--------+--------------+
| Time_zone_id | Transition_type_id | Offset | Is_DST | Abbreviation |
+--------------+--------------------+--------+--------+--------------+
| 1 | 0 | -968 | 0 | LMT |
| 1 | 1 | 0 | 0 | GMT |
+--------------+--------------------+--------+--------+--------------+
2 rows in set (0.00 sec)

表欄位含義。

• Time_zone_id： 時區ID。

• Transition_type_id： 與time_zone_transition表中的Transition_type_id值對應。

• Offset： 與UTC時間之間的偏移量。

• Is_DST： ？

• Abbreviation： 某某標準時間的縮寫，例如：GMT，該值為time_zone系統變數的有效值之一。

本期內容就介紹到這裡，本期內容參考連結如下：

https://dev.mysql.com/doc/refman/5.7/en/time-zone-support.html

"翻過這座山，你就可以看到一片海！"。堅持閱讀我們的"全方位認識 mysql 系統庫"系列文章分享，你就可以系統地學完它。謝謝你的閱讀，我們下期不見不散！

| 作者簡介

羅小波·沃趣科技高階資料庫技術專家