MySQL批量删除空表的存储过程

[yuanrui]

之前写了一个Oracle版删除空表存储过程和函数，现在提供一个MySQL版的。

delimiter //
drop procedure if exists sp_drop_empty_table;
create procedure sp_drop_empty_table(dbname varchar(200), tablename varchar(200))
begin
  declare done int default false;
  declare v_index int;
  declare v_result text;
  declare v_cnt int;
  declare v_table varchar(200);
  declare cur_tables cursor for select table_name from information_schema.tables where lower(table_schema) = lower(dbname) and lower(table_name) like lower(concat(tablename, '%'));
  declare continue handler for not found set done = true;
  set v_index = 0;
  set v_result = '';

  open cur_tables;

  read_loop: loop
    fetch cur_tables into v_table;
    if done then
      leave read_loop;
    end if;
    set v_index = v_index + 1;
    set @sql_text = concat('select count(*) into @cnt from ', v_table, ';');
    prepare querystmt from @sql_text;
    execute querystmt;
    deallocate prepare querystmt;
    set v_cnt = @cnt;
    
    if v_cnt = 0 then
      -- if not set foreign_key_checks=0, will be:> 1217 - cannot delete or update a parent row: a foreign key constraint fails
      set foreign_key_checks=0;
      set @sql_text = concat('drop table ', v_table, ';');
      prepare dropstmt from @sql_text;
      execute dropstmt;
      deallocate prepare dropstmt;
      set foreign_key_checks=1;
    
      set v_result = concat(v_result, char(10), concat('table:', v_table, ' is empty, drop success.'));
    else
      set v_result = concat(v_result, char(10), concat('table:', v_table, ' has records, drop fail. total count=', v_cnt));
    end if;    
  end loop;

  close cur_tables;  
  
  if v_index = 0 then
    set v_result = concat('table:', tablename, ' does not exist.');
  end if;
  
  select v_result;
end;
//
delimiter ;

备注：创建函数版本时，出现如下错误。

-- > 1336 - Dynamic SQL is not allowed in stored function or trigger

MySQL处于安全考虑，不允许在函数和触发器动态执行语句。执行删除操作使用的语句如下：

call sp_drop_empty_table('xx_db', 't_xxxx');

update at: 2023/5/30
同事反馈在mysql命令行客户端窗口执行会报脚本异常，发现主要是由分号分隔符导致出错，需要使用delimiter重新指定分隔符。

If you use the mysql client program to define a stored program containing semicolon characters, a problem arises. By default, mysql itself recognizes the semicolon as a statement delimiter, so you must redefine the delimiter temporarily to cause mysql to pass the entire stored program definition to the server.

参考链接：https://dev.mysql.com/doc/refman/5.7/en/stored-programs-defining.html