mysql 按主键分库，按日期分表 shardingsphere springboot

1、pom.xml文件引入shardingsphere依赖
<dependency>
   <groupId>org.apache.shardingsphere</groupId>
   <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
   <version>4.1.1</version>
</dependency>

2、配置数据源，分片规则

分为4个库，按照年月进行数据表拆分。

spring:
  application:
    name: businessapi
  ###数据源名称，多数据源以逗号分隔
  shardingsphere:
    datasource:
      names: ds0,ds1,ds2,ds3
      # 数据源ds0
      ds0:
        url: jdbc:mysql://dev.db.mydb.com:3306/mydb_0?useUnicode=true&useSSL=false&characterEncoding=UTF-8
        username: mydb
        password: mydb
        # 使用druid数据源
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.jdbc.Driver
      # 数据源ds1
      ds1:
        url: jdbc:mysql://dev.db.mydb.com:3306/mydb_1?useUnicode=true&useSSL=false&characterEncoding=UTF-8
        username: mydb
        password: mydb
        # 使用druid数据源
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.jdbc.Driver
        # 数据源ds2
      ds2:
        url: jdbc:mysql://dev.db.mydb.com:3306/mydb_2?useUnicode=true&useSSL=false&characterEncoding=UTF-8
        username: mydb
        password: mydb
        # 使用druid数据源
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.jdbc.Driver
      # 数据源ds1
      ds3:
        url: jdbc:mysql://dev.db.mydb.com:3306/mydb_3?useUnicode=true&useSSL=false&characterEncoding=UTF-8
        username: mydb
        password: mydb
        # 使用druid数据源
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.jdbc.Driver
    sharding:
      default-data-source-name: ds0
      tables:
        shading_table: #shading_table
          key-generator-column-name: id  #主键
          actual-data-nodes: ds${0..3}.shading_table_${2022}_${1..12}   #数据节点,均匀分布
          database-strategy: #分库策略
            inline: #行表达式
              sharding-column: id
              algorithm-expression: ds${id % 4}
          table-strategy:
            standard:
              sharding-column: create_time
              precise-algorithm-class-name: com.mydb.business.framework.sharding.TableShardingAlgorithm
        #                    table-strategy: #分表策略
        #                        inline: #行表达式
        #                            sharding-column: appid
        #                            algorithm-expression: shading_table${appid % 2}
        

数据库分片规则：

@Slf4j
public class DBShardingAlgorithm implements PreciseShardingAlgorithm<Long> {
    @Override
    public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<Long> shardingValue) {

        System.out.println("DB  PreciseShardingAlgorithm  ");
        // 真实节点
        availableTargetNames.stream().forEach((item) -> {
            log.info("actual node db:{}", item);
        });

        log.info("logic table name:{},rout column:{}", shardingValue.getLogicTableName(), shardingValue.getColumnName());

        //精确分片
        log.info("column value:{}", shardingValue.getValue());

        long orderId = shardingValue.getValue();

        long db_index = orderId & (2 - 1);

        for (String each : availableTargetNames) {
            if (each.equals("b"+db_index)) {
                return each;
            }
        }

        throw new IllegalArgumentException();
    }
}

数据表分片规则：

@Slf4j
public class TableShardingAlgorithm implements PreciseShardingAlgorithm<Date> {
    @Override
    public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<Date> shardingValue) {

        System.out.println("table PreciseShardingAlgorithm ");
        // 真实节点
        availableTargetNames.stream().forEach((item) -> {
            log.info("actual node table:{}", item);
        });

        log.info("logic table name:{},rout column:{}", shardingValue.getLogicTableName(), shardingValue.getColumnName());

        //精确分片
        log.info("column value:{}", shardingValue.getValue());


        String tb_name = shardingValue.getLogicTableName() + "_";


        // 根据当前日期 来 分库分表
        Date date = shardingValue.getValue();
        String year = String.format("%tY", date);
        String mon =String.valueOf(Integer.parseInt(String.format("%tm", date))); // 去掉前缀0
        String dat = String.format("%td", date);


        // 选择表
        tb_name = tb_name + year + "_" + mon;
        System.out.println("tb_name:" + tb_name);

        for (String each : availableTargetNames) {
//            System.out.println("t_order_:" + each);
            if (each.equals(tb_name)) {
                return each;
            }
        }

        throw new IllegalArgumentException();
    }
}