sharding-jdbc 分表操作一直不生效怎么办？

Question

问题描述

sharding-jdbc 范围分表失败

问题出现的环境背景及自己尝试过哪些方法

环境：springboot(若依框架)，mysql

相关代码

yml：

    shardingsphere:
        # 是否启用sharding
        enabled: true
        props:
            sql.show: true
        datasource:
            names: sharding
            # 分库数据源
            sharding:
                type: com.alibaba.druid.pool.DruidDataSource
                driver-class-name: com.mysql.cj.jdbc.Driver
                url: jdbc:mysql://222.190.41.66:30001/lyg_vehicle?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=false&serverTimezone=GMT%2B8
                username: root
                password: aaX9AEx2Gu1M/8pM6EvC+qvtXX0D3i7XJ+9oPCU3rA1D/wNFlCcOgbmKcBlaImBnMv58Hw4Sq+tqG6IJREHjpQ==
        # 分片规则
        sharding:
            tables:
                # 表名
                lyg_tsvol:
                    actual-data-nodes: sharding.lyg_tsvol$->{2023..2024}0$->{1..9},sharding.lyg_tsvol$->{2022..2024}1$->{0..2} # 相当于lyg_tsvol_202301->lyg_tsvol_202412
                    table-strategy: # 分表策略
                        standard: # 标准分表策略
                            sharding-column: createtime                        # 分表列名
                            preciseAlgorithmClassName: com.itssky.modules.common.core.config.MyPreciseShardingAlgorithm     # 精准的分片算法
                            RangeShardingAlgorithm: com.itssky.modules.common.core.config.MyRangeShardingAlgorithm    # 范围分片算法
                # 表名
                lyg_vehicle:
                    actual-data-nodes: sharding.lyg_vehicle_$->{2023..2024}0$->{1..9},ds.t_log_$->{2022..2024}1$->{0..2} # 相当于lyg_vehicle_202301->lyg_vehicle_202412
                    table-strategy: # 分表策略
                        standard: # 标准分表策略
                            sharding-column: createtime                        # 分表列名
                            preciseAlgorithmClassName: com.itssky.modules.common.core.config.MyPreciseShardingAlgorithm    # 精准的分片算法
                            RangeShardingAlgorithm: com.itssky.modules.common.core.config.MyRangeShardingAlgorithm    # 范围分片算法

分片规则：

public class MyPreciseShardingAlgorithm implements PreciseShardingAlgorithm<Timestamp> {

    private String tableNamePrefix;

    public MyPreciseShardingAlgorithm(String tableNamePrefix) {
        this.tableNamePrefix = tableNamePrefix;
    }

    @Override
    public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<Timestamp> shardingValue) {
        // 获取年份和月份
        Calendar calendar = Calendar.getInstance();
        calendar.setTime(shardingValue.getValue());
        int year = calendar.get(Calendar.YEAR);
        int month = calendar.get(Calendar.MONTH) + 1;

        // 构造表名
        String tableName = tableNamePrefix + year + String.format("%02d", month);

        // 如果这个表名存在于可用的目标表名中，那么返回这个表名
        if (availableTargetNames.contains(tableName)) {
            return tableName;
        }

        // 否则，抛出一个异常
        throw new UnsupportedOperationException("未找到匹配的数据表: " + tableName);
    }

}

public class MyRangeShardingAlgorithm implements RangeShardingAlgorithm<Timestamp> {
    @Override
    public Collection<String> doSharding(Collection<String> collection,
                                         RangeShardingValue<Timestamp> rangeShardingValue) {
        Range<Timestamp> valueRange = rangeShardingValue.getValueRange();
        Date lowerEnd = valueRange.lowerEndpoint();
        Date upperEnd = valueRange.upperEndpoint();
        log.info("范围分片开始时间:{},结束时间：{}",lowerEnd,upperEnd);
        Set<String> routTables = new HashSet<>();
        if (lowerEnd != null && upperEnd != null) {
            List<String> rangeNameList = getTableNames(lowerEnd, upperEnd);
            for (String string : rangeNameList) {
                //这里的表名是逻辑表名+年月
                routTables.add(rangeShardingValue.getLogicTableName() + string);
            }
        }
        log.info("范围分片表名为:{}",routTables.toString());
        return routTables;

    }


