mysql解决时区相关问题

URML

2020-08-15

前言：

在使用 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)

如果需要永久生效，还需写入配置文件中。例如将时区改为东八区，则需要在配置文件[mysqld]部分增加一行：default_time_zone = '+8:00'。

3.时区常见问题及如何避免

时区设置不妥可能会产生各种问题，下面我们列举下几个常见的问题及解决方法：

3.1 MySQL 内部时间不是北京时间

遇到这类问题，首先检查下系统时间及时区是否正确，然后看下 MySQL 的 time_zone，建议将 time_zone 改为'+8:00'。

3.2 Java 程序存取的时间与数据库中的时间相差 8 小时

出现此问题的原因大概率是程序时区与数据库时区不一致导致的。我们可以检查下两边的时区，如果想统一采用北京时间，则可以在 jdbc 连接串中增加 serverTimezone=Asia/Shanghai，并且 MySQL 方面也可以将 time_zone 改为'+8:00'。

3.3 程序时间与数据库时间相差 13 小时或 14 小时

如果说相差 8 小时不够让人惊讶，那相差 13 小时可能会让很多人摸不着头脑。出现这个问题的原因是 JDBC 与 MySQL 对 “CST” 时区协商不一致。因为 CST 时区是一个很混乱的时区，有四种含义：

美国中部时间 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 值，则时区会继承为系统时区 CST，MySQL 内部将其认为是 UTC+08:00。而 jdbc 会将 CST 认为是美国中部时间，这就导致会相差 13 小时，如果处在冬令时还会相差 14 个小时。

解决此问题的方法也很简单，我们可以明确指定 MySQL 数据库的时区，不使用引发误解的 CST，可以将 time_zone 改为'+8:00'，同时 jdbc 连接串中也可以增加 serverTimezone=Asia/Shanghai。

3.4 如何避免出现时区问题

如何避免上述时区问题，可能你心里也有了些方法，简要总结几点如下：

首先保证系统时区准确。
jdbc 连接串中指定时区，并与数据库时区一致。
time_zone 参数建议设置为'+8:00'，不使用容易误解的 CST。
各环境数据库实例时区参数保持相同。

可能有的同学说了，我们数据库中 time_zone 参数选择的是默认的 SYSTEM 值，也没有发生程序时间和数据库时间不一致的问题。此时是否需要将 time_zone 改为'+8:00'？在这种情况下还是建议将 time_zone 改为'+8:00'，特别是经常查询 TIMESTAMP 字段，因为当 time_zone=system 的时候，查询 timestamp 字段会调用系统的时区做时区转换，有全局锁__libc_lock_lock 的保护，可能导致线程并发环境下系统性能受限。而改为'+8:00'则不会触发系统时区转换，使用 MySQL 自身转换，大大提高了性能。

总结：

读完本篇文章，你是否对数据库时区有了更深刻的认识呢。希望这篇文章对你有所帮助，特别是想了解 MySQL 时区相关内容时，可以拿来多读读。如果你遇到过其他时区相关问题，欢迎留言讨论。

mysql log mysql数据库

安科网

mysql解决时区相关问题

URML

URML

相关推荐

MySQL主从复制原理以及需要注意的地方

Mysql联表update数据的示例详解

mysql 8.0.22 安装配置图文教程

MySQL用truncate命令快速清空一个数据库中的所有表

修改MySQL8.0 默认的数据目录(快捷操作无配置)

阿里云服务器安装Mysql数据库的详细教程

MySQL中的undo日志

php7连接MySQL实现简易查询程序的方法

JDBC连接MySQL

Uber为什么放弃Postgres选择迁移到MySQL？

聊聊MySQL中的参数

shell脚本定时备份MySQL数据库数据并保留指定时间

Mysql误删数据解决方案及kill语句原理

MySQL 如何修改root用户的密码

通过实例解析MySql CURRENT_TIMESTAMP函数

mysql主从复制配置过程

Golang操作MySql数据库的完整步骤记录

MySQL数据类型优化原则

专业级的MySQL开发设计规范及SQL编写规范

Mysql 查询JSON结果的相关函数汇总

URML