ShardingSphere Pipeline 兼容 MySQL 时间类型解读

hexiaox810

发布于 2023-9-11 11:46

浏览

0收藏

背景

ShardingSphere

在日常中，开发者经常会用到时间类型，如果熟悉的时间类型涉及到了时区、精度，哪种时间类型更合适？JDBC 驱动中又有哪些注意事项？因此，对数据库时间类型有更深入的了解，有助于开发者对于相关问题的排查。

本文简要介绍常用的 MySQL 时间类型，并结合 ShardingSphere Pipeline 中的逻辑，解读在程序中如何兼容 MySQL 的时间类型。

MySQL 时间类型简介

ShardingSphere

MySQL 时间类型主要有如下 5 种

时间类型	所需空间（5.6.4前）	所需空间（5.6.4后）	"0" 值
YEAR	1 byte	1 byte	0
DATE	3 bytes	3 bytes	'0000-00-00'
TIME	3 bytes	3 bytes + fractional seconds storage	'00:00:00'
DATETIME	8 bytes	5 bytes + fractional seconds storage	'0000-00-00 00:00:00'
TIMESTAMP	4 bytes	4 bytes + fractional seconds storage	'0000-00-00 00:00:00'

TIME，DATETIME，TIMESTAMP 的存储结构在 MySQL 版本 5.6.4前后发生了变化，在5.6.4之后这 3 种类型都支持小数（Fractional Seconds Precision），可以给出0到6范围内的可选fsp值，以指定小数秒精度。值为 0 表示不存在小数部分。如果省略，则默认精度为0。

01 服务器时区的影响

这里的时区，指的是 MySQL 中的

time_zone

配置，默认值是 SYSTEM，此时使用的是全局参数 system_time_zone 的值（默认是当前系统的时区），可以在 session 级别覆盖。

展示受时区影响的只有 TIMESTAMP，其他类型都不受时区影响，这里解释下 DATETIME 和 TIME 的底层数据结构，便于理解为什么不受时区影响。

DATETIME 的数据结构如下（5.6.4之后的版本）

1 bit  sign           (1= non-negative, 0= negative)
17 bits year*13+month  (year 0-9999, month 0-12)
 5 bits day            (0-31)
 5 bits hour           (0-23)
 6 bits minute         (0-59)
 6 bits second         (0-59)
--------------------------- 40 bits = 5 bytes1.
2.
3.
4.
5.
6.
7.

底层是按照年月日时分秒存储的，这些数据在存取时未进行时区转换，同理，TIME 和 DATE 也是相似的。

而 TIMESTAMP 类型存入数据库的实际是 UTC 的时间，查询显示时会根据具体的时区显示不同的时间。

测试用例如下：

// 初始化表
create table test
(
    id          int       not null
        primary key,
    c_timestamp timestamp null,
    c_datetime  datetime  null,
    c_date      date      null,
    c_time      time      null,
    c_year      year      null
);

// 插入一条数据
INSERT INTO test (id, c_timestamp, c_datetime, c_date, c_time, c_year) VALUES (1, '2023-07-03 08:00:00', '2023-07-03 08:00:00', '2023-07-03', '08:00:00', 2023);1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.

通过 MySQL 客户端连上 MySQL 服务端

mysql> show global variables like '%time_zone%';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone | CST    |
| time_zone        | +00:00 |
+------------------+--------+
2 rows in set (0.01 sec)

mysql> select * from test;
+----+---------------------+---------------------+------------+----------+--------+
| id | c_timestamp         | c_datetime          | c_date     | c_time   | c_year |
+----+---------------------+---------------------+------------+----------+--------+
|  1 | 2023-07-03 00:00:00 | 2023-07-03 08:00:00 | 2023-07-03 | 08:00:00 |   2023 |
+----+---------------------+---------------------+------------+----------+--------+
1 row in set (0.00 sec)

mysql> SET SESSION time_zone = "+8:00";
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test;
+----+---------------------+---------------------+------------+----------+--------+
| id | c_timestamp         | c_datetime          | c_date     | c_time   | c_year |
+----+---------------------+---------------------+------------+----------+--------+
|  1 | 2023-07-03 08:00:00 | 2023-07-03 08:00:00 | 2023-07-03 | 08:00:00 |   2023 |
+----+---------------------+---------------------+------------+----------+--------+
1 row in set (0.00 sec)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.

通过 SET SESSION time_zone = "+8:00"; 修改了时区，timestamp 类型的字段查询结果会发生变化，其他的类型则都不会发生变化。

