代码之家 › 专栏 › 技术社区 › JYelton Melchior Blausand

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

mysql-error-1248 mysql

1

JYelton Melchior Blausand · 技术社区 · 15 年前

我有一个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;

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

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

最后,我应该在获得表列表后在程序中循环,还是有一个更有效的查询结构?

3 回复 | 直到 14 年前

1

jbochi 15 年前

您可以遍历所有表以动态创建单个查询,如下所示:

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;

2

0

MindStalker 15 年前

您必须创建一个存储过程。您需要一个循环光标,它将遍历

选择表名从信息表其中table_schema='database_name';

然后,您将希望构建一个像jbochi提供的长SQL语句。

@statement=concat(@statement,“union select device_name,sensor_value from”,@table_name,“其中sensor_value>10”);

一旦构建了大型查询,您将使用

从@statement准备stmt;

执行stmt;

解除分配准备STmt;

注意:我真的不知道怎么做循环光标,对不起。

3

0

JYelton Melchior Blausand 14 年前

最终,为了解决这个问题,我从单独的表中得到结果,并在代码中进行循环和比较。之后,将所有传感器数据合并到一个表中,并用以前缺失的索引优化该表,从而提高了效率。

这个故事的寓意是,正确的表结构是消除许多编码难题的关键!