sharding-jdbc 分表操作一直不生效怎么办？
问题描述
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配置信息
@Configurationpublic 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一直没有生效，各位牛爷爷给看看
以上是 sharding-jdbc 分表操作一直不生效怎么办？ 的全部内容， 来源链接： utcz.com/p/945311.html