02 特殊的 "0" 值

MySQL 允许存储 "0" 值，有时候也不一定是 "0" 值，例如 date 类型，MySQL 也可以将不存在的日期值转为 "0" 值，比如 2023-02-30。

不过这种行为有一个条件，sql_mode不能开启严格模式，即不能包含 STRICT_TRANS_TABLES or STRICT_ALL_TABLES。关于 sql_mode 的内容还有很多，这里就不展开了。

详情可参考官方文档：

https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html

// 本地将 sql_mode 清空，即不配置严格模式
mysql> show global variables like '%sql_mode%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_mode      |       |
+---------------+-------+
1 row in set (0.05 sec)

mysql> INSERT INTO test (id, c_timestamp, c_datetime, c_date, c_time, c_year) VALUES (2, null, null, '2023-02-30', null, null);
Query OK, 1 row affected, 1 warning (0.08 sec)

mysql> select * from test;
+----+---------------------+---------------------+------------+----------+--------+
| id | c_timestamp         | c_datetime          | c_date     | c_time   | c_year |
+----+---------------------+---------------------+------------+----------+--------+
|  1 | 2023-07-03 00:00:00 | 2023-07-03 08:00:00 | 2023-07-03 | 08:00:00 |   2023 |
|  2 | NULL                | NULL                | 0000-00-00 | NULL     |   NULL |
+----+---------------------+---------------------+------------+----------+--------+
2 rows in set (0.00 sec)1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.

有些细节需要注意，MySQL 之前的版本支持 YEAR 类型指定长度，比如 YEAR(2) 和 YEAR(4)，但是在 MySQL 8 最新的版本已经不允许指定 YEAR 的长度了。并且 MySQL 8 默认是使用严格模式的。

严格模式有助于保证数据的完整性，便于数据在不同环境，不同数据库系统中流转。

Pipeline 中对于时间类型的兼容

ShardingSphere

数据迁移，数据一致性校验，CDC 都会依赖 Pipeline 的底层功能，要求保证数据的正确性和完整性。由于时间类型会涉及到时区、精度，这部分相对来会有更多的地方需要兼容处理。

这里比较核心的就是 JDBC 驱动了，程序和数据库都是通过 JDBC 进行交互的，所以 JDBC 的一些参数配置，会影响到数据的展示。

ShardingSphere Pipeline 兼容 MySQL 时间类型解读-鸿蒙开发者社区

在 Java 程序里，需要注意 Application 到 JDBC 这层的数据转换，了解时间类型在这步是如何处理的，在下文会通过一些例子来说明。MySQL 官方的 JDBC 驱动是 MySQL Connector/J ，使用 C/J 缩写来表示。

以下所有的例子，MySQL 服务器的时区默认都是 UTC 时区，如下

mysql> show global variables like '%time_zone%';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone | CST    |
| time_zone        | +00:00 |
+------------------+--------+
2 rows in set (0.01 sec)1.
2.
3.
4.
5.
6.
7.
8.

01 时区的注意事项

通过设置 C/J 连接属性 serverTimezone，可以覆盖服务器的时区。如果不指定，服务器配置的默认时区会生效。

最好手动指定 serverTimezone，避免出现 MySQL 中的默认时区和 Java 时区含义不一致的情况，比如 CST，在 Java 的 TimeZone 中指的是 America/Chicago，但是在 MySQL 中却是China Standard Time。

参考：

https://docs.oracle.com/javase/8/docs/api/java/time/ZoneId.html

serverTimezone 的值，正常情况都需要和服务器的时区保持一致，否则可能会导致写入数据不符合预期。

这里使用 C/J 8 测试下，版本是 8.0.23

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.23</version>
</dependency>1.
2.
3.
4.
5.

下面是测试用例

class TimeTest {
    
