mysql批量删除分区

-- 假设分区键为`sale_date`，并且分区名称格式为`pYYYYMM`
DELIMITER //

CREATE PROCEDURE delete_old_partitions()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE partition_name VARCHAR(255);
    DECLARE cur_date DATE;
    SET cur_date = CURDATE() - INTERVAL 1 YEAR;

    -- 获取需要删除的分区名称
    DECLARE cur CURSOR FOR
        SELECT DISTINCT CONCAT('p', YEAR(sale_date), LPAD(MONTH(sale_date), 2, '0'))
        FROM sales
        WHERE sale_date < cur_date;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN cur;

    read_loop: LOOP
        FETCH cur INTO partition_name;
        IF done THEN
            LEAVE read_loop;
        END IF;

        -- 删除分区
        SET @drop_stmt = CONCAT('ALTER TABLE sales DROP PARTITION ', partition_name);
        PREPARE stmt FROM @drop_stmt;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    END LOOP;

    CLOSE cur;
END //

DELIMITER ;

-- 调用存储过程
CALL delete_old_partitions();

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'sales' AND PARTITION_NAME = partition_name) THEN
    SET @drop_stmt = CONCAT('ALTER TABLE sales DROP PARTITION ', partition_name);
    PREPARE stmt FROM @drop_stmt;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END IF;

START TRANSACTION;

-- 删除分区的代码

COMMIT;