MySQL数据库时区不一致怎么办?服务器时间与数据库时间配置详解

"订单时间比实际早了 8 小时""定时任务该在凌晨 2 点跑,结果下午 2 点才触发""日志里应用打的时间和数据库里的时间对不上,排查问题全靠猜"——这些现象几乎都能归结到一个隐蔽但影响巨大的配置:时区。时区不像字符集那样会立刻报乱码,它通常"看起来正常",直到某天你对账、跑批、做统计时,才发现数据里的时间整体偏移了几个小时,而这时错误数据已经积累了很久。

MySQL 的时区涉及好几层:操作系统的时区、MySQL 服务器的 time_zone 参数、连接会话的 time_zone、以及列类型 TIMESTAMP 和 DATETIME 的不同行为。最坑的是 TIMESTAMP 会随时区变化而"自动转换",而 DATETIME 不会——很多人根本不知道手上这张表的列是哪种类型,于是"我明明存对了"和"读出来不对"就同时成立了。

这篇文章会按"先定位、再理解、后治理"的顺序,把服务器时间与数据库时间的配置讲清楚:操作系统时区怎么设、MySQL 的 time_zone 和 system_time_zone 怎么区分、TIMESTAMP 与 DATETIME 的取舍、NOW()/CURRENT_TIMESTAMP 的行为、以及跨时区业务的统一方案。改时区同样属于会影响数据语义的操作,文中会给出风险提示和回滚方式。

一、先把每一层的时间都查出来

排查时区问题,第一步是列出所有相关的时间值,一处一处对比。先看操作系统:

# 查看当前系统时间与时区
date
timedatectl status            # systemd 系统(主流发行版均支持)

# 输出关注三行:
#      Local time: ...
#  Universal time: ...        <- UTC 时间
#    Time zone: Asia/Shanghai (CST, +0800)

再看 MySQL:

-- 服务器时区(通常继承自操作系统)与会话时区
SELECT @@global.time_zone, @@session.time_zone, @@system_time_zone;

-- 三者含义:
-- system_time_zone : 系统时区,MySQL 启动时从操作系统读取,运行期不可改(CST/UTC/+08:00 等)
-- global.time_zone : 全局时区,默认常为 SYSTEM,即跟随系统
-- session.time_zone: 会话时区,可被客户端单独设置

SELECT NOW(), UTC_TIMESTAMP(), CURRENT_TIMESTAMP;
-- NOW() / CURRENT_TIMESTAMP 返回的是"会话时区"下的当前时间
-- UTC_TIMESTAMP() 永远返回 UTC,可用于对照判断偏移量

SELECT @@global.system_time_zone, @@global.time_zone;

把这三组值放在一起看,通常就能定位问题:

现象 可能原因
NOW() 与系统 date 相差整 8 小时 会话时区与系统时区不一致
应用写入后读出的 TIMESTAMP 偏移 写入侧与读取侧会话时区不同
UTC_TIMESTAMP() 与真实 UTC 一致,但 NOW() 不对 会话 time_zone 没设对
定时任务触发时间偏移 应用/JVM 时区或数据库会话时区错位
迁移后时间整体变了 源库与目标库时区不同,TIMESTAMP 被转换

二、TIMESTAMP 和 DATETIME:为什么同一个值会"变"

这是理解时区问题最关键的一点,务必分清。

DATETIME 存的是"字面时间",不做任何时区转换。你写进去 2027-01-01 10:00:00,读出来就是 2027-01-01 10:00:00,与服务器、会话的时区无关。它就像一张写着时间的纸条。

TIMESTAMP 存的是"UTC 时间戳",会随会话时区换算。写入时,MySQL 把当前会话时区下的时间转换成 UTC 存起来(内部占 4 字节,范围到 2038 年);读取时,再按当前会话时区转换回本地时间显示。所以同一个时间戳,在不同时区的会话里读出来是不同的"字面时间"。

对比项 DATETIME TIMESTAMP
存储本质 字面日期时间 UTC 时间戳
时区转换 否 是,随会话时区变化
存储空间 5 字节(8.0 起 5 字节,5.6 前 8 字节) 4 字节
时间范围 1000-01-01 ~ 9999-12-31 1970 ~ 2038 年(2038 问题)
自动初始化 支持 DEFAULT / ON UPDATE(5.6+) 支持 DEFAULT / ON UPDATE
适用场景 业务时间、跨时区不敏感、需超范围 记录"绝对时刻"、自动时间戳

理解了这一点,"我存对了但读出来不对"就说得通了:如果列是 TIMESTAMP,而写入会话时区是 +08:00、读取会话时区是 +00:00,那读出来的时间就会比写入时早 8 小时——数据本身没坏,是"显示时区"变了。

