从表名在数组中的多个表中删除

Question

**瓷砖可能不正确**

在不手工列出表名的情况下，我是否可以使用一个函数获取表名，然后在delete语句中使用该函数？即

DELETE FROM IN (
  SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE';
) AS T
WHERE
  T.ColumnName = 'value';

有没有一个好的，明确的方法来做这件事？我不会把桌子丢在这里的。

Answer 1

除了Ross Smith的答案之外，我还增加了一个表名look Up。

delimiter //

drop procedure if exists hunt //
create procedure hunt()
begin
    DECLARE done BOOL default false;
    DECLARE tablename CHAR(255);
    
    -- This was the only bit I've added
    DECLARE cur1 cursor for (
        SELECT TABLE_NAME
            FROM information_schema.COLUMNS 
                WHERE 
                    TABLE_SCHEMA = 'schema' 
                AND COLUMN_NAME = 'column_name'
    );
    -- End what I've added

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    open cur1;

    myloop: loop
        fetch cur1 into tablename;
        if done then
            leave myloop;
        end if;
        -- Then my delete query
        set @sql = CONCAT('DELETE FROM ', tablename, ' WHERE 'column_name' = 'value'');
        prepare stmt from @sql;
        execute stmt;
        drop prepare stmt;
    end loop;

    close cur1;
end //

delimiter ;

call hunt();