mysql的分区表

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 mapper
        PUBLIC "-//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_TABLE
            ADD PARTITION (  
                PARTITION ${partitionName} VALUES LESS THAN (TO_DAYS(#{lessThanValue}))
            )
    </update>

    <!-- 删除旧分区 -->
    <update id="dropPartition">
        ALTER TABLE TEST_TABLE
        DROP
        PARTITION
        ${partitionName}
    </update>

    <!--查询是否存在分区-->
    <select id="exitsPartition" resultType="boolean">
        SELECT COUNT(1) > 0
        FROM INFORMATION_SCHEMA.PARTITIONS
        WHERE 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 {
    @Autowired
    TestTableRepository 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);
    }
}