    @Test
    void testServerTimezone() throws SQLException {
        System.out.println(TimeZone.getDefault().getID());
        Timestamp timestamp = Timestamp.valueOf(LocalDateTime.of(2023, 7, 7, 12, 1, 0));
        System.out.println(timestamp + ", unix timestamp(seconds):" + timestamp.getTime() / 1000);
        int id = 10;
        try (Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/ds_0?allowPublicKeyRetrieval=true&useSSL=false", "root", "root")) {
            PreparedStatement preparedStatement = connection.prepareStatement("INSERT INTO test (id, c_timestamp, c_datetime) VALUES (?,?,?)");
            preparedStatement.setInt(1, id);
            preparedStatement.setTimestamp(2, timestamp);
            preparedStatement.setTimestamp(3, timestamp);
            preparedStatement.executeUpdate();
        }
        try (Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/ds_0?serverTimezone=GMT&allowPublicKeyRetrieval=true&useSSL=false", "root", "root")) {
            PreparedStatement preparedStatement = connection.prepareStatement("INSERT INTO test (id, c_timestamp, c_datetime) VALUES (?,?,?)");
            preparedStatement.setInt(1, id + 1);
            preparedStatement.setTimestamp(2, timestamp);
            preparedStatement.setTimestamp(3, timestamp);
            preparedStatement.executeUpdate();
        }
}
// 输出结果如下
Asia/Shanghai
2023-07-07 12:01:00.0, unix timestamp(seconds):16887024601.
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.

数据库的数据如下，其中 10，11 两条记录是新插入的数据。

mysql> select * from test;
+----+---------------------+---------------------+------------+----------+--------+
| id | c_timestamp         | c_datetime          | c_date     | c_time   | c_year |
+----+---------------------+---------------------+------------+----------+--------+
|  1 | 2023-07-03 00:00:00 | 2023-07-03 08:00:00 | 2023-07-03 | 08:00:00 |   2023 |
| 10 | 2023-07-07 12:01:00 | 2023-07-07 12:01:00 | NULL       | NULL     |   NULL |
| 11 | 2023-07-07 04:01:00 | 2023-07-07 04:01:00 | NULL       | NULL     |   NULL |
+----+---------------------+---------------------+------------+----------+--------+
3 rows in set (0.01 sec)1.
2.
3.
4.
5.
6.
7.
8.
9.

其中 10 这条数据的 c_timestamp 和 c_datetime 已经发生数据的偏移，而 11 这条数据是正确的，可以通过下面命令验证

mysql> select id,unix_timestamp(c_timestamp), unix_timestamp(c_datetime) from test where id in (10, 11);
+----+-----------------------------+----------------------------+
| id | unix_timestamp(c_timestamp) | unix_timestamp(c_datetime) |
+----+-----------------------------+----------------------------+
| 10 |                  1688731260 |                 1688731260 |
| 11 |                  1688702460 |                 1688702460 |
+----+-----------------------------+----------------------------+
2 rows in set (0.00 sec)1.
2.
3.
4.
5.
6.
7.
8.

11 中的时间已经比程序中插入时间多了 8 小时。

通过结果我们来分析下原因，当 JDBC URL 中没有指定 serverTimezone 的时候发生了什么。在一开始获取 Connection 时，com.mysql.cj.protocol.a.NativeProtocol#configureTimeZone 中设置了 session 的 timezone。

ShardingSphere Pipeline 兼容 MySQL 时间类型解读-鸿蒙开发者社区

connectionTimezone 等同于 serverTimezone ，执行是走了 if 逻辑中的第一个判断，也就是上图的 1。所以这个时候的 session timezone 已经变成了 Asia/Shanghai。

接下来看下 preparedStatement 设置的 java.sql.Timestamp 参数，JDBC 驱动是如何进行转换的，相关代码在 com.mysql.cj.ClientPreparedQueryBindings#bindTimestamp，如下

ShardingSphere Pipeline 兼容 MySQL 时间类型解读-鸿蒙开发者社区

这里生效的逻辑是上图红色圈起来的部分，也就是 session timezone，上面确认过，是 Asia/Shanghai。所以这时 SimpleDateFormat 和 Timestamp 时区是一致的，format 结果就是 2023-07-07 12:01:00。

当 JDBC URL 中带有 serverTimezone时，此时 SimpleDateFormat 和 Timestamp 的时区不一致，format 结果就发生了时区转换。这种时区转换，结果是正确的。

ShardingSphere Pipeline 兼容 MySQL 时间类型解读-鸿蒙开发者社区

但是回头看看最开头的那句话，和这个现象是违背的

通过设置 C/J 连接属性 serverTimezone，可以覆盖服务器的时区。如果不指定，服务器配置的默认时区会生效。

从结果来说，如果不设置 serverTimezone，MySQL 并不会使用服务器的时区，用的是 C/J 程序默认所在时区。这个现象，可以追溯到 https://bugs.mysql.com/bug.php?id=85816。

这里面相关的内容提到，C/J 8 为了保持和历史版本的行为一致，默认在 C/J 5 中，如果 JDBC URL 中没有配置 serverTimezone，用的就是 C/J 程序运行时所在的时区，事实上，C/J 5 只配置 serverTimezone 参数的话，和 C/J 8 的行为仍然是不一致的。

即如果需要开启时区调整

C/J 5.1 需要同时配置 useLegacyDatetimeCode=false&serverTimezone=XXX这两个参数
C/J 8 只需要配置 serverTimezone=XXX

除了使用 Timestamp，也可以使用字符串形式写入时间类型，这种方式不会触发时区转换，感兴趣的同学可以自行 Debug 源码里面看一下。

@Test
void testServerTimezoneWithString() throws SQLException {
    int id = 111;
    Timestamp timestamp = Timestamp.valueOf(LocalDateTime.of(2023, 7, 7, 12, 1, 0));
    try (Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/ds_0?serverTimezone=GMT&allowPublicKeyRetrieval=true&useSSL=false", "root", "root")) {
        PreparedStatement preparedStatement = connection.prepareStatement("INSERT INTO test (id, c_timestamp, c_datetime) VALUES (?,?,?)");
        preparedStatement.setInt(1, id);
        preparedStatement.setString(2, "2023-07-07 12:01:02");
        preparedStatement.setTimestamp(3, timestamp);
        preparedStatement.executeUpdate();
    }
}1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.

对应的结果，可以看到使用 setString() 方法设置的时间是不会被调整的。

mysql> mysql> * from test where id in (11,111);
+-----+---------------------+---------------------+--------+--------+--------+
| id  | c_timestamp         | c_datetime          | c_date | c_time | c_year |
+-----+---------------------+---------------------+--------+--------+--------+
|  11 | 2023-07-07 04:01:00 | 2023-07-07 04:01:00 | NULL   | NULL   |   NULL |
| 111 | 2023-07-07 12:01:02 | 2023-07-07 04:01:00 | NULL   | NULL   |   NULL |
+-----+---------------------+---------------------+--------+--------+--------+
2 rows in set (0.10 sec)1.
2.
3.
4.
5.
6.
7.
8.

关于时区，除了关键的 serverTimezone 参数，还有一些另外的参数也会影响时区转换的逻辑，比如 C/J 8 中的 preserveInstants 和 forceConnectionTimeZoneToSession，可以参考官网的解释，这里就不展开了。https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-time-instants.html。如果出现时间不符合预期的情况，可以通过类似的方式进行排查。

02 精度的注意事项

MySQL 时间是有小数位的，最小精度是微秒，不过有以下几个要求

1、建表的时候需要指定小数位，不指定默认小数位是 0，也就说最小精度就是秒，将建表语句改写下。

create table test_fst
(
    id          int       not null
        primary key,
    c_timestamp timestamp(6) null,
    c_datetime  datetime(6)  null,
    c_time      time(6)      null
);1.
2.
3.
4.
5.
6.
7.
8.

测试用例如下

@Test
void testFst() throws SQLException {
    int id = 1;
    Timestamp timestamp = Timestamp.valueOf(LocalDateTime.of(2023, 7, 7, 12, 1, 0, 123456789));
    try (Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/ds_0?allowPublicKeyRetrieval=true&useSSL=false&", "root", "root")) {
        PreparedStatement preparedStatement = connection.prepareStatement("INSERT INTO test_fst (id, c_timestamp, c_datetime, c_time) VALUES (?,?,?,?)");
        preparedStatement.setInt(1, id);
        preparedStatement.setString(2, "2023-07-07 12:01:00.123456789");
        preparedStatement.setTimestamp(3, timestamp);
        preparedStatement.setTime(4, Time.valueOf(LocalTime.of(1, 1, 1, 123456789)));
        // preparedStatement.setObject(4, LocalTime.of(1, 1, 1, 123456789));
        preparedStatement.executeUpdate();
    }
}1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.

查看插入的数据，结果如下

mysql> select * from test_fst;
+----+----------------------------+----------------------------+-----------------+
| id | c_timestamp                | c_datetime                 | c_time          |
+----+----------------------------+----------------------------+-----------------+
|  1 | 2023-07-07 12:01:00.123457 | 2023-07-07 12:01:00.123457 | 01:01:01.000000 |
+----+----------------------------+----------------------------+-----------------+
1 row in set (0.00 sec)1.
2.
3.
4.
5.
6.
7.

c_timestamp 和 c_datetime 的值都包含小数位，最后一位发生了四舍五入，所以小数位变成了 .123457。
c_time 并没有小数位，虽然我们在代码中明确指定了。

改成使用 MySQL Client 插入类似的数据，发现可以正常写入小数位。所以这一定又和 C/J 的实现有关。

mysql> insert into test_fst(id, c_time) values (2, '01:01:01.123457');
Query OK, 1 row affected (0.02 sec)

mysql> select * from test_fst where id = 2;
+----+-------------+------------+-----------------+
| id | c_timestamp | c_datetime | c_time          |
+----+-------------+------------+-----------------+
|  2 | NULL        | NULL       | 01:01:01.123457 |
+----+-------------+------------+-----------------+
1 row in set (0.00 sec)1.
2.
3.
4.
5.
6.
7.
8.
9.
10.

MySQL 官网中提到：根据 JDBC 的规范，对于 java.sql.Time 类型，设计上不应该包含小数，但是因为 java.sql.Time 是 java.sql.Date 的子类，实际使用中会包含小数信息，所以在高版本的驱动中其实是支持了小数，并且默认是打开的。在 8.0.23 版本中引入了一个连接属性，sendFractionalSecondsForTime，默认值就是 true。

从 8.0.23 开始，JDBC URL 中sendFractionalSeconds 的配置是全局控制所有时间类型发送小数点的。如果 sendFractionalSeconds=false，无论

sendFractionalSecondsForTime 的值如何，都不会发送小数秒。

我们用的就是 C/J 8，并且版本也是 8.0.23，但是为什么结果是错的，其实是 Java 代码中 LocalTime 和 Time 转换的行为导致的，Time.valueOf 方法会舍去 LocalTime 中的纳秒值，所以应该使用注释掉的那行方法。

public class Time extends java.util.Date {

