파티션 테이블 AUTO 스위칭 SP

DELIMITER $$

DROP PROCEDURE IF EXISTS `test`.`partition_test`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `partition_test`()
BEGIN
DECLARE var_min_date INT;
DECLARE var_now_date INT;
DECLARE var_max_date INT;

SELECT MIN(PARTITION_DESCRIPTION) INTO var_min_date
FROM information_schema.partitions
WHERE table_name='joonho_smt_log';

SELECT MIN(partition_name) INTO @partition_name
FROM information_schema.partitions
WHERE table_name='joonho_smt_log';

SELECT MAX(PARTITION_DESCRIPTION) INTO var_max_date
FROM information_schema.partitions
WHERE table_name='joonho_smt_log';

SELECT TO_DAYS(DATE_SUB(NOW(),INTERVAL 365 DAY)) INTO var_now_date;

SELECT DATE_ADD(FROM_DAYS(var_max_date), interval 1 MONTH) INTO @new_partition_name;

IF var_min_date < var_now_date then
BEGIN
SET @i := CONCAT("ALTER TABLE test.joonho_smt_log DROP PARTITION ", @partition_name, " ;");
PREPARE stmt FROM @i;
EXECUTE stmt;
END;
BEGIN
SET @i := CONCAT("ALTER TABLE test.joonho_smt_log ADD PARTITION (PARTITION P",REPLACE(@new_partition_name,'-',''), " VALUES LESS THAN (TO_DAYS('",@new_partition_name,"')));");
PREPARE stmt FROM @i;
EXECUTE stmt;
END;
END IF;
END$$

DELIMITER ;