is there a way to apply a query to each table in a mysql database?
有没有办法将查询应用于mysql数据库中的每个表?
Something like
就像是
SELECT count(*) FROM {ALL TABLES}
-- gives the number of count(*) in each Table
and
和
DELETE FROM {ALL TABLES}
-- Like DELETE FROM TABLE applied on each Table
2 个解决方案
#1
11
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.
然后运行此查询的输出。
UPDATE.
UPDATE。
This is a stored procedure to apply truncate table
to all tables in a specific database
这是将truncate table应用于特定数据库中的所有表的存储过程
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');
#2
0
if tables are related by any field you can use alias of tables like
如果表与任何字段相关,则可以使用表的别名
select count(*) from table1 tb1, table2 tb2, table3 tb3 where
tb1.field1 = tb2.field2 and tb2.field2 = tb3.field3
similary,
与之相似,
delete from table1 tb1, table2 tb2, table3 tb3 where
tb1.field1 = tb2.field2 and tb2.field2 = tb3.field3
you can include conditions as per you requirements.
您可以根据您的要求包括条件。
If tables have no relation then use below
如果表没有关系,请在下面使用
SELECT
(SELECT COUNT(*) FROM table1 WHERE someCondition) as count1,
(SELECT COUNT(*) FROM table2 WHERE someCondition) as count2,
(SELECT COUNT(*) FROM table3 WHERE someCondition) as count3
you can remove where clause if there is no conditions.
如果没有条件,你可以删除where子句。
OUTPUT:
OUTPUT:
|count1 | count2 | count3|
| count1 | count2 |共3个记录|
|50 |36 |21 |
| 50 | 36 | 21 |
#1
11
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.
然后运行此查询的输出。
UPDATE.
UPDATE。
This is a stored procedure to apply truncate table
to all tables in a specific database
这是将truncate table应用于特定数据库中的所有表的存储过程
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');
#2
0
if tables are related by any field you can use alias of tables like
如果表与任何字段相关,则可以使用表的别名
select count(*) from table1 tb1, table2 tb2, table3 tb3 where
tb1.field1 = tb2.field2 and tb2.field2 = tb3.field3
similary,
与之相似,
delete from table1 tb1, table2 tb2, table3 tb3 where
tb1.field1 = tb2.field2 and tb2.field2 = tb3.field3
you can include conditions as per you requirements.
您可以根据您的要求包括条件。
If tables have no relation then use below
如果表没有关系,请在下面使用
SELECT
(SELECT COUNT(*) FROM table1 WHERE someCondition) as count1,
(SELECT COUNT(*) FROM table2 WHERE someCondition) as count2,
(SELECT COUNT(*) FROM table3 WHERE someCondition) as count3
you can remove where clause if there is no conditions.
如果没有条件,你可以删除where子句。
OUTPUT:
OUTPUT:
|count1 | count2 | count3|
| count1 | count2 |共3个记录|
|50 |36 |21 |
| 50 | 36 | 21 |