本文按年分库，按月分表 如需按ID分库分表详见：参考->配置
1、官方文档
2、引入pom
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
<version>4.0.1</version>
</dependency>
3、配置
#控制台打印sql
spring.shardingsphere.props.sql.show=true
spring.main.allow-bean-definition-overriding=true
#数据源 先创建2个数据库
spring.shardingsphere.datasource.names=db-test,db-test-2019,db-test-2020,db-test-2021,db-test-2022,db-test-2023
# 配置db-test
spring.shardingsphere.datasource.db-test.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.db-test.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.db-test.jdbc-url=jdbc:mysql://localhost:3306/db-test?characterEncoding=utf-8&useLegacyDatetimeCode=false&serverTimezone=UTC
spring.shardingsphere.datasource.db-test.username=root
spring.shardingsphere.datasource.db-test.password=root
# 配置db-test-2019
spring.shardingsphere.datasource.db-test-2019.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.db-test-2019.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.db-test-2019.jdbc-url=jdbc:mysql://localhost:3306/db-test-2019?characterEncoding=utf-8&useLegacyDatetimeCode=false&serverTimezone=UTC
spring.shardingsphere.datasource.db-test-2019.username=root
spring.shardingsphere.datasource.db-test-2019.password=root
# 配置db-test-2020
spring.shardingsphere.datasource.db-test-2020.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.db-test-2020.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.db-test-2020.jdbc-url=jdbc:mysql://localhost:3306/db-test-2020?characterEncoding=utf-8&useLegacyDatetimeCode=false&serverTimezone=UTC
spring.shardingsphere.datasource.db-test-2020.username=root
spring.shardingsphere.datasource.db-test-2020.password=root
# 配置db-test-2021
spring.shardingsphere.datasource.db-test-2021.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.db-test-2021.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.db-test-2021.jdbc-url=jdbc:mysql://localhost:3306/db-test-2021?characterEncoding=utf-8&useLegacyDatetimeCode=false&serverTimezone=UTC
spring.shardingsphere.datasource.db-test-2021.username=root
spring.shardingsphere.datasource.db-test-2021.password=root
# 配置db-test-2022
spring.shardingsphere.datasource.db-test-2022.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.db-test-2022.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.db-test-2022.jdbc-url=jdbc:mysql://localhost:3306/db-test-2022?characterEncoding=utf-8&useLegacyDatetimeCode=false&serverTimezone=UTC
spring.shardingsphere.datasource.db-test-2022.username=root
spring.shardingsphere.datasource.db-test-2022.password=root
# 配置db-test-2023
spring.shardingsphere.datasource.db-test-2023.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.db-test-2023.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.db-test-2023.jdbc-url=jdbc:mysql://localhost:3306/db-test-2023?characterEncoding=utf-8&useLegacyDatetimeCode=false&serverTimezone=UTC
spring.shardingsphere.datasource.db-test-2023.username=root
spring.shardingsphere.datasource.db-test-2023.password=root
#默认数据库
spring.shardingsphere.sharding.default-data-source-name=db-test
#绑定数据表
spring.shardingsphere.sharding.binding-tables=t_product
# 配置进行分库的字段
spring.shardingsphere.sharding.default-database-strategy.standard.sharding-column=create_time
# 具体的配置规则我们会在自定义类中指定，也就是这个PreciseModuloDatabaseShardingAlgorithm类，这写自定义类路径
spring.shardingsphere.sharding.default-database-strategy.standard.precise-algorithm-class-name=com.mxc.split.config.algorithm.PreciseModuloDatabaseShardingAlgorithm
spring.shardingsphere.sharding.default-database-strategy.standard.range-algorithm-class-name=com.mxc.split.config.algorithm.PreciseModuloDatabaseRangeShardingAlgorithm
# 配置库与表结合，就是你有几个库，几个表，例如：我有db-test2019-2023这5个库，每个库下有12张表
spring.shardingsphere.sharding.tables.t_product.actual-data-nodes=db-test-$->{2019..2023}.t_product_0$->{1..9},db-test-$->{2019..2023}.t_product_1$->{0..2}
# 配置进行分表的字段
spring.shardingsphere.sharding.default-table-strategy.standard.sharding-column=create_time
# 具体的配置规则我们会在自定义类中指定，也就是这个PreciseModuloTableShardingAlgorithm类，这写自定义类路径
spring.shardingsphere.sharding.default-table-strategy.standard.precise-algorithm-class-name=com.mxc.split.config.algorithm.PreciseModuloTableShardingAlgorithm
spring.shardingsphere.sharding.default-table-strategy.standard.range-algorithm-class-name=com.mxc.split.config.algorithm.PreciseModuloTableRangeShardingAlgorithm
3、java分片逻辑代码
package com.mxc.split.config.algorithm;
import java.text.SimpleDateFormat;
import java.util.Collection;
import java.util.Date;
import org.apache.commons.lang3.StringUtils;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingValue;
/**
* @ClassName: PreciseModuloDatabaseShardingAlgorithm
* @Description: 分库的自定义类(精确)
* @author mengxc
* @date 2020年9月29日 下午3:58:46
*/
public class PreciseModuloDatabaseShardingAlgorithm implements PreciseShardingAlgorithm<Date> {
/**
* @param collection 存放的是所有的库的列表
* @return 将数据写入的哪个库
*/
@Override
public String doSharding(Collection<String> collection, PreciseShardingValue<Date> preciseShardingValue) {
// 对于库的分片collection存放的是所有的库的列表，这里代表db-test-2019~db-test-2023
// 配置的分片的sharding-column对应的值
Date createTime = preciseShardingValue.getValue();
String timeValue = new SimpleDateFormat("yyyyMMddHHmmss").format(createTime);
// 分库时配置的sharding-column
// String time = preciseShardingValue.getColumnName();
// 需要分库的逻辑表
// String table = preciseShardingValue.getLogicTableName();
if (StringUtils.isBlank(timeValue)) {
throw new UnsupportedOperationException("精确分片值为NULL;");
}
// 按年路由
for (String each : collection) {
String value = StringUtils.substring(timeValue, 0, 4); // 获取到年份
if (each.endsWith(value)) {
// 这里返回回去的就是最终需要查询的库名
return each;
}
}
throw new UnsupportedOperationException();
}
}
package com.mxc.split.config.algorithm;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.Collection;
import java.util.LinkedHashSet;
import org.apache.shardingsphere.api.sharding.standard.RangeShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.standard.RangeShardingValue;
import com.google.common.collect.Range;
/**
* @ClassName: PreciseModuloDatabaseRangeShardingAlgorithm
* @Description: 分库的自定义类(范围)
* @author mengxc
* @date 2020年9月29日 下午3:58:46
*/
public class PreciseModuloDatabaseRangeShardingAlgorithm implements RangeShardingAlgorithm<String> {
protected static final DateTimeFormatter dtfTime = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
@Override
public Collection<String> doSharding(Collection<String> availableTargetNames,
RangeShardingValue<String> rangeShardingValue) {
Range<String> ranges = rangeShardingValue.getValueRange();
String lower = ranges.lowerEndpoint();
String upper = ranges.upperEndpoint();
LocalDateTime start = LocalDateTime.parse(lower, dtfTime);
LocalDateTime end = LocalDateTime.parse(upper, dtfTime);
int startYear = start.getYear();
int endYear = end.getYear();
Collection<String> databases = new LinkedHashSet<String>();
if (start.isBefore(end) && (endYear - startYear) == 0) {
for (String c : availableTargetNames) {
int cYear = Integer.parseInt(c.substring(c.length() - 4, c.length()));
if (cYear >= startYear && cYear <= endYear) {
databases.add(c);
}
}
}
return databases;
}
}
package com.mxc.split.config.algorithm;
import org.apache.commons.lang3.StringUtils;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingValue;
import java.text.SimpleDateFormat;
import java.util.Collection;
import java.util.Date;
/**
* @ClassName: PreciseModuloTableShardingAlgorithm
* @Description: 分表的自定义规则类(精确)
* @author mengxc
* @date 2020年9月29日 下午4:00:01
*/
public class PreciseModuloTableShardingAlgorithm implements PreciseShardingAlgorithm<Date> {
@Override
public String doSharding(Collection<String> collection, PreciseShardingValue<Date> preciseShardingValue) {
// 对于库的分片collection存放的是所有的库的列表，这里代表t_product_01~t_product_12
// 配置的分片的sharding-column对应的值
Date createTime = preciseShardingValue.getValue();
String timeValue = new SimpleDateFormat("yyyyMMddHHmmss").format(createTime);
// 分库时配置的sharding-column
// String time = preciseShardingValue.getColumnName();
// 需要分库的逻辑表
// String table = preciseShardingValue.getLogicTableName();
if (StringUtils.isBlank(timeValue)) {
throw new UnsupportedOperationException("精确分片值为NULL;");
}
// 按月路由
for (String each : collection) {
String value = StringUtils.substring(timeValue, 4, 6); // 获取到月份
System.out.println("each==" + each);
if (each.endsWith(value)) {
// 这里返回回去的就是最终需要查询的表名
return each;
}
}
return null;
}
}
package com.mxc.split.config.algorithm;
import org.apache.shardingsphere.api.sharding.standard.RangeShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.standard.RangeShardingValue;
import com.google.common.collect.Range;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.Collection;
import java.util.LinkedHashSet;
/**
* @ClassName: PreciseModuloTableRangeShardingAlgorithm
* @Description: 分表的自定义规则类(范围)
* @author mengxc
* @date 2020年9月29日 下午4:00:01
*/
public class PreciseModuloTableRangeShardingAlgorithm implements RangeShardingAlgorithm<String> {
protected static final DateTimeFormatter dtfTime = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
@Override
public Collection<String> doSharding(Collection<String> availableTargetNames,
RangeShardingValue<String> rangeShardingValue) {
Range<String> ranges = rangeShardingValue.getValueRange();
String lower = ranges.lowerEndpoint();
String upper = ranges.upperEndpoint();
LocalDateTime start = LocalDateTime.parse(lower, dtfTime);
LocalDateTime end = LocalDateTime.parse(upper, dtfTime);
int startYear = start.getYear();
int endYear = end.getYear();
int startMonth = start.getMonthValue();
int endMonth = end.getMonthValue();
Collection<String> tables = new LinkedHashSet<String>();
if (start.isBefore(end) && (endYear - startYear) == 0 && (endMonth - startMonth) >= 0) {
for (String c : availableTargetNames) {
int cMonth = Integer.parseInt(c.substring(c.length() - 2, c.length()));
if (cMonth >= startMonth && cMonth <= endMonth) {
tables.add(c);
}
}
}
return tables;
}
}
Demo
下载:https://mengxc.lanzouh.com/iGPbv0271jlg
密码:ahai