当前位置：首页 > news >正文

MySQL 解决时区相关问题

news 来源：原创 2024/9/20 14:38:04

在使用 MySQL 的过程中，你可能会遇到时区相关问题，比如说时间显示错误、时区不是东八
区、程序取得的时间和数据库存储的时间不一致等等问题。其实，这些问题都与数据库时区设
置有关。
MySQL Server 中有 2 个环境变量和时区有关，你通过下述 SQL 语句能查看得到它俩的值：

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

不出意外的话，你看到的大概是类似如下内容：

+------------------+--------+
| Variable_name | Value |
+------------------+--------+
| system_time_zone | CST |
| time_zone | SYSTEM |
+------------------+--------+

还有一种小概率的可能，你的 system_time_zone 的值是空的。

system_time_zone 环境变量

它的值是 MySQL server 启动时，它自己去查询到的其所在服务器的系统时区。这个值是我们
无法影响、改变的，除非你去动你的操作系统时区设置。
如果你的 Linux 操作系统开启了 NTP 服务的话，这个时区应该是你的电脑硬件所位于的真实
的、物理时区。
通常情况下，我们看到的这个环境变量的值是 CST ：中国标准时间（即，UTC+08:00 ）。这个值也有可能为空，那就是你系统时区设置有问题。
但是 CST 有个问题：还有另外的三个时区（美国中部时间、澳大利亚中部时间、古巴标准时）缩写也叫 CST ，这就导致了你如果使用 CST 来代表你的时区的话，那么它有可能会引起歧义。简
单来说，你从『中国标准时间』这个说法可以“得到” CST ，但是你从 CST 不一定能反推出
『中国标准时间』这个说法。
system_time_zone 环境变量的值和我们未来使用 MySQL 并没有直接的关系，但是有间接关
系：它的值是 time_zone 环境变量的默认值。
对于你的 Linux 操作系统的时区，你可以通过如下命令查看得到：

# 时区显示的是 CST
date
# 时区显示的是 +0800
date -R

time_zone 环境变量

MySQL Server 通过这个环境变量实现了这样的一个功能：你可以无视 MySQL Server 实际所
在的时区，而强行认为、假想 MySQL Server 是在某个时区。此时，你的有关日期时间的操
作，都是基于这个你指定、假象的逻辑上的时区得到的数据。
在连上 MySQL Server 之后，执行如下命令，『强行认为』MySQL Server 位于东九区（日本
所在时区）：

mysql> set time_zone = '+9:00';

这个操作意味着：在此次连接/会话过程中，你认为 MySQL Server 是位于东九区的服务器上
的（而无视它真实所在地）。

提示:

当然，你也不必对 time_zone 畏之如虎，它并非对所有的日期时间操作都有影响。就我
们的日常操作而言，受它影响的主要是 now() / curtime() 函数和 TIMESTAMP 类型字
段。

now() 函数

在设置当前连接/会话的 time_zone ，即，指定你心里认为的 MySQL Server 所处于的时区之
后，执行如下命令向 MySQL Server “询问”当前时间：

mysql> select now(), curtime();

你会发现 MySQL Server “回复” 你的日期时间好像要比当下要快一个小时，这正是此时此刻
东九区（日本）的时间。

补充:

有三个 utc_X() 函数，它们永远返回的是当下的 UTC 时间，即格林威治时间（ +0:00
）：

mysql> select utc_date(), utc_time(), utc_timestamp();

timestamp 类型

timestamp 类型的数据也会受到 time_zone 的影响。
当你通过 insert sql 语句，要求 MySQL Server 在 timestamp 类型的字段中存储 12:00
时，MySQL Server 考虑到自己现在是在一台东九区的服务器上，所以它存储的时间本质上就
是 UTC 标准时 3:00 。
未来，你再通过 select sql 语句，从 MySQL Server 中查询这个 timestamp 类型的值时，
MySQL Server 如果发现自己是在一台东八区的服务器上，那么它返回给你的就是东八区
11:00 。
你会发现：你存进去的是 12:00 ，但是取出来的是 11:00 。
timestamp 类型的数据也会受到 time_zone 的影响，这也是不建议使用 timestamp 的原因之
一。

提示:

date、time 和 datetime 类型不似 timestamp，它们不受 time_zeon 所设置的时区影
响。它们的值是多少就是多少，不会出现 timestamp 这种存进去是这个值，取出来是那
个值的情况。

默认值 SYSTEM

大家也看到了，time_zone 环境变量的默认值是 SYSTEM ，它的含义是『 time_zone 的值与
system_time_zone 环境变量的值保持一致』。在之前的例子中，这个值就是 CST ，即，东八
区。
为了减少 MySQL Server 对于服务器环境的依赖，以及前面提到的 CST 会引起歧义的问题，
通常我们最好还是自己指定 time_zone 的值，将它固定成事实上的、MySQL Server 其所在的
时区。
之前也见过，使用如下设置可以临时改变 time_zone 的值

-- 对当前连接生效
mysql> set time_zone = '+8:00';
-- 对此后的所有新连接/会话生效，但是 MySQL Server 重启后失效。
mysql> set global time_zone = '+8:00';

想要一劳永逸地修改它，你需要改动 mysql 配置文件，在配置文件 [mysqld] 部分增加一行：

default_time_zone = '+8:00'

另外，当 time_zone=system 的时候，调用 now()，或查询 timestamp 字段会调用系统的时
区做时区转换，有全局锁 __libc_lock_lock 的保护，可能导致线程并发环境下系统性能受
限。而改为'+8:00'则不会触发系统时区转换，使用 MySQL 自身转换，大大提高了性能。这也
是为什么建议改以下 time_zone ，不要让它是 SYSTEM 的原因之一。

JDBC 中的 serverTimezone

JDBC 在 MySQL 8.0 本之后要求连接的 URL 中要求必须带上 serverTimezone 属性，而之前
这个属性是非必须的。
在之前的版本中，当我们的 URL 中没有带上 serverTimezone 时，我们的程序与 MySQL
Server 之间的连接/会话的 time_zone 就是 MySQL Server 中的配置（的默认值）所决定的。
现在我们的 URL 中带上 serverTimezone 之后，就由我们 URL 中的这个值决定了。
不过，理论上，这个值也就应该等于我们的程序要连上的 MySQL Server 那台服务器所在的真
实、物理时区。
相差 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。

总结

为了保证不出现存取的日期时间有偏差的情况，大家务必确保如下几点：
1. MySQL Server 所在的服务器的系统时区尽量是正确的、真实的、物理时区；
default_time_zone = '+8:00'
2. 无论服务器的系统时区是否是对的，MySQL Server 的 time_zone 的值要通过修改配置文件
的方式，一劳永逸地确定下来，保证是正确的、真实的、物理时区；
3. JDBC 的 URL 中现在规定必须要带上 serverTimezone 参数，这个参数的值必须是 MySQL
Server 的所在的真实时区，即与 2 中的 time_zone 一致。