mysql批量建表和批量删除表

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%';