MySQL遍历表 [英] MySQL loop through tables

问题描述

我想从每个包含first_name列的表中输出数据.我将以下过程放在一起，但是在我的循环中，mysql从字面上解释表名，而不是对变量table_name求值.解决办法是什么?

I want to output data from every table that contains a first_name column. 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?

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();

推荐答案

尝试一下:

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();

这篇关于MySQL遍历表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！