mysql 存储过程执行while循环 Lost connection to MySQL server during query

1. 存储过程

存储过程中可以执行while循环,其实还有repeat, loop等循环,但是自己写java比较多,还是比较习惯while循环.

今天在执行while循环更新表的过程中,出现了死循环,程序已经执行了,但是跳不出循环.

 

 

 点击停止后

显示结果,但是程序没有正常结束,而是在人为的干预下才结束.

 

 

 

2.脚本

drop procedure if exists msgSolu;

CREATE PROCEDURE msgSolu ( )
begin 
        declare tableCount int default 0;
    declare tableName varchar(1000) character set utf8;
    declare sqlStr varchar(1000) character set utf8;
        declare tableNameTotal varchar(1000) character set utf8;
    declare done int default 0;
        
    -- 声明游标
    declare mc cursor for  select table_name from INFORMATION_SCHEMA.tables where table_name like 'message_info_%' and table_schema = 'eip-nvwa';
        declare continue handler for not found set done = 1;
        set tableCount =  (select count(1) from INFORMATION_SCHEMA.tables where table_name like 'message_info_%' and table_schema = 'eip-nvwa');
        set tableNameTotal = '';
    -- 打开游标
    open mc;
        
        
        WHILE done<=tableCount DO
        -- 获取结果
        fetch mc into tableName;
        IF done > tableCount-5 THEN
         select tableName;
        END IF;
         
     set done=done+1; 
         
         set @sqlStr = CONCAT('
         UPDATE ',tableName,' SET PARAM = CONCAT( ''2'' ) WHERE MSGTYPE = ''审批结果''
         ');
         PREPARE DS FROM @sqlStr;
         execute DS;
         DEALLOCATE prepare DS;
         END WHILE;
    -- 这里是为了显示获取结果
    
    -- 关闭游标
    close mc;
end 

就是根据表名统一更新表,是利用游标和while循环完成的.

 

3.解决

        declare continue handler for not found set done = 1;

存储过程里面有这句话,这句话的意思是如果游标为空则给done赋值为1,所以当游标运行到超过最大数量以后,done就重新赋值为1,while循环判断 1 <  tableCount,所以就成了死循环了.

我们直接把 done = 1  改成 done = 100 , 超过最大的 tableCount 即可跳出循环.