sharding-sphere 单库分表实例

生成表SQL，多张表日期累加

fund_record20208

fund_record20209

fund_record20210

-- ----------------------------
-- Table structure for fund_record20208
-- ----------------------------
DROP TABLE IF EXISTS `fund_record20208`;
CREATE TABLE `fund_record20208`  (
  `id` bigint(64) NOT NULL AUTO_INCREMENT,
  `user_id` varchar(50) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT '用户ID',
  `amount` decimal(20, 8) NULL DEFAULT NULL COMMENT '流水金额',
  `amount_fee` decimal(20, 8) NULL DEFAULT NULL COMMENT '手续费',
  `operate` int(11) NULL DEFAULT NULL COMMENT '1用户充值,2 商城消费, 3转账支出 ',
  `order_no` varchar(50) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT '订单编号',
  `reason` varchar(500) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT '原因(充值or商品名称)',
  `create_by` varchar(50) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT '创建用户',
  `create_date` datetime(0) NULL DEFAULT NULL COMMENT '创建时间',
  `type` tinyint(1) NULL DEFAULT NULL COMMENT '货币类型：1 RMB 2 沃金(WG) 3 元宝(DT) ',
  `symbol` varchar(2) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT '收入支出符号： + (加号)  - (减号)',
  `after_balance` decimal(20, 8) NULL DEFAULT NULL COMMENT '变动后余额',
  `old_id` int(11) NULL DEFAULT NULL COMMENT '原流水Id',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1304357960402001923 CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;

yaml 配置文件

spring:


  shardingsphere:
    props:
      sql:
        #设置sql是否展示
        show: true
        check:
          table:
            metadata:
              enabled: true
    datasource:
      #数据库名称（可以与数据库中的库名不一致）
      names: master
      master:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://wqspro.tpddns.cn:13306/yuan_mall?allowMultiQueries=true&useUnicode=true&characterEncoding=UTF-8&useSSL=false&tinyInt1isBit=false
        username: root
        password: cYyq8wtbuBO$XnpF
        # 数据库连接池的最小连接数
        initial-size: 10
        max-active: 100
        min-idle: 10
        max-wait: 60000
        pool-prepared-statements: true
        max-pool-prepared-statement-per-connection-size: 20
        time-between-eviction-runs-millis: 60000
        min-evictable-idle-time-millis: 300000
        validation-query: SELECT 1 FROM DUAL
        test-while-idle: true
        test-on-borrow: false
        test-on-return: false
        stat-view-servlet:
          enabled: true
          url-pattern: /druid/*
          #login-username: admin
          #login-password: admin
        filter:
          stat:
            log-slow-sql: true
            slow-sql-millis: 1000
            merge-sql: true
          wall:
            config:
              multi-statement-allow: true
    sharding:
      #分库策略
      #      default-database-strategy:
      #        inline:
      #          #根据id取模决定去那个库
      #          algorithm-expression: ds$->{id % 2}
      #          sharding-column: id
      tables:
        #逻辑表名，fund_record，fund_record20209,fund_record202010,fund_record202011为实际表
        fund_record:
          actual-data-nodes: master.fund_record2020$->{8..12} #master.fund_record2019$->{11..12},
          table-strategy:
            #根据id取模决定去那个表
            #            inline:
            #              sharding-column: id
            #              algorithm-expression: user_info_$->{id % 3}
            #algorithm-expression: fund_record_2019$->{id % 3}
            standard:
              shardingColumn: create_date
              precise-algorithm-class-name: com.jk.mall.api.config.MyPreciseShardingAlgorithm #精确分片算法类名称，用于=和IN。。该类需实现PreciseShardingAlgorithm接口并提供无参数的构造器
              range-algorithm-class-name: com.jk.mall.api.config.MyRangeShardingAlgorithm #范围分片算法类名称，用于BETWEEN，可选。。该类需实现RangeShardingAlgorithm接口并提供无参数的构造器

          key-generator:
            #id使用雪花算法
            column: id
            #雪花算法
            type: SNOWFLAKE

自定义分片查询算法 “=”和“in”的查询

public class MyPreciseShardingAlgorithm implements PreciseShardingAlgorithm<Date> {

    /**
     * "="与"IN"进行分片的场景
     * @param collection
     * @param preciseShardingValue
     * @return
     */
    @Override
    public String doSharding(Collection<String> collection, PreciseShardingValue<Date> preciseShardingValue) {
        //对于库的分片collection存放的是所有的库的列表，这里代表flow_01~flow_12
        //配置的分片的sharding-column对应的值
        Date timeValue = preciseShardingValue.getValue();
        //分库时配置的sharding-column
        String columnName = preciseShardingValue.getColumnName();
        //需要分库的逻辑表
        String table = preciseShardingValue.getLogicTableName();
        if(StringUtils.isEmpty(timeValue)){
            throw new UnsupportedOperationException("preciseShardingValue is null");
        }
        SimpleDateFormat sdf = new SimpleDateFormat("yyyyM");
        //按月路由
        for (String each : collection) {
            //获取到月份
            String date  =  sdf.format(timeValue);
            if(each.endsWith(date)){
                //这里返回回去的就是最终需要查询的表名
                return each;
            }
        }
        return "";
    }

#范围分片算法类名称，用于BETWEEN，"<,>"  需要注意的是 实现 RangeShardingAlgorithm接口的类型一定要与数据中表的 列名 数据类型一致

public class MyRangeShardingAlgorithm implements RangeShardingAlgorithm<Date> {
    /**
     * BETWEEN AND进行分片的场景
     *
     * @param collection
     * @param rangeShardingValue
     * @return
     */

    @Override
    public Collection<String> doSharding(Collection<String> collection, RangeShardingValue<Date> rangeShardingValue) {

        Range<Date> valueRange = rangeShardingValue.getValueRange();
        String strDateFormat = "yyyyM";
        String yyyy = "yyyy";
        String mm = "M";

        SimpleDateFormat sdf = new SimpleDateFormat(strDateFormat);
        SimpleDateFormat yyyySdf = new SimpleDateFormat(yyyy);
        SimpleDateFormat mmSdf = new SimpleDateFormat(mm);

        Date startTime = valueRange.lowerEndpoint();
        Date endTime = valueRange.upperEndpoint();

        List<String> month = new ArrayList<>();
        if (yyyySdf.format(startTime).equals(yyyySdf.format(endTime))) {
            //年 相等
            if (mmSdf.format(startTime).equals(mmSdf.format(endTime))) {
                //月 相等 则不需要做任何处理
                month.add(sdf.format(endTime));

            } else {
                // 月 不相等 则将开始时间月份累加到结束时间的月份
                Calendar forcalendar = Calendar.getInstance();
                forcalendar.setTime(startTime);

                month.add(sdf.format(forcalendar.getTime()));
                for (int i = 0; i < 12; i++) {
                    forcalendar.add(Calendar.MONTH, 1);
                    if (mmSdf.format(forcalendar.getTime()).equals(mmSdf.format(endTime))) {

                        break;
                    }
                    month.add(sdf.format(forcalendar.getTime()));
                }
            }
        } else {
            //年 不相等 将开始时间的月累加 直到与结束时间相等
            Calendar yearCalendar = Calendar.getInstance();
            yearCalendar.setTime(startTime);

            month.add(sdf.format(startTime));
            for (int i = 0; i < 12; i++) {
                yearCalendar.add(Calendar.MONTH, 1);
                if (sdf.format(yearCalendar.getTime()).equals(sdf.format(endTime))) {
                    break;
                }

                month.add(sdf.format(yearCalendar.getTime()));
            }
        }


        month.add(sdf.format(endTime));


        // 如果有固定范围则取
//        if (valueRange.hasLowerBound()) {
//            Date lowerEndpoint = valueRange.lowerEndpoint();
//            leftDate = sdf.format(lowerEndpoint);
//
//        }
//        if (valueRange.hasUpperBound()) {
//            Date upperEndpoint = valueRange.upperEndpoint();
//            rightDate = sdf.format(upperEndpoint);
//
//        }

        ArrayList<String> list = new ArrayList<>();

        for (String tableOriginName : collection) {

            String tableMonth = tableOriginName.replace("fund_record", "");
            if (month.contains(tableMonth)) {

                list.add(tableOriginName);
            }
        }

        return list;
    }
}

shardingsphere的jdbc连接不能用springboot的 druid-spring-boot-starter ，只能用阿里的druid

    <!--mysql相关包开始-->
<!--    <dependency>-->
<!--      <groupId>com.alibaba</groupId>-->
<!--      <artifactId>druid-spring-boot-starter</artifactId>-->
<!--      <version>${druid.version}</version>-->
<!--    </dependency>-->

    <dependency>
      <groupId>com.alibaba</groupId>
      <artifactId>druid</artifactId>
      <version>1.1.20</version>
    </dependency>

    <!--shardingsphere-->
    <dependency>
      <groupId>org.apache.shardingsphere</groupId>
      <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
      <version>4.1.1</version>
    </dependency>
    <dependency>
      <groupId>org.apache.shardingsphere</groupId>
      <artifactId>sharding-jdbc-spring-namespace</artifactId>
      <version>4.1.1</version>
    </dependency>

官网：https://shardingsphere.apache.org/document/legacy/4.x/document/cn/overview/