需求：有一系列后綴帶有序數(shù)字的表香伴，如何利用存儲(chǔ)過程循環(huán)清空表醋寝。
下面是利用了mysql存儲(chǔ)過程循環(huán)處理的代碼豆挽。
DROP PROCEDURE ABC;
DELIMITER $$
CREATE PROCEDURE ABC()
BEGIN
DECLARE a INT Default 0 ;
simple_loop: LOOP
SET a=a+1;
SET @s = CONCAT('DELETE FROM tb_name', convert(a, char));
SET @s2 = CONCAT(@s, ' WHERE id > 1000');
PREPARE stmt3 FROM @s2;
EXECUTE stmt3;
IF a=99 THEN
LEAVE simple_loop;
END IF;
END LOOP simple_loop;
END $$
call ABC()
利用了CONCAT連接字符串甲捏， 利用 PREPARE stmt3 來執(zhí)行語句
今天拿到另一個(gè)類似的需求，要把玩家100-304段的道具遍歷的加到200000, 多利用convert拼接了一下以政。
DROP PROCEDURE ADDPROP;
DELIMITER $$
CREATE PROCEDURE ADDPROP()
BEGIN
DECLARE id INT Default 80052;
DECLARE begin_pid INT Default 100;
DECLARE end_pid INT Default 304;
DECLARE val INT Default 200000;
simple_loop: LOOP
SET begin_pid = begin_pid +1;
SET @s = CONCAT("REPLACE INTO user_properties(id, pid, value) VALUES (", convert(id, char));
SET @ss = CONCAT(@s, ",", convert(begin_pid, char), ",", convert(val, char), ")");
PREPARE final FROM @ss;
EXECUTE final;
IF begin_pid = end_pid THEN
LEAVE simple_loop;
END IF;
END LOOP simple_loop;
END $$
call ADDPROP()