三、全局配置:让三层时区保持一致

治理时区问题的核心思路是:明确一个基准时区,让操作系统、MySQL 全局、应用连接会话三层都对齐。

操作系统层: 用 timedatectl 设置(RHEL/CentOS/Rocky 与 Debian/Ubuntu 通用):

# 查看可用时区
timedatectl list-timezones | grep -i shanghai

# 设置为中国标准时间
sudo timedatectl set-timezone Asia/Shanghai

# 确认
timedatectl status

# 老系统没有 timedatectl 时,可用软链接方式
sudo ln -sf /usr/share/zoneinfo/Asia/Shanghai /etc/localtime
sudo hwclock --systohc      # 把系统时间写回硬件时钟

MySQL 层: 在配置文件里设置全局时区。

# 配置位置:
# RHEL/CentOS/Rocky: /etc/my.cnf
# Debian/Ubuntu:     /etc/mysql/mysql.conf.d/mysqld.cnf
vi /etc/my.cnf
[mysqld]
# 推荐方式之一:跟随系统(需保证操作系统时区已正确设置)
default-time-zone = '+08:00'
# 也可写 'Asia/Shanghai',但这要求 MySQL 已加载时区表(见下一节)

改完重启生效(风险提示:重启中断连接,低峰操作;改前备份配置):

# RHEL/CentOS/Rocky
sudo systemctl restart mysqld
# Debian/Ubuntu
sudo systemctl restart mysql

也可以用 SQL 动态设置,无需重启:

-- 全局生效(新会话),重启后失效
SET GLOBAL time_zone = '+08:00';

-- 仅当前会话生效
SET time_zone = '+08:00';

-- MySQL 8.0 可持久化
SET PERSIST time_zone = '+08:00';

应用会话层: 在连接串或连接池里指定时区,最稳妥。示例:

# JDBC
jdbc:mysql://127.0.0.1:3306/db?serverTimezone=Asia/Shanghai

# 或在连接建立后执行
SET time_zone = '+08:00';

四、命名时区与 'Asia/Shanghai' 的时区表

用 '+08:00' 这种偏移量最省事,但有个缺点:它不处理夏令时。中国不用夏令时影响不大,但对跨时区业务(比如服务器在海外、用户遍布多国)就有隐患。要用 'Asia/Shanghai' 这类命名时区,必须先让 MySQL 加载时区表。

先检查是否已加载:

SELECT COUNT(*) FROM mysql.time_zone_name;
-- 返回 0 表示未加载,此时设置 SET time_zone='Asia/Shanghai' 会报错
-- ERROR 1298 (HY000): Unknown or incorrect time zone: 'Asia/Shanghai'

加载时区表有几种方式:

# 方式一:如果系统时区数据已存在(多数 Linux 自带 /usr/share/zoneinfo)
mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -p mysql

# 方式二:导入官方下载的时区 SQL 包
# 下载后(主流发行版或官方提供的 tz 数据),导入 mysql 库
mysql -u root -p mysql < timezone_posix.sql

# 验证
mysql -u root -p -e "SELECT COUNT(*) FROM mysql.time_zone_name;"

导入后就能安全使用命名时区:

SET GLOBAL time_zone = 'Asia/Shanghai';

风险提示:加载时区表会向 mysql 系统库写入数据,建议先对 mysql 库做备份;在生产库上操作前最好在测试环境验证。命名时区比固定偏移更"聪明"(自动处理夏令时),跨时区业务优先用它。

五、跨时区业务怎么统一

如果业务只服务国内用户,最简单的方案是:把操作系统、MySQL 全局、应用会话全部统一为 +08:00,存储用 DATETIME,前后端都按北京时间处理。这样最省心,也不涉及转换。

如果业务跨时区(用户分布多国),推荐"存储统一 UTC,展示按用户时区转换"的方案:

  • 数据库全局时区设为 +00:00(UTC)。
  • 列类型用 TIMESTAMP(自动 UTC 存储)或 DATETIME 明确声明为 UTC 语义。
  • 应用层负责转换:入库前转 UTC,出库后按用户所在时区转换展示。
  • 日志、监控、告警也统一用 UTC,避免排查时来回换算。

两种策略对比如下:

策略 存储时区 列类型建议 适用场景 复杂度
统一北京时间 +08:00 DATETIME 只服务国内用户 低
统一 UTC + 前端转换 +00:00 TIMESTAMP / UTC DATETIME 跨时区、跨国业务 中

关键在于"选定一种,全链路贯彻"。最容易出事的是混用:有的表存北京时间、有的存 UTC,有的列是 TIMESTAMP、有的是 DATETIME,加上各服务时区还不一致,最后没人说得清一个时间到底是哪个时区。

