接着上一篇:SpringBoot 分库分表sharding-sphere
上一篇的自定义分片算法有个错误DateShardingAlgorithm.java
该类的日期转换错误,正确如下:
package com.pack.sharding.algorithm;import java.time.ZoneId;import java.time.format.DateTimeFormatter;import java.util.Collection;import java.util.Date;import org.apache.shardingsphere.api.sharding.standard.PreciseShardingAlgorithm;import org.apache.shardingsphere.api.sharding.standard.PreciseShardingValue;public class DateTableShardingAlgorithm implements PreciseShardingAlgorithm {private static DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyyMM");@Overridepublic String doSharding(Collection availableTargetNames, PreciseShardingValue shardingValue) {Date value = shardingValue.getValue() ;if (value == null) {value = new Date() ;}String actualTableName = shardingValue.getLogicTableName() + "_" + formatter.format(value.toInstant().atZone(ZoneId.systemDefault()).toLocalDateTime()) ;if (availableTargetNames.contains(actualTableName)) {return actualTableName ;}throw new UnsupportedOperationException("未知的表名称: " + actualTableName);}}
接下来我们实现一个范围查询sql中 between...and
分片算法的字段是create_time 日期类型
当我们没有配置RangeShardingAlgorithm,我们的sql中出现了between...and时这时候会对所有库的所有表进行查询。
范围查询DataTabeRangeShardingAlgorithm.java
DataTabeRangeShardingAlgorithm
配置:
spring: shardingsphere: props: sql: show: true datasource: names: ds0 ds0: type: com.zaxxer.hikari.HikariDataSource driverClassName: com.mysql.cj.jdbc.Driver jdbcUrl: jdbc:mysql://localhost:3306/ds0?serverTimezone=GMT%2B8 username: root password: 123123 minimumIdle: 20 maximumPoolSize: 200 autoCommit: true idleTimeout: 30000 poolName: DS0DatabookHikariCP maxLifetime: 1800000 connectionTimeout: 30000 connectionTestQuery: SELECT 1 sharding: broadcastTables: t_user tables: t_order: #逻辑表名 actualDataNodes: ds$->{0..0}.t_order_$->{2020..2020}0$->{1..9}, ds$->{0..0}.t_order_$->{2020..2020}$->{10..12} #实际表 tableStrategy: #分表策略 standard: #标准分片策略 shardingColumn: create_time #分片字段 preciseAlgorithmClassName: com.pack.sharding.algorithm.DateTableShardingAlgorithm rangeAlgorithmClassName: com.pack.sharding.algorithm.DataTabeRangeShardingAlgorithm
rangeAlgorithmClassName: com.pack.sharding.algorithm.DataTabeRangeShardingAlgorithm
这是配置的范围,用于支持BETWEEN AND, >, =, <= 分⽚
OrdersMapper.xml
SELECT * FROM T_ORDER T WHERE 1 = 1
注意:AND create_time between #{params.startDate} and #{params.endDate}
create_time是分片列,如果这里的#{params.startDate}、#{params.endDate}两个查询参数值用了函数,如:DATE_FORMAT(#{params.endDate},'%Y-%m-%d')那么我们的分片算法将不会生效,将会进行全库全表的查询是哪里配置理解错误?
测试:
查询参数:
执行结果:
实际我们分了12张表,加入范围查询后根据入参startDate和endDate把查询表限定在了这两个日期之间。
当sql 是如下形式也是生效的:
= #{params.startDate} and create_time <= #{params.endDate}]]>
完毕!!!