自己改,查表列表的列成你自己的,游标中执行的 SQL 语句换成你自己的 use db1; delimiter $$ create procedure p() begin declare _v_Table varchar(100); declare _v_cur boolean default true; declare cur cursor for select concat(table_schema, '.', table_name) from information_schema.tables where table_schema='mysql' limit 10; declare exit handler for not found set _v_cur=false; open cur; while _v_cur do fetch cur into _v_table; set @sql=concat('select count(*) from ', _v_table); prepare st from @sql; execute st; deallocate prepare st; end while; close cur; end$$ delimiter ; call p(); drop procedure if exists p;
可以用动态语句,只要表的结构都一样,而只是表的名称不同,就可以写个存储过程,里面用动态语句来实现的,每次用一个表名够成一个sql,然后执行就可以了
这就很尴尬了,我标签里都有写,用肯定知道要用,关键是不知道怎么用,以前只写过一个简单的游标,来这问就是希望有个详细点的回答
use db1;
delimiter $$
create procedure p()
begin
declare _v_Table varchar(100);
declare _v_cur boolean default true;
declare cur cursor for
select concat(table_schema, '.', table_name)
from information_schema.tables
where table_schema='mysql' limit 10;
declare exit handler for not found set _v_cur=false;
open cur;
while _v_cur do
fetch cur into _v_table;
set @sql=concat('select count(*) from ', _v_table);
prepare st from @sql;
execute st;
deallocate prepare st;
end while;
close cur;
end$$
delimiter ;
call p();
drop procedure if exists p;