sharding-jdbc多表共用一个自定义分片算法，仅有一个表生效

北漂老男孩

已于 2023-05-27 21:16:41 修改

阅读量265

点赞数

分类专栏：分库分表文章标签：算法 mybatis java

于 2023-05-27 20:58:46 首次发布

本文链接：https://blog.csdn.net/weixin_39863120/article/details/130905556

版权

分库分表专栏收录该内容

1 篇文章 0 订阅

订阅专栏

【慢慢成长的北漂老男孩】

springboot环境设置：

（springboot+gradle+nacos）

主要依赖包

  implementation('org.springframework.boot:spring-boot-starter-web:2.3.12.RELEASE')
  implementation 'org.mybatis.spring.boot:mybatis-spring-boot-starter:2.1.3'
  implementation 'mysql:mysql-connector-java:8.0.18'
  implementation group: 'org.apache.shardingsphere', name: 'sharding-jdbc-spring-boot-starter', version: '4.1.1'

nacos配置文件：

mybatis-plus:
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
mybatis:
  mapper-locations: classpath*:mybatis/**/*.xml
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
spring:
  application:
    name: hdl-test
  jackson:
    date-format: yyyy-MM-dd HH:mm:ss
    time-zone: GMT+8
  shardingsphere:
    props:
      sql:
        show: true
    datasource:
      names: ds
      ds:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.jdbc.Driver
        jdbc-url: jdbc:xxoo
        username: xx
        password: 00
    sharding:
      # default-database-strategy:
      #   standard:
      #     sharding-column: month_date
      #     # precise-algorithm-class-name: com.xx.oo.infrastructure.config.QuarterShardingTableAlgorithm
      # binding-tables : rebate_account_report,brand_account_report,reward_account_report,verify_bill_report
      tables:
        rebate_account_report:
          actual-data-nodes: ds.rebate_account_report_$->{2023..2025}Q$->{1..4}
          table-strategy:
            standard:
              sharding-column: month_date
              # 精确分片算法的类名
              precise-algorithm-class-name: com.xx.oo.infrastructure.config.QuarterShardingTableAlgorithm
          # key-generator:
          #   column: rebate_id
          #   type: SNOWFLAKE
          #   props:
          #     worker:
          #       id: 123
        brand_account_report:
          actual-data-nodes: ds.brand_account_report_$->{2023..2025}Q$->{1..4}
          table-strategy:
            standard:
              sharding-column: month_date
              # 精确分片算法的类名
              precise-algorithm-class-name: com.xx.oo.infrastructure.config.QuarterShardingTableAlgorithm
          # key-generator:
          #   column: special_id
          #   type: SNOWFLAKE
          #   props:
          #     worker:
          #       id: 123
        reward_account_report:
          actual-data-nodes: ds.reward_account_report_$->{2023..2025}Q$->{1..4}
          table-strategy:
            standard:
              sharding-column: month_date
              # 精确分片算法的类名
              precise-algorithm-class-name: com.xx.oo.report.infrastructure.config.QuarterShardingTableAlgorithm
          # key-generator:
          #   column: reward_id
          #   type: SNOWFLAKE
          #   props:
          #     worker:
          #       id: 1234
        verify_bill_report:
          actual-data-nodes: ds.verify_bill_report_$->{2023..2025}Q$->{1..4}
          table-strategy:
            standard:
              sharding-column: month_date
              # 精确分片算法的类名
              precise-algorithm-class-name: com.xx.oo.report.infrastructure.config.QuarterShardingTableAlgorithm
          # key-generator:
          #   column: id
          #   type: SNOWFLAKE
          #   props:
          #     worker:
          #       id: 1235

自定义季度表算法：

import org.apache.shardingsphere.api.sharding.standard.PreciseShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingValue;
import org.springframework.stereotype.Component;

import java.time.LocalDate;
import java.time.YearMonth;
import java.time.format.DateTimeFormatter;
import java.util.Collection;

/**
 * 自定义实现 精确分片算法（PreciseShardingAlgorithm）接口
 * 数据表 table 的季度分片 分片字段为Integer
 * @author haifeng
 * @version 1.0
 * fileName QuarterShardingTableAlgorithm
 * createTime 2023/5/19
 */

@Component
public class QuarterShardingTableAlgorithm3 implements PreciseShardingAlgorithm<Integer> {

    private static final DateTimeFormatter YEAR_FORMATTER = DateTimeFormatter.ofPattern("yyyy");
    private static final int QUARTERS = 4;

    @Override
    public String doSharding(final Collection<String> availableTargetNames,
                             final PreciseShardingValue<Integer> shardingValue) {
        String targetTable = null;

        // 定义日期格式
        DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyyMM");
        // 将日期字符串解析为YearMonth对象
        YearMonth yearMonthValue = YearMonth.parse(String.valueOf(shardingValue.getValue()), formatter);
        // 将日期设为这个月份的第一天
        LocalDate dateValue = yearMonthValue.atDay(1);
        int quarter = (dateValue.getMonthValue() - 1) / 3 + 1;
        //ds.brand_account_report_2023Q2
        String quarterSuffix = String.format("_%sQ%s", YEAR_FORMATTER.format(dateValue), quarter);
        for (String each : availableTargetNames) {
            if (each.endsWith(quarterSuffix)) {
                targetTable = each;
                break;
            }
        }
        if (targetTable == null) {
            throw new SystemException("we can't find the target table:" + quarterSuffix);
        }

        return targetTable;
    }
}

问题排查总结：

key-generator:
           column: id
          type: SNOWFLAKE
          #   props:
          #     worker:
          #       id: 1235
 # 指定主键生成策略为SNOWFLAKE，
 #id相同时，可共用一个自定义分片算法。
 #id不同时，最小id数生效。
 # 其他场景未测试。