mysql解决时区相关问题

前言:

在使用 MySQL 的过程中,你可能会遇到时区相关问题,比如说时间显示错误、时区不是东八区、程序取得的时间和数据库存储的时间不一致等等问题。其实,这些问题都与数据库时区设置有关,本篇文章将从数据库参数入手,逐步介绍时区相关内容。

1.log_timestamps 参数介绍

首先说明下log_timestamps参数并不影响时区,只是设置不同会影响某些日志记录的时间。该参数主要是控制 error log、slow log、genera log 日志文件中的显示时间,但不会影响 general log 和 slow log 写到表 (mysql.general_log, mysql.slow_log) 中的显示时间。

log_timestamps 是全局参数,可动态修改,默认使用 UTC 时区,这样会使得日志中记录的时间比北京时间慢 8 个小时,导致查看日志不方便。可以修改为 SYSTEM 变成使用系统时区。下面简单测试下该参数的作用及修改方法:

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

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

# 慢日志文件记录内容 发现时间是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();

# 修改参数值 再次测试
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)

# 慢日志文件记录内容 时间是对的
# 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 参数介绍

time_zone参数用来设置每个连接会话的时区,该参数分为全局和会话级别,可以动态修改。默认值为 SYSTEM,此时使用的是全局参数 system_time_zone 的值,而 system_time_zone 默认继承自当前系统的时区,即默认情况下 MySQL 时区和系统时区相同。

时区设置主要影响时区敏感的时间值的显示和存储。包括一些函数(如 now()、curtime())显示的值,以及存储在 TIMESTAMP 类型中的值,但不影响 DATE、TIME 和 DATETIME 列中的值,因为这些数据类型在存取时未进行时区转换,而 TIMESTAMP 类型存入数据库的实际是 UTC 的时间,查询显示时会根据具体的时区来显示不同的时间。

下面我们来测试下 time_zone 参数修改产生的影响:

# 查看linux系统时间及时区
[root@centos ~]# date
Sun Jun 28 14:29:10 CST 2020

# 查看MySQL当前时区、时间
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)

# 创建测试表、插入部分数据
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时区 并重新连接 发现timestamp存储的时间会随时区变化
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)

# 改回东八时区,恢复正常
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)

mysql解决时区相关问题

扫一扫手机访问