六、注意事项与迁移

注意一:DATETIME 不会因为改时区而变,TIMESTAMP 会。 所以改全局时区前,先确认哪些表用了 TIMESTAMP——改时区会让这些列在显示上整体偏移。查一下:

SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, DATA_TYPE
FROM information_schema.COLUMNS
WHERE DATA_TYPE IN ('timestamp', 'datetime')
ORDER BY TABLE_SCHEMA, TABLE_NAME;

注意二:TIMESTAMP 有 2038 问题。 2038 年 1 月 19 日之后,4 字节存不下,会溢出。长期系统建议用 DATETIME 存业务时间,或关注 MySQL 后续版本的解决方案(8.0 部分场景已扩展支持)。

注意三:NOW() 与会话时区绑定,UTC_TIMESTAMP() 永远 UTC。 写"当前时间"这类逻辑时要想清楚用哪个,并确保写入和读取用的是同一套时区基准。

注意四:连接池可能覆盖会话时区。 即使 MySQL 全局设了 +08:00,如果连接串显式指定了别的时区,会话仍以其为准。要在应用层确认,而不是假设"数据库设了就行"。

迁移场景的风险控制:

如果需要把已有数据从一种时区口径迁到另一种(比如从"存北京时间"改成"存 UTC"),不要直接 UPDATE 全表时间字段,那样一旦算错就全表污染。正确做法:

# 1) 全量备份
mysqldump -u root -p --single-transaction --databases your_db > /backup/your_db_before_tz.sql

# 2) 在测试库上演练转换脚本,核对结果

# 3) 确认无误后,低峰执行,并保留回滚点(binlog 位置或备份)
-- 示例:把 DATETIME 列按 +8 转为 UTC 口径(仅示意,务必先验证)
-- 先看影响行数
SELECT COUNT(*) FROM t_order WHERE created_at IS NOT NULL;

-- 转换(低峰、事务内、可回滚)
START TRANSACTION;
UPDATE t_order SET created_at = CONVERT_TZ(created_at, '+08:00', '+00:00');
-- 核对无误
COMMIT;
-- 有问题则 ROLLBACK

风险提示:UPDATE 大批量数据会造成大量 binlog、锁竞争和 IO 压力,可能影响线上。务必分批、低峰、先备份、可回滚。CONVERT_TZ() 依赖时区表已加载。

常见问题

Q1:@@global.time_zone 显示 SYSTEM 是什么意思?
表示 MySQL 全局时区跟随操作系统时区(@@system_time_zone)。这本身没问题,只要操作系统时区正确即可。但如果有的服务器系统时区不同,就会出现"多台机器时间不一致",所以生产环境更推荐显式设置为固定值。

Q2:应用和数据库时间差了 8 小时,一定是数据库的问题吗?
不一定。要看是写入侧还是读取侧。JDBC 的 serverTimezone 没设、JVM 默认时区不对、连接池会话时区错,都可能造成。先用 SELECT NOW(), UTC_TIMESTAMP() 和程序侧打印时间对照,逐层定位。

Q3:把 timestamps 存成 DATETIME 是不是更好?
看场景。DATETIME 不会因时区变化而变,语义直白,适合固定时区的业务;TIMESTAMP 自动 UTC 存储,适合"绝对时刻"。但 TIMESTAMP 有 2038 限制。跨时区业务可以用 TIMESTAMP,国内单时区业务用 DATETIME 更省心。

Q4:SET GLOBAL time_zone 后旧连接会变吗?
不会,全局修改只影响之后建立的新会话。已存在的连接保持原会话时区。所以改完后应用需要重连才能生效,或直接在连接串里指定。

Q5:CONVERT_TZ 返回 NULL 是怎么回事?
多半是时区表没加载,无法识别命名时区(如 'Asia/Shanghai')。先用 mysql_tzinfo_to_sql 加载时区表;用固定偏移('+08:00')则不依赖时区表。

总结

  • 时区问题要先定位"哪一层不一致":操作系统、MySQL 全局、会话、应用连接串,用 date、@@time_zone、NOW()、UTC_TIMESTAMP() 逐层对照。
  • 牢记 DATETIME 不随时区转换、TIMESTAMP 会随会话时区换算——这是"存对了读不对"的根源。
  • 只服务国内用户:全链路统一 +08:00,用 DATETIME;跨时区业务:全链路统一 UTC,展示层按用户时区转换。
  • 要用命名时区(Asia/Shanghai)必须先加载 MySQL 时区表,否则报 Unknown or incorrect time zone。
  • 改时区、改时间字段数据都属于高危操作:先备份、测试库演练、低峰分批、保留回滚点,TIMESTAMP 列尤其要提前清点。