一、命名空间增加如下图标红所示
XML中增加相关配置
<!--分片策略配置-->
<sharding:sharding-algorithm id="yearShardingAlgorithm" type="CLASS_BASED">
<props>
<prop key="strategy">standard</prop>
<prop key="algorithmClassName">com.demo.algorithm.YearShardingAlgorithm</prop>
</props>
</sharding:sharding-algorithm>
<sharding:sharding-algorithm id="monthShardingAlgorithm"
<sharding:standard-strategy id="yearShardingStrategy" sharding-column="rec_date" algorithm-ref="yearShardingAlgorithm"/>
<!--分片规则配置-->
<sharding:rule id="shardingRule">
<sharding:table-rules>
<!--表达式ds_${0..1}枚举的数据源名称为主从配置的逻辑数据源名称-->
<sharding:table-rule logic-table="d_trade_rec" actual-data-nodes="dataSource.d_trade_rec$->{2020..2023}0$->{1..9},dataSource.d_trade_rec$->{2020..2023}1$->{0..2}" table-strategy-ref="yearShardingAlgorithm" />
</sharding:table-rules>
<sharding:binding-table-rules>
<sharding:binding-table-rule logic-tables="s_station_daily"/>
</sharding:binding-table-rules>
</sharding:rule>
<!-- 分库分表 *end* -->
算法相关代码
public class YearShardingAlgorithm implements StandardShardingAlgorithm<String> {
private static final Logger LOGGER = LoggerFactory.getLogger(YearShardingAlgorithm.class);
private static AtomicInteger count = new AtomicInteger();
public YearShardingAlgorithm() {
LOGGER.info("YearShardingAlgorithm 初始化,{}",count.getAndIncrement());
}
@Override
public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<String> shardingValue) {
LOGGER.info("YearShardingAlgorithm doSharding availableTargetNames:{}---shardingValue:{}", JSON.toJSONString(availableTargetNames),JSON.toJSONString(shardingValue));
// 根据配置的分表规则生成目标表的后缀
String tableExt = shardingValue.getValue().replaceAll(" ","").replaceAll("-","").replaceAll(":","").substring(0, 4);
for (String availableTableName : availableTargetNames) {
if (availableTableName.endsWith(tableExt)) {
return availableTableName;
}
}
return shardingValue.getLogicTableName();
}
@Override
public Collection<String> doSharding(Collection<String> availableTargetNames, RangeShardingValue<String> shardingValue) {
LOGGER.info("YearShardingAlgorithm doRangeSharding availableTargetNames:{}---shardingValue:{}", JSON.toJSONString(availableTargetNames),JSON.toJSONString(shardingValue));
Collection<String> collect = new ArrayList<>();
Range<String> valueRange = shardingValue.getValueRange();
String start = valueRange.lowerEndpoint().replaceAll(" ","").replaceAll("-","").replaceAll(":","").substring(0,4);
String end = valueRange.upperEndpoint().replaceAll(" ","").replaceAll("-","").replaceAll(":","").substring(0,4);
for (Long i = Long.valueOf(start); i <= Long.valueOf(end); i++) {
for (String each : availableTargetNames) {
if (each.endsWith(i + "")) {
collect.add(each);
}
}
}
return collect;
}
@Override
public void init() {
}
@Override
public String getType() {
return "CLASS_BASED";
}
@Override
public Properties getProps() {
return null;
}
@Override
public void setProps(Properties props) {
}
}
需要注意两点:
1、其中的getType返回的值要与xml中sharding-algorithm的type一致
2、在resource目录下创建META-INF\services文件夹,然后创建一个org.apache.shardingsphere.sharding.spi.ShardingAlgorithm文件,内容是算法的全路径名称,如com.demo.algorithm.YearShardingAlgorithm
另外SQL有以下几个需要注意的地方:
1、相关条件需要包括分片字段,范围查询最好包括开始时间和结束时间
2、分片字段不要做计算 比如时间字段使用 date_format(REC_DATE, '%Y-%m-%d')
3、关联查询时,分片表如果有别名,查询时分片字段前要加上别名,否则SQL不会报错,但是会遍历所有分表