    /**
     * 根据开始时间和结束时间获取表名
     * @param start
     * @param end
     * @return
     */
    private static List<String> getTableNames(Date start, Date end) {
        List<String> result = Lists.newArrayList();
        // 定义日期实例
        Calendar calendar = Calendar.getInstance();
        // 设置日期起始时间
        calendar.setTime(start);
        // 判断是否到结束日期
        while (calendar.getTime().before(end)) {
            int year = calendar.get(Calendar.YEAR);
            int month = calendar.get(Calendar.MONTH) + 1; // Calendar.MONTH 是从0开始的，所以需要加1
            String yearMonth = String.format("%d%02d", year, month); // 格式化为年月，例如202308或者202411
            result.add(yearMonth);
            // 进行当前日期月份加1
            calendar.add(Calendar.MONTH, 1);
        }
        Calendar endCalendar = Calendar.getInstance();
        endCalendar.setTime(end);
        if (calendar.get(Calendar.MONTH)==endCalendar.get(Calendar.MONTH)){
            int year = calendar.get(Calendar.YEAR);
            int month = calendar.get(Calendar.MONTH) + 1;
            String yearMonth = String.format("%d%02d", year, month);
            result.add(yearMonth);
        }
        return result;
    }

}

sharding_jdbc配置信息

@Configuration
public class ShardingDataSourceConfig {

    @Bean
    @ConfigurationProperties("spring.datasource.druid.sharding")
    @ConditionalOnProperty(prefix = "spring.datasource.druid.sharding", name = "enabled", havingValue = "true")
    public DataSource shardingDataSource(DruidProperties druidProperties)
    {
        DruidDataSource dataSource = DruidDataSourceBuilder.create().build();
        return druidProperties.dataSource(dataSource);
    }


    @Bean(name = "shardingDataSource")
    public DataSource shardingDataSource(@Qualifier("shardingDataSource") DataSource shardingDataSource) throws SQLException {
        Map<String, DataSource> dataSourceMap = new HashMap<>();
        dataSourceMap.put("sharding", shardingDataSource);
        // 创建lyg_tsvol表规则
        TableRuleConfiguration tsvolRuleConfig = createTableRule("lyg_tsvol");
        // 创建lyg_vehicle表规则
        TableRuleConfiguration vehicleRuleConfig = createTableRule("lyg_vehicle");
        // 配置分片规则
        ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();
        shardingRuleConfig.getTableRuleConfigs().add(tsvolRuleConfig);
        shardingRuleConfig.getTableRuleConfigs().add(vehicleRuleConfig);
        // 获取数据源对象
        return ShardingDataSourceFactory.createDataSource(dataSourceMap, shardingRuleConfig, getProperties());
    }

    /**
     * 创建表规则
     * @param tableName 表名
     * @return 表规则配置
     */
    private TableRuleConfiguration createTableRule(String tableName) {
        // 获取当前日期
        LocalDate currentDate = LocalDate.now();
        int currentYear = currentDate.getYear();
        int currentMonth = currentDate.getMonthValue();

        // 表规则配置
        TableRuleConfiguration tableRuleConfig = new TableRuleConfiguration(tableName, tableName + "$->{2023.." + currentYear + "}0$->{6.." + currentMonth + "}");
        // 配置分表策略
        tableRuleConfig.setTableShardingStrategyConfig(new StandardShardingStrategyConfiguration("createtime", new MyPreciseShardingAlgorithm(tableName)));
        // 分布式主键
        tableRuleConfig.setKeyGeneratorConfig(new KeyGeneratorConfiguration("SNOWFLAKE", "ID"));

        return tableRuleConfig;
    }

    /**
     * 系统参数配置
     */
    private Properties getProperties() {
        Properties shardingProperties = new Properties();
        shardingProperties.put("sql.show", true);
        return shardingProperties;
    }
}

多数据源配置：DruidConfig

    @Bean(name = "dynamicDataSource")
    @Primary
    public DynamicDataSource dataSource(DataSource masterDataSource)
    {
        Map<Object, Object> targetDataSources = new HashMap<>();
        targetDataSources.put(DataSourceType.MASTER.name(), masterDataSource);
        setDataSource(targetDataSources, DataSourceType.SLAVE.name(), "slaveDataSource");
        setDataSource(targetDataSources, DataSourceType.SHARDING.name(), "shardingDataSource");
        return new DynamicDataSource(masterDataSource, targetDataSources);
    }

这是我的数据库表格式：

你期待的结果是什么？实际看到的错误信息又是什么？

我在MyRangeShardingAlgorithm文件中打断点了，但是我一直进不来这个页面，
我的sql查询的也不是分表：

SELECT count(0) FROM lyg_tsvol a LEFT JOIN mst_gcz b ON a.devcode = b.equipment_code WHERE date_format(a.createtime, '%Y-%m-%d %H:%i') BETWEEN date_format(?, '%Y-%m-%d %H:%i') AND date_format(?, '%Y-%m-%d %H:%i')

感觉我的sharding-jdbc一直没有生效，各位牛爷爷给看看