前言：
在使用 MySQL 的過程中，你可能會(huì)遇到時(shí)區(qū)相關(guān)問題，比如說時(shí)間顯示錯(cuò)誤、時(shí)區(qū)不是東八區(qū)、程序取得的時(shí)間和數(shù)據(jù)庫存儲(chǔ)的時(shí)間不一致等等問題。其實(shí)，這些問題都與數(shù)據(jù)庫時(shí)區(qū)設(shè)置有關(guān)，本篇文章將從數(shù)據(jù)庫參數(shù)入手，逐步介紹時(shí)區(qū)相關(guān)內(nèi)容。
1.log_timestamps 參數(shù)介紹
首先說明下log_timestamps參數(shù)并不影響時(shí)區(qū)，只是設(shè)置不同會(huì)影響某些日志記錄的時(shí)間。該參數(shù)主要是控制 error log、slow log、genera log 日志文件中的顯示時(shí)間，但不會(huì)影響 general log 和 slow log 寫到表 (mysql.general_log, mysql.slow_log) 中的顯示時(shí)間。
log_timestamps 是全局參數(shù)，可動(dòng)態(tài)修改，默認(rèn)使用 UTC 時(shí)區(qū)，這樣會(huì)使得日志中記錄的時(shí)間比北京時(shí)間慢 8 個(gè)小時(shí)，導(dǎo)致查看日志不方便。可以修改為 SYSTEM 變成使用系統(tǒng)時(shí)區(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)時(shí)間是UTC時(shí)間
# 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)容 時(shí)間是對(duì)的
# 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ù)用來設(shè)置每個(gè)連接會(huì)話的時(shí)區(qū)，該參數(shù)分為全局和會(huì)話級(jí)別，可以動(dòng)態(tài)修改。默認(rèn)值為 SYSTEM，此時(shí)使用的是全局參數(shù) system_time_zone 的值，而 system_time_zone 默認(rèn)繼承自當(dāng)前系統(tǒng)的時(shí)區(qū)，即默認(rèn)情況下 MySQL 時(shí)區(qū)和系統(tǒng)時(shí)區(qū)相同。
時(shí)區(qū)設(shè)置主要影響時(shí)區(qū)敏感的時(shí)間值的顯示和存儲(chǔ)。包括一些函數(shù)(如 now()、curtime())顯示的值，以及存儲(chǔ)在 TIMESTAMP 類型中的值，但不影響 DATE、TIME 和 DATETIME 列中的值，因?yàn)檫@些數(shù)據(jù)類型在存取時(shí)未進(jìn)行時(shí)區(qū)轉(zhuǎn)換，而 TIMESTAMP 類型存入數(shù)據(jù)庫的實(shí)際是 UTC 的時(shí)間，查詢顯示時(shí)會(huì)根據(jù)具體的時(shí)區(qū)來顯示不同的時(shí)間。
下面我們來測試下 time_zone 參數(shù)修改產(chǎn)生的影響：
# 查看linux系統(tǒng)時(shí)間及時(shí)區(qū)
[root@centos ~]# date
Sun Jun 28 14:29:10 CST 2020
# 查看MySQL當(dāng)前時(shí)區(qū)、時(shí)間
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)建測試表、插入部分?jǐn)?shù)據(jù)
mysql> CREATE TABLE `time_zone_test` (
-> `id` int unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主鍵',
-> `dt_col` datetime DEFAULT NULL COMMENT 'datetime時(shí)間',
-> `ts_col` timestamp DEFAULT NULL COMMENT 'timestamp時(shí)間',
-> 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時(shí)區(qū) 并重新連接 發(fā)現(xiàn)timestamp存儲(chǔ)的時(shí)間會(huì)隨時(shí)區(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)
# 改回東八時(shí)區(qū)，恢復(fù)正常
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)
如果需要永久生效，還需寫入配置文件中。例如將時(shí)區(qū)改為東八區(qū)，則需要在配置文件[mysqld]部分增加一行：default_time_zone = '+8:00'。
3.時(shí)區(qū)常見問題及如何避免
時(shí)區(qū)設(shè)置不妥可能會(huì)產(chǎn)生各種問題，下面我們列舉下幾個(gè)常見的問題及解決方法：
3.1 MySQL 內(nèi)部時(shí)間不是北京時(shí)間
遇到這類問題，首先檢查下系統(tǒng)時(shí)間及時(shí)區(qū)是否正確，然后看下 MySQL 的 time_zone，建議將 time_zone 改為'+8:00'。
3.2 Java 程序存取的時(shí)間與數(shù)據(jù)庫中的時(shí)間相差 8 小時(shí)
出現(xiàn)此問題的原因大概率是程序時(shí)區(qū)與數(shù)據(jù)庫時(shí)區(qū)不一致導(dǎo)致的。我們可以檢查下兩邊的時(shí)區(qū)，如果想統(tǒng)一采用北京時(shí)間，則可以在 jdbc 連接串中增加 serverTimezone=Asia/Shanghai，并且 MySQL 方面也可以將 time_zone 改為'+8:00'。
3.3 程序時(shí)間與數(shù)據(jù)庫時(shí)間相差 13 小時(shí)或 14 小時(shí)
如果說相差 8 小時(shí)不夠讓人驚訝，那相差 13 小時(shí)可能會(huì)讓很多人摸不著頭腦。出現(xiàn)這個(gè)問題的原因是 JDBC 與 MySQL 對(duì) “CST” 時(shí)區(qū)協(xié)商不一致。因?yàn)?CST 時(shí)區(qū)是一個(gè)很混亂的時(shí)區(qū)，有四種含義：
- 美國中部時(shí)間 Central Standard Time (USA) UTC-05:00 或 UTC-06:00
- 澳大利亞中部時(shí)間 Central Standard Time (Australia) UTC+09:30
- 中國標(biāo)準(zhǔn)時(shí) China Standard Time UTC+08:00
- 古巴標(biāo)準(zhǔn)時(shí) Cuba Standard Time UTC-04:00
MySQL 中，如果 time_zone 為默認(rèn)的 SYSTEM 值，則時(shí)區(qū)會(huì)繼承為系統(tǒng)時(shí)區(qū) CST，MySQL 內(nèi)部將其認(rèn)為是 UTC+08:00。而 jdbc 會(huì)將 CST 認(rèn)為是美國中部時(shí)間，這就導(dǎo)致會(huì)相差 13 小時(shí)，如果處在冬令時(shí)還會(huì)相差 14 個(gè)小時(shí)。
解決此問題的方法也很簡單，我們可以明確指定 MySQL 數(shù)據(jù)庫的時(shí)區(qū)，不使用引發(fā)誤解的 CST，可以將 time_zone 改為'+8:00'，同時(shí) jdbc 連接串中也可以增加 serverTimezone=Asia/Shanghai。
3.4 如何避免出現(xiàn)時(shí)區(qū)問題
如何避免上述時(shí)區(qū)問題，可能你心里也有了些方法，簡要總結(jié)幾點(diǎn)如下：
- 首先保證系統(tǒng)時(shí)區(qū)準(zhǔn)確。
- jdbc 連接串中指定時(shí)區(qū)，并與數(shù)據(jù)庫時(shí)區(qū)一致。
- time_zone 參數(shù)建議設(shè)置為'+8:00'，不使用容易誤解的 CST。
- 各環(huán)境數(shù)據(jù)庫實(shí)例時(shí)區(qū)參數(shù)保持相同。
可能有的同學(xué)說了，我們數(shù)據(jù)庫中 time_zone 參數(shù)選擇的是默認(rèn)的 SYSTEM 值，也沒有發(fā)生程序時(shí)間和數(shù)據(jù)庫時(shí)間不一致的問題。此時(shí)是否需要將 time_zone 改為'+8:00'？在這種情況下還是建議將 time_zone 改為'+8:00'，特別是經(jīng)常查詢 TIMESTAMP 字段，因?yàn)楫?dāng) time_zone=system 的時(shí)候，查詢 timestamp 字段會(huì)調(diào)用系統(tǒng)的時(shí)區(qū)做時(shí)區(qū)轉(zhuǎn)換，有全局鎖__libc_lock_lock 的保護(hù)，可能導(dǎo)致線程并發(fā)環(huán)境下系統(tǒng)性能受限。而改為'+8:00'則不會(huì)觸發(fā)系統(tǒng)時(shí)區(qū)轉(zhuǎn)換，使用 MySQL 自身轉(zhuǎn)換，大大提高了性能。
總結(jié)：
讀完本篇文章，你是否對(duì)數(shù)據(jù)庫時(shí)區(qū)有了更深刻的認(rèn)識(shí)呢。希望這篇文章對(duì)你有所幫助，特別是想了解 MySQL 時(shí)區(qū)相關(guān)內(nèi)容時(shí)，可以拿來多讀讀。如果你遇到過其他時(shí)區(qū)相關(guān)問題，歡迎留言討論。
以上就是mysql解決時(shí)區(qū)相關(guān)問題的詳細(xì)內(nèi)容，更多關(guān)于mysql時(shí)區(qū)相關(guān)問題的資料請(qǐng)關(guān)注腳本之家其它相關(guān)文章！
您可能感興趣的文章:- 關(guān)于Java中的mysql時(shí)區(qū)問題詳解
- MySQL查看和修改時(shí)區(qū)的方法
- MySQL修改時(shí)區(qū)的方法小結(jié)
- 詳解MySQL查詢時(shí)區(qū)分字符串中字母大小寫的方法
- mysql時(shí)區(qū)問題
- 解決MySQL8.0時(shí)區(qū)的問題步驟
