mysql每天自动新建分区，删除分区和备份

1：新增分区

CREATE DEFINER=`root`@`%` PROCEDURE `proc_create_partition`(`tableName` varchar(128),`timeColName` varchar(128))
BEGIN
	DECLARE p_id int;
    DECLARE nextDate date;
    DECLARE lastDate LONG;
    DECLARE p_name varchar(16);
    
    SELECT COUNT(partition_name) INTO p_id 
    FROM INFORMATION_SCHEMA.partitions
    WHERE TABLE_NAME=tableName;
    
    IF p_id=0 THEN
        
        SELECT DATE_ADD(CURDATE(),INTERVAL 1 DAY) INTO nextDate  
        FROM DUAL;
 
        SET p_name=DATE_FORMAT(CURDATE(),%Y%m%d);
        SET @v_add=CONCAT(ALTER table ,tableName
                , PARTITION by range(TO_DAYS(
                , timeColName
                ,))(partition 
                , CONCAT(p,p_name)
                , values less than (TO_DAYS("
                , nextDate,"))));
 
   ELSE
 
        SELECT max(partition_description) des INTO lastDate 
        FROM INFORMATION_SCHEMA.partitions 
        WHERE TABLE_NAME=tableName;
 
        SELECT DATE_ADD(FROM_DAYS(lastDate),INTERVAL 1 DAY) INTO nextDate 
        FROM DUAL;
 
        SET p_name=DATE_FORMAT(FROM_DAYS(lastDate),%Y%m%d);
        SET @v_add=CONCAT(alter table ,tableName
                , add partition (partition 
                , CONCAT(p,p_name)
                , values less than (TO_DAYS("
                ,nextDate,"))));
    END IF; 
 
     PREPARE stmt from @v_add;
     EXECUTE stmt;
     DEALLOCATE PREPARE stmt;

END

2：删除分区

CREATE DEFINER=`root`@`%` PROCEDURE `proc_drop_paratition`(`tableName` varchar(128),`existDay` int)
BEGIN
    DECLARE p_id int;
    DECLARE p_name varchar(16);
 
    SELECT COUNT(partition_name) into p_id 
    FROM INFORMATION_SCHEMA.PARTITIONS 
    WHERE TABLE_NAME=tableName;
    
    IF p_id > existDay THEN 
    
      SELECT PARTITION_NAME into p_name 
      FROM INFORMATION_SCHEMA.PARTITIONS 
      WHERE TABLE_NAME=tableName
      ORDER BY partition_ordinal_position LIMIT 1;
      
      IF p_name is not null THEN 
          SET @v_add=CONCAT(alter table ,tableName, drop partition ,p_name);
          PREPARE stmt from @v_add;
          EXECUTE stmt;
          DEALLOCATE PREPARE stmt;
       END IF; 
    END IF;  
END