需求:创建了64张表,需要全部删除。


# 第一步:创建存储过程
CREATE PROCEDURE drop_64_table(in val_s int, in val_e int)
begin
declare i int;
set i=val_s;
while i<=val_e do

set @sql_drop_table = concat(
"DROP TABLE xxx_task_", i);
PREPARE sql_drop_table FROM @sql_drop_table;
EXECUTE sql_drop_table;

set @sql_drop_table = concat(
"DROP TABLE xxx_record_", i);
PREPARE sql_drop_table FROM @sql_drop_table;
EXECUTE sql_drop_table;

set @sql_drop_table = concat(
"DROP TABLE xxx_achievement_", i);
PREPARE sql_drop_table FROM @sql_drop_table;
EXECUTE sql_drop_table;

set @sql_drop_table = concat(
"DROP TABLE xxx_record_", i);
PREPARE sql_drop_table FROM @sql_drop_table;
EXECUTE sql_drop_table;
set i=i+1;
end while;
end

# 第二步:执行存储过程
call drop_64_table(0,63);

# 第三步:删除存储过程
DROP PROCEDURE IF EXISTS drop_64_table

 

最后的说明:某些版本对 ""  或  ‘’ 有严格要求,报错的话,注意字符串的拼接。

posted on 2021-07-23 11:58  毛会懂  阅读(70)  评论(0编辑  收藏  举报