一文解決MySQL時區(qū)相關(guān)問題

前言：

在使用 MySQL 的過程中派阱，你可能會遇到時區(qū)相關(guān)問題阅懦，比如說時間顯示錯誤缨睡、時區(qū)不是東八區(qū)判呕、程序取得的時間和數(shù)據(jù)庫存儲的時間不一致等等問題饲帅。其實，這些問題都與數(shù)據(jù)庫時區(qū)設置有關(guān)，本篇文章將從數(shù)據(jù)庫參數(shù)入手朗鸠，逐步介紹時區(qū)相關(guān)內(nèi)容灯变。

1.log_timestamps 參數(shù)介紹

首先說明下log_timestamps參數(shù)并不影響時區(qū)炫刷，只是設置不同會影響某些日志記錄的時間淹魄。該參數(shù)主要是控制 error log、slow log水评、genera log 日志文件中的顯示時間猩系，但不會影響 general log 和 slow log 寫到表 (mysql.general_log, mysql.slow_log) 中的顯示時間。

log_timestamps 是全局參數(shù)中燥，可動態(tài)修改寇甸，默認使用 UTC 時區(qū)，這樣會使得日志中記錄的時間比北京時間慢 8 個小時疗涉，導致查看日志不方便拿霉。可以修改為 SYSTEM 變成使用系統(tǒng)時區(qū)咱扣。下面簡單測試下該參數(shù)的作用及修改方法：

# 查看參數(shù)值
mysql> show global variables like 'log_timestamps';
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| log_timestamps | UTC   |
+----------------+-------+
1 row in set (0.00 sec)

# 產(chǎn)生慢日志
mysql> select sleep(10),now();
+-----------+---------------------+
| sleep(10) | now()               |
+-----------+---------------------+
|         0 | 2020-06-24 17:12:40 |
+-----------+---------------------+
1 row in set (10.00 sec)

# 慢日志文件記錄內(nèi)容 發(fā)現(xiàn)時間是UTC時間
# Time: 2020-06-24T09:12:50.555348Z
# User@Host: root[root] @ localhost []  Id:    10
# Query_time: 10.000354  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 1
SET timestamp=1592989960;
select sleep(10),now();

# 修改參數(shù)值 再次測試
mysql> set global log_timestamps = SYSTEM;
Query OK, 0 rows affected (0.00 sec)

mysql> select sleep(10),now();
+-----------+---------------------+
| sleep(10) | now()               |
+-----------+---------------------+
|         0 | 2020-06-24 17:13:44 |
+-----------+---------------------+
1 row in set (10.00 sec)

# 慢日志文件記錄內(nèi)容 時間是對的
# Time: 2020-06-24T17:13:54.514413+08:00
# User@Host: root[root] @ localhost []  Id:    10
# Query_time: 10.000214  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 1
SET timestamp=1592990024;
select sleep(10),now();

2.time_zone 參數(shù)介紹

time_zone參數(shù)用來設置每個連接會話的時區(qū)绽淘，該參數(shù)分為全局和會話級別，可以動態(tài)修改闹伪。默認值為 SYSTEM沪铭，此時使用的是全局參數(shù) system_time_zone 的值，而 system_time_zone 默認繼承自當前系統(tǒng)的時區(qū)偏瓤，即默認情況下 MySQL 時區(qū)和系統(tǒng)時區(qū)相同杀怠。

時區(qū)設置主要影響時區(qū)敏感的時間值的顯示和存儲。包括一些函數(shù)(如 now()硼补、curtime())顯示的值驮肉，以及存儲在 TIMESTAMP 類型中的值，但不影響 DATE已骇、TIME 和 DATETIME 列中的值，因為這些數(shù)據(jù)類型在存取時未進行時區(qū)轉(zhuǎn)換票编，而 TIMESTAMP 類型存入數(shù)據(jù)庫的實際是 UTC 的時間褪储，查詢顯示時會根據(jù)具體的時區(qū)來顯示不同的時間。

下面我們來測試下 time_zone 參數(shù)修改產(chǎn)生的影響：

