个人中心
gpt4 book ai didi

mysql解决时区相关问题

转载 作者：qq735679552 更新时间：2022-09-29 22:32:09 28 4
gpt4 key购买 nike

CFSDN坚持开源创造价值，我们致力于搭建一个资源共享平台，让每一个IT人在这里找到属于你的精彩世界.

这篇CFSDN的博客文章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 变成使用系统时区。下面简单测试下该参数的作用及修改方法:

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
# 查看参数值
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 参数修改产生的影响:

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
# 查看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 如何避免出现时区问题 。

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

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

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

总结:

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

以上就是mysql解决时区相关问题的详细内容，更多关于mysql时区相关问题的资料请关注我其它相关文章！ 。

原文链接：https://cloud.tencent.com/developer/article/1655945 。

最后此篇关于mysql解决时区相关问题的文章就讲到这里了,如果你想了解更多关于mysql解决时区相关问题的内容请搜索CFSDN的文章或继续浏览相关文章，希望大家以后支持我的博客！ 。

28 4 0
文章推荐： python实战之实现excel读取、统计、写入的示例讲解
文章推荐： MySQL8.0内存相关参数总结
文章推荐： python向已存在的excel中新增表,不覆盖原数据的实例
文章推荐： 高防服务器防御的原理详解
qq735679552
个人简介

我是一名优秀的程序员,十分优秀！

滴滴打车优惠券免费领取
滴滴打车优惠券
全站热门文章
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com