mysql存储过程批量删除数据库

测试用的数据库都是以“tb_”开头的，可以先筛选查询出要删除的数据库，再遍历游标删除数据库。

delimiter //
drop procedure if exists drop_db;
CREATE PROCEDURE "drop_db"()
BEGIN
  declare dynamicsql varchar(500);
  declare tname varchar(64);
  declare flag int default 0;
  -- 打印要删除的数据库
  select t.schema_name from information_schema.schemata t where t.schema_name like 'tb_%';
  -- 定义游标
  declare tnames cursor for select t.schema_name from information_schema.schemata t where t.schema_name like 'tb_%';
  declare continue handler for not found set flag=1;
  
  open tnames;
    fetch tnames into name;
    while flag <> 1 do
          -- 拼接动态sql
      set dynamicsql=concat('DROP DATABASE IF EXISTS ', tname);
      set @executesql=dynamicsql;
      prepare preparesql from @executesql;
      excute preparesql;
      DEALLOCATE prepare preparesql;
      fetch tnames into tname;
    end while;
  close tnames;
 END
 //
 call drop_db();