说明
分区字段为int类型,并且为主键,如下示例的date
CREATE TABLE `mytest` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '(id)',
`gmt_create` datetime DEFAULT NULL,
`gmt_modify` datetime DEFAULT NULL,
`name` varchar(50) DEFAULT NULL COMMENT '名称',
`age` int DEFAULT NULL COMMENT '年龄',
`sdate` int NOT NULL COMMENT '天(分区键,20231129)',
PRIMARY KEY (`id`,`sdate`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci AVG_ROW_LENGTH=585 ROW_FORMAT=DYNAMIC COMMENT='';
job
import com.alibaba.gts.cdyj.task.biz.dao.mapper.PartitionManagerDayMapper;
import com.alibaba.gts.flm.common.utils.DateUtil;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.springframework.context.annotation.Configuration;
import org.springframework.scheduling.annotation.Scheduled;
import javax.annotation.PostConstruct;
import javax.annotation.Resource;
import java.util.ArrayList;
import java.util.List;
@Configuration
@Slf4j
public class PartitionManagerDayJob {
@Resource
private PartitionManagerDayMapper partitionManagerMapper;
private static final String prefix = "p_";
private static final List<Config> configs = new ArrayList<Config>() {{
add(new Config("mytest", "sdate", 7));
}};
@PostConstruct
private void init() {
handle();
}
/**
* 0点和12点执行
*/
@Scheduled(cron = "0 0 0,12 * * ? ")
public void task() {
handle();
}
public void handle() {
log.info("分区管理逻辑开始执行... configs.size:{}", configs.size());
for (Config config : configs) {
String table = config.getTable();
String field = config.getField();
Integer partitionCount = partitionManagerMapper.selectCountPartition(table);
log.info("table:{} 分区数量:{}", table, partitionCount);
if (partitionCount > 0) {
// 已有分区
/// 创建新分区(创建未来2天分区)
for (int i = 0; i < 2; i++) {
String day = DateUtil.format(System.currentTimeMillis() + 1000 * 60 * 60 * 24 * i, DateUtil.PATTERN_YYYYMMDD);
if (!exist(table, prefix + day)) {
partitionManagerMapper.createPartition(table, field, prefix + day, day);
log.info("table:{} 创建分区:{}", table, prefix + day);
}
}
long deleteTime = System.currentTimeMillis() - (config.getExpireDay() * 1000L * 60 * 60 * 24);
String deleteDay = DateUtil.format(deleteTime, DateUtil.PATTERN_YYYYMMDD);
/// 删除旧分区
if (exist(table, prefix + deleteDay)) {
partitionManagerMapper.deletePartition(table, prefix + deleteDay);
log.info("table:{} 删除分区:{}", table, prefix + deleteDay);
}
} else {
// 无分区
// 初始化分区,初始化当前小时分区
String currentDay = DateUtil.format(System.currentTimeMillis(), DateUtil.PATTERN_YYYYMMDD);
partitionManagerMapper.initPartition(table, field, prefix + currentDay, currentDay);
log.info("table:{} 初始化分区:{}", table, prefix + currentDay);
// 创建未来4天分区
for (int i = 0; i < 4; i++) {
String day = DateUtil.format(System.currentTimeMillis() + 1000L * 60 * 60 * 24 * i, DateUtil.PATTERN_YYYYMMDD);
if (!exist(table, prefix + day)) {
partitionManagerMapper.createPartition(table, field, prefix + day, day);
log.info("table:{} 创建分区:{}", table, prefix + day);
}
}
}
}
log.info("分区管理逻辑执行完成");
}
private Boolean exist(String table, String partitionName) {
return partitionManagerMapper.selectPartitionIsExist(table, partitionName) > 0;
}
@Data
@AllArgsConstructor
@NoArgsConstructor
public static class Config {
/* 表名 */
private String table;
/* 分区字段 */
private String field;
/* 多少小时后过期 */
private Integer expireDay;
}
}
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;
/**
* @Author: liyue
* @Date: 2023/04/25/15:15
* @Description: 组件库
*/
@Mapper
public interface PartitionManagerDayMapper {
@Select("SELECT count(1) FROM INFORMATION_SCHEMA.partitions WHERE TABLE_SCHEMA = schema() AND TABLE_NAME=#{tableName} and PARTITION_NAME is not null;")
Integer selectCountPartition(@Param("tableName") String tableName);
@Select("SELECT count(1) FROM INFORMATION_SCHEMA.partitions WHERE TABLE_SCHEMA = schema() AND TABLE_NAME=#{tableName} AND partition_name = #{partitionName};")
Integer selectPartitionIsExist(@Param("tableName") String tableName, @Param("partitionName") String partitionName);
@Update("ALTER TABLE ${tableName} DROP PARTITION ${partitionName};")
int deletePartition(@Param("tableName") String tableName, @Param("partitionName") String partitionName);
@Update("ALTER TABLE ${table} partition by range (${timeField})( partition ${partitionName} values less than(${partitionVal} ));")
int initPartition(@Param("table") String table, @Param("timeField") String timeField, @Param("partitionName") String partitionName, @Param("partitionVal") String partitionVal);
@Update("ALTER TABLE ${table} ADD PARTITION ( partition ${partitionName} values less than(${partitionVal} ))")
int createPartition(@Param("table") String table, @Param("timeField") String timeField, @Param("partitionName") String partitionName, @Param("partitionVal") String partitionVal);
}
原文地址:https://blog.csdn.net/Lxinccode/article/details/134688085
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。
如若转载,请注明出处:http://www.7code.cn/show_33360.html
如若内容造成侵权/违法违规/事实不符,请联系代码007邮箱:suwngjj01@126.com进行投诉反馈,一经查实,立即删除!
声明:本站所有文章,如无特殊说明或标注,均为本站原创发布。任何个人或组织,在未征得本站同意时,禁止复制、盗用、采集、发布本站内容到任何网站、书籍等各类媒体平台。如若本站内容侵犯了原著者的合法权益,可联系我们进行处理。