DELIMITER $$
#该表所在数据库名称
USE `dataxweb`$$
DROP PROCEDURE IF EXISTS `create_partition_by_day`$$
CREATE PROCEDURE `create_partition_by_day`(IN_SCHEMANAME VARCHAR(64), IN_TABLENAME VARCHAR(64))
BEGIN
    #当前日期存在的分区的个数
    DECLARE ROWS_CNT INT UNSIGNED;
    #目前日期，为当前日期的后一天
    DECLARE TARGET_DATE TIMESTAMP;
    #分区的名称，格式为p20180620
    DECLARE PARTITIONNAME VARCHAR(9);
    #当前分区名称的分区值上限，即为 PARTITIONNAME + 1
    DECLARE PARTITION_ADD_DAY datetime(0);
    SET TARGET_DATE = NOW() + INTERVAL 1 DAY;
    SET PARTITIONNAME = DATE_FORMAT( TARGET_DATE, 'p%Y%m%d' );
    SET TARGET_DATE = TARGET_DATE + INTERVAL 1 DAY;
    SET PARTITION_ADD_DAY = DATE(NOW() + INTERVAL 2 DAY);
    SELECT COUNT(*) INTO ROWS_CNT FROM information_schema.partitions
    WHERE table_schema = IN_SCHEMANAME AND table_name = IN_TABLENAME AND partition_name = PARTITIONNAME;
    IF ROWS_CNT = 0 THEN
        SET @SQL = CONCAT( 'ALTER TABLE `', IN_SCHEMANAME, '`.`', IN_TABLENAME, '`',
        ' ADD PARTITION (PARTITION ', PARTITIONNAME, " VALUES LESS THAN (
            '",PARTITION_ADD_DAY,"') ENGINE = InnoDB);" );
        PREPARE STMT FROM @SQL;
        EXECUTE STMT;
        DEALLOCATE PREPARE STMT;
     ELSE
       SELECT CONCAT("partition `", PARTITIONNAME, "` for table `",IN_SCHEMANAME, ".", IN_TABLENAME, "` already exists") AS result;
     END IF;
END$$
DELIMITER ;

DELIMITER $$
USE `dataxweb`$$
alter EVENT IF NOT EXISTS `daily_generate_partition`
ON SCHEDULE EVERY 1 DAY   #执行周期，还有天、月等等
STARTS '2022-12-03 16:59:00'
ON COMPLETION PRESERVE
ENABLE
COMMENT 'Creating partitions'
DO BEGIN
    #调用刚才创建的存储过程，第一个参数是数据库名称，第二个参数是表名称
    CALL create_partition_by_day('dataxweb','job_log');
 END$$
DELIMITER ;


 
 DELIMITER $$
USE `dataxweb`$$
alter EVENT  `daily_generate_partition`
 ON SCHEDULE    EVERY 1 DAY
 STARTS '2025-02-28'
ENABLE
COMMENT 'Creating partitions'
DO BEGIN
    CALL create_partition_by_day('dataxweb','job_log');
 END$$
DELIMITER ;


SHOW WARNINGS\G

查看最后执行时间

SELECT * FROM information_schema.EVENTS\G;