MySQL游标循环中如何仅执行一次插入 避免无匹配品牌误创建活动

核心调整逻辑 #

• 新增v_campaign_created标识变量，默认值为0，用于记录营销活动是否已完成创建
• 将活动ID计算、Campaign表插入逻辑迁移至游标循环内部，仅在首次匹配到品牌商品且未创建活动时执行活动插入，避免重复插入触发主键冲突
• 新增IFNULL兼容活动表为空的场景，避免活动ID计算结果为NULL
• 无匹配品牌商品时，所有插入逻辑均不会触发，符合需求

修改后完整代码 #

delimiter //
create procedure BrandNameCampaign (in brandname varchar(50))
begin
    declare v_finished int default 0;
    declare v_campaign_created int default 0;
    declare prod_id int;
    declare newcampid int;
    declare camp_brand varchar(255);
    declare camp_c cursor for
        select productid, brand
        from product
        where brand = brandname
        order by price desc limit 5;
        
    declare continue handler for not found set v_finished = 1;
    
    -- 打开游标遍历商品
    open camp_c;
    repeat
        fetch camp_c into prod_id, camp_brand;
        if not (v_finished = 1) then
            -- 首次匹配到商品时才创建营销活动
            if v_campaign_created = 0 then
                SELECT IFNULL(MAX(CampaignID), 0) INTO newcampid FROM campaign;
                set newcampid = 1 + newcampid;    
                insert into `Campaign`(`CampaignID`,`CampaignStartDate`,`CampaignEndDate`) values 
                (newcampid,date_add(curdate(), interval 4 week), date_add(curdate(), interval 8 week));
                -- 标记活动已创建，后续循环不再重复执行插入
                set v_campaign_created = 1;
            end if;
            -- 插入对应商品的折扣规则
            insert into discountdetails values (prod_id, newcampid, 'S', 20);
            insert into discountdetails values (prod_id, newcampid, 'G', 30);
            insert into discountdetails values (prod_id, newcampid, 'P', 40);
        end if;
    until v_finished
    end repeat;
    close camp_c;
end//
delimiter ;

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