MySQL存储过程，在遍历游标时执行查询语句会影响游标结果，解决方法：在遍历游标记录前对需要查询的表进行一次连表查询。

DROP PROCEDURE IF EXISTS repairMenuData;

DELIMITER //
CREATE PROCEDURE repairMenuData()
BEGIN
declare _account_id varchar(255);
declare _content varchar(255);
declare done int;
declare count int DEFAULT 0;
declare mpnewsid varchar(36);

# 定义游标
declare rs_cursor cursor for 
    select p_menu.account_id,content from p_menu    INNER JOIN p_mpnews on p_mpnews.account_id=p_menu.account_id and p_mpnews.modified_by=p_menu.content where type=0 and content is not null and content !='';
#如果不用join连接上p_mpnews这个表，下面执行红色的查询语句时，游标的结果会被改变。
#declare continue handler for not found set done=1;
declare continue handler for SQLSTATE '02000' set done=1;
select count(*) from p_menu    INNER JOIN p_mpnews on p_mpnews.account_id=p_menu.account_id and p_mpnews.modified_by=p_menu.content where type=0 and content is not null and content !='';
# 打开游标
open rs_cursor;
select count;
cursor_loop:loop
    fetch rs_cursor into _account_id,_content;
    set count = count+1;


    if done=1 then 
        leave cursor_loop;
    end if;

    select id into mpnewsid from p_mpnews where account_id=_account_id and modified_by=_content limit 1;
    # 操作数据
    
    if mpnewsid is not NULL then
        #select _account_id,_content,mpnewsid;
    UPDATE p_menu set type=2,content=concat('{"id":"',mpnewsid,'","modified_on":"1496721600","media_id":"0","articles":[]}') where type=0 and account_id=_account_id and content=_content;
    end if;
end loop cursor_loop;
close rs_cursor;
select count;

END //
DELIMITER ;
call repairMenuData();

DROP PROCEDURE IF EXISTS repairMenuData;

转载请注明：MitNick » MySQL存储过程，在遍历游标时执行查询语句会影响游标结果