MySql:查询多个相同的动态表

Question

我有一个包含 500 多个表的数据库，每个表都具有相同的结构，其中包含来自传感器的历史数据。我正在尝试提出一个查询，该查询将定位例如传感器 n 超过 x 的所有实例。问题是表是动态的，查询必须能够动态获取表的列表。

我可以查询 information_schema.tables 来获取表的列表，如下所示:

SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'database_name';

我可以用它在程序中创建一个循环，然后重复查询数据库，但似乎应该有一种方法让 MySql 进行多表搜索。

我无法创建一个有效的存储过程，但我能找到的示例通常用于在任何列中搜索字符串。我想专门查找所有表中存在的特定列中的数据。我承认我不明白如何正确使用存储过程，也不知道它们是否是解决此问题的合适方法。

循环内的示例查询是:

SELECT device_name, sensor_value
FROM device_table
WHERE sensor_value > 10;

尝试以下方法无效:

SELECT device_name, sensor_value
FROM
    (
    SELECT table_name FROM information_schema.tables WHERE table_schema = 'database_name'
    )
WHERE sensor_value > 10;

它会导致错误:“每个派生表都必须有自己的别名。”

目标是在其日志(表格)的任何位置列出具有给定传感器值的所有设备。

最终，我是应该在获得表列表后就在我的程序中循环，还是有更高效的查询结构？

Answer 1

您可以像这样循环遍历所有表以动态创建单个查询:

SELECT device_name, sensor_value FROM device_table WHERE sensor_value > 10
UNION
SELECT device_name, sensor_value FROM device_table2 WHERE sensor_value > 10
UNION
SELECT device_name, sensor_value FROM device_table3 WHERE sensor_value > 10;