一文讲透MySQL driver读取时间时的时区处理

理解连接属性 serverTimezone/connectionTimezone

在 java 中使用 mysql client 创建 JDBC 连接时可以指定以上两个连接参数。两者的功能是一致的,所以后面统一就用 serverTimezone 为例说明。官方文档描述如下所述。这个参数主要是告诉 jdbc,数据库处理 timestamp 是按照什么时区处理的。jdbc 第一次从数据库读取到的原始的年、月、日、时分秒都是按照 server timezone 处理好的。知道了 server timezone 之后,jdbc 可以根据用户配置的 serverTimezone 的值,逆将这个年月日转换成 UTC 时间的毫秒数,最终 jdbc 可以根据自己本地 jvm 的时区以及这个逆向得到的 UTC 时间,得到正确的本地时间。

1
Specifies how the server's session time zone (in reference to which the timestamps are saved onto the server) is to be determined by Connector/J.

以上理解非常重要,理解了 serverTimezone 本质含义后,自然也就理解后续 jdbc 处理时区转换的原理了。下面分别介绍下 jdbc 读写数据库时,是如何配合 serverTimezone 做时区转换的。

读取时间字段

jdbc 读取数据时区转换的基本步骤

  1. 数据库毫秒数存储
  2. 按照数据库时区转换成年月日
  3. client 根据使用不同的方法进行不同的时间处理

rs.getString()的底层原理

JDBC 如果使用 rs.getString,则不会对数据库上读取出来的时间做任何转换,直接输出读取到的年月日信息。使用的 value 处理器是 StringValueFactory,这个 value factory 不依赖时区,直接按照读取到的年月日信息来组装输出结果。可以参考下面的堆栈了解具体调用链。

rs.getString 根据 StringValueFactory 处理时间类型,format 的时候 createFromDate 不涉及时区相关处理

1
2
3
4
public String createFromTimestamp(InternalTimestamp its) {
return String.format("%s %s", createFromDate(its),
createFromTime(new InternalTime(its.getHours(), its.getMinutes(), its.getSeconds(), its.getNanos(), its.getScale())));
}

rs.getTimestamp 的底层原理

源码可能比较冗杂,但是 getTimestamp 简单总结读取的时区转换逻辑其实还是比较简单的:

  1. client 从数据库读取到的时间是数据库按照时区转换后的年月日信息,因此根据配置的 JDBC 属性 serverTimezone 先将时间逆向处理成 UTC 时间的毫秒数,用来初始化得到一个 Timestamp 实例对象
  2. Timestamp 实例对象 toString 输出时,使用 jvm 本身的 local timezone(默认会取本机系统时区)的时区转成最终的本地时间

例如有个时间为 1970-00-00 00:00:00,serverTimezone 为+9,本机为+8。那么数据库读取出来的值 1970-00-00 09:00:00 根据 serverTimezone 转换成毫秒数为 0,然后由于本地时区为+8,再加 8 个小时,也就得到最终本地的输出时间 1970-00-00 08:00:00

当我们使用 rs.getTimestamp 的时候则使用 SqlTimestampValueFactory 处理时间,SqlTimestampValueFactory 中关键的时间 value 处理如下:

  • cal 是 Calendar 的实现类,是 SqlTimestampValueFactory 中的重要成员变量。处理 value 的时候会调用 getTimeInMillis()方法中的 updateTime()方法会根据 JDBC 设置的 serverTimezone 属性值将年月日转成该时区相当于 0 时区的毫秒数。然后使用这个值初始化 Timestamp 对象。例如 severTimezone 是+9,我们从数据库查询出来的毫秒数需要减少 9 小时的毫秒数,才是数据库底层真正存储的 UTC 时间
  • Timestamp 对象是时区相关的,toString 会按照 local timezone 再最终生成本地展示的时间



normalize 会使用 local timezone 处理时间

tips: 以上说的比较细,但是核心逻辑还是比较简单的,就是先将读取到的年月日信息转成 server timezone 对应的毫秒数来初始化 Timestamp 对象。

getString 和 getTimestamp 读取时间处理总结

读取时间的测试

读取测试环境说明

测试使用的表结构如下,后续读写的测试都会使用这张表结构:

1
2
3
4
5
6
create table ws1.test_timezone
(
id int(11) primary key,
t timestamp,
d datetime
);

通过以下代码我们可以来加深之前原理的理解,以下代码在笔者本机 mac 上运行,代码中指定的本地时区和 serverTimezone 在后续测试中可以自行调整。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
package com.clougence.wanshao.example.mysql;

import com.alibaba.druid.pool.DruidDataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Properties;
import lombok.SneakyThrows;

