mysql的分区表

1.SQL表创建
下面以时间范围进行创建（每月一个分区，表中创建了四个月的分区）

1. 创建：
3. CREATE TABLE test_table (  
4.     id INT NOT NULL AUTO_INCREMENT,  
5.     content VARCHAR(255),  
6.     create_time DATETIME NOT NULL,
7.    PRIMARY KEY (id, create_time)
8. ) PARTITION BY RANGE (TO_DAYS(create_time)) (  
9.     PARTITION p20240601 VALUES LESS THAN (TO_DAYS('2024-06-01')),  
10.     PARTITION p20240701 VALUES LESS THAN (TO_DAYS('2024-07-01')),  
11.     PARTITION p20241801 VALUES LESS THAN (TO_DAYS('2024-08-01')),  
12.     PARTITION p20240901 VALUES LESS THAN (TO_DAYS('2024-09-01'))
13. );  
15. 查询分区详情：
16. SELECT *
17. FROM
18.     INFORMATION_SCHEMA.PARTITIONS
19. WHERE
20.     TABLE_NAME = 'test_table';

复制代码

2、mapper文件

1. <?xml version="1.0" encoding="UTF-8"?>
2. <!DOCTYPE mapper
3.         PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
4.         "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
5. <mapper namespace="*.infrastructure.mapper.TestTableMapper">
7.     <resultMap id="TestTable" type="*.domain.entity.TestTable">
8.         <id column="id" property="id" typeHandler="org.apache.ibatis.type.LongTypeHandler"/>
9.         <result property="content" column="content" jdbcType="VARCHAR"/>
10.         <result property="createTime" column="create_time" jdbcType="TIMESTAMP"
11.                 typeHandler="org.apache.ibatis.type.LocalDateTimeTypeHandler"/>
12.     </resultMap>
14.     <!-- 创建新分区 -->
15.     <update id="createNewPartition">
16.         ALTER TABLE TEST_TABLE
17.             ADD PARTITION (  
18.                 PARTITION ${partitionName} VALUES LESS THAN (TO_DAYS(#{lessThanValue}))
19.             )
20.     </update>
22.     <!-- 删除旧分区 -->
23.     <update id="dropPartition">
24.         ALTER TABLE TEST_TABLE
25.         DROP
26.         PARTITION
27.         ${partitionName}
28.     </update>
30.     <!--查询是否存在分区-->
31.     <select id="exitsPartition" resultType="boolean">
32.         SELECT COUNT(1) > 0
33.         FROM INFORMATION_SCHEMA.PARTITIONS
34.         WHERE TABLE_NAME = 'TEST_TABLE'
35.           AND PARTITION_NAME = #{partitionName}
36.     </select>
38. </mapper>

复制代码

3、service

1. package *.domain.service;
3. import *.domain.entity.TestTable;
4. import *.infrastructure.repo.TestTableRepository;
6. import org.springframework.beans.factory.annotation.Autowired;
7. import org.springframework.dao.DataAccessException;
8. import org.springframework.stereotype.Service;
10. import java.time.LocalDate;
11. import java.time.LocalDateTime;
12. import java.time.YearMonth;
13. import java.time.format.DateTimeFormatter;
14. import java.util.Random;
16. @Service
17. public class TestTableService {
18.     @Autowired
19.     TestTableRepository repository;
21.         // 插入数据，如果分区不存在，就创建分区，重新插入
22.     public void insert() {
23.         TestTable testTable = new TestTable();
24.         testTable.setContent("test");
26.         Random random = new Random();
27.         int i = Math.abs(random.nextInt()) % 365;
28.         LocalDateTime dateTime = LocalDateTime.now().minusDays(i);
29.         testTable.setCreateTime(dateTime);
31.         try {
32.             repository.getBaseMapper().insert(testTable);
33.         } catch (DataAccessException e) {
34.             LocalDate nextMonthFirstDay = YearMonth.from(dateTime).plusMonths(1).atDay(1);
35.             String lessThanValue = nextMonthFirstDay.format(DateTimeFormatter.ISO_DATE);
36.             String partitionName = "p" + lessThanValue.replaceAll("-", "");
37.             // 创建分区时加锁，如果是多节点，需要分布式锁
38.             synchronized (this) {
39.                 if (!repository.getBaseMapper().exitsPartition(partitionName)) {
40.                     repository.getBaseMapper().createNewPartition(partitionName, lessThanValue);
41.                 }
42.             }
43.             repository.getBaseMapper().insert(testTable);
44.         }
45.     }
47.         // 创建分区
48.     public void createNewPartition(String partitionName, String lessThanValue) {
49.         repository.getBaseMapper().createNewPartition(partitionName, lessThanValue);
50.     }
52.         // 删除分区
53.     public void dropPartition(String partitionName) {
54.         repository.getBaseMapper().dropPartition(partitionName);
55.     }
56. }

复制代码

----------------分割线-------------------------------
上述方法用代码来判定分区，新增分区，大概会引入一些奇奇怪怪的题目，因此，优化如下：

【针对mysql，使用mysql的定时事件】
1、首先确认mysql的时间调理器是否已经开启：

1. -- 查询事件调度器是否开启
2. SHOW VARIABLES LIKE 'event_scheduler';
4. -- 确保事件调度器已经开启  
5. SET GLOBAL event_scheduler = ON;  

复制代码

2、写存储过程，用于创建新的分区, 这里是按天创建新的分区

1. DELIMITER //  
2.   
3. CREATE PROCEDURE `AddDailyPartition`()  
4. BEGIN  
5.     DECLARE tomorrow DATE;  
6.     DECLARE partition_name VARCHAR(20);  
7.   
8.     -- 计算明天的日期  
9.     SET tomorrow = DATE_FORMAT(CURDATE() + INTERVAL 1 DAY, '%Y-%m-%d');  
10.     SET partition_name = CONCAT('p', DATE_FORMAT(tomorrow, '%Y%m%d'));  
11.   
12.     -- 构建ALTER TABLE语句来添加分区  
13.     SET @sql = CONCAT('ALTER TABLE TEST_TABLE ',  
14.                       'ADD PARTITION (PARTITION ', partition_name,   
15.                       ' VALUES LESS THAN (TO_DAYS(\'', tomorrow, '\')))');  
16.   
17.     -- 执行ALTER TABLE语句  
18.     PREPARE stmt FROM @sql;  
19.     EXECUTE stmt;  
20.     DEALLOCATE PREPARE stmt;  
21. END //  
22.   
23. DELIMITER ;  

复制代码

3、创建定时事件，调用存储过程

1. -- 创建定时事件  
2. CREATE EVENT `CreateDailyPartition`  
3.     ON SCHEDULE EVERY 1 DAY STARTS TIMESTAMP(CURDATE())  
4.     DO CALL AddDailyPartition();  

复制代码

4、查察已经创建的定时事件

1. SELECT * FROM information_schema.EVENTS;
3. 在查看事件时，重要的列包括：
4. EVENT_NAME: 事件的名称。
5. EVENT_SCHEMA: 事件所属的数据库。
6. STATUS: 事件的状态，比如是否为ENABLED或DISABLED。
7. STARTS: 事件开始的时间。
8. ENDS: 事件结束的时间（如果有设置的话）。
9. LAST_EXECUTED: 事件上一次执行的时间。
10. EVENT_DEFINITION: 事件定义，即事件中要执行的SQL语句。

复制代码

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。