CREATE PROCEDURE `add_table_partition`()
COMMENT '增加表分区'
BEGIN
DECLARE v_table_name varchar(50);
DECLARE v_par_name varchar(20);
DECLARE i int DEFAULT 0;
DECLARE no_more int DEFAULT 0;
DECLARE cursor_employee CURSOR FOR
SELECT table_name,max(partition_name)
FROM information_schema.partitions
WHERE table_schema='test' and partition_name is not null and partition_name<>'p_date_min'
GROUP BY table_name;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more= 1;
OPEN cursor_employee;
FETCH cursor_employee INTO v_table_name,v_par_name;
WHILE !no_more DO
my_loop: LOOP
SET @j:= DATE_ADD(curdate(),INTERVAL i DAY);
SET @end_date:= last_day(DATE_ADD(last_day(curdate()),INTERVAL 1 DAY));
IF @j > @end_date THEN
LEAVE my_loop;
END IF;
SET i=i+1;
SET @par_value:= UNIX_TIMESTAMP(DATE_ADD(curdate(),INTERVAL i DAY));
SET @par_name:= concat('p_date_',date_format(@j,'%Y%m%d'));
IF @par_name>v_par_name THEN
SET @add_par:= concat('ALTER TABLE ',v_table_name,' ADD PARTITION(PARTITION ',@par_name,' VALUES LESS THAN (',@par_value,'));');
PREPARE stmt from @add_par;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END IF;
END LOOP;
SET i= 0;
FETCH cursor_employee INTO v_table_name,v_par_name;
END WHILE;
CLOSE cursor_employee;
END
CREATE PROCEDURE `drop table partition`(
in start_date date ,
in end_date date
)
COMMENT '删除分区'
BEGIN
DECLARE v_table_name varchar(50);
DECLARE v_par_name varchar(20);
DECLARE i int DEFAULT 0;
DECLARE no_more int DEFAULT 0;
DECLARE cursor_employee CURSOR FOR
SELECT table_name,partition_name
FROM information_schema.partitions
WHERE table_schema='test' and partition_name is not null and partition_name<>'p_date_min'
AND partition_name>=concat('p_date_',date_format(end_date,'%Y%m%d'))
AND partition_name<=concat('p_date_',date_format(end_date,'%Y%m%d'));
DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more= 1;
OPEN cursor_employee;
FETCH cursor_employee INTO v_table_name,v_par_name;
WHILE !no_more DO
SET @drop_par:= concat('ALTER TABLE ',v_table_name,' DROP PARTITION ',v_par_name,';');
PREPARE stmt from @drop_par;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
FETCH cursor_employee INTO v_table_name,v_par_name;
END WHILE;
CLOSE cursor_employee;
END