1、问题再现

在撰写 飞书 API 2-5 时,需要新建一些数据表,以便实施从数据库到多维表的数据同步。
我建了2个测试数据表,连表查询之后,将时间戳转为时间格式返回,结果发现少了 8 小时。

具体逻辑抽象为以下,创建一个数据表

BEGIN;
-- 建库建表
create table if not exists user_orders(
  id             bigint       auto_increment    	comment '订单ID'
  ,user_id       bigint       not null    				comment '用户ID'
  ,production_id varchar(50)  not null     				comment '商品ID'
  ,paid_time     bigint       not null      			comment '用户付款时间'
  ,amount        bigint      						 					comment '订单付款金额/分'
  ,created_at    timestamp    not null default current_timestamp comment '创建时间'
  ,updated_at    timestamp    not null default current_timestamp on update current_timestamp comment '更新时间'
  ,primary key(id)
)engine=InnoDB comment '用户订单表';


-- 插入数据
insert into my_datas.user_orders(user_id,production_id,paid_time,amount) values
(1,10,1704095836,99900),
(2,10,1704195836,99900),
(3,10,1704295836,99900),
(4,12,1704395836,89900);
COMMIT;

然后查询数据(结果如下),直接看这个结果可能没有什么概念,找个在线的时间戳来做下对比。

SELECT from_unixtime(paid_time) aa,paid_time FROM user_orders LIMIT 100

image.png

拿第一个时间戳示例,可以发现 MySQL 中是“2024-01-01 07:57:16”,而实际上是“2024-01-01 15:57:16”少了 8 小时。
image.png

2、搜寻“解药”

首先我找了通义千问,它给了三个“药方”:

# 1、会话(本次会话):影响当前客户端的会话
set time_zone = '+08:00';
# 2、全局(本次服务):影响所有新连接到MySQL服务器的客户端
set global time_zone = '+08:00';
# 3、永久有效:my.ini 文件添加时区配置(如下)
default-time-zone='+08:00'

第一、二种是没问题,但是它们不能长期有效,当服务器重启的时候,便都失效了!于是尝试第三种。但是配置好了,也重启了,但是最终的结果并不符合预期,执行以下 SQL 代码,返回的都是 0 时区的,而没有北京时间。

show variables like 'time_zone';

image.png

一度怀疑是不是配置文件失效,于是做了个测试,修改 my.ini 文件中的一些配置,比如max_connections,一开始是 20,然后我把它改为 10,重启服务,修改前后的结果显示均和 my.ini 一致,所以说明文件没问题,是其他的问题导致的。

# 允许最大连接数
max_connections=20

修改后结果:

show variables like 'max_connections';

image.png

一度以为是default-time-zone='+08:00'格式有问题,但很快就打消了给想法,疑问,有问题的话,服务器连启动都会无法启动。

尝试百度查一查,发现来来回回基本上就是介绍这几种解决方案,似乎屡试不爽,从未失败,但是到我这里,第三种却不灵了,所以还得找自身的问题。

3、解决办法

后来看到一些资料,说在数据库数据文件路径(D:\MySQL\Database\)下有日志文件,于是到其下寻找 log 或 error 关键词的文件,看到好些 binlog 日志和一个 Learning.err。biglog 日志是二进制,肉眼看不出来,而 Learning.err 中没有记录到关于 time_zone 相关的错误。
所以 my.ini 的设置是 OK 的?

在该目录下,发现了2个配置文件:auto.cnf 和 mysqld-auto.cnf。
image.png

分别打开看看,auto.cnf 只有一个服务 ID,跟时区无关。
image.png

mysqld-auto.cnf 中有一个 time_zone 的值是 “+00:00”,难道是它?

{ "Version" : 1 , "mysql_server" : { "time_zone" : { "Value" : "+00:00" , "Metadata" : { "Timestamp" : 1612540350344832 , "User" : "root" , "Host" : "localhost" } } } }

image.png

赶紧修改为 “+08:00” 保存,重启服务器试试。
命令行重启 MySQL 服务器的方法如下

net stop mysql		# 关闭服务
net start mysql		# 启动服务

重新连接 MySQL 然后查询时区信息,发现已经修改为北京时间。
image.png

后来查了一些资料了解到,MySQL 数据库数据目录下的 mysqld-auto.cnf 文件是用来存储由 MySQL 系统自动设置并需要持久化的配置变量值。此文件在启动时最后被读取,它的设置会覆盖其他配置文件中相同变量的值,提供了一种高级别的配置优先级。
这个文件是在 MySQL 8.0 版本引入的一个特性,它与 auto.cnf 文件功能相似但更专门化。当使用SET PERSISTSET PERSIST_ONLY命令来改变某些系统变量的值时,这些变更会被写入到 mysqld-auto.cnf 文件中,以确保即使在 MySQL 服务重启之后,这些设置仍然生效。

这样做的好处是允许数据库管理员动态地更改某些配置,并且这些更改能够在数据库重启时保持不变,无需手动编辑传统的配置文件如 my.cnf 或 mysqld.cnf。这个文件增强了灵活性和管理效率,特别是在需要频繁调整配置或进行故障恢复的情况下。

4、小结

解决时区的方法有 4 种,分别对应不同的场景下的操作,其中最后一种是本次问题的唯一解法,解决了 my.ini 文件中一些配置“失效”的问题。

  • 会话(本次会话):影响当前客户端的会话。
    • 优点:不需要重启MySQL服务,缺点:临时策略,一旦MySQL服务被重启,设置就会消失。
set time_zone = '+08:00';
  • 全局(本次服务):影响所有新连接到MySQL服务器的客户端。
    • 优点:不需要重启MySQL服务,缺点:临时策略,一旦MySQL服务被重启,设置就会消失。
set global time_zone = '+08:00';
  • 服务器 my.ini 配置:长期策略
    • 优点:永久保存设置,缺点:需重启MySQL服务
default-time-zone='+08:00'
  • 数据库 mysqld-auto.cnf 配置:长期策略,在 my.ini 之上的配置,修改 time_zone 的值为 “+08:00”
"time_zone" : { "Value" : "+08:00" , 
  "Metadata" : { "Timestamp" : 1612540350344832 , "User" : "root" , "Host" : "localhost" }
}

点赞(0) 打赏

评论列表 共有 0 条评论

暂无评论

微信公众账号

微信扫一扫加关注

发表
评论
返回
顶部