mysql(47) : 天分区表自动管理

说明
分区字段为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 {@Resourceprivate PartitionManagerDayMapper partitionManagerMapper;private static final String prefix = "p_";private static final List<Config> configs = new ArrayList<Config>() {{add(new Config("mytest", "sdate", 7));}};@PostConstructprivate 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@NoArgsConstructorpublic static class Config {/* 表名 */private String table;/* 分区字段 */private String field;/* 多少小时后过期 */private Integer expireDay;}
}

mapper


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);
}