生成表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/