1.SQL表创建
下面以时间范围进行创建（每月一个分区，表中创建了四个月的分区）
创建：CREATE TABLE test_table ( id INT NOT NULL AUTO_INCREMENT, content VARCHAR(255), create_time DATETIME NOT NULL,PRIMARY KEY (id, create_time)
) PARTITION BY RANGE (TO_DAYS(create_time)) ( PARTITION p20240601 VALUES LESS THAN (TO_DAYS('2024-06-01')), PARTITION p20240701 VALUES LESS THAN (TO_DAYS('2024-07-01')), PARTITION p20241801 VALUES LESS THAN (TO_DAYS('2024-08-01')), PARTITION p20240901 VALUES LESS THAN (TO_DAYS('2024-09-01'))
); 查询分区详情：
SELECT *
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_NAME = 'test_table';
2、mapper文件
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapperPUBLIC "-//mybatis.org//DTD Mapper 3.0//EN""http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="*.infrastructure.mapper.TestTableMapper"><resultMap id="TestTable" type="*.domain.entity.TestTable"><id column="id" property="id" typeHandler="org.apache.ibatis.type.LongTypeHandler"/><result property="content" column="content" jdbcType="VARCHAR"/><result property="createTime" column="create_time" jdbcType="TIMESTAMP"typeHandler="org.apache.ibatis.type.LocalDateTimeTypeHandler"/></resultMap><!-- 创建新分区 --><update id="createNewPartition">ALTER TABLE TEST_TABLEADD PARTITION ( PARTITION ${partitionName} VALUES LESS THAN (TO_DAYS(#{lessThanValue})))</update><!-- 删除旧分区 --><update id="dropPartition">ALTER TABLE TEST_TABLEDROPPARTITION${partitionName}</update><!--查询是否存在分区--><select id="exitsPartition" resultType="boolean">SELECT COUNT(1) > 0FROM INFORMATION_SCHEMA.PARTITIONSWHERE TABLE_NAME = 'TEST_TABLE'AND PARTITION_NAME = #{partitionName}</select></mapper>
3、service
package *.domain.service;import *.domain.entity.TestTable;
import *.infrastructure.repo.TestTableRepository;import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.stereotype.Service;import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.YearMonth;
import java.time.format.DateTimeFormatter;
import java.util.Random;@Service
public class TestTableService {@AutowiredTestTableRepository repository;// 插入数据，如果分区不存在，就创建分区，重新插入public void insert() {TestTable testTable = new TestTable();testTable.setContent("test");Random random = new Random();int i = Math.abs(random.nextInt()) % 365;LocalDateTime dateTime = LocalDateTime.now().minusDays(i);testTable.setCreateTime(dateTime);try {repository.getBaseMapper().insert(testTable);} catch (DataAccessException e) {LocalDate nextMonthFirstDay = YearMonth.from(dateTime).plusMonths(1).atDay(1);String lessThanValue = nextMonthFirstDay.format(DateTimeFormatter.ISO_DATE);String partitionName = "p" + lessThanValue.replaceAll("-", "");// 创建分区时加锁，如果是多节点，需要分布式锁synchronized (this) {if (!repository.getBaseMapper().exitsPartition(partitionName)) {repository.getBaseMapper().createNewPartition(partitionName, lessThanValue);}}repository.getBaseMapper().insert(testTable);}}// 创建分区public void createNewPartition(String partitionName, String lessThanValue) {repository.getBaseMapper().createNewPartition(partitionName, lessThanValue);}// 删除分区public void dropPartition(String partitionName) {repository.getBaseMapper().dropPartition(partitionName);}
}
----------------分割线-------------------------------
上述方法用代码来判断分区，新增分区，可能会引入一些奇奇怪怪的问题，因此，优化如下：
1、首先确认mysql的时间调度器是否已经开启：
-- 查询事件调度器是否开启
SHOW VARIABLES LIKE 'event_scheduler'; -- 确保事件调度器已经开启
SET GLOBAL event_scheduler = ON;
2、写存储过程，用于创建新的分区, 这里是按天创建新的分区
DELIMITER // CREATE PROCEDURE `AddDailyPartition`()
BEGIN DECLARE tomorrow DATE; DECLARE partition_name VARCHAR(20); -- 计算明天的日期 SET tomorrow = DATE_FORMAT(CURDATE() + INTERVAL 1 DAY, '%Y-%m-%d'); SET partition_name = CONCAT('p', DATE_FORMAT(tomorrow, '%Y%m%d')); -- 构建ALTER TABLE语句来添加分区 SET @sql = CONCAT('ALTER TABLE TEST_TABLE ', 'ADD PARTITION (PARTITION ', partition_name, ' VALUES LESS THAN (TO_DAYS('', tomorrow, '')))'); -- 执行ALTER TABLE语句 PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
END // DELIMITER ;
3、创建定时事件，调用存储过程
-- 创建定时事件
CREATE EVENT `CreateDailyPartition` ON SCHEDULE EVERY 1 DAY STARTS TIMESTAMP(CURDATE()) DO CALL AddDailyPartition();
4、查看已经创建的定时事件
SELECT * FROM information_schema.EVENTS; 在查看事件时，重要的列包括：
EVENT_NAME: 事件的名称。
EVENT_SCHEMA: 事件所属的数据库。
STATUS: 事件的状态，比如是否为ENABLED或DISABLED。
STARTS: 事件开始的时间。
ENDS: 事件结束的时间（如果有设置的话）。
LAST_EXECUTED: 事件上一次执行的时间。
EVENT_DEFINITION: 事件定义，即事件中要执行的SQL语句。
