MySQL 时区处理完全指南

目录

• 一、前言
  • 1.1 问题背景
  • 1.2 为什么需要关注时区
• 二、核心结论
  • 2.1 关键要点总结
  • 2.2 快速决策表
• 三、日期时间类型详解
  • 3.1 TIMESTAMP vs DATETIME
  • 3.2 时区转换机制
  • 3.3 实验验证
• 四、当前日期时间函数
  • 4.1 常用函数列表
  • 4.2 时区行为验证
  • 4.3 DST 夏令时问题
• 五、时区配置与管理
  • 5.1 查看时区设置
  • 5.2 修改时区配置
  • 5.3 Docker 环境特殊处理
• 六、最佳实践
  • 6.1 Java 应用开发建议
  • 6.2 SQL 编写规范
  • 6.3 常见问题排查
• 参考资料

一、前言

1.1 问题背景

当 JVM 时区和数据库时区不一致时，会发生什么？这个问题也许你从来没有注意过，但是当把 Java 程序容器化的时候，问题就浮现出来了，因为目前几乎所有的 Docker Image 的时区都是 UTC。

典型场景：

1
2
3
4

Java 应用（Asia/Shanghai） → MySQL（UTC）
    ↓                           ↓
插入: 2024-01-01 10:00:00   存储: 2024-01-01 02:00:00 (UTC)
查询: 2024-01-01 10:00:00   返回: 2024-01-01 10:00:00 (自动转换)

1.2 为什么需要关注时区

• 🌍 全球化应用：用户分布在不同时区
• 🐳 容器化部署：Docker 默认使用 UTC 时区
• ⏰ 时间敏感业务：订单、日志、定时任务等
• 🔄 数据迁移：跨时区数据库迁移

本文探究了 MySQL 及其 JDBC 驱动对于时区的处理方式，并尝试给出最佳实践。

二、核心结论

2.1 关键要点总结

✅ 推荐做法：

1. DATE 和 TIME 类型不支持时区转换

   • 存储什么值，查询就是什么值
   • 不受 connection 时区影响

2. TIMESTAMP 类型自动进行时区转换

   • 插入时：从 connection 时区 → UTC 存储
   • 查询时：从 UTC → connection 时区返回
   • JDBC 程序无需特别处理，只需保证 JVM 时区与用户所在时区一致

3. CURRENT_TIMESTAMP() 等函数安全可用

   • 返回结果会自动转换为 connection 时区
   • 可以安全使用

❌ 避免做法：

1. 不要在服务器端做日期时间字符串格式化

   • DATE_FORMAT() 返回的是服务端时区，不是 connection 时区
   • 应该在应用层进行格式化

2. 不要混用 TIMESTAMP 和 DATETIME

   • 同一业务场景应统一使用一种类型
   • 避免时区转换混乱

2.2 快速决策表

三、日期时间类型详解

3.1 TIMESTAMP vs DATETIME

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.

核心要点：

1. TIMESTAMP：存储时从当前时区转换为 UTC，查询时从 UTC 转换回当前时区
2. DATETIME：不进行任何时区转换，存储什么值就返回什么值
3. 默认时区：每个连接的默认时区是服务器时区，可以按连接设置

3.2 时区转换机制

1
2
3
4
5
6
7
8
9
10
11
12
13

插入流程（TIMESTAMP）：
Java Date (Asia/Shanghai: 2024-01-01 10:00:00)
    ↓ JDBC 驱动
MySQL Connection (Asia/Shanghai)
    ↓ 转换为 UTC
MySQL Storage (UTC: 2024-01-01 02:00:00)

查询流程（TIMESTAMP）：
MySQL Storage (UTC: 2024-01-01 02:00:00)
    ↓ 转换为 Connection 时区
MySQL Connection (Europe/Paris)
    ↓ JDBC 驱动
Java Date (Europe/Paris: 2024-01-01 03:00:00)

关键点：

• ✅ 只要 connection 时区不变，存入和取出的值一致
• ⚠️ 如果改变时区后查询，返回的值会不同
• ❌ DATETIME 类型不会进行任何转换

3.3 实验验证

为了验证这个结论，我写了一段程序来实验，这个程序做了四件事情：

