MySQL 遍历表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12718596/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL loop through tables
提问by JellicleCat
I want to output data from every table that contains a first_namecolumn. I put together the following procedure, but in my loop, mysql interprets the table name literally instead of evaluating the variable table_name. What's the fix?
我想从包含一first_name列的每个表中输出数据。我将以下过程放在一起，但在我的循环中，mysql 从字面上解释表名而不是评估变量table_name。有什么解决办法？
delimiter //
drop procedure if exists hunt //
create procedure hunt()
begin
DECLARE done int default false;
DECLARE table_name CHAR(255);
DECLARE cur1 cursor for SELECT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = "wholesale_production" and COLUMN_NAME LIKE "%first%" ;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
open cur1;
myloop: loop
fetch cur1 into table_name;
if done then
leave myloop;
end if;
select * from `wholesale_production`.table_name where created_at >= '2012-10-01';
end loop;
close cur1;
end //
delimiter ;
call hunt();
回答by Ross Smith II
Try this:
尝试这个：
delimiter //
drop procedure if exists hunt //
create procedure hunt()
begin
DECLARE done BOOL default false;
DECLARE tablename CHAR(255);
DECLARE cur1 cursor for SELECT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = "wholesale_production" and COLUMN_NAME LIKE "%first%" ;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
open cur1;
myloop: loop
fetch cur1 into tablename;
if done then
leave myloop;
end if;
set @sql = CONCAT('select * from `wholesale_production`.', tablename, ' where created_at >= '2012-10-01');
prepare stmt from @sql;
execute stmt;
drop prepare stmt;
end loop;
close cur1;
end //
delimiter ;
call hunt();
回答by Daniel Adenew
a little edit of the above to itertate ahtoug all the tables and select them.
对上述内容进行一些编辑以迭代 ahtoug 所有表格并选择它们。
delimiter //
drop procedure if exists hunt //
create procedure hunt()
begin
DECLARE done int default false;
DECLARE table_name CHAR(255);
DECLARE cur1 cursor for SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE table_schema ='mbu4u';
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
open cur1;
myloop: loop
fetch cur1 into table_name;
if done then
leave myloop;
end if;
set @sql = CONCAT('select * from `mbu4u`.',table_name);
prepare stmt from @sql;
execute stmt;
drop prepare stmt;
end loop;
close cur1;
end //
delimiter //
回答by Pau Dominguez
table_name is a reserved token use another variable name in "DECLARE table_name CHAR(255);"
table_name 是保留标记在“DECLARE table_name CHAR(255);”中使用另一个变量名
回答by dev24-7
in cur1 you are using TABLE_NAME there try using a real name of the table
在 cur1 中，您正在使用 TABLE_NAME 尝试使用表的真实名称
