如何创建存储过程遍历城市表并将city_id传入订单清理存储过程？

问题背景 #

我有一个庞大的orders表，需要清理带有特定city_id的记录。已创建接受1个参数、每次删除10000条记录的存储过程purge_orders_by_city，运行效果良好。

符合条件的city_id来自以下查询：

SELECT city_id from cities where state_name in ('NY', 'TX');

目前需手动逐个为结果集中的每个city_id调用该存储过程，例如：

CALL purge_orders_by_city(12);
CALL purge_orders_by_city(16);
CALL purge_orders_by_city(28);
CALL purge_orders_by_city(39);
...

曾尝试常规批量删除语句：

DELETE from orders where city_id in (1, 2, 3, 4...) order by id limit 10000;

但执行时间随批次增加变长，单个city_id的批量删除速度更快。

需求 #

如何创建存储过程，遍历符合条件的城市表记录并将city_id传入purge_orders_by_city()？

解决方案：创建遍历游标存储过程 #

通过MySQL游标遍历目标城市ID列表，逐个调用已有的存储过程即可实现自动化处理，具体代码如下：

DELIMITER //

CREATE PROCEDURE purge_orders_by_state()
BEGIN
    -- 声明变量存储当前city_id
    DECLARE current_city_id INT;
    -- 声明游标结束标识
    DECLARE done INT DEFAULT FALSE;
    -- 定义游标，获取指定州的city_id
    DECLARE city_cursor CURSOR FOR
        SELECT city_id FROM cities WHERE state_name IN ('NY', 'TX');
    -- 设置游标遍历完毕后的处理逻辑
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    -- 打开游标
    OPEN city_cursor;
    -- 开始循环遍历
    city_loop: LOOP
        -- 读取下一个city_id到变量
        FETCH city_cursor INTO current_city_id;
        -- 若游标已到末尾，退出循环
        IF done THEN
            LEAVE city_loop;
        END IF;
        -- 调用现有存储过程处理当前城市的订单
        CALL purge_orders_by_city(current_city_id);
    END LOOP city_loop;
    -- 关闭游标
    CLOSE city_cursor;
END //

DELIMITER ;

使用步骤 #

1. 执行上述代码创建存储过程purge_orders_by_state
2. 直接调用该存储过程即可自动处理所有目标城市的订单：

CALL purge_orders_by_state();

注意事项 #

• 若目标城市数量较多，建议在业务低峰期执行该存储过程
• 可根据需求修改游标中的state_name条件，适配不同的清理范围
• 如需监控进度，可在循环中添加日志逻辑（比如插入到自定义日志表）

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