1. 使用 Asia/Shanghai 时区构造一个日期 java.util.Date：2018-09-14 10:00:00，然后插入到数据库里（表：test，列：timestamp 类型）
2. 使用 Asia/Shanghai 时区把这个值再查出来，看看结果
3. 使用 Asia/Shanghai 时区，获得这个字段的格式化字符串（使用 DATE_FORMAT() 函数）
4. 使用 Europe/Paris 时区重复第 2-3 步的动作

实验结果：

1
2
3
4
5
6
7
8
9
10
11

Insert data, Time Zone        : 中国标准时间
java.util.Date                : 2018-09-14 10:00:00
Insert into timestamp column  : 2018-09-14 10:00:00
--------------------
Retrieve data, Time Zone      : 中国标准时间
Retrieve java.util.Date       : 2018-09-14 10:00:00
Retrieve formatted string     : 2018-09-14 02:00:00
--------------------
Retrieve data, Time Zone      : 中欧时间
Retrieve java.util.Date       : 2018-09-14 04:00:00
Retrieve formatted string     : 2018-09-14 02:00:00

结果分析：

结论：

• ✅ Retrieve java.util.Date 返回的结果根据 JVM 时区做了转换
• ❌ Retrieve formatted string 返回的结果则是 UTC 时间（不受 connection 时区影响）

四、当前日期时间函数

4.1 常用函数列表

MySQL 与”当前日期时间”相关的函数：

官方文档说明：

The CURRENT_TIMESTAMP(), CURRENT_TIME(), CURRENT_DATE(), and FROM_UNIXTIME() functions return values in the connection’s current time zone, which is available as the value of the time_zone system variable.

4.2 时区行为验证

为了验证这个结论，分别使用 Asia/Shanghai 和 Europe/Paris 来调用 CURRENT_TIMESTAMP()、CURRENT_TIME()、CURRENT_DATE()。

运行结果：

1
2
3
4
5
6
7
8
9

JVM Time Zone              : 中国标准时间
Test CURRENT_DATE()        : 2018-09-18
Test CURRENT_TIME()        : 10:55:41
Test CURRENT_TIMESTAMP()   : 2018-09-18 10:55:41.0
--------------------
JVM Time Zone              : 中欧时间
Test CURRENT_DATE()        : 2018-09-18
Test CURRENT_TIME()        : 03:56:02
Test CURRENT_TIMESTAMP()   : 2018-09-18 04:56:02.0

结果分析：

• ✅ CURRENT_DATE()：两个时区返回相同日期（符合预期）
• ✅ CURRENT_TIME()：相差约 7 小时（上海 10:55 vs 巴黎 03:56）
• ✅ CURRENT_TIMESTAMP()：相差约 7 小时（上海 10:55 vs 巴黎 04:56）

4.3 DST 夏令时问题

发现的问题：

在 Europe/Paris 时区，CURRENT_TIME() 和 CURRENT_TIMESTAMP() 的时间部分相差一小时：

• CURRENT_TIME(): 03:56:02
• CURRENT_TIMESTAMP(): 04:56:02

原因分析：

• CURRENT_TIMESTAMP() 返回的是 UTC + DST offset 结果
• CURRENT_TIME() 返回的是 UTC offset 结果（未考虑夏令时）

这看上去是一个 Bug，已登记 Bug #92453。

关于 Europe/Paris 的 DST 信息：

可以在 Wiki - List of tz database time zones 找到详细信息。

建议：

• ⚠️ 在涉及夏令时的时区，谨慎使用 CURRENT_TIME()
• ✅ 优先使用 CURRENT_TIMESTAMP() 获取完整时间戳
• ✅ 在应用层进行时区转换和格式化

五、时区配置与管理

5.1 查看时区设置

1
2
3
4
5
6
7
8

-- 查询系统时区和 session 时区
SELECT @@global.time_zone, @@session.time_zone;

-- 查询当前时区
SELECT @@time_zone;

-- 查询系统时区变量
SHOW VARIABLES LIKE '%time_zone%';

输出示例：

1
2
3
4
5

