shardingjdbc按月份分表

按月份分表"/>

shardingjdbc按月份分表

首先，准备一个分片的表

CREATE TABLE `t_log` (`id` bigint NOT NULL AUTO_INCREMENT,`log` varchar(256) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,`time` varchar(12) DEFAULT NULL,`created_time` datetime DEFAULT NULL,PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

接着，创建一个新的项目，引入相关依赖，这用的是sharding-jdbc 4.0.0-RC1版本

<parent><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-parent</artifactId><version>1.5.3.RELEASE</version><relativePath/></parent><properties><java.version>1.8</java.version><project.build.sourceEncoding>UTF-8</project.build.sourceEncoding></properties><dependencies><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-test</artifactId><scope>test</scope></dependency><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-web</artifactId></dependency><dependency><groupId>org.mybatis.spring.boot</groupId><artifactId>mybatis-spring-boot-starter</artifactId><version>1.3.0</version></dependency><dependency><groupId>mysql</groupId><artifactId>mysql-connector-java</artifactId><version>8.0.22</version><scope>runtime</scope></dependency><dependency><groupId>org.apache.shardingsphere</groupId><artifactId>sharding-jdbc-spring-boot-starter</artifactId><version>4.0.0-RC1</version></dependency><dependency><groupId>com.alibaba</groupId><artifactId>druid-spring-boot-starter</artifactId><version>1.1.21</version></dependency></dependencies><build><plugins><plugin><groupId>org.springframework.boot</groupId><artifactId>spring-boot-maven-plugin</artifactId></plugin><plugin><groupId>org.mybatis.generator</groupId><artifactId>mybatis-generator-maven-plugin</artifactId><version>1.3.2</version><configuration><verbose>true</verbose><overwrite>false</overwrite></configuration><dependencies><dependency><groupId>mysql</groupId><artifactId>mysql-connector-java</artifactId><version>8.0.22</version></dependency></dependencies></plugin></plugins></build>

添加配置：

# 配置存放到内存中
spring.shardingsphere.mode.type=Memory
# 打印sql日志
spring.shardingsphere.props.sql.show=true
# 配置数据源
spring.shardingsphere.datasource.names=ds
spring.shardingsphere.datasource.ds.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ds.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds.url=jdbc:mysql://192.168.3.4:3306/sharding?autoReconnect=true&useUnicode=true&characterEncoding=utf8&allowMultiQueries=true&useSSL=false
spring.shardingsphere.datasource.ds.username=root
spring.shardingsphere.datasource.ds.password=root# 配置数据节点，这里是按月分表，时间范围设置在202201 ~ 210012
spring.shardingsphere.sharding.tables.t_log.actual-data-nodes=ds.t_log_$->{2022..2100}0$->{1..9},ds.t_log_$->{2022..2100}1$->{0..2}
# 使用标准分片策略，配置分片字段
spring.shardingsphere.sharding.tables.t_log.table-strategy.standard.sharding-column=time
# 配置精确、范围查询分片算法
spring.shardingsphere.sharding.tables.t_log.table-strategy.standard.precise-algorithm-class-name=com.example.springboot.algorithm.TimeShardingAlgorithm
spring.shardingsphere.sharding.tables.t_log.table-strategy.standard.range-algorithm-class-name=com.example.springboot.algorithm.TimeShardingAlgorithm
# 配置主键以及生成算法
spring.shardingsphere.sharding.tables.t_log.key-generator.column=id
spring.shardingsphere.sharding.tables.t_log.key-generator.type=SNOWFLAKE

这里要注意下precise-algorithm是配置精确分片算法实现类（如=、in），range-algorithm是配置范围分片算法实现类（如between and），实现如下：

package com.example.springboot.algorithm;import com.googlemon.collect.Range;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingValue;
import org.apache.shardingsphere.api.sharding.standard.RangeShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.standard.RangeShardingValue;import java.time.LocalDate;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.Collection;/*** 分片算法，按月分片*/
public class TimeShardingAlgorithm implements PreciseShardingAlgorithm<String>, RangeShardingAlgorithm<String> {/*** 需要空构造方法*/public TimeShardingAlgorithm() {}/*** 时间格式*/private static final DateTimeFormatter DATE_TIME_FORMATTER = DateTimeFormatter.ofPattern("yyyyMMdd");/*** 精确分片* @param collection* @param preciseShardingValue* @return*/@Overridepublic String doSharding(Collection<String> collection, PreciseShardingValue<String> preciseShardingValue) {return buildShardingTable(preciseShardingValue.getLogicTableName(), preciseShardingValue.getValue());}/*** 构建分片后的表名* @param logicTableName* @param date* @return*/private String buildShardingTable(String logicTableName, String date) {StringBuffer stringBuffer = new StringBuffer(logicTableName).append("_").append(date, 0, 6);return stringBuffer.toString();}/*** 范围分片* @param collection* @param rangeShardingValue* @return*/@Overridepublic Collection<String> doSharding(Collection<String> collection, RangeShardingValue<String> rangeShardingValue) {Range<String> valueRange = rangeShardingValue.getValueRange();String lower = valueRange.lowerEndpoint();String upper = valueRange.upperEndpoint();LocalDate start = LocalDate.parse(lower, DATE_TIME_FORMATTER);LocalDate end = LocalDate.parse(upper, DATE_TIME_FORMATTER);Collection<String> tables = new ArrayList<>();while (startpareTo(end) <= 0) {tables.add(buildShardingTable(rangeShardingValue.getLogicTableName(), start.format(DATE_TIME_FORMATTER)));start = start.plusMonths(1L);}// collection配置的数据节点表，这里是排除不存在配置中的表collection.retainAll(tables);return collection;}}