sharding-sphere 单库分表实例
生成表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/
