Mysql > alter table drop

use xxx_db;

delimiter $$
create procedure sp_create_table()
begin
    declare i int(3) unsigned zerofill;
    declare sqlstr varchar(2048);
    set i = 0;
    set sqlstr = "";
    while i < 1000 do
        set sqlstr = concat(
            "create table yyy_tab_00000",
            i,
            "( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `region` varchar(4) NOT NULL, `device_id` varchar(128) NOT NULL, `first_open_app_time` int(10) unsigned NOT NULL, `create_time` int(10) unsigned NOT NULL, `update_time` int(10) unsigned NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `idx_device_id` (`device_id`), KEY `idx_first_open_app_time` (`first_open_app_time`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; "
        );
        set @sqlstr = sqlstr;
        prepare stmt from @sqlstr;
        execute stmt;
        set i = i + 1;
    end while;
end$$
delimiter ;
call sp_create_table();
drop procedure sp_create_table;

-- produce batch delete statement
-- select concat('drop table ', group_concat(table_name), '; ') as statement from infomation_schema.tables where table_schema = 'xxx_db' and table_name like 'yyy_tab_00000%';
Copy the code