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
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.
之前写了一个Oracle版删除空表存储过程和函数,现在提供一个MySQL版的。
备注:创建函数版本时,出现如下错误。
MySQL处于安全考虑,不允许在函数和触发器动态执行语句。执行删除操作使用的语句如下:
update at: 2023/5/30 同事反馈在mysql命令行客户端窗口执行会报脚本异常,发现主要是由分号分隔符导致出错,需要使用delimiter重新指定分隔符。
参考链接:https://dev.mysql.com/doc/refman/5.7/en/stored-programs-defining.html