ShardingSphere 5.1分库分表配置

背景

有一个业务目前是单库单表的，随着业务的发展需要拓展为多库多表的形式，而且还需要将数据从原先的实例转移到分库分表后的DB中

CREATE TABLE `t_topic_2` (
  `id` bigint(20) DEFAULT NULL,
  `topic` varchar(20) DEFAULT NULL,
  `state` int(11) DEFAULT NULL,
  `connect` int(11) DEFAULT NULL,
  `is_print_log` tinyint(4) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8

本地测试过后的配置如下

spring:
  application:
    name: test
  shardingsphere:
    mode:
      type: Memory
    props:
      sql-show: true
    datasource:
      names: ds0, ds1
      ds0:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.jdbc.Driver
        jdbc-url: jdbc:mysql://localhost:3306/test
        username: root
        password: 12345678
      ds1:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.jdbc.Driver
        jdbc-url: jdbc:mysql://localhost:3306/test2
        username: root
        password: 12345678
    rules:
      sharding:
        tables:
          t_topic:
            actualDataNodes: ds${0..1}.t_topic_${0..2}
            tableStrategy:
              standard:
                shardingColumn: id
                shardingAlgorithmName: table-inline
        default-database-strategy:
          standard:
            sharding-column: id
            sharding-algorithm-name: id-inline-database-strategy
        sharding-algorithms:
          # id-inline-database-strategy是自定义的名称，
          # !!!最好别以下划线分隔，会有问题
          id-inline-database-strategy:
            type: INLINE
            props:
              # 该表达式是指把 id 值是偶数路由到 ds0，奇数路由到 ds1
              algorithm-expression: ds$->{id % 2}
          table-inline:
            type: INLINE
            props:
              algorithm-expression: t_topic_$->{id % 3}
#
debug: true
mybatis-plus:
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
  type-aliases-package: com.dym.entity
  mapper-locations: classpath*:/mapper/*.xml

动态数据源

spring:
  datasource:
    dynamic:
      allowed-public-only: true
      strict: true
      primary: first
      datasource:
        first:
          driverClassName: com.mysql.jdbc.Driver
          type: com.zaxxer.hikari.HikariDataSource
          url: jdbc:mysql://localhost:3306/test
          username: root
          password: chenshun
  shardingsphere:
    mode:
      type: Memory
    props:
      sql-show: true
    datasource:
      //动态数据源，参与下边的分库
      names: ds0, ds1
      ds0:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.jdbc.Driver
        jdbc-url: jdbc:mysql://localhost:3306/test1
        username: root
        password: chenshun
      ds1:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.jdbc.Driver
        jdbc-url: jdbc:mysql://localhost:3306/test2
        username: root
        password: chenshun
    rules:
      //分片规则
      sharding:
        tables:
          t_topic:
            actualDataNodes: ds${0..1}.t_topic_${0..2}
            tableStrategy:
              standard:
                shardingColumn: id
                shardingAlgorithmName: table-inline
        default-database-strategy:
          standard:
            sharding-column: id
            sharding-algorithm-name: id-inline-database-strategy
        sharding-algorithms:
          id-inline-database-strategy:
            type: INLINE
            props:
              algorithm-expression: ds$->{id % 2}
          table-inline:
            type: INLINE
            props:
              algorithm-expression: t_topic_$->{id % 3}

package io.github.chenshun00.springcloud.provider.config;
/**
 * @author chenshun00@gmail.com
 * @since 2022/5/22 2:40 PM
 */
import com.baomidou.dynamic.datasource.DynamicRoutingDataSource;
import com.baomidou.dynamic.datasource.provider.AbstractDataSourceProvider;
import com.baomidou.dynamic.datasource.provider.DynamicDataSourceProvider;
import com.baomidou.dynamic.datasource.spring.boot.autoconfigure.DataSourceProperty;
import com.baomidou.dynamic.datasource.spring.boot.autoconfigure.DynamicDataSourceAutoConfiguration;
import com.baomidou.dynamic.datasource.spring.boot.autoconfigure.DynamicDataSourceProperties;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.SpringBootConfiguration;
import org.springframework.boot.autoconfigure.AutoConfigureBefore;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Lazy;
import org.springframework.context.annotation.Primary;
import javax.annotation.Resource;
import javax.sql.DataSource;
import java.util.Map;
/**
 * 动态数据源配置：
 *
 * 使用{@link com.baomidou.dynamic.datasource.annotation.DS}注解，切换数据源
 *
 * <code>@DS(DataSourceConfiguration.SHARDING_DATA_SOURCE_NAME)</code>
 *
 * @author fanqie
 * @date 2021/8/16 下午12:36
 */
@Configuration
@AutoConfigureBefore({DynamicDataSourceAutoConfiguration.class, SpringBootConfiguration.class})
public class DataSourceConfiguration {
    /**
     * 分表数据源名称
     */
    public static final String SHARDING_DATA_SOURCE_NAME = "sharding";
    /**
     * 动态数据源配置项
     */
    @Autowired
    private DynamicDataSourceProperties dynamicDataSourceProperties;
    /**
     * shardingjdbc有四种数据源，需要根据业务注入不同的数据源
     *
     * <p>1. 未使用分片, 脱敏的名称(默认): shardingDataSource;
     * <p>2. 主从数据源: masterSlaveDataSource;
     * <p>3. 脱敏数据源：encryptDataSource;
     * <p>4. 影子数据源：shadowDataSource
     *
     * shardingjdbc默认就是 shardingDataSource
     *  如果需要设置其他的可以使用
     * @Resource(value="") 设置
     */
    @Lazy
    @Resource
    DataSource shardingDataSource;
    /**
     * 将shardingDataSource放到了多数据源（dataSourceMap）中
     * 注意有个版本的bug，3.1.1版本 不会进入loadDataSources 方法，这样就一直造成数据源注册失败
     */
    @Bean
    public DynamicDataSourceProvider dynamicDataSourceProvider() {
        Map<String, DataSourceProperty> datasourceMap = dynamicDataSourceProperties.getDatasource();
        return new AbstractDataSourceProvider() {
            @Override
            public Map<String, DataSource> loadDataSources() {
                Map<String, DataSource> dataSourceMap = createDataSourceMap(datasourceMap);
                // 将 shardingjdbc 管理的数据源也交给动态数据源管理
                dataSourceMap.put(SHARDING_DATA_SOURCE_NAME, shardingDataSource);
                return dataSourceMap;
            }
        };
    }
    /**
     * 将动态数据源设置为首选的
     * 当spring存在多个数据源时, 自动注入的是首选的对象
     * 设置为主要的数据源之后，就可以支持shardingjdbc原生的配置方式了
     *
     * @return
     */
    @Primary
    @Bean
    public DataSource dataSource(DynamicDataSourceProvider dynamicDataSourceProvider) {
        DynamicRoutingDataSource dataSource = new DynamicRoutingDataSource();
        dataSource.setPrimary(dynamicDataSourceProperties.getPrimary());
        dataSource.setStrict(dynamicDataSourceProperties.getStrict());
        dataSource.setStrategy(dynamicDataSourceProperties.getStrategy());
        dataSource.setProvider(dynamicDataSourceProvider);
        dataSource.setP6spy(dynamicDataSourceProperties.getP6spy());
        dataSource.setSeata(dynamicDataSourceProperties.getSeata());
        return dataSource;
    }
}