新项目可能存在国际化的问题,所以花了点时间了解了下 MySQL 和 JDBC 驱动相关的时间问题。看了好多篇博客发现,不少人理解的都是错误的,所以结合官方的文档,重新梳理了一下。
大家都知道在 MySQL 中有两个专门用来存取日期时间的类型,timestamp 和 datetime。大家总是说 datetime 不包含时区信息,timestamp 存的 utc 时间戳更适合国际化场景下的本地时间转换。经过了解,上述说法对,但不完全对。
版本信息:
- MySQL5.7
- mysql-connector-java 8.0.31
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 时区发生变化时,很容易出现问题。
JDBC 驱动和 MySQL 的时区问题
刚刚讲了 MySQL 的 timestamp 类型的查询结果会受到 session time_zone 的变化而变化,那么这节我们就来讲讲时区的问题。
那么从 Springboot Application 到 MySQL,哪些地方存在时区的配置呢?自然是通信的两端,MySQL 服务端和 MySQL 客户端。
MySQL Client
这里的 Client 是一个笼统的概念,涉及 client 所在机器 os 的时区、对应应用程序的时区(eg: Java 应用就是 JVM 时区)、JDBC 驱动的时区。
- 机器 OS 时区
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 的驱动中,是通过配置以下字段指定的:
这两个参数指定了,通过 JDBC 驱动连接到 MySQL 的 session time_zone 为 Asia/Shanghai。那么通过 JDBC 驱动去调用 SQL 中日期时间相关的方法都会以此时区为准。
- connectionTimeZone=Asia/Shanghai
- forceConnectionTimeZoneToSession=true
MySQL Server
这里的 Server 指的是数据库,涉及数据库所在机器 os 的时区、MySQL 的时区。
- 机器 OS 时区
同上,但 MySQL 默认使用的是 OS 时区
- MySQL 会话时区
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,具体的:
- MySQL 存在一个默认时区,一般是 SYSTEM。可通过配置文件或 SQL 进行修改。
- Client 连接到 MySQL 时若不指定 session time_zone,默认使用 MySQL Server 的时区。也可通过 JdbcUrl 在连接的时候指定或通过
SET time_zone ='UTC';
指定。 - Client 在指定了 session time_zone 后,所有的日期时间查询均会按照所在时区返回结果。
timestamp 类型与时区的影响
timestamp 在 MySQL 中其实保存就是 UTC 时间戳,当 Client 配置不同的会话时区后,会进行转换显示。
下面举几个最直观的例子。
表 time_test,有一个 timestamp 类型的字段 client_time。
直接在 MySQL 控制台操作
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 会自动进行转换。
- 通过 JDBC 驱动操作
通过 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的时间限制。总结下:
- 优先使用bigint和datetime,国际化在代码层面做。
- 要使用timestamp,需要了解清楚各种时区的信息并做好配置。防止因为机器时区改变、数据库时区改变而影响查询的结果。
参考文献
- 6.3.11 Datetime types processing
- 6.6.1 Preserving Time Instants
- Support for Date-Time Types in Connector/J 8.0
到此这篇关于MySQL timestamp与时区问题的解决的文章就介绍到这了,更多相关MySQL timestamp与时区内容请搜索aitechtogether.com以前的文章或继续浏览下面的相关文章希望大家以后多多支持aitechtogether.com!