数据库中有多张表要做分区表（按日期分区），但是这些表用于分区的日期字段记录形式并不统一，有如下三种类型：

    PARTITION par_20151225 VALUES LESS THAN (736323) ENGINE = InnoDB
    PARTITION par_20151010 VALUES LESS THAN (1444492800) ENGINE = InnoDB
    PARTITION par_20151006 VALUES LESS THAN (1452480) ENGINE = InnoDB

    首先创建一个元数据信息表，用于记录要分区的表以及分区类型，如下：

mysql> select * from op_tb_partition;
+-------------------------------+----------+
| tb_name                       | timetype |
+-------------------------------+----------+
| NL_APP_ACTION_ERROR_TRACE     | unixtime |
| NL_APP_ACTION_SQL_TRACE       | unixtime |
| NL_APP_ACTION_TRACE           | unixtime |
| NL_MOB_APP_CRASH_DATA         | usertime |
| NL_MOB_APP_INTERACTION_TRACE  | unixtime |
| NL_NET_MOB_PAGE_DATA          | unixtime |
| NL_NET_MOB_PING_DATA          | unixtime |
| NL_NET_PAGE_DATA              | unixtime |
| NL_NET_PING_DATA              | unixtime |
| NL_MOB_APP_ERROR_TRACE        | datetime |
| NL_MOB_APP_SOCKET_ERROR_TRACE | datetime |
+-------------------------------+----------+
11 rows in set (0.00 sec)

    此外，还需创建一张日志表，用于记录存储过程执行的详细信息，如下：

mysql> desc change_partition_log;
+------------+---------------+------+-----+-------------------+-----------------------------+
| Field      | Type          | Null | Key | Default           | Extra                       |
+------------+---------------+------+-----+-------------------+-----------------------------+
| tm_base    | timestamp     | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| op_type    | varchar(20)   | YES  |     | NULL              |                             |
| change_sql | varchar(2000) | YES  |     | NULL              |                             |
+------------+---------------+------+-----+-------------------+-----------------------------+
3 rows in set (0.00 sec)

    为了是维护工作变的简单一些，希望能用一个存储过程来实现所有类型表的分区维护，因此编写了如下的存储过程：

DELIMITER $$                                                      
DROP PROCEDURE IF EXISTS lens_mobapp_trace.add_partition_original;
CREATE PROCEDURE `add_partition_original`()
BEGIN
  DECLARE table_name varchar(255) DEFAULT "";
  DECLARE tmp_sql varchar(255) DEFAULT "";
  DECLARE max_day_will int DEFAULT 0;
  DECLARE max_day_now int DEFAULT 0;
  DECLARE difference int DEFAULT 0;
  DECLARE i int DEFAULT 1;
  DECLARE tmp_day_value int DEFAULT 0;
  DECLARE tmp_day int DEFAULT 1;
  DECLARE alter_sql varchar(255) DEFAULT "";
  DECLARE par_name varchar(255) DEFAULT "";
  DECLARE done int DEFAULT - 1;
  DECLARE myCursor CURSOR FOR
  SELECT
    tb_name
  FROM op_tb_partition;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

  SET @tmp_sql = CONCAT("select to_days(date_add(now(),interval 6 day)) into @max_day_will");
  PREPARE stmt FROM @tmp_sql;
  EXECUTE stmt;


  OPEN myCursor;
myLoop:
LOOP
  FETCH myCursor INTO table_name;
  IF done = 1 THEN
    LEAVE myLoop;
  END IF;

  set @tmp_sql = concat("SELECT timetype FROM op_tb_partition WHERE tb_name = '", table_name, "' into @timetype");
  prepare stmt from @tmp_sql;
  execute stmt;
    case
    when @timetype="datetime" then
    set @tmp_sql = concat("SELECT max(substring(partition_name,5)) FROM INFORMATION_SCHEMA.partitions WHERE TABLE_NAME = '", table_name, "' and  TABLE_SCHEMA ='lens_mobapp_trace'  into @max_day_now");
    prepare stmt from @tmp_sql;
    execute stmt;


    set @tmp_sql = concat("select @max_day_will - to_days('",@max_day_now,"') into @difference");
    prepare stmt from @tmp_sql;
    execute stmt;


    while i <= @difference do
           set @tmp_sql = concat("select date_format(date_add('",@max_day_now,"' ,interval ",i," day), '%Y%m%d') into @tmp_day");
    prepare stmt from @tmp_sql;
    execute stmt;

           set @tmp_sql = concat("select to_days(date_format(date_add('",@max_day_now,"' ,interval 1+",i," day), '%Y%m%d')) into @tmp_day_value");
    prepare stmt from @tmp_sql;
    execute stmt;

    set @par_name = concat("par_",@tmp_day);
        SET @alter_sql = CONCAT("alter table ", table_name, " add partition(partition ", @par_name, " values less than (", @tmp_day_value, "))");

    insert into change_partition_log values( now(), "add_trace", @alter_sql);
    prepare stmt from @alter_sql;
    execute stmt;
