关于debezium同步mysql字段类型datetime、date、time、timestamp的格式转换说明

1.情景展示

使用debezium的插件：debezium-connector-mysql（io.debezium.connector.mysql.MySqlConnector），自动读取mysql日志binlog相关表的数据变更记录，然后将其发布到kafka topic当中。

现在遇到的问题是：

在mysql当中，表示日期类型的数据类型有：datetime、date和timestamp；

表示时间类型的是：time。

当使用io.debezium.connector.mysql.MySqlConnector插件读取mysql数据后，debezium自动对日期类型进行了格式转换。

2.具体分析

先说都转成了什么数据类型，再说怎么解决。

如上图所示，该表当中每个日期类型都有，下面我们进行实战演练。

抓取数据

我使用的REST API的方式来抓取mysql数据。

消费数据

得到其中一条数据如下：

所使用的debezium-connector-mysql插件版本号：2.2.1

id=4的mysql数据展示如下：

由此可以得出以下结论：

mysql datetime类型：使用的转换类是io.debezium.time.Timestamp，最终转成了时间戳（1970年01月01日0时0分0秒到指定日期的毫秒数），形如：1702027934000。

mysql date类型：使用的转换类是io.debezium.time.Date，最终转成了天数（1970年01月01日到指定日期的天数），形如：19699。

mysql timestamp类型：使用的转换类是io.debezium.time.ZonedTimestamp，最终转成了UTC时间，形如：2023-12-08T15:32:19Z

mysql time类型：使用的转换类是io.debezium.time.MicroTime，最终转成了微妙数（将小时转成了微妙），形如：34341000000。

3.解决方案

在mysql当中，最常用的是datetime类型，当将我们将时间戳转回日期字符串时，发现：所得时间比原来的时间早了8个小时。

这是因为：debezium在将datetime类型转成时间戳时，所用时区为：UTC，而我们在解析时间的时候，用的时区却是：UTC+8，所以最终转换得来的时间会比实际时间早8个小时。

网上搜到的解决办法都是：

无论是设置连接数据库所用时区还是设置数据库服务时间为东八区，统统不管用！

debezium在进行日期格式转换时，使用的是UTC，这是在代码里面写死的，所以你再怎么设置数据库时间都无济于事！

推荐使用第三种解决方案

第一种方案：更改debezium源码

源码地址：https://github.com/debezium/debezium

更改源码后，重新打包。

第二种方案：自定义开发Sink Connector

既然我们没有办法通过参数设置，将日期还原成正确时区，那我们完全可以从kafka拿数据，然后自行解析。

先说如何将日期还原成真实日期。

datetime还原

// 年月日（大写M：表示月份，小写m：表示分钟）
public static final String FORMAT_DATE = "yyyy-MM-dd";
// 时分秒（大写H：表示24小时制，小写h：表示12小时制）
public static final String FORMAT_TIME = "HH:mm:ss";
// 年月日时分秒
public static final String FORMAT_DATE_TIME = FORMAT_DATE + " " + FORMAT_TIME;

public static String timestampToString(Long timestamp, String timeZone) {
    if (String.valueOf(timestamp).length() == 16) {// 16：毫秒
        return toDateTimeString(fromTimeMills(timestamp / 1000, timeZone), FORMAT_DATE_TIME);
    } else {// 13：秒
        return toDateTimeString(fromTimeMills(timestamp, timeZone), FORMAT_DATE_TIME);
    }
}

public static LocalDateTime fromTimeMills(long timeMills, String timeZone){
    return LocalDateTime.ofInstant(Instant.ofEpochMilli(timeMills), ZoneId.of(timeZone));
}

/**
 * 日期转字符串（日期+时间）
 * @attention: jdk>=1.8
 * @date: 2020年08月31日 0031 17:04
 * @param: dateTime
 * @param: pattern
 * @return: java.lang.String
 */
public static String toDateTimeString(LocalDateTime dateTime, String pattern) {
    DateTimeFormatter dateTimeFormatter = DateTimeFormatter.ofPattern(pattern);
    return dateTimeFormatter.format(dateTime);
}

调用

timestampToString(1702027934000L,"UTC");// 2023-12-08 09:32:14

date还原

/**
 * 根据天数倒推日期
 * https://www.cnblogs.com/Marydon20170307/p/10672030.html
 * @param days
 * @return
 */
public static String getSomeDay(int days){
    SimpleDateFormat sdf = new SimpleDateFormat(FORMAT_DATE);
    Date date;
    try {
        date = sdf.parse("1970-01-01");
    } catch (ParseException e) {
        throw new RuntimeException(e);
    }
    Calendar calendar = Calendar.getInstance();
    calendar.setTime(date);
    calendar.add(Calendar.DAY_OF_YEAR, days);
    date = calendar.getTime();
    return sdf.format(date);
}

