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

----------------分割线-------------------------------

上述方法用代码来判断分区，新增分区，可能会引入一些奇奇怪怪的问题，因此，优化如下：

【针对mysql，使用mysql的定时事件】

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语句。