    // 舍去了 LocalTime 中的小数值
    public static Time valueOf(LocalTime time) {
        return new Time(time.getHour(), time.getMinute(), time.getSecond());
    }
    
    // 这里也会舍去 Time 本身中含有的小数值
    public LocalTime toLocalTime() {
        return LocalTime.of(getHours(), getMinutes(), getSeconds());
    }
}1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.

但是如果使用的是低版本的驱动，比如 C/J 5，不论怎样都无法将小数秒传递到 MySQL 服务器。

使用 C/J 5.1 的测试下

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.49</version>
</dependency>1.
2.
3.
4.
5.

ShardingSphere Pipeline 兼容 MySQL 时间类型解读-鸿蒙开发者社区

JDBC42Helper.convertJavaTimeToJavaSql 的方法如下，对于 LocalTime，在这里使用的就是会丢失纳秒 Time.valueOf 方法。

public class JDBC42Helper {
    /**
     * JDBC 4.2 Helper methods.
     */
    static Object convertJavaTimeToJavaSql(Object x) {
        if (x instanceof LocalDate) {
            return Date.valueOf((LocalDate) x);
        } else if (x instanceof LocalDateTime) {
            return Timestamp.valueOf((LocalDateTime) x);
        } else if (x instanceof LocalTime) {
            return Time.valueOf((LocalTime) x);
        }
        return x;
    }
}1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.

看完了写入，再来看看读取，我们上面通过 MySQL Client 写入了一条 id = 2 的数据，通过 C/J 查询这条数据。

@Test
void testFst() throws SQLException {
    try (Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/ds_0?allowPublicKeyRetrieval=true&useSSL=false&", "root", "root")) {
        PreparedStatement queryStatement = connection.prepareStatement("select id, c_time from test_fst where id = ?");
        queryStatement.setInt(1, 2);
        ResultSet resultSet = queryStatement.executeQuery();
        resultSet.next();
        System.out.println(resultSet.getObject(2).getClass());
        System.out.println("getObject: " + resultSet.getObject(2));
        System.out.println("getTimestamp: " + new Timestamp(resultSet.getTime(2).getTime()).getNanos());
    }
}1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.

结果如下，数据库中含有的小数位也被丢弃了。

class java.sql.Time
getObject: 01:01:01
getTimestamp: 01.
2.
3.

03 关于数据一致性对比

Pipeline 中的数据一致性校验模块被用于比较迁移前后的数据是否一致。这里也有一些问题需要注意

时区问题（上面已经讨论过，这里不重复了）
数据库字段有特殊值的问题（比如上面提到的 "0" 值）
数据库同一类型的字段在程序中展现的类型不一致（通常和 JDBC 驱动参数有关）

第二个问题，虽然数据库层面可以存储 "0" 值，但是通过 C/J 查询数据的时候，收到 zeroDateTimeBehavior参数的影响，这个参数 C/J 5 和 8 之间是不兼容的，仅在写法上有区别，具体含义没有发生变化。

C/J 5：exception，round，convertToNull
C/J 8：EXCEPTION，ROUND， CONVERT_TO_NULL

其中 exception 是默认值，通过下面的测试用例来验证下。

先插入一条数据

mysql> INSERT INTO test (id, c_timestamp, c_datetime, c_date, c_time, c_year) VALUES (1000, '0000-00-00', '0000-00-00', '2023-02-30', null, null);1.

通过 JDBC 查询数据

@Test
void tesZero() throws SQLException {
    try (Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/ds_0?allowPublicKeyRetrieval=true&useSSL=false&", "root", "root")) {
        PreparedStatement queryStatement = connection.prepareStatement("select id, c_timestamp from test where id = ?");
        queryStatement.setInt(1, 1000);
        ResultSet resultSet = queryStatement.executeQuery();
        resultSet.next();
        System.out.println(resultSet.getObject(2));
    }
}1.
2.
3.
4.
5.
6.
7.
8.
9.
10.

得到一个异常

java.sql.SQLException: Zero date value prohibited

