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