MySQL存储过程游标重复替换问题求助（MySQL Workbench 8.0.30）

问题根源 #

你的存储过程出现重复替换，核心原因是游标fetch失败后仍执行了更新逻辑：
当游标遍历完最后一条记录并处理完成后，下一次循环会执行fetch get_cur_ingred into c_id，此时游标已无数据，触发sqlstate '02000'的handler将d设为1，但代码没有立即判断d的值，而是继续执行后续的替换和更新操作，导致上一条记录被重复更新一次。
此外还有两处小问题：循环结束符写错成.（应为;），以及多次重复查询buffet表获取同一条数据，造成不必要的性能损耗。

修复方案 #

1. 在fetch操作后立即检查d的值，若d=1则直接跳出循环，避免执行重复更新
2. 一次性获取当前记录的id和ingredients到变量中，减少对buffet表的查询次数
3. 修正循环结束的语法错误

修复后的完整代码 #

CREATE PROCEDURE cur_snak(in name_snak varchar(20), in old_ingred text,
in new_ingred text)
BEGIN
    declare res text;
    declare d int default 0;
    declare c_id int ;
    declare current_ingred text; -- 新增变量存储当前配料
    -- 直接查询id和配料，避免多次查表
    declare get_cur_ingred cursor for select id, ingredients from buffet where `name`=name_snak; 
    declare continue handler for sqlstate '02000' set d=1;
    declare continue handler for sqlstate '23000' set d=1;

    open get_cur_ingred ;
    lbl: loop
        if d=1 then 
            leave lbl;
        end if;

        -- 一次fetch获取id和配料数据
        fetch get_cur_ingred into c_id, current_ingred ;
        -- 新增判断：fetch失败直接跳出循环
        if d=1 then 
            leave lbl;
        end if;

        -- 直接用变量执行替换逻辑
        set res = insert(current_ingred, locate(old_ingred, current_ingred), length(old_ingred), new_ingred);
        update buffet set ingredients = res where id=c_id;
    end loop; -- 修正循环结束符为分号
    close get_cur_ingred;
END

额外优化提示 #

如果你的需求是将指定名称餐品中的old_ingred全部替换为new_ingred，完全不需要游标，一条UPDATE语句就能实现，性能更优：

CREATE PROCEDURE cur_snak(in name_snak varchar(20), in old_ingred text,
in new_ingred text)
BEGIN
    update buffet
    set ingredients = replace(ingredients, old_ingred, new_ingred)
    where `name` = name_snak;
END

REPLACE函数更适合全局字符串替换场景，代码简洁且无需遍历游标，执行效率更高。

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