MySQL timestamp 与时区问题

最新推荐文章于 2024-04-11 10:33:53 发布

肥肥技术宅

最新推荐文章于 2024-04-11 10:33:53 发布

阅读量1.9k

点赞数 2

分类专栏： java 文章标签： mysql java 数据库

本文链接：https://blog.csdn.net/m0_71777195/article/details/131393063

版权

3552 篇文章 113 订阅

订阅专栏

新项目可能存在国际化的问题，所以花了点时间了解了下 MySQL 和 JDBC 驱动相关的时间问题。看了好多篇博客发现，不少人理解的都是错误的，所以结合官方的文档，重新梳理了一下。

大家都知道在 MySQL 中有两个专门用来存取日期时间的类型，timestamp 和 datetime。大家总是说 datetime 不包含时区信息，timestamp 存的 utc 时间戳更适合国际化场景下的本地时间转换。经过了解，上述说法对，但不完全对。

版本信息：

MySQL 用什么类型存日期时间？

目前可以存日期时间的数据类型主要分两类：

bigint：直接将 utc 时间戳存到 int 类型的字段中。后期根据用户本地时区进行转换。
日期时间类型： timestamp：MySQL 官方定义的时间戳，内部使用 utc 时间戳存储，但查询时返回的结果会随着 session time_zone 的变化而变化。 datetime：只存日期时间的值，不包含时区信息。

那么我们应该选择哪个？先说结论在分析，根据需求选 bigint 或 datetime。

类型	优点	缺点
bigint	1. 直观，不需要考虑时区 2. 国际化场景直接根据本地时区转化 3. 存储和查询效率高	1. 数据库直接查看时可读性差，需要转化
timestamp	无	1. 查询结果收到 session time_zone 的影响，容易出错 2. 最大时间为 2038 年
datetime	1. 可读性好 2. MySQL 自带各种操作函数，便于查询 3. 存储日期范围大，到 9999 年	1. 存储空间略大 2. 查询效率较前两者低

所以在我看来，如果是后台管理类的系统，更适合使用 datetime，因为对性能要求没那么高，并且查询方便；其他的可以考虑 bigint。

其中最不建议使用的是 timestamp，因为这个会随着 session time_zone 的变化而变化的，如果开发者对时区理解不深，当服务器或、MySQL 或者 JVM 时区发生变化时，很容易出现问题。

刚刚讲了 MySQL 的 timestamp 类型的查询结果会受到 session time_zone 的变化而变化，那么这节我们就来讲讲时区的问题。

那么从 Springboot Application 到 MySQL，哪些地方存在时区的配置呢？自然是通信的两端，MySQL 服务端和 MySQL 客户端。

MySQL Client

这里的 Client 是一个笼统的概念，涉及 client 所在机器 os 的时区、对应应用程序的时区（eg: Java 应用就是 JVM 时区）、JDBC 驱动的时区。

机器 OS 时区
bash复制代码
root@T630-03:/# cat /etc/timezone Asia/Shanghai root@T630-03:/# date 2023 年 06 月 21 日星期三 11:20:28 CST
OS 时区影响的是 OS 自身的日期时间命令结果的返回，同时还是影响部分软件，因为有些软件会默认使用 OS 时区作为软件时区。
应用程序时区
这里我们 Spring Boot 应用程序为例，对应的就是 JVM 时区，它默认使用的系统时区，可通过输出 ZoneId.*systemDefault*() 来查看。可通过 TimeZone.setDefault(TimeZone.getTimeZone("America/New_York")); 来配置。主要涉及在 Java 中操作日期和时间的相关类。比如 LocalDateTime.now() ，假设北京时间为 20:00，如果将 TimeZone 设置为 Asia/Tokyo，则会返回 21:00。
JDBC 驱动时区
在 Springboot 应用程序中，我们通过 JDBC 驱动来连接到数据库，在连接的过程中，我们需要配置 jdbcUrl 来指定 JDBC 时区。
在 8.0 的驱动中，是通过配置以下字段指定的：
connectionTimeZone=Asia/Shanghai
forceConnectionTimeZoneToSession=true
这两个参数指定了，通过 JDBC 驱动连接到 MySQL 的 session time_zone 为 Asia/Shanghai。那么通过 JDBC 驱动去调用 SQL 中日期时间相关的方法都会以此时区为准。

MySQL Server

这里的 Server 指的是数据库，涉及数据库所在机器 os 的时区、MySQL 的时区。

机器 OS 时区
同上，但 MySQL 默认使用的是 OS 时区
MySQL 会话时区
sql复制代码
mysql> show global variables like '%time_zone%'; +------------------+--------+ | Variable_name | Value | +------------------+--------+ | system_time_zone | CST | | time_zone | SYSTEM | +------------------+--------+ 2 rows in set (0.00 sec)
MySQL 有一个默认的时区配置，如上，是使用 OS 时区作为 MySQL 默认时区的。MySQL 其实本身是没有所谓的时区的，我们所说的都是它的 session time_zone，具体的：
1. MySQL 存在一个默认时区，一般是 SYSTEM。可通过配置文件或 SQL 进行修改。
2. Client 连接到 MySQL 时若不指定 session time_zone，默认使用 MySQL Server 的时区。也可通过 JdbcUrl 在连接的时候指定或通过 SET time_zone ='UTC'; 指定。
3. Client 在指定了 session time_zone 后，所有的日期时间查询均会按照所在时区返回结果。

timestamp 在 MySQL 中其实保存就是 UTC 时间戳，当 Client 配置不同的会话时区后，会进行转换显示。

下面举几个最直观的例子。

表 time_test，有一个 timestamp 类型的字段 client_time。

直接在 MySQL 控制台操作
sql复制代码
SET time_zone = 'Asia/Shanghai'; # 2023-06-20 08:00:00 insert into time_test(client_time) values (current_timestamp()); select * from time_test ; SET time_zone = 'UTC'; # 2023-06-20 00:00:00 select * from time_test ; # 2023-06-20 00:01:00 insert into time_test(client_time) values (current_timestamp()); select * from time_test ; SET time_zone = 'Asia/Shanghai'; # 2023-06-20 08:01:00 select * from time_test ;

结果应该很好理解。按照当前时区插入数据后，查询结果是当前时区的日期，更换时区后，会进行相应的偏移，MySQL 会自动进行转换。

通过 SpringBoot 调用 JDBC 驱动查询，虽然中间涉及到了多个时区概念，但其实转换过程也很简单。

JVM 时区	JVM 日期（假设是 LocalDateTime.now() 返回）	JDBC 驱动时区	插入后返回的查询结果
Asia/Shanghai	2023-06-20 08:00:00	Asia/Shanghai	2023-06-20 08:00:00
Asia/Shanghai	2023-06-20 08:00:00	UTC	2023-06-20 00:00:00
Asia/Tokyo	2023-06-20 09:00:00	Asia/Shanghai	2023-06-20 08:00:00
Asia/Tokyo	2023-06-20 09:00:00	UTC	2023-06-20 01:00:00

说白了，只与 JDBC 驱动的时区有关。

当我们做国际化项目时，只需要保持 JVM 时区和 JDBC 驱动时区一致，均为 Asia/Shanghai。其他用户，只需要根据设置的本地时区进行转换即可。

一番了解下来，最易用的其实还是bigint和datetime这两个时区无关的类型，时区相关的操作直接由我们自己控制最理想。并且也没有timestamp的时间限制。总结下：

关注