BEGIN
set @p_name = CONCAT(dtable,'_p',date_format(DATE_ADD(curdate(),interval 2 MONTH),'%Y%m'));
SET @exist=(
SELECT
COUNT(*)
FROM
INFORMATION_SCHEMA.partitions
WHERE
TABLE_SCHEMA = schema()
AND TABLE_NAME= dtable AND partition_name=@p_name);
SELECT @exist,@p_name;
if @exist <1 then
-- 提前建立包含当前时间的分区
set @p_date = date_format(DATE_ADD(curdate(),interval 3 MONTH),'%Y-%m-01');
set @p_sql = CONCAT('ALTER TABLE ',dtable,' ADD PARTITION(PARTITION ',@p_name,' VALUES LESS THAN ( unix_timestamp(\'',@p_date,'\')))');
PREPARE stmt FROM @p_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
end if;
-- 清除半年以前的数据和分区
set @oldp_name = CONCAT(dtable,'_p',date_format(DATE_ADD(curdate(),interval -7 MONTH),'%Y%m'));
SET @exist=(
SELECT
COUNT(*)
FROM
INFORMATION_SCHEMA.partitions
WHERE
TABLE_SCHEMA = schema()
AND TABLE_NAME= dtable AND partition_name=@oldp_name);
SELECT @exist,@oldp_name;
if @exist >0 then
set @p_sql = CONCAT('ALTER TABLE ',dtable,' DROP PARTITION ',@oldp_name);
PREPARE stmt FROM @p_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
end if;
-- call setpartitionplan('a000')
END
网友评论