set i = i+1;
    end while;
    set i = 1;
    when @timetype="usertime" then
    set @tmp_sql = concat("SELECT max(substring(partition_name,5)) FROM INFORMATION_SCHEMA.partitions WHERE TABLE_NAME = '", table_name, "' and  TABLE_SCHEMA ='lens_mobapp_trace'  into @max_day_now");
    prepare stmt from @tmp_sql;
    execute stmt;


    set @tmp_sql = concat("select @max_day_will - to_days('",@max_day_now,"') into @difference");
    prepare stmt from @tmp_sql;
    execute stmt;

    while i <= @difference do
           set @tmp_sql = concat("select date_format(date_add('",@max_day_now,"' ,interval ",i," day), '%Y%m%d') into @tmp_day");
    prepare stmt from @tmp_sql;
    execute stmt;

           set @tmp_sql = concat("select nl_to_timestamp(date_format(date_add('",@max_day_now,"' ,interval 1+",i," day), '%Y%m%d')) into @tmp_day_value");
    prepare stmt from @tmp_sql;
    execute stmt;

    set @par_name = concat("par_",@tmp_day);

        SET @alter_sql = CONCAT("alter table ", table_name, " add partition(partition ", @par_name, " values less than (", @tmp_day_value, "))");


    insert into change_partition_log values( now(),"add_trace" , @alter_sql);
    prepare stmt from @alter_sql;
    execute stmt;
    set i = i+1;
    end while;
    set i = 1;
    when @timetype="unixtime" then
    set @tmp_sql = concat("SELECT max(substring(partition_name,5)) FROM INFORMATION_SCHEMA.partitions WHERE TABLE_NAME = '", table_name, "' and  TABLE_SCHEMA ='lens_mobapp_trace'  into @max_day_now");
    prepare stmt from @tmp_sql;
    execute stmt;


    set @tmp_sql = concat("select @max_day_will - to_days('",@max_day_now,"') into @difference");
    prepare stmt from @tmp_sql;
    execute stmt;

    
    while i <= @difference do
           set @tmp_sql = concat("select date_format(date_add('",@max_day_now,"' ,interval ",i," day), '%Y%m%d') into @tmp_day");
    prepare stmt from @tmp_sql;
    execute stmt;

      set @tmp_sql = concat("select unix_timestamp(date_format(date_add('",@max_day_now,"' ,interval 1+",i," day), '%Y%m%d')) into @tmp_day_value");
    prepare stmt from @tmp_sql;
    execute stmt;

    set @par_name = concat("par_",@tmp_day);

      SET @alter_sql = CONCAT("alter table ", table_name, " add partition(partition ", @par_name, " values less than (", @tmp_day_value, "))");

    insert into change_partition_log values( now(),"add_trace" , @alter_sql);
    prepare stmt from @alter_sql;
    execute stmt;
set i = i+1;
    end while;
    set i = 1;
  end case;
END LOOP myLoop;

  CLOSE myCursor;
END$$      
DELIMITER ;

    由于业务需求，这些表的数据只需要保留3个月，再次编写删除分区的存储过程，如下：

DELIMITER $$                                                       
DROP PROCEDURE IF EXISTS lens_mobapp_trace.del_partition_original; CREATE PROCEDURE `del_partition_original`()
BEGIN
declare table_name varchar(255) default 0;
declare tmp_sql varchar(255) default "";
declare max_par_now varchar(255) default "";
declare par_del_num varchar(255) default "";
declare par_del varchar(255) default "";
declare alter_sql varchar(255) default "";
declare i int default 0;


declare done int default -1;
declare myCursor cursor for select tb_name from op_tb_partition;
declare continue handler for not found set done = 1;

open myCursor;
myLoop:LOOP
fetch myCursor into table_name;
if done = 1 then
leave myLoop;
end if;

set @tmp_sql = concat("select count(*) FROM INFORMATION_SCHEMA.partitions WHERE TABLE_NAME = '",table_name,"' and  TABLE_SCHEMA ='lens_mobapp_trace'  into @max_par_now");
prepare stmt from @tmp_sql;
execute stmt;
set @par_del_num = @max_par_now-90-7;

while i < @par_del_num do
       set @tmp_sql = concat("select partition_name from  INFORMATION_SCHEMA.partitions  where TABLE_SCHEMA ='lens_mobapp_trace' and table_name='",table_name,"' order by partition_name limit 1 into @par_del");
prepare stmt from @tmp_sql;
execute stmt;
       set @alter_sql = concat("alter table ",table_name," drop partition ",@par_del);
prepare stmt from @alter_sql;
execute stmt;

insert into change_partition_log values(now(),"drop_trace", concat(i,"--",@par_del,"--",@alter_sql));
                set i = i+1;
end while;
set i = 0;

end loop myLoop;

close myCursor;

END$$         
DELIMITER ;

    最后编写定期执行上述存储过程的调度event，如下：

DELIMITER ;;
CREATE EVENT lens_mobapp_trace.`manager_partition_original` ON SCHEDULE EVERY 1 DAY STARTS '2013-12-24 00:30:00' ON COMPLETION PRESERVE ENABLE DO BEGIN
call add_partition_original;
call del_partition_original;
END ;;
DELIMITER ;