将mysql查询应用于数据库中的每个表 [英] Apply mysql query to each table in a database
本文介绍了将mysql查询应用于数据库中的每个表的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
是否可以将查询应用于mysql数据库中的每个表?
is there a way to apply a query to each table in a mysql database?
类似
SELECT count(*) FROM {ALL TABLES}
-- gives the number of count(*) in each Table
和
DELETE FROM {ALL TABLES}
-- Like DELETE FROM TABLE applied on each Table
推荐答案
select sum(table_rows) as total_rows
from information_schema.tables
where table_schema = 'your_db_name'
请注意,这只是一个近似值
beware this is just an approximate value
要删除所有表的内容,您可以执行以下操作
In order to delete the contents of all your tables you can do something like this
select concat('truncate ',table_name,';')
from information_schema.tables
where table_schema = 'your_db_name'
然后运行此查询的输出.
Then run the output of this query.
更新.
这是一个存储过程,用于将truncate table
应用于特定数据库中的所有表
This is a stored procedure to apply truncate table
to all tables in a specific database
delimiter //
drop procedure if exists delete_contents //
create procedure delete_contents (in db_name varchar(100))
begin
declare finish int default 0;
declare tab varchar(100);
declare cur_tables cursor for select table_name from information_schema.tables where table_schema = db_name and table_type = 'base table';
declare continue handler for not found set finish = 1;
open cur_tables;
my_loop:loop
fetch cur_tables into tab;
if finish = 1 then
leave my_loop;
end if;
set @str = concat('truncate ', tab);
prepare stmt from @str;
execute stmt;
deallocate prepare stmt;
end loop;
close cur_tables;
end; //
delimiter ;
call delete_contents('your_db_name');
这篇关于将mysql查询应用于数据库中的每个表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文