一文解决MySQL时区相关问题

电玩女神 2023-02-22 12:09 13阅读 0赞

**前言：**

在使用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 如何避免出现时区问题**

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

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时区相关内容时，可以拿来多读读。如果你遇到过其他时区相关问题，欢迎留言讨论。

![wx\_blog.png][wx_blog.png]

[wx_blog.png]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9jZG4ubmxhcmsuY29tL3l1cXVlLzAvMjAyMC9wbmcvMTE5NTM3LzE1ODk0NDUxNTUwNjgtNjE3N2NiYjQtNzkyMS00YzIzLWIwNDAtN2Y3NjhlYzNhZTA3LnBuZw?x-oss-process=image/format,png