mysql批量删除空表_mysql 删除批量删除表

BEGIN

declare done int default false;

declare table_name_str VARCHAR(64);

declare table_rows_mum int;

DECLARE sqltext VARCHAR(2000);# 拼接sql串

DECLARE auto_sql_str VARCHAR(2000);

declare _Cur cursor for select table_name,table_rows from information_schema.`TABLES` where TABLE_SCHEMA = 'tzgame' and table_rows = 0 order by table_rows desc ;#主订单查询

declare continue HANDLER for not found set done = true;

OPEN _Cur;

FETCH _Cur INTO table_name_str,table_rows_mum;

while(not done) do

set sqltext:= CONCAT('TRUNCATE ', table_name_str,';');

set auto_sql_str:= CONCAT(' ALTER TABLE ', table_name_str, ' AUTO_INCREMENT = 1; ');

PREPARE STMT FROM sqltext;

EXECUTE STMT;

PREPARE STMT FROM auto_sql_str;

EXECUTE STMT;

fetch _Cur into table_name_str,table_rows_mum;

end while;

CLOSE _Cur;

end