调用

getSomeDay(19699);// 2023-12-08

timestamp还原

/**
 * ISO 8601标准日期转成字符串
 * @param dateTimeStr
 * 2023-12-07T16:00:00Z
 * "2023-12-07T16:00:00Z"是一种ISO 8601标准的日期时间表示方式
 * 这个字符串表示的是一个特定的时间点：2023年12月7日，下午4点（16点），0分钟，0秒。其中“T”是时间标识符，“Z”表示的是协调世界时（UTC）。
 * 这种格式是可以精确到秒的时间戳
 * @return
 */
public static String fromISO8601(String dateTimeStr) {
    return toDateTimeString(toLocalDateTime(dateTimeStr), FORMAT_DATE_TIME);
}

/**
 * 日期字符串按指定格式转LocalDateTime
 * @attention:
 * @date: 2021/7/28 15:05
 * @param: dateTimeStr 日期字符串
 * 2023-12-07T16:00:00Z
 * "2023-12-07T16:00:00Z"是一种ISO 8601标准的日期时间表示方式
 * 这个字符串表示的是一个特定的时间点：2023年12月7日，下午4点（16点），0分钟，0秒。其中“T”是时间标识符，“Z”表示的是协调世界时（UTC）。
 * 这种格式是可以精确到秒的时间戳
 * @return: java.time.LocalDateTime
 */
public static LocalDateTime toLocalDateTime(String dateTimeStr) {
    // UTC时间
    DateTimeFormatter formatter = DateTimeFormatter.ISO_DATE_TIME;
    ZonedDateTime zonedDateTime = ZonedDateTime.parse(dateTimeStr, formatter);
    // TODO
    return zonedDateTime.toLocalDateTime().plusHours(-6);
}

说明：将UTC时间转成本地时间后，需要向前或向后推几个小时，需要自己算一下。

这里的-6，不一定对，我也不知道debezium是怎么进行转换的，竟然比实际时间早了6个小时，而不是8个小时。

调用

fromISO8601("2023-12-08T15:32:19Z");// 2023-12-08 09:32:19

至于自定义开发Sink Connector组件，请看文末推荐。

2024年2月19日16:54:19

第三种方案：使用io.debezium.connector.jdbc.JdbcSinkConnector完成数据同步（推荐使用）

由于网络上的误导，上面两种方式都走偏了。

其实，使用debezium官方组件debezium-connector-jdbc来同步debezium-connector-mysql组件生成的数据，数据是可以实现正常转换的。

无需我们再进行手动转换！！！

2024-06-17 10:56:39

当源库是mysql，源表有timestamp字段，且使用debezium-connector-mysql捕获数据，且目标表要同步源表的timestamp类型字段时，timestamp字段在同步至目标表时，会存在时间差的问题，解决方案需要分情况讨论：

如果目标库是mysql，用debezium-connector-jdbc订阅数据，debezium-connector-mysql需要增加配置参数：database.connectionTimeZone=GMT+8；

如果目标库是oceanbase，用debezium-connector-jdbc订阅数据，debezium-connector-mysql需要增加配置参数：database.connectionTimeZone=GMT+8；

如果目标库是sqlserver，用debezium-connector-jdbc订阅数据，debezium-connector-mysql需要增加配置参数：database.connectionTimeZone=GMT；

如果目标库是oracle，用debezium-connector-jdbc订阅数据，debezium-connector-mysql需要增加配置参数：database.connectionTimeZone=GMT。

4.拓展

mysql（debezium-connector-mysql）

datetime类型：会被转成io.debezium.time.Timestamp；

date类型：会被转成io.debezium.time.Date；

timestamp类型：会被转成io.debezium.time.ZonedTimestamp；

time类型：会被转成io.debezium.time.MicroTime。

oracle（debezium-connector-oracle）

date类型（包含带时间和不带时间）：会被转成io.debezium.time.Timestamp；

timestamp类型：会被转成io.debezium.time.MicroTimestamp。

sqlserver（debezium-connector-sqlserver）

datetime类型：会被转成io.debezium.time.Timestamp；

datetime2类型：会被转成io.debezium.time.Timestamp；

date类型：会被转成io.debezium.time.Date；

smalldatetime类型：会被转成io.debezium.time.Timestamp；

datetimeoffset类型：会被转成io.debezium.time.ZonedTimestamp；

time类型：会被转成io.debezium.time.MicroTime。

说明：

至于将debezium转换后date类型数据，如何转回来，感兴趣的，可以看文末推荐的假删除文章。

 

posted @ 2023-12-22 16:29  Marydon  阅读(1027)  评论(0)  编辑  收藏  举报