+--------------------+---------------------+
| @@global.time_zone | @@session.time_zone |
+--------------------+---------------------+
| SYSTEM             | SYSTEM              |
+--------------------+---------------------+

时区值说明：

5.2 修改时区配置

方法一：修改 Session 时区（仅当前连接有效）

1
2
3
4
5
6
7
8

-- 设置为指定时区
SET time_zone = 'Asia/Shanghai';

-- 设置为 UTC 偏移量
SET time_zone = '+08:00';

-- 恢复为系统时区
SET time_zone = 'SYSTEM';

方法二：修改 Global 时区（新连接生效）

1
2
3
4
5

-- 需要 SUPER 权限
SET GLOBAL time_zone = 'Asia/Shanghai';

-- 刷新权限
FLUSH PRIVILEGES;

方法三：修改配置文件（永久生效）

编辑 my.cnf 或 mysqld.cnf：

1
2
3
4
5

[mysqld]
# 设置默认时区
default-time-zone = '+08:00'
# 或使用时区名称
default-time-zone = 'Asia/Shanghai'

重启 MySQL 服务：

1

sudo systemctl restart mysql

5.3 Docker 环境特殊处理

问题：Docker 容器默认使用 UTC 时区

解决方案：

方案 1：挂载时区文件（推荐）

1
2
3
4
5
6

# Dockerfile
FROM mysql:8.0

# 挂载主机时区文件
VOLUME /etc/localtime:/etc/localtime:ro
VOLUME /etc/timezone:/etc/timezone:ro

1
2
3
4
5
6
7
8
9
10

# docker-compose.yml
version: '3'
services:
  mysql:
    image: mysql:8.0
    volumes:
      - /etc/localtime:/etc/localtime:ro
      - /etc/timezone:/etc/timezone:ro
    environment:
      - TZ=Asia/Shanghai

方案 2：设置环境变量

1
2
3
4
5

docker run -d \
  --name mysql \
  -e TZ=Asia/Shanghai \
  -e MYSQL_ROOT_PASSWORD=root \
  mysql:8.0

方案 3：在容器中安装时区数据

1
2
3
4
5
6

FROM mysql:8.0

RUN apt-get update && \
    apt-get install -y tzdata && \
    ln -sf /usr/share/zoneinfo/Asia/Shanghai /etc/localtime && \
    echo "Asia/Shanghai" > /etc/timezone

方案 4：JDBC URL 中指定时区

1
2

# application.properties
spring.datasource.url=jdbc:mysql://localhost:3306/mydb?serverTimezone=Asia/Shanghai

最佳实践：

• ✅ 推荐方案 1 + 方案 4 组合使用
• ✅ MySQL 服务器使用 UTC，JDBC 连接指定时区
• ❌ 避免在多个层面同时设置时区（容易混乱）

六、最佳实践

6.1 Java 应用开发建议

✅ 推荐做法：

1. 统一使用 TIMESTAMP 类型

   1 2 3 4 5

   // ✅ 好：使用 java.time 包 LocalDateTime now = LocalDateTime.now(ZoneId.of("Asia/Shanghai")); // ✅ 好：JDBC 4.2+ 支持 preparedStatement.setObject(1, now);

2. JVM 时区与用户时区保持一致

   1 2 3 4 5

   // 启动参数设置 // -Duser.timezone=Asia/Shanghai // 或在代码中设置（不推荐） TimeZone.setDefault(TimeZone.getTimeZone("Asia/Shanghai"));

3. 使用 java.time 替代 Date/Calendar

   1 2 3 4 5 6 7

   // ✅ 推荐：Java 8+ ZonedDateTime zonedDateTime = ZonedDateTime.now(ZoneId.of("Asia/Shanghai")); Instant instant = zonedDateTime.toInstant(); // ❌ 不推荐：旧 API Date date = new Date(); Calendar calendar = Calendar.getInstance();

4. 在应用层进行格式化

   1 2 3 4 5 6

   // ✅ 好：应用层格式化 DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"); String formatted = zonedDateTime.format(formatter); // ❌ 不好：数据库层格式化 // SELECT DATE_FORMAT(created_at, '%Y-%m-%d %H:%i:%s') FROM table

❌ 避免做法：

