I love you three thousand

# MySQL 解决时区相关问题

在使用 MySQL 的过程中，你可能会遇到时区相关问题，比如说时间显示错误、时区不是东八区、程序取得的时间和数据库存储的时间不一致等等问题。其实，这些问题都与数据库时区设置有关。

# 1. GMT 时间和 UTC 时间

GMT 时间是格林威治平时，也称格林威治时间。它规定太阳每天经过位于英国伦敦郊区的皇家格林威治天文台的时间为中午 12 点。格林威治天文台位于本初子午线上。

我们常说的『时区』（例如东八区，表示为 +8:00）就是『相对于』格林威治时间。

GMT 时间是『真实世界中的标准时』。

由于地球轨道并非圆形，其运行速度又随着地球与太阳的距离改变而出现变化，因此 GMT 时间欠缺均匀性。这种非均匀性对于日常生活而言影响不大，但是对于时间粒度更细、要求更严格的计算机和通信领域而言，就十分不方便。

为此，国际无线电咨询委员会在 1960 年规范统一了 UTC 的概念，并在次年投入实际使用。UTC 时间相当于是一个均匀的、理想化的 GMT 时间。

相当于在 UTC 的世界中，它假定了地球围绕太阳进行严格地匀速公转和自转。

在 UTC 的概念提出和广泛传播后，GMT 时间就慢慢地不再是世界的标准，取而代之的就是 UTC 时间。

由于 UTC 时间是一个理想的、理论化的时间，它比我们现实中的时间（即 GMT 时间）要快。国际无线电咨询委员会又规定 UTC 时间相较于 GMT 时间而言，累计不超过 1 秒。

当 UTC 累计领先 GMT 时间 1 秒之后，UTC 时间会『停顿』1 秒，从而达到 UTC 时间和 GMT 时间的再次统一。周而复始 ...

UTC 停顿的这 1 秒被称为『时钟回调』。即，相当于将 UTC 时钟回拨 1 秒，从而与 GMT 一致。

# 2. MySQL 中的时区

读取日期时间时的自动转换。

show variables like '%time_zone%';

  • time_zone 说明 MySQL 使用 system 的时区，即，MySQL Server 所在的服务器的系统的时区。

  • system_time_zone 说明 system 使用 CST 时区。这个值有可能为空。

# time_zone 参数介绍

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

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

  • 查看 linux 系统时间及时区

    > date

  • 查看 MySQL 当前时区、时间

    mysql> show global variables like '%time_zone%';

  • 验证

    select now();
set global time_zone = '+4:00'; -- 或
set time_zone = '+4:00';
select now();

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

default_time_zone = '+8:00'

# 时区常见问题及如何避免

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

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

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

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

serverTimeZone 的作用就是指定 web 服务器和 mysql 服务器的会话期间的 mysql 服务器时区，就是临时指定 mysql 服务器的时区。

# 程序时间与数据库时间相差 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。

# 如何避免出现时区问题

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

  • 首先保证操作系统系统时区准确。
  • 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 自身转换，大大提高了性能。

← 素材 1 →