lcj-jarvis commented on issue #32598:
URL:
https://github.com/apache/shardingsphere/issues/32598#issuecomment-2296871296
1、schema SQL
CREATE TABLE t_side_monitor_data_1
(
id BIGINT AUTO_INCREMENT PRIMARY KEY,
type VARCHAR(100),
name VARCHAR(100),
code VARCHAR(100),
value DOUBLE,
collect_time datetime,
backup_id BIGINT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
2、application.yml (tip: hidden my database info, such as url)
server:
port: 8080
# 数据库配置
spring:
shardingsphere:
# 开启sql日志
props:
sql-show: true
datasource:
names: ds0
ds0:
type: com.alibaba.druid.pool.DruidDataSource
url: xxxxxxxxxx
username: xxxxxxxxxx
password: xxxxxxxxxx
driver-class-name: com.mysql.cj.jdbc.Driver
druid:
initial-size: 5
min-idle: 5
max-active: 20
max-wait: 60000
time-between-eviction-runs-millis: 60000
min-evictable-idle-time-millis: 300000
validation-query: SELECT 1
test-while-idle: true
test-on-borrow: false
test-on-return: false
pool-prepared-statements: true
max-pool-prepared-statement-per-connection-size: 20
rules:
sharding:
tables:
t_side_monitor_data:
actual-data-nodes: ds0.t_side_monitor_data_${1..24}
# 等同于上面
# actual-data-nodes: ds0.t_side_monitor_data_${01..24}
table-strategy:
complex:
sharding-columns: collect_time, type
sharding-algorithm-name: customShardingAlgorithm
key-generator:
column: id
type: SNOWFLAKE
sharding-algorithms:
customShardingAlgorithm:
type: COMPLEX
props:
strategyClass: com.mrlu.sharding.route.CustomShardingAlgorithm
allow-range-query-with-inline-sharding: true
# MyBatis-Plus 配置
mybatis-plus:
configuration:
map-underscore-to-camel-case: true
3、custom rule
package com.mrlu.sharding.route;
import com.google.common.collect.Range;
import lombok.extern.slf4j.Slf4j;
import
org.apache.shardingsphere.sharding.api.sharding.complex.ComplexKeysShardingAlgorithm;
import
org.apache.shardingsphere.sharding.api.sharding.complex.ComplexKeysShardingValue;
import java.util.*;
/**
* 自定义分片策略
* 先根据类型hash范围，再根据日期确定月份，最终确定所属的表
*/
@Slf4j
public class CustomShardingAlgorithm implements
ComplexKeysShardingAlgorithm<String> {
private static final String COLLECT_TIME = "collect_time";
private static final String TYPE = "type";
@Override
public Collection<String> doSharding(Collection<String>
availableTargetNames,
ComplexKeysShardingValue<String>
shardingValue) {
Collection<String> tables = new HashSet<>();
// 根据类型的hashCode取模
int typeHashMod = getTypeHashMod(shardingValue);
// 计算月份
List<Integer> months = getMonths(shardingValue);
for (Integer month : months) {
// 保留两位数
tables.add("t_side_monitor_data_" + String.format("%02d",
typeHashMod * 12 + month));
}
log.info("tables={}", tables);
return tables;
}
private int getTypeHashMod(ComplexKeysShardingValue shardingValue) {
// 精确查询的字段
Map<String, Collection<String>> columnNameAndShardingValuesMap =
shardingValue.getColumnNameAndShardingValuesMap();
// 获取分片列的值
int typeHashMod =
Math.abs(columnNameAndShardingValuesMap.get(TYPE).iterator().next().hashCode()
% 2);
return typeHashMod;
}
private List<Integer> getMonths(ComplexKeysShardingValue shardingValue) {
// 精确查询的字段
Map<String, Collection<Date>> columnNameAndShardingValuesMap =
shardingValue.getColumnNameAndShardingValuesMap();
if (columnNameAndShardingValuesMap.containsKey(COLLECT_TIME)) {
// Check type and convert to Date
Date collectTime =
columnNameAndShardingValuesMap.get(COLLECT_TIME).iterator().next();
// 使用 Calendar 获取月份
Calendar calendar = Calendar.getInstance();
calendar.setTime(collectTime);
// Calendar 的月份从 0 开始，所以要 +1
int month = calendar.get(Calendar.MONTH) + 1;
return Collections.singletonList(month);
}
// 范围查询的月份
return computeMonths(shardingValue);
}
private List<Integer> computeMonths(ComplexKeysShardingValue
shardingValue) {
// 范围查询的字段
Map<String, Range> columnNameAndRangeValuesMap =
shardingValue.getColumnNameAndRangeValuesMap();
Range<Date> valueRange =
columnNameAndRangeValuesMap.get(COLLECT_TIME);
// 范围查询时候要查询哪些表
Collection<Integer> result = new LinkedHashSet<>();
Calendar calendar = Calendar.getInstance();
// 获取起始日期的年份和月份
calendar.setTime(valueRange.lowerEndpoint());
int startYear = calendar.get(Calendar.YEAR);
int startMonth = calendar.get(Calendar.MONTH) + 1;
// 获取结束日期的年份和月份
calendar.setTime(valueRange.upperEndpoint());
int endYear = calendar.get(Calendar.YEAR);
int endMonth = calendar.get(Calendar.MONTH) + 1;
// 处理跨年情况
for (int year = startYear; year <= endYear; year++) {
int start = (year == startYear) ? startMonth : 1;
int end = (year == endYear) ? endMonth : 12;
for (int month = start; month <= end; month++) {
result.add(month);
}
}
return new ArrayList<>(result);
}
@Override
public String getType() {
return "COMPLEX";
}
@Override
public Properties getProps() {
return null;
}
@Override
public void init(Properties properties) {
}
}
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
