mysql 数据库自动分区 存储过程

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