MySQL按月自动设置表分区的实现！

admin • • SQL技巧

MySQL按月自动设置表分区的实现！

本文主要介绍了MySQL按月自动设置表分区的实现,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友们下面随着小编来一起学习学习吧。

开始检查

首先，确保 ticket_history_info表是一个分区表。如果未设置分区，需要修改表结构以支持分区，将 ticket_history_info 表设置为按日期范围进行分区

1
2
3
4
ALTER TABLE ticket_history_info
PARTITION BY RANGE (TO_DAYS(CALL_DATE)) (
PARTITION p0 VALUES LESS THAN (TO_DAYS('2022-09-01'))
);

1.创建分区函数

1
2
3
4
5
6
7
8
9
10
11
12
13
-- 创建分区函数
DELIMITER //
CREATE FUNCTION get_partition_name(p_date DATE)
RETURNS VARCHAR(20)
DETERMINISTIC
BEGIN
DECLARE p_month VARCHAR(2);
DECLARE p_year VARCHAR(4);
SET p_month = LPAD(MONTH(p_date), 2, '0');
SET p_year = YEAR(p_date);
RETURN CONCAT('p', p_year, p_month);
END//
DELIMITER ;

检查是否成功创建函数

1
SHOW FUNCTION STATUS LIKE 'get_partition_name';

2.创建存储过程，用于自动生成分区

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
-- 存储过程
DELIMITER //
CREATE PROCEDURE create_monthly_partition()
BEGIN
DECLARE next_month VARCHAR(20);
DECLARE next_month_first_day DATE;
-- 计算下一个月份的名称和下个月的第一天
SET next_month = CONCAT('p', DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL 1 MONTH), '%Y%m'));
SET next_month_first_day = LAST_DAY(DATE_ADD(CURDATE(), INTERVAL 1 MONTH)) + INTERVAL 1 DAY;
-- 检查分区是否已存在
IF NOT EXISTS (
SELECT NULL
FROM information_schema.PARTITIONS
WHERE TABLE_NAME = 'ticket_history_info' AND TABLE_SCHEMA = 'ccm' AND PARTITION_NAME = next_month
) THEN
-- 创建新的分区
SET @sql = CONCAT('ALTER TABLE ticket_history_info ADD PARTITION (PARTITION ', next_month, ' VALUES LESS THAN (TO_DAYS(\'', next_month_first_day, '\')))');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END IF;
END//
DELIMITER ;

 进行测试

检查是否成功创建函数

1
SHOW PROCEDURE STATUS LIKE 'create_monthly_partition';

执行函数

1
CALL create_monthly_partition();

查询表分区，查看是否成功分区。

1
SELECT PARTITION_NAME FROM information_schema.PARTITIONS WHERE TABLE_NAME = '表名' AND TABLE_SCHEMA = '数据库名';

可通过修改本地系统时间，来进行反复测试是否可按照月份进行分区。

2024080809394217

返回成功，显示已创建表分区。

2024080809394218

3.创建自动删除半年以前的表空间函数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
DELIMITER //
CREATE PROCEDURE delete_old_partitions()
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE drop_partition_name VARCHAR(64);
DECLARE cur CURSOR FOR
SELECT PARTITION_NAME
FROM information_schema.PARTITIONS
WHERE TABLE_SCHEMA = '数据库名'
AND TABLE_NAME = 'ticket_history_info'
AND PARTITION_DESCRIPTION < TO_DAYS(NOW() - INTERVAL 6 MONTH);
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
OPEN cur;
read_loop: LOOP
FETCH cur INTO drop_partition_name;
IF done THEN
LEAVE read_loop;
END IF;
SET @sql = CONCAT('ALTER TABLE ticket_history_info DROP PARTITION ', drop_partition_name);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END LOOP;
CLOSE cur;
-- 删除超过半年的数据
DELETE FROM ticket_history_info_copy1
WHERE CALL_DATE < CURDATE() - INTERVAL 6 MONTH;
END//
DELIMITER ;

4.创建调度任务，修改到每月最后一天执行执行存储过程

1
2
3
4
5
6
7
8
CREATE EVENT IF NOT EXISTS monthly_partition_event
ON SCHEDULE EVERY 1 MONTH
STARTS CONCAT(DATE_FORMAT(LAST_DAY(CURRENT_DATE), '%Y-%m-'), DAY(LAST_DAY(CURRENT_DATE)), ' 23:00:00')
DO
BEGIN
CALL create_monthly_partition();
CALL delete_old_partitions();
END;

查看事件调度器是否启动

1
SET GLOBAL event_scheduler = ON;

查看事件

1
SHOW EVENTS;

或

1
SHOW EVENTS FROM `数据库名`;

创建成功

2024080809394219

5.进行测试

插入测试数据进行测试，根据CALL_DATE字段进行数据分区，

1
2
3
4
5
INSERT INTO ticket_history_info (CALL_DATE, SRC_ADD, CALLING_NUM, DURATION) VALUES
('2024-05-24 00:00:00', 'Address1', '1234567890', 60),
('2024-06-20 00:00:00', 'Address2', '0987654321', 120),
('2024-05-10 00:00:00', 'Address3', '1122334455', 180),
('2024-10-01 00:00:00', 'Address4', '5566778899', 240);

-查询每个分区的行数

1
2
3
SELECT PARTITION_NAME, TABLE_ROWS
FROM information_schema.PARTITIONS
WHERE TABLE_SCHEMA = 'ccm' AND TABLE_NAME = 'ticket_history_info';

或者单独查询某一个分区的数据

1
2
SELECT *
FROM ticket_history_info PARTITION (p202406);

到此这篇关于MySQL按月自动设置表分区的实现的文章就介绍到这了。

 

学习资料见知识星球。

以上就是今天要分享的技巧，你学会了吗？若有什么问题，欢迎在下方留言。

快来试试吧，小琥 my21ke007。获取 1000个免费 Excel模板福利​​​​！

更多技巧， www.excelbook.cn

欢迎 加入 零售创新 知识星球，知识星球主要以数据分析、报告分享、数据工具讨论为主；

如何快速写出10W+爆款文章！

你将获得：

1、价值上万元的专业的PPT报告模板。

2、专业案例分析和解读笔记。

3、实用的Excel、Word、PPT技巧。

4、VIP讨论群，共享资源。

5、优惠的会员商品。

6、一次付费只需129元，即可下载本站文章涉及的文件和软件。

文章版权声明 1、本网站名称：Excelbook
2、本站永久网址：http://www.excelbook.cn
3、本网站的文章部分内容可能来源于网络，仅供大家学习与参考，如有侵权，请联系站长王小琥进行删除处理。
4、本站一切资源不代表本站立场，并不代表本站赞同其观点和对其真实性负责。
5、本站一律禁止以任何方式发布或转载任何违法的相关信息，访客发现请向站长举报。
6、本站资源大多存储在云盘，如发现链接失效，请联系我们我们会第一时间更新。

THE END
分享
二维码
< <上一篇
下一篇>>