社区首页 >专栏 >r2dbc指定时区问题

r2dbc指定时区问题

阿超

发布于 2022-11-12 05:37:49

93900

代码可运行

文章被收录于专栏：快乐阿超快乐阿超

运行总次数：0

代码可运行

政治能把一个人突然变老——巴尔扎克

今天看见这个警告

我的配置项如下：

import cn.hutool.core.util.StrUtil;
import com.alibaba.druid.util.JdbcUtils;
import com.baomidou.dynamic.datasource.spring.boot.autoconfigure.DataSourceProperty;
import com.baomidou.dynamic.datasource.spring.boot.autoconfigure.DynamicDataSourceProperties;
import io.r2dbc.spi.ConnectionFactories;
import io.r2dbc.spi.ConnectionFactory;
import io.r2dbc.spi.ConnectionFactoryOptions;
import io.r2dbc.spi.Option;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.ComponentScan;
import org.springframework.context.annotation.Configuration;

import java.time.Duration;
import java.util.Map;

import static io.r2dbc.spi.ConnectionFactoryOptions.*;

@Configuration
public class R2dbcConfig {

	@Bean
    public ConnectionFactory connectionFactory(DynamicDataSourceProperties dataSource) {
        Map<String, DataSourceProperty> datasourceMap = dataSource.getDatasource();
        DataSourceProperty master = datasourceMap.get("master");
        String host = StrUtil.subBetween(master.getUrl(), "mysql://", ":");
        String port = StrUtil.subBetween(master.getUrl(), host + ":", "/");
        String database = StrUtil.subBetween(master.getUrl(), port + "/", "?");
        ConnectionFactoryOptions options = ConnectionFactoryOptions.builder()
                .option(DRIVER, JdbcUtils.MYSQL)
                .option(HOST, host)
                .option(USER, master.getUsername())
                .option(PORT, Integer.valueOf(port))  // optional, default 3306
                .option(PASSWORD, master.getPassword()) // optional, default null, null means has no password
                .option(DATABASE, database) // optional, default null, null means not specifying the database
                .option(CONNECT_TIMEOUT, Duration.ofSeconds(3)) // optional, default null, null means no timeout
                .option(Option.valueOf("socketTimeout"), Duration.ofSeconds(4)) // optional, default null, null means no timeout
                .option(SSL, false) // optional, default sslMode is "preferred", it will be ignore if sslMode is set
                .option(Option.valueOf("zeroDate"), "use_null") // optional, default "use_null"
                .option(Option.valueOf("useServerPrepareStatement"), true) // optional, default false
                .option(Option.valueOf("tcpKeepAlive"), true) // optional, default false
                .option(Option.valueOf("tcpNoDelay"), true) // optional, default false
                .option(Option.valueOf("autodetectExtensions"), false) // optional, default false
                .build();
        ConnectionFactory connectionFactory = ConnectionFactories.get(options);
        return connectionFactory;
    }
}

我想到能在这里配置时区，于是按照警告提示的timezone配置发现不生效、换成serverTimezone依旧不行

搜issue，没有(这里其实是搜错仓库了，正确的仓库是这个https://github.com/mirromutth/r2dbc-mysql ，在readme里就提到了时区配置)

翻阅文档，没找到：https://r2dbc.io/

看警告的代码行数dev.miku.r2dbc.mysql.MySqlConnection:451

然后发现调用convertZoneId的地方在97和100行

于是debug，这个timeZone和systemTimeZone都是从row获取到的

看到确实拿到的是乱码

在row里找到了zeroDateOption，因为我们上面配置了zeroDate为use_null，我有印象

所以看到这里的serverZoneId，就试着配置了一下

@Bean
public ConnectionFactory connectionFactory(DynamicDataSourceProperties dataSource) {
    Map<String, DataSourceProperty> datasourceMap = dataSource.getDatasource();
    DataSourceProperty master = datasourceMap.get("master");
    String host = StrUtil.subBetween(master.getUrl(), "mysql://", ":");
    String port = StrUtil.subBetween(master.getUrl(), host + ":", "/");
    String database = StrUtil.subBetween(master.getUrl(), port + "/", "?");
    ConnectionFactoryOptions options = ConnectionFactoryOptions.builder()
            .option(DRIVER, JdbcUtils.MYSQL)
            .option(HOST, host)
            .option(USER, master.getUsername())
            .option(PORT, Integer.valueOf(port))  // optional, default 3306
            .option(PASSWORD, master.getPassword()) // optional, default null, null means has no password
            .option(DATABASE, database) // optional, default null, null means not specifying the database
            .option(CONNECT_TIMEOUT, Duration.ofSeconds(3)) // optional, default null, null means no timeout
            .option(Option.valueOf("socketTimeout"), Duration.ofSeconds(4)) // optional, default null, null means no timeout
            .option(SSL, false) // optional, default sslMode is "preferred", it will be ignore if sslMode is set
            .option(Option.valueOf("zeroDate"), "use_null") // optional, default "use_null"
            .option(Option.valueOf("useServerPrepareStatement"), true) // optional, default false
            .option(Option.valueOf("tcpKeepAlive"), true) // optional, default false
            .option(Option.valueOf("tcpNoDelay"), true) // optional, default false
            .option(Option.valueOf("autodetectExtensions"), false) // optional, default false
            .option(Option.valueOf("serverZoneId"), TimeZone.getTimeZone("GMT+8").getID()) // optional
            .build();
    ConnectionFactory connectionFactory = ConnectionFactories.get(options);
    return connectionFactory;
}