MySQL 删除空表 删除没有数据的表

#xybb为数据库名 有两个地方需要改
BEGIN
	 declare tab_name varchar(100);#表名
   DECLARE Done INT DEFAULT 0;
   declare tab_count int;       #数据量
   DECLARE IMP_TABLE_CUR CURSOR FOR #获取所有的表名
             (select table_name from information_schema.tables where table_schema = 'xybb'); #xybb为数据库名
   DECLARE IMP_TABLE_VALID_CUR CURSOR FOR 
            select table_name from information_schema.tables where table_schema = 'xybb'; 
   DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET Done = 1;

   
 open IMP_TABLE_CUR;
          
    FETCH next from IMP_TABLE_CUR INTO tab_name;   -- 获取游标里的值 获取表名
    repeat 
       IF NOT Done THEN
          #查询表的数据条数
          set @dropSql =concat('select ifnull((select count(*) from ', tab_name,'),0) INTO @tnum');
          PREPARE s1 from @dropSql; 
          EXECUTE s1; 
          deallocate prepare s1;
          set tab_count=@tnum;
          #为空的删除
          if tab_count=0 then 
             set @dropSql2 = concat('DROP TABLE ', tab_name);
             prepare droptab from @dropSql2;
             execute droptab;
             deallocate prepare droptab;
          end if;

       end if;
    FETCH next from IMP_TABLE_CUR INTO tab_name;
  until done end repeat;

END

 

1.打开数据库

2.点击函数然后右击

3.点击新建函数

4.点击过程

5.复制上面的存储过程粘贴

6.保存修改数据库名

7.运行

 

如果你觉得这篇内容对你挺有启发请点赞+关注