mysql - 从表名中具有唯一ID的多个表中选择信息

Question

场景如下:我将表存储在我的监控应用程序数据库中，使用 ID 作为表名的一部分。例如，对于 10 个受监控的设备，设备日志存储在每个设备的表中，设备 ID 作为名称的一部分，如下所示:

Device ID      Table Name
1              device_logs.log_1
2              device_logs.log_2

我希望能够:

select * from all device log tables where ID IN (a-list-of-IDs)

我经常引用这类信息，在快速查询和可能的报告中这样做会更容易。对于一些小的设备列表，联合查询有效，但在大约 4-5 个设备之后，它变得太长了。以编程方式，我可以在 python 中使用字符串替换来执行此操作，但如何在 MySQL 中作为查询执行此操作？

添加一个代码段我正在尝试开始工作，但在语法上遇到困难:

    drop table if exists tmp_logs;
create temporary table tmp_logs
(
device_name varchar(30) not null,
date datetime,
message varchar (255)
)
engine=innodb;
drop procedure if exists load_tmp_log_data;

delimiter #
create procedure load_tmp_log_data()
begin

declare vid int unsigned;

  truncate table tmp_logs;
  start transaction;
  while vid in(4976,4956) do
    insert into tmp_logs values 

(           SELECT 
                dev.device,
                date,
                message

                FROM device_logs.logs_vid

                inner join master_dev.legend_device dev on dev.id=vid

                where date >= '2014-7-1'

                and message not like '%HDMI%'
                and message not like '%DVI%'
                and message not like '%SIP%'
                and message not like '%Completed%'
                and message not like '%Template%'
                and message not like '%collection%'
                and message not like '%Cache%'
                and message not like '%Disconnect%'
                and message not like '%Row removed%'
                and message not like '%detailed discovery%'
                and message not like '%restarted%'
                and message not like '%Auto Answer%'

);

  end while;
  commit;
end #

delimiter ;

call load_tmp_log_data();

select * from tmp_logs order by device_name;

Answer 1

您不能动态指定用户名作为常规 SQL 中的查询。您可以将 prepare 语句与动态 SQL 一起使用。

另一种方法是将查询设置为:

select l.*
from ((select l.*
       from device_logs.log_1 l
       where device_id = 1 and id in (list of ids)
      ) union all
      (select l.*
       from device_logs.log_2 l
       where device_id = 2 and id in (list of ids)
      ) . . . 
     ) l;

您需要在每个子查询中重复条件——这会使它们更有效率。并且，使用 union all 而不是 union 来避免重复。

但是，从根本上说，具有相同结构的表通常是数据库设计不佳的标志。拥有一个包含指定 id 的列的表会好得多。那么您的查询将非常简单:

select l.*
from logs l
where id in (list of ids);

您可以通过更改创建表格的应用程序来生成这样的表格。或者您可以通过在每个子表上使用 insert 触发器来生成这样的表。或者，如果数据可能已过时一天左右，则运行每晚重新创建表的作业。