# 查看linux系統(tǒng)時間及時區(qū)
[root@centos ~]# date
Sun Jun 28 14:29:10 CST 2020

# 查看MySQL當前時區(qū)慧域、時間
mysql> show global variables like '%time_zone%';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone | CST    |
| time_zone        | SYSTEM |
+------------------+--------+
2 rows in set (0.00 sec)

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2020-06-28 14:31:12 |
+---------------------+
1 row in set (0.00 sec)

# 創(chuàng)建測試表鲤竹、插入部分數(shù)據(jù)
mysql> CREATE TABLE `time_zone_test` (
    ->   `id` int unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主鍵',
    ->   `dt_col` datetime DEFAULT NULL COMMENT 'datetime時間',
    ->   `ts_col` timestamp DEFAULT NULL COMMENT 'timestamp時間',
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COMMENT='time_zone測試表';
Query OK, 0 rows affected, 1 warning (0.07 sec)

mysql> insert into time_zone_test (dt_col,ts_col) values ('2020-06-01 17:30:00','2020-06-01 17:30:00'),(now(),now());
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from time_zone_test;
+----+---------------------+---------------------+
| id | dt_col              | ts_col              |
+----+---------------------+---------------------+
|  1 | 2020-06-01 17:30:00 | 2020-06-01 17:30:00 |
|  2 | 2020-06-28 14:34:55 | 2020-06-28 14:34:55 |
+----+---------------------+---------------------+

# 改為UTC時區(qū) 并重新連接 發(fā)現(xiàn)timestamp存儲的時間會隨時區(qū)變化
mysql> set global time_zone='+0:00';
Query OK, 0 rows affected (0.00 sec)
mysql> set  time_zone='+0:00';
Query OK, 0 rows affected (0.00 sec)

mysql> show global variables like '%time_zone%';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone | CST    |
| time_zone        | +00:00 |
+------------------+--------+
2 rows in set (0.00 sec)

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2020-06-28 06:36:16 |
+---------------------+
1 row in set (0.00 sec)

mysql> select * from time_zone_test;
+----+---------------------+---------------------+
| id | dt_col              | ts_col              |
+----+---------------------+---------------------+
|  1 | 2020-06-01 17:30:00 | 2020-06-01 09:30:00 |
|  2 | 2020-06-28 14:34:55 | 2020-06-28 06:34:55 |
+----+---------------------+---------------------+
2 rows in set (0.00 sec)

# 改回東八時區(qū)，恢復正常
mysql> set global time_zone='+8:00';
Query OK, 0 rows affected (0.00 sec)

mysql> set time_zone='+8:00';
Query OK, 0 rows affected (0.00 sec)

mysql> show global variables like '%time_zone%';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone | CST    |
| time_zone        | +08:00 |
+------------------+--------+
2 rows in set (0.00 sec)

mysql>  select now();
+---------------------+
| now()               |
+---------------------+
| 2020-06-28 14:39:14 |
+---------------------+
1 row in set (0.00 sec)

mysql> select * from time_zone_test;
+----+---------------------+---------------------+
| id | dt_col              | ts_col              |
+----+---------------------+---------------------+
|  1 | 2020-06-01 17:30:00 | 2020-06-01 17:30:00 |
|  2 | 2020-06-28 14:34:55 | 2020-06-28 14:34:55 |
+----+---------------------+---------------------+
2 rows in set (0.00 sec)

如果需要永久生效，還需寫入配置文件中辛藻。例如將時區(qū)改為東八區(qū)碘橘，則需要在配置文件[mysqld]部分增加一行：default_time_zone = '+8:00'。

3.時區(qū)常見問題及如何避免

時區(qū)設置不妥可能會產(chǎn)生各種問題吱肌，下面我們列舉下幾個常見的問題及解決方法：

3.1 MySQL 內(nèi)部時間不是北京時間

遇到這類問題痘拆，首先檢查下系統(tǒng)時間及時區(qū)是否正確，然后看下 MySQL 的 time_zone氮墨，建議將 time_zone 改為'+8:00'纺蛆。

3.2 Java 程序存取的時間與數(shù)據(jù)庫中的時間相差 8 小時

出現(xiàn)此問題的原因大概率是程序時區(qū)與數(shù)據(jù)庫時區(qū)不一致導致的。我們可以檢查下兩邊的時區(qū)规揪，如果想統(tǒng)一采用北京時間桥氏，則可以在 jdbc 連接串中增加 serverTimezone=Asia/Shanghai，并且 MySQL 方面也可以將 time_zone 改為'+8:00'猛铅。

3.3 程序時間與數(shù)據(jù)庫時間相差 13 小時或 14 小時

如果說相差 8 小時不夠讓人驚訝字支，那相差 13 小時可能會讓很多人摸不著頭腦。出現(xiàn)這個問題的原因是 JDBC 與 MySQL 對 “CST” 時區(qū)協(xié)商不一致奸忽。因為 CST 時區(qū)是一個很混亂的時區(qū)堕伪，有四種含義：

美國中部時間 Central Standard Time (USA) UTC-05:00 或 UTC-06:00
澳大利亞中部時間 Central Standard Time (Australia) UTC+09:30
中國標準時 China Standard Time UTC+08:00
古巴標準時 Cuba Standard Time UTC-04:00

MySQL 中，如果 time_zone 為默認的 SYSTEM 值月杉，則時區(qū)會繼承為系統(tǒng)時區(qū) CST刃跛，MySQL 內(nèi)部將其認為是 UTC+08:00。而 jdbc 會將 CST 認為是美國中部時間苛萎，這就導致會相差 13 小時桨昙，如果處在冬令時還會相差 14 個小時。

解決此問題的方法也很簡單腌歉，我們可以明確指定 MySQL 數(shù)據(jù)庫的時區(qū)蛙酪，不使用引發(fā)誤解的 CST，可以將 time_zone 改為'+8:00'翘盖，同時 jdbc 連接串中也可以增加 serverTimezone=Asia/Shanghai桂塞。

3.4 如何避免出現(xiàn)時區(qū)問題

如何避免上述時區(qū)問題，可能你心里也有了些方法馍驯，簡要總結(jié)幾點如下：

首先保證系統(tǒng)時區(qū)準確阁危。
jdbc 連接串中指定時區(qū)，并與數(shù)據(jù)庫時區(qū)一致汰瘫。
time_zone 參數(shù)建議設置為'+8:00'狂打，不使用容易誤解的 CST。
各環(huán)境數(shù)據(jù)庫實例時區(qū)參數(shù)保持相同混弥。

可能有的同學說了趴乡，我們數(shù)據(jù)庫中 time_zone 參數(shù)選擇的是默認的 SYSTEM 值，也沒有發(fā)生程序時間和數(shù)據(jù)庫時間不一致的問題。此時是否需要將 time_zone 改為'+8:00'晾捏？在這種情況下還是建議將 time_zone 改為'+8:00'蒿涎，特別是經(jīng)常查詢 TIMESTAMP 字段，因為當 time_zone=system 的時候惦辛，查詢 timestamp 字段會調(diào)用系統(tǒng)的時區(qū)做時區(qū)轉(zhuǎn)換劳秋，有全局鎖__libc_lock_lock 的保護，可能導致線程并發(fā)環(huán)境下系統(tǒng)性能受限裙品。而改為'+8:00'則不會觸發(fā)系統(tǒng)時區(qū)轉(zhuǎn)換俗批，使用 MySQL 自身轉(zhuǎn)換，大大提高了性能市怎。

總結(jié)：

讀完本篇文章岁忘，你是否對數(shù)據(jù)庫時區(qū)有了更深刻的認識呢。希望這篇文章對你有所幫助区匠，特別是想了解 MySQL 時區(qū)相關(guān)內(nèi)容時干像，可以拿來多讀讀。如果你遇到過其他時區(qū)相關(guān)問題驰弄，歡迎留言討論麻汰。