ShardingSphere Pipeline 兼容 MySQL 时间类型 解读

背景

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 bytes

底层是按照年月日时分秒存储的，这些数据在存取时未进行时区转换，同理，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);

通过 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)

通过 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 (