/**
* @author wanshao create time is 2021/8/17
**/
public class TestConnection {

@SneakyThrows
public static void main(String[] args) {
TimeZone.setDefault(TimeZone.getTimeZone("Asia/Shanghai"));
Locale.setDefault(Locale.CHINESE);

String jdbcUrl = "jdbc:mysql://rm-xxxxx.mysql.rds.aliyuncs.com/ws1?serverTimezone=UTC";
//
DruidDataSource ds = new DruidDataSource();
ds.setDriverClassName("com.mysql.cj.jdbc.Driver");
ds.setUrl(jdbcUrl);
ds.setUsername("wanshao");
ds.setPassword("xxxxx");
Properties prop = new Properties();
prop.put("useUnicode", "true");
ds.setConnectProperties(prop);
ds.init();
try (Connection connection = ds.getConnection(); PreparedStatement ps = connection
.prepareStatement("select * from ws1.test_timezone"); ResultSet rs = ps.executeQuery();) {
if (rs.next()) {
System.out.println("getTimestamp is " + rs.getTimestamp("t"));
System.out.println("getString is " + rs.getString("d"));
} else {
throw new RuntimeException("can not get version for mysql ");
}
}
}
}

读取测试结果

数据库读取到的年月日信息为:2022-06-19 22:29:34
结果可以看到,getString 和本地时区、serverTimezone 均无关,getTimestamp 按照 serverTimezone 和本地时区转换得到最终结果

输出结果 本地时区 serverTimezone

| getTimestamp is 2022-06-19 21:29:34.0
getString is 2022-06-19 22:29:34 | +8 | +9 |
| getTimestamp is 2022-06-19 13:29:34.0
getString is 2022-06-19 22:29:34 | +0 | +9 |
| getTimestamp is 2022-06-19 22:29:34.0
getString is 2022-06-19 22:29:34 | +0 | +0 |

写入时间字段

后续介绍写入原理时涉及参考示例,参考示例统一的时区配置如下:

  • 本地时区: +8
  • client 配置的 serverTimezone 属性: +9
  • 数据库的时区: -5

不同写入方式测试时对以下代码做略微改动即可适配。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
package com.clougence.wanshao.example.mysql;

import com.alibaba.druid.pool.DruidDataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Locale;
import java.util.Properties;
import java.util.TimeZone;
import lombok.SneakyThrows;

/**
* @author wanshao create time is 2021/8/17
**/
public class TestConnection {

@SneakyThrows
public static void main(String[] args) {
TimeZone.setDefault(TimeZone.getTimeZone("Asia/Shanghai"));
Locale.setDefault(Locale.CHINESE);

String jdbcUrl = "jdbc:mysql://rm-xxxx.mysql.rds.aliyuncs.com/ws1?serverTimezone=Japan";
//
DruidDataSource ds = new DruidDataSource();
ds.setDriverClassName("com.mysql.cj.jdbc.Driver");
ds.setUrl(jdbcUrl);
ds.setUsername("wanshao");
ds.setPassword("xxxx");
Properties prop = new Properties();
prop.put("useUnicode", "true");
ds.setConnectProperties(prop);
ds.init();

try (Connection connection = ds.getConnection(); PreparedStatement ps = connection
.prepareStatement("select now()"); ResultSet rs = ps.executeQuery();) {
while (rs.next()) {
System.out.println("get Now result is(getTimestamp) " + rs.getTimestamp(1));
System.out.println("get Now result is(getString) " + rs.getString(1));

}
}

try (Connection connection = ds.getConnection(); PreparedStatement ps = connection
.prepareStatement("insert into ws1.test_timezone(id,t,d) values (9,now(),now())");) {
ps.execute();
}

try (Connection connection = ds.getConnection(); PreparedStatement ps = connection
.prepareStatement("select * from ws1.test_timezone where id=9"); ResultSet rs = ps.executeQuery();) {
while (rs.next()) {
System.out.println("getTimestamp is " + rs.getTimestamp("t"));
System.out.println("getString is " + rs.getString("d"));
}
}
}
}

数据库 server 侧对于写入的处理

DB 接收到的年月日字符串信息,都当成是自身数据库时区 format 的结果进行存储,与写入时 client 使用的参数都无关,因为根据 mysql 协议,都是按照文本将年、月日信息作为字符串数据发给数据库 server 的。

写入方式 1:按照时间 now()写入

insert 语句执行 now 函数,数据库 server 端会获取数据库当前时区的 format 时间。客户端读取的时候如果使用 getTimestamp 则按照前文所说的方式获取处理后的结果,如果 getString 则获取数据库按照其自身时区 format 的原始的年月日信息。

1
2
### 参考sql
insert into ws1.test_timezone(id,t,d) values (8,now(),now())"

例如上面的例子,数据库取到的本地时区时间 format 结果为 2022-06-22 03:29:29
按照 now()写入后,测试读取的结果为:

  • client 端使用 getString 读取结果为:2022-06-22 03:29:29
  • client 端使用 getTimestamp 读取结果为:2022-06-22 16:29:29

写入方式 2:按照字符串写入

sql 中指定具体时间的 value,这个按照文本直接传递给数据库,数据库认为该年月日即是自身的时区的时间,然后转成毫秒数存储。