        at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129)
        at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
        at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:89)
        at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:63)
        at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:73)
        at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:99)
        at com.mysql.cj.jdbc.result.ResultSetImpl.getTimestamp(ResultSetImpl.java:933)
        at com.mysql.cj.jdbc.result.ResultSetImpl.getObject(ResultSetImpl.java:1265)1.
2.
3.
4.
5.
6.
7.
8.
9.
10.

我们通过 C/J 8 中的一段源码来看下这个转换逻辑

@Override
public T createFromTimestamp(InternalTimestamp its) {
    if (its.isZero()) {
        switch (this.pset.<PropertyDefinitions.ZeroDatetimeBehavior>getEnumProperty(PropertyKey.zeroDateTimeBehavior).getValue()) {
            case CONVERT_TO_NULL:
                return null;
            case ROUND:
                return localCreateFromTimestamp(new InternalTimestamp(1, 1, 1, 0, 0, 0, 0, 0));
            default:
                break;
        }
    }
    return localCreateFromTimestamp(its);
}1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.

默认的话会走到 localCreateFromTimestamp 方法， "0" 值的话会在下面这里抛出异常。

public class SqlTimestampValueFactory extends AbstractDateTimeValueFactory<Timestamp> {

    @Override
    public Timestamp localCreateFromTimestamp(InternalTimestamp its) {
        if (its.getYear() == 0 && its.getMonth() == 0 && its.getDay() == 0) {
            throw new DataReadException(Messages.getString("ResultSet.InvalidZeroDate"));
        }
        ......
    }
}1.
2.
3.
4.
5.
6.
7.
8.
9.
10.

