一文讲透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根据使用不同的方法进行不同的时间处理

image.png

rs.getString()的底层原理

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

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再最终生成本地展示的时间

image.png

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

getString和getTimestamp读取时间处理总结

image.png

读取时间的测试

读取测试环境说明

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

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的调用链如下:
image.png

写入方式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的时候发送的就是这个文本。
image.png
知道了数据库实际是以数据库时区的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类型是按照时间存储,取出来的时候根据时区做转换。
image.png

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

全量迁移

全量迁移一般是基于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个小时。
image.png

解决方案:

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

总结

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