mysql存储过程执行动态sql

delimiter //
drop PROCEDURE IF EXISTS attentBatch;
create PROCEDURE attentBatch(userid INT,num INT)
BEGIN
DECLARE self_tab_index INT;
DECLARE done INT DEFAULT 0;
DECLARE tabindex INT;
DECLARE fansuserid INT;
DECLARE sql1 VARCHAR(500);
DECLARE sql2 VARCHAR(500);
DECLARE cur_user CURSOR FOR SELECT user_id FROM em_user limit num;-- 定义光标
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;-- 处理异常
SET self_tab_index = userid % 4;

OPEN cur_user;
REPEAT
FETCH cur_user into fansuserid;
SET tabindex = fansuserid % 4;-- 分表算法


SET sql1 = CONCAT("INSERT IGNORE INTO em_user_relation",tabindex,"(user_id,follow_id,type)","VALUES(",fansuserid,',',userid,',',1,")");
SET @sql1 = sql1;-- 注意很重要，将连成成的字符串赋值给一个变量（可以之前没有定义，但要以@开头
PREPARE stmt FROM @sql1; -- 预处理需要执行的动态SQL，其中stmt是一个变量  
EXECUTE stmt; -- 执行sql语句
DEALLOCATE PREPARE stmt;-- 释放预处理段


SET sql2 = CONCAT("INSERT IGNORE INTO em_user_relation",self_tab_index,"(user_id,follow_id,type)","VALUES(",userid,',',fansuserid,',',0,")");
SET @sql2 = sql2;
PREPARE stmt1 FROM @sql2;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;

UNTIL done END REPEAT;
SELECT sql2; -- 测试sql
CLOSE cur_user;
END
//