如何不创建视图，动态将表名作为参数传入cursor实现分批提交删除

完全不需要创建视图，可通过临时表中转游标所需的分组数据实现：先通过动态SQL把动态表名下符合删除条件的日期分组统计结果写入会话临时表，再用静态游标读取临时表数据执行分批删除即可。 #

修正后完整代码 #

CREATE PROCEDURE `sp_datacleanup_test`(IN tab_name VARCHAR(50),
                            IN modified_col VARCHAR(20),
                            IN cleanup_window VARCHAR(30),
                            OUT recordsProcessed INT)
BEGIN
    DECLARE No_More_Rows INT DEFAULT FALSE; 
    DECLARE modified_dt varchar(50);
    DECLARE v_counter INT;
    DECLARE rowsdeleted INT DEFAULT 0;
    DECLARE commit_counter INT DEFAULT 0;

    -- 清理可能存在的残留临时表
    DROP TEMPORARY TABLE IF EXISTS temp_cleanup_data;
    -- 创建内存临时表存储待清理的分组数据
    CREATE TEMPORARY TABLE temp_cleanup_data (
        modified_dt VARCHAR(50),
        del_count INT
    ) ENGINE = MEMORY;

    -- 动态拼接SQL插入分组统计结果到临时表
    SET @insert_stmt = CONCAT(
        'INSERT INTO temp_cleanup_data ',
        'SELECT CAST(', modified_col, ' AS CHAR) AS modified_dt, COUNT(1) AS del_count ',
        'FROM ', tab_name, ' ',
        'WHERE status_flag = ''D'' AND ', modified_col, ' <= ''', cleanup_window, ''' ',
        'GROUP BY ', modified_col
    );
    PREPARE stmt FROM @insert_stmt;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

    BEGIN
        -- 游标直接读取静态临时表数据，无需动态表名
        DECLARE Sel_cur CURSOR FOR SELECT modified_dt, del_count FROM temp_cleanup_data;
        DECLARE CONTINUE HANDLER FOR NOT FOUND SET No_More_Rows = TRUE;

        OPEN Sel_cur;
        READ_LOOP:LOOP
            FETCH Sel_cur INTO modified_dt, v_counter;
            IF No_More_Rows THEN
                LEAVE READ_LOOP;
            END IF;

            -- 动态拼接删除语句执行
            SET @del_stmt = CONCAT(
                'DELETE FROM ', tab_name, ' ',
                'WHERE ', modified_col, ' = ''', modified_dt, ''' AND status_flag = ''D'''
            );
            PREPARE stmt FROM @del_stmt;
            EXECUTE stmt;
            DEALLOCATE PREPARE stmt;

            -- 计数更新
            SET commit_counter = commit_counter + v_counter;
            SET rowsdeleted = rowsdeleted + v_counter;

            -- 每删满1万条提交一次
            IF commit_counter >= 10000 THEN
                COMMIT;
                SET commit_counter = 0;
            END IF;
        END LOOP;
        CLOSE Sel_cur;
    END;

    -- 提交最后剩余未提交的删除操作
    COMMIT;
    -- 销毁临时表
    DROP TEMPORARY TABLE IF EXISTS temp_cleanup_data;
    -- 赋值输出参数
    SET recordsProcessed = rowsdeleted;
END;

注意事项 #

1. 原代码参数命名易混淆，已将原modified_date参数重命名为modified_col，明确代表传入的是日期字段名，避免和字段值混淆
2. 修正了原代码的计数逻辑错误，删除条数累加和提交计数逻辑分离，避免统计错误
3. 入参的表名、日期字段名建议增加白名单校验，避免SQL注入风险
4. 临时表为会话级别，执行结束后会自动销毁，不会产生永久冗余对象

内容的提问来源于stack exchange，提问作者Ram