mysql解決時(shí)區(qū)相關(guān)問(wèn)題

# 查看參數(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ù)值 再次測(cè)試
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();

# 查看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)建測(cè)試表、插入部分?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測(cè)試表';
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)