mysql动态创建表分区

DELIMITER //  


DROP PROCEDURE IF EXISTS history_procedure
//


CREATE  PROCEDURE  history_procedure()
BEGIN
    Set @pName=concat('p_',(SELECT EXTRACT(YEAR_MONTH  FROM now())));
    Set @pNameNext=concat('p_',(SELECT EXTRACT(YEAR_MONTH  FROM DATE_SUB(now(),INTERVAL -1 MONTH))));
    Set @pNameNext2=concat('p_',(SELECT EXTRACT(YEAR_MONTH  FROM DATE_SUB(now(),INTERVAL -2 MONTH))));
    Set @pNameNext3=concat('p_',(SELECT EXTRACT(YEAR_MONTH  FROM DATE_SUB(now(),INTERVAL -3 MONTH))));
    set @tableName='history';
    set @columnName='clock';
    set @pValue=(select UNIX_TIMESTAMP(date_sub(date_sub(date_format(now(),'%y-%m-%d 00:00:00'),interval extract( day from now())-1 day),interval -1 month)) );/*下个月1号零点*/
    set @pValueNext=(select UNIX_TIMESTAMP(date_sub(date_sub(date_format(now(),'%y-%m-%d 00:00:00'),interval extract( day from now())-1 day),interval -2 month)) );/*下下个月1号零点*/
    set @pValueNext2=(select UNIX_TIMESTAMP(date_sub(date_sub(date_format(now(),'%y-%m-%d 00:00:00'),interval extract( day from now())-1 day),interval -3 month)) );/*下下下个月1号零点*/
    set @pValueNext3=(select UNIX_TIMESTAMP(date_sub(date_sub(date_format(now(),'%y-%m-%d 00:00:00'),interval extract( day from now())-1 day),interval -4 month)) );/*下下下个月1号零点*/
    set @p_size=(SELECT count(*) FROM  INFORMATION_SCHEMA.partitions  WHERE TABLE_SCHEMA = schema() AND TABLE_NAME=@tableName AND partition_name='p_x');/*检查是否有表分区，没有就创建，有就追加*/
    set @p_add0_size=(SELECT count(*) FROM  INFORMATION_SCHEMA.partitions  WHERE TABLE_SCHEMA = schema() AND TABLE_NAME=@tableName AND partition_name=@pNameNext);
    set @p_add1_size=(SELECT count(*) FROM  INFORMATION_SCHEMA.partitions  WHERE TABLE_SCHEMA = schema() AND TABLE_NAME=@tableName AND partition_name=@pNameNext2);
    set @p_add2_size=(SELECT count(*) FROM  INFORMATION_SCHEMA.partitions  WHERE TABLE_SCHEMA = schema() AND TABLE_NAME=@tableName AND partition_name=@pNameNext3);
    Set @s_add0=CONCAT('alter table  ',@tableName,' add partition ( partition  ',@pNameNext,' values less than (',@pValueNext,'));');
    Set @s_add1=CONCAT('alter table  ',@tableName,' add partition ( partition  ',@pNameNext2,' values less than (',@pValueNext2,'));');
    Set @s_add2=CONCAT('alter table  ',@tableName,' add partition ( partition  ',@pNameNext3,' values less than (',@pValueNext3,'));');
    Set @s_create=CONCAT('alter table  ',@tableName,'  PARTITION BY RANGE (',@columnName,')( partition p_x values less than (0),partition  ',@pName,' values less than (',@pValue,'),partition  ',@pNameNext,' values less than (',@pValueNext,'),partition  ',@pNameNext2,' values less than (',@pValueNext2,'));');
    if @p_size>0 then 
        if @p_add0_size<=0 then
         PREPARE result0 FROM @s_add0;
         EXECUTE result0;
        end if;
        if @p_add1_size<=0 then
         PREPARE result1 FROM @s_add1;
         EXECUTE result1;
        end if;
        if @p_add2_size<=0 then
         PREPARE result2 FROM @s_add2;
         EXECUTE result2;
        end if;
    else
      PREPARE result3 FROM @s_create;
      EXECUTE result3;
    end if;
END
//