1. ❌ 不要混用 TIMESTAMP 和 DATETIME
2. ❌ 不要在 SQL 中使用 DATE_FORMAT() 返回给前端
3. ❌ 不要硬编码时区偏移量（如 +08:00），应使用时区名称
4. ❌ 不要忽略 DST 夏令时问题

6.2 SQL 编写规范

✅ 推荐写法：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

-- ✅ 好：使用 CURRENT_TIMESTAMP 默认值
CREATE TABLE orders (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    order_no VARCHAR(32) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- ✅ 好：查询时使用 TIMESTAMP 类型
SELECT * FROM orders 
WHERE created_at >= '2024-01-01 00:00:00' 
  AND created_at < '2024-01-02 00:00:00';

-- ✅ 好：使用 UTC 函数（跨时区场景）
SELECT UTC_TIMESTAMP() AS utc_now;

❌ 避免写法：

1
2
3
4
5
6
7
8
9
10
11
12

-- ❌ 不好：使用 DATE_FORMAT 返回字符串
SELECT DATE_FORMAT(created_at, '%Y-%m-%d %H:%i:%s') AS created_time 
FROM orders;

-- ❌ 不好：混用 TIMESTAMP 和 DATETIME
CREATE TABLE bad_example (
    created_at TIMESTAMP,
    updated_at DATETIME  -- 不一致！
);

-- ❌ 不好：硬编码时区偏移
SET time_zone = '+08:00';  -- 应使用 'Asia/Shanghai'

6.3 常见问题排查

Q1: 查询时间与插入时间不一致？

排查步骤：

1
2
3
4
5
6
7
8
9
10
11

-- 1. 检查当前会话时区
SELECT @@session.time_zone;

-- 2. 检查全局时区
SELECT @@global.time_zone;

-- 3. 检查字段类型
DESCRIBE your_table;

-- 4. 检查服务器系统时区
SELECT @@system_time_zone;

解决方案：

1
2

-- 统一设置为相同时区
SET time_zone = 'Asia/Shanghai';

Q2: Docker 环境中时区不正确？

排查步骤：

1
2
3
4
5
6
7
8

# 1. 检查容器时区
docker exec -it mysql_container date

# 2. 检查 MySQL 时区
docker exec -it mysql_container mysql -uroot -p -e "SELECT @@time_zone;"

# 3. 检查时区数据是否加载
docker exec -it mysql_container mysql -uroot -p -e "SELECT COUNT(*) FROM mysql.time_zone_name;"

解决方案：

1
2

# 重新加载时区数据
docker exec -it mysql_container mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -uroot -p mysql

Q3: Java 应用获取的时间差 8 小时？

原因：JVM 时区与 MySQL 时区不一致

解决方案：

1
2
3
4
5
6
7
8

# 方案 1：JDBC URL 指定时区
spring.datasource.url=jdbc:mysql://localhost:3306/mydb?serverTimezone=Asia/Shanghai

# 方案 2：JVM 启动参数
# -Duser.timezone=Asia/Shanghai

# 方案 3：Spring Boot 配置
spring.jackson.time-zone=Asia/Shanghai

Q4: 如何处理历史数据的时区问题？

场景：数据库中存在大量 DATETIME 类型数据，需要迁移到 TIMESTAMP

步骤：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

-- 1. 备份数据
CREATE TABLE orders_backup AS SELECT * FROM orders;

-- 2. 添加新列
ALTER TABLE orders ADD COLUMN created_at_ts TIMESTAMP NULL;

-- 3. 转换数据（假设原数据是北京时间）
UPDATE orders 
SET created_at_ts = CONVERT_TZ(created_at, '+08:00', 'UTC');

-- 4. 删除旧列，重命名新列
ALTER TABLE orders DROP COLUMN created_at;
ALTER TABLE orders CHANGE created_at_ts created_at TIMESTAMP;

-- 5. 验证数据
SELECT * FROM orders LIMIT 10;

参考资料

• MySQL 官方文档 - Date and Time Types
• MySQL 官方文档 - Date and Time Functions
• MySQL 时区支持
• Bug #92453: CURRENT_TIME() doesn’t respect DST
• MySQL 时区处理最佳实践
• Java Time API 官方文档