如果 C/J 中增加 CONVERT_TO_NULL 参数，则会返回 null，如果是 round 参数，则返回 0001-01-01 00:00:00，这些情况会导致源端和目标端两者的数据不一致。

除此之外，yearIsDateType也会影响 YEAR 的返回类型，如果为 true 则程序中读到的是 java.sql.Date 类型，否则就是 Short 类型。

结论

ShardingSphere

根据上面的一些结果和原因分析，可以得出一些通用的结论，可以尽量避免时间类型使用中出现的问题。

1、首先 MySQL time_zone 不推荐使用 SYSTEM，官网原文如下

If set to SYSTEM, every MySQL function call that requires a time zone calculation makes a system library call to determine the current system time zone. This call may be protected by a global mutex, resulting in contention.

2、尽量使用高版本的驱动，支持的功能更多，官方文档也更详细。

3、最好保证应用程序和数据库之间时区是一样的，如果真的有跨时区展示的需求，考虑是否可以用整型代替 MySQL 中的 timestamp 和 datetime 类型。

以上就是本次分享的全部内容，如果读者对 Apache ShardingSphere 有任何疑问或建议，欢迎在 GitHub issue 列表提出。

文章转载自公众号：ShardingSphere官微

分类

其他

标签

ShardingSphere

已于2023-9-11 11:46:49修改

51CTO

51CTO博客

51CTO学堂

ShardingSphere Pipeline 兼容 MySQL 时间类型解读

订阅鸿蒙技术特刊，精选内容抢先看

51CTO

51CTO博客

51CTO学堂

ShardingSphere Pipeline 兼容 MySQL 时间类型 解读

订阅鸿蒙技术特刊，精选内容抢先看

ShardingSphere Pipeline 兼容 MySQL 时间类型解读