如何参数化含Cursor的MySQL存储过程中的源表？

核心思路 #

由于MySQL的游标无法直接绑定动态生成的查询语句，且DECLARE声明必须放在存储过程逻辑的最开头，因此需要通过临时表中转的方式实现source_table的参数化：

1. 接收传入的源表名称参数
2. 动态生成SQL，将源表中的id数据插入临时表
3. 基于临时表创建游标进行遍历
4. 执行各目标表的删除操作
5. 清理临时表

完整代码实现 #

DROP PROCEDURE IF EXISTS demo;

DELIMITER $$ 
CREATE PROCEDURE demo(IN p_source_table VARCHAR(255))
BEGIN
    -- 声明变量与游标，必须放在逻辑最开头
    DECLARE done INT DEFAULT FALSE;
    DECLARE _id VARCHAR(255) DEFAULT '';
    -- 基于临时表定义游标
    DECLARE _cursor CURSOR FOR SELECT id FROM temp_source_ids;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    -- 创建临时表存储源表id
    DROP TEMPORARY TABLE IF EXISTS temp_source_ids;
    SET @create_temp_sql = CONCAT('CREATE TEMPORARY TABLE temp_source_ids AS SELECT id FROM ', p_source_table);
    PREPARE create_stmt FROM @create_temp_sql;
    EXECUTE create_stmt;
    DEALLOCATE PREPARE create_stmt;

    -- 遍历游标执行删除
    OPEN _cursor;
    _loop: LOOP
        FETCH _cursor INTO _id;
        IF done THEN LEAVE _loop;
        END IF;
        DELETE FROM dest_table1 WHERE id = _id;
        DELETE FROM dest_table2 WHERE id = _id;
        DELETE FROM dest_table3 WHERE id = _id;
        DELETE FROM dest_table4 WHERE id = _id;
        DELETE FROM dest_table5 WHERE id = _id;
        DELETE FROM dest_table6 WHERE id = _id;
        DELETE FROM dest_table7 WHERE id = _id;
    END LOOP _loop;
    CLOSE _cursor;

    -- 清理临时表
    DROP TEMPORARY TABLE IF EXISTS temp_source_ids;
END $$ 
DELIMITER ;

-- 调用示例：传入源表名称
CALL demo('souce_table');

关键说明 #

• 临时表是会话级别的，存储过程执行完毕后会自动销毁（也可手动删除），不会污染全局表空间
• 动态SQL部分使用CONCAT拼接表名，注意传入的表名需合法，避免SQL注入风险（若表名来自不可信来源，需额外做校验）
• 原代码中调用demo('project_push_id')存在参数不匹配问题，修改后的存储过程接收一个表名参数，调用时需传入正确的源表名称

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