学步园

这个东西不好用简短的话描述

大概就是要根据查询结果更新数据，查询出来的数据又有很多行。

我下面的代码看起来很复杂，但是必须像下面那样,必须用PREPARE,否则update语句不起作用。

drop PROCEDURE `UPGRADE_SKILL`//
CREATE DEFINER=`root`@`%` PROCEDURE `UPGRADE_SKILL`()
BEGIN
	 DECLARE stopFlag, us_skill_id , s_id, ct int(11);
     DECLARE s_uuid, str_sql varchar(255);      
     DECLARE cursor1 CURSOR FOR 
     SELECT us.skill_id , s.id, us.uuid FROM user_skill us, dragon_fx.skill s 
	 WHERE s.old_id=us.skill_id and s.id>us.skill_id limit 1;
     
     DECLARE CONTINUE HANDLER FOR NOT FOUND set stopFlag=1;      
     SET stopFlag=0;     
     OPEN cursor1;
     SET ct =0;
     REPEAT
     		SET ct=ct+1;
            FETCH cursor1 INTO us_skill_id , s_id, s_uuid;
            SET @s_id=s_id;
            SET @s_uuid=s_uuid;
            SET @str_sql=concat('UPDATE user_skill SET `skill_id` = ? WHERE `uuid` = ?;');
            PREPARE stmt FROM @str_sql;
            EXECUTE stmt USING @s_id,@s_uuid;
     UNTIL stopFlag = 1 END REPEAT;
     select ct;
END;

//
call `UPGRADE_SKILL`//
select * from user_skill where uuid='00012bd21f614b02b42450fc8ec5e9f6'//

你可能觉得一句sql就可以搞定：UPDATE dragon.user_skill us, dragon_fx.skill s
SET us.skill_id = s.id
WHERE s.old_id=us.skill_id and s.id>us.skill_id

但执行起来，很慢，无法忍受，而且似乎根本没有起作用（困惑中）。