1
insert into ws1.test_timezone(id,t,d) values (8,'2022-06-22 03:29:29','2022-06-22 03:29:29')"

上面这个 insert 写入后,数据库将’2022-06-22 03:29:29’认为是数据库时区的一个时间进行存储。client 测试读取的结果为:

  • client 端使用 getString 读取结果为:2022-06-22 03:29:29
  • client 端使用 getTimestamp 读取结果为:2022-06-22 16:29:29.0

now()函数和字符串写入总结

字符串或者函数写入的时间,其实都是按照文本传递给数据库 server,数据库 server 直接当做是自己时区的时间进行存储。这个具体可以看 driver 写入时的源码,核心类是 ClientPreparedStatement。执行 ps.execute 的调用链如下:

其中 executeInternal()方法执行时 sendPacket 中的内容就是我们 sql 中的字符串。理解写入时数据库 server 如何去记录值,再配合前文提到的读取原理,自然就能知道读取出来的时间是什么值了。

写入方式 3:通过变量绑定写入

之前写入的 sql 我们做以下略微调整。按照 ps.set 方式去设置值。

按照 ps.setString()处理

写入方式如下:

1
2
3
4
5
6
try (Connection connection = ds.getConnection(); PreparedStatement ps = connection
.prepareStatement("insert into ws1.test_timezone(id,t,d) values (13,?,?)")) {
ps.setString(1, "2022-06-22 03:29:29");
ps.setString(2, "2022-06-22 03:29:29");
ps.execute();
}

这种方式使用的类是 ClientPreparedQueryBindings 类中的 setString 方法,mysql client 不会对文本做改动,直接赋值后发送给 server,和之前字符串直接表示的写入效果是一样的。

  • client 端使用 getString 读取结果为:2022-06-22 03:29:29
  • client 端使用 getTimestamp 读取结果为:2022-06-22 16:29:29.0

按照 ps.setTimestamp()处理

使用的代码示例如下:

1
2
3
4
5
6
7
8
9
10
11
try (Connection connection = ds.getConnection(); PreparedStatement ps = connection
.prepareStatement("insert into ws1.test_timezone(id,t,d) values (15,?,?)")) {

//本地东八区 2022-06-22 03:29:29对应的毫秒数
Timestamp ts1 = new Timestamp(1655839769000L);
Timestamp ts2 = new Timestamp(1655839769000L);

ps.setTimestamp(1, ts1);
ps.setTimestamp(2, ts2);
ps.execute();
}

会调用 ClientPreparedQueryBindings 类中的 setTimestamp 方法,这里会获取配置的 serverTimezone,我们是+9。传递的 Timestamp 对象,按照本地时区展示的时间应该是 2022-06-22 03:29:29,在通过 setTimestamp 写入的时候,会根据指定的 serverTimezone 来调整时间。serverTimezone 是+9,相比本地的时间需要多加 1 小时,因此 client 重新 format 后的结果是 2022-06-22 04:29:29,最终 sendPacket 的时候发送的就是这个文本。

知道了数据库实际是以数据库时区的 2022-06-22 04:29:29 存储的,根据我们读取数据的原理,也就知道了获取到的数据为:

  • client 端使用 getString 读取结果为:2022-06-22 04:29:29
  • client 端使用 getTimestamp 读取结果为:2022-06-22 17:29:29.0

binlog 中的时区

数据库 timestamp 类型是按照时间戳记录的,因此 binlog 中 timestamp 类型是按照时间存储,取出来的时候根据时区做转换。

应用——数据同步中的跨时区同步

全量迁移

全量迁移一般是基于 query sql 下发给 mysql driver 将数据 scan 出来的。为了保证源对端的市区一致性,采用如下方式即可:

1
2
- 源端使用getString获取timestamp列的value
- 对端写入时使用setString设置timestamp列的value

统一采用 getString 和 setString 按照字符串处理,则值的读写和本地时区以及 jdbc 的 serverTimezone 均无关

增量同步

MySQL 增量同步主要是基于 binlog 解析来完成同步的。binlog 中的 timestamp 类型是按照距离 1970 的时间戳存储的(小数点后的值单独存储)。 以 canal/cloudcanal 为例,对 timestamp 类型的解析处理代码如下,将毫秒数直接转成本地 local timezone 的 Timestamp 对象。这样处理的话就直接改变了时区的值。例如源端数据库是-5 时区,按照本地程序东八时区构造的 timestamp 对象,则会比原始数据库读取显示的值大 13 个小时。

解决方案:

解析的时候处理 binlog 中的时间戳时,format 的时候需要按照源端数据库的时区进行 format,然后对端写入的时候使用 setString 处理或者 setObject(也是按照字符串写入)的进行处理即可。

总结

本文详细介绍了 mysql driver 读写时时区处理的各种情况,后续在涉及各种跨时区的时间处理问题上,都可以按照以上原理来判断时区的处理是否准确。