mysql 与 time_zone

问题背景

业务产生了时间不一致的问题，数据相差的特征值很明显，八个小时，很容易就想到了是时区的问题

从中也了解到mysql 使用到的字段类型为timestamp的一些细节。翻阅了文档之后简单记录一下

程序中哪里有 time_zone

小小复习一下时间戳的定义: Unix 时间戳是从 1970 年 1 月 1 日（UTC/GMT 的午夜）开始所经过的秒数，不考虑闰秒。

所以时间戳是UTC时区的,在不同时区的程序中产生也是一致的。但时间字符串是需要根据时区来产生的，否则进行时区转换时就会产生差值问题

<?php
date_default_timezone_set('PRC');
var_dump(date("Y-m-d H:i:s"));
var_dump(time());


echo "\n";


date_default_timezone_set('UTC');
var_dump(date("Y-m-d H:i:s"));
var_dump(time());

string(19) "2022-12-29 22:30:32"
int(1672324232)

string(19) "2022-12-29 14:30:32"
int(1672324232)

mysql中的time_zone

mysql中的时区配置，跟字符编码的配置类似。

新手遇到乱码的问题必检查的三个点：

• 程序源文件编码
• mysql client(在程序中) 声明的编码
• mysql server的编码(存储)

时区配置也是类似一样的道理

• 程序使用时区(操作系统 -> 程序配置)
• mysql client声明的时区 (没有特别声明则用mysql配置文件中的默认全局时区)
• mysql server的存储时区

mysql server默认时区

--方法1：在启动命令中添加
mysqld --default-time-zone='+08:00' &

--方法2：在配置文件中添加
[mysqld]
default-time-zone='+08:00'

mysql 查询时区变量

show variables like '%time%zone%';

得到类似如下的结果

Variable_name	Value
system_time_zone	CST
time_zone	SYSTEM

‘SYSTEM’ 表明使用系统时间

相对于 UTC 时间的偏移，比如 ‘+08:00’ 或者 ‘-6:00’

某个时区的名字，比如 ‘Europe/Helsinki’，‘‘Asia/Shanghai’’ 或 ‘UTC’，

前提是已经把时区信息导入到了 mysql 库，否则会报错。

导入方法：mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -S /tmp/mysqld.sock mysql

mysql timestamp的存储

引用mysql官方文档原文

MySQL converts TIMESTAMP values from the current time zone to UTC for storage, and back from UTC to the current time zone for retrieval. (This does not occur for other types such as DATETIME.) By default, the current time zone for each connection is the server’s time. The time zone can be set on a per-connection basis. As long as the time zone setting remains constant, you get back the same value you store. If you store a TIMESTAMP value, and then change the time zone and retrieve the value, the retrieved value is different from the value you stored. This occurs because the same time zone was not used for conversion in both directions. The current time zone is available as the value of the time_zone system variable. For more information, see Section 5.1.15, “MySQL Server Time Zone Support”.

翻译为中文

MySQL将TIMESTAMP值从当前时区转换为UTC进行存储，并从UTC返回到当前时区进行检索。(默认情况下，每个连接的当前时区是服务器的时间。时区可以在每个连接的基础上设置。只要时区设置保持不变，你就会得到与你存储的相同的值。如果你存储一个TIMESTAMP值，然后改变时区并检索该值，检索到的值与你存储的值不同。出现这种情况是因为在两个方向的转换中没有使用相同的时区。当前的时区可以作为time_zone系统变量的值。欲了解更多信息，请参阅第5.1.15节 “MySQL服务器时区支持”。

可以看到高亮的几个字 转换为UTC进行存储

以下是我在业务场景中由于程序、mysql server之间配置的时区不一致导致的问题

• 程序和系统正确配置了 +8:00 中国时区
• mysql client没有特别声明, 使用的是mysql全局默认时区(UTC)

所以在程序插入数据时候，生成的insert into语句使用的是+8:00中国时区的时间字符串，但是mysql底层会转换为UTC的时间戳进行存储。

后续有一天运维将mysql配置调整成了中国时区，导致在查询的时候 会自动再次进行时区转换，所以查出来的时间比真实需要存储的时间多了8个小时

各位同学在进行开发如果使用到 NOW () 和 CURTIME () 系统函数或者timestamp 数据类型字段的时候还是务必小心检查一下时区的配置

由于mysql会自动转换存储UTC时间，查询的时候又自动转换 如果开发上线之前没有将时区配置一致，后续产生问题还是比较麻烦的（要么在开发上线之前调整一致，要么不能在中途又更改mysql server的时区 不然就会前后数据不一致 造成历史遗留数据问题）