MySQL에서 자동으로 파티션을 추가하기 위해 MySQL의 event와 procedure를 활용한다.
1) 프로시져 생성
2) event 생성
1. 프로시저 생성
파티션된 테이블에 maxvalue 파티션이 없는 경우와 maxvalue 파티션이 있는 경우에 대해 각각 파티션을 추가하는 프로시저이다.
1) maxvalue 가 없는 파티션인 경우
maxvalue가 없는 파티션 테이블
| CREATE TABLE sales ( sale_id INT NOT NULL, sale_date DATETIME NOT NULL, customer_id INT NOT NULL, amount DECIMAL(10,2), PRIMARY KEY (sale_id, sale_date) ) PARTITION BY RANGE (TO_DAYS(sale_date)) ( PARTITION p_2025_01 VALUES LESS THAN (TO_DAYS('2025-02-01')), PARTITION p_2025_02 VALUES LESS THAN (TO_DAYS('2025-03-01')), PARTITION p_2025_03 VALUES LESS THAN (TO_DAYS('2025-04-01')), PARTITION p_2025_04 VALUES LESS THAN (TO_DAYS('2025-05-01')) ); |
자동으로 매월 새로운 파티션을 추가하는 프로시저
| DELIMITER // CREATE PROCEDURE pr_monthly_add_partition(IN partition_time DATETIME) BEGIN SET @sql = CONCAT( 'ALTER TABLE sales ADD PARTITION (', 'PARTITION p_', DATE_FORMAT(DATE_SUB(DATE_FORMAT(partition_time, '%Y%m%d'), interval 1 month), '%Y_%m'), ' VALUES LESS THAN (', TO_DAYS(partition_time), '));' ); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; END // DELIMITER ; |
파티션 추가를 위한 프로시저 호출
| CALL pr_monthly_add_partition('2025-06-01'); |
2) maxvalue 가 있는 파티션인 경우
maxvalue가 있는 파티션 테이블
| CREATE TABLE sales_max ( sale_id INT NOT NULL, sale_date DATETIME NOT NULL, customer_id INT NOT NULL, amount DECIMAL(10,2), PRIMARY KEY (sale_id, sale_date) ) PARTITION BY RANGE (TO_DAYS(sale_date)) ( PARTITION p_2025_01 VALUES LESS THAN (TO_DAYS('2025-02-01')), PARTITION p_2025_02 VALUES LESS THAN (TO_DAYS('2025-03-01')), PARTITION p_2025_03 VALUES LESS THAN (TO_DAYS('2025-04-01')), PARTITION p_2025_04 VALUES LESS THAN (TO_DAYS('2025-05-01')), PARTITION p_maxvalue VALUES LESS THAN MAXVALUE ); |
| DELIMITER // CREATE PROCEDURE pr_monthly_add_partition_max(IN partition_time DATETIME) BEGIN SET @sql = CONCAT( 'ALTER TABLE sales_max REORGANIZE PARTITION p_maxvalue INTO(\n', 'PARTITION p_', DATE_FORMAT(partition_time, '%Y_%m'), ' VALUES LESS THAN (', TO_DAYS(partition_time), '),\n', 'PARTITION p_maxvalue', ' VALUES LESS THAN MAXVALUE);'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; END // DELIMITER ; |
파티션 추가를 위한 프로시저 호출
| CALL pr_monthly_add_partition_max('2025-06-01'); |
2. event 생성
위에서 생성한 프로시저를 event로 등록하여 필요한 시점에 수행되도록 한다.
event를 수행하기 위해서는 event_scheduler가 ON 으로 설정되어 있어야 한다.
이벤트 스케줄러 활성화
SET GLOBAL event_scheduler = ON;
매달 1일 자동 실행되는 EVENT 생성
매달 1일에 자동으로 파티션을 추가하는 event를 생성한다. event는 매달 1일 00:00:00 에 수행된다.
| CREATE EVENT ev_monthly_add_partition ON SCHEDULE EVERY 1 MONTH STARTS TIMESTAMP(LAST_DAY(CURRENT_DATE() - INTERVAL 1 MONTH) + INTERVAL 1 DAY) ON COMPLETION PRESERVE COMMENT '매달 1일에 파티션 추가 EVENT' DO CALL pr_monthly_add_partition(DATE_FORMAT(LAST_DAY(CURRENT_DATE() - INTERVAL 1 MONTH) + INTERVAL 1 DAY, '%Y-%m-%d')); |
만일, 매달 1일 01:00 수행하기를 원하면
STARTS TIMESTAMP(LAST_DAY(CURRENT_DATE() - INTERVAL 1 MONTH) + INTERVAL 1 DAY)를
STARTS TIMESTAMP(LAST_DAY(CURRENT_DATE() - INTERVAL 1 MONTH) + INTERVAL 25 HOUR) 로 수정하면 된다.
위와 같은 방법으로 수행하기를 원하는 시분초를 지정할 수 있다.
'MySQL' 카테고리의 다른 글
| MySQL uptime (0) | 2025.03.27 |
|---|---|
| MySQL event란 (0) | 2024.12.05 |
| MySQL Lock 발생 (0) | 2023.09.22 |
| MySQL - stored procedure 성능 (0) | 2023.06.29 |
| MySQL - Delimiter (0) | 2023.04.10 |
