MySql:查询多个相同的动态表 [英] MySql: Query multiple identical dynamic tables

问题描述

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

I have a database with 500+ tables, each with identical structure, that contain historical data from sensors. I am trying to come up with a query that will locate, for example, all instances where sensor n exceeds x. The problem is that the tables are dynamic, the query must be able to dynamically obtain the list of tables.

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

I can query information_schema.tables to get a list of the tables, like so:

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

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

I can use this to create a loop in the program and then query the database repeatedly, however it seems like there should be a way to have MySql do the multiple table search.

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

I have not been able to make a stored procedure that works, but the examples I can find are generally for searching for a string in any column. I want to specifically find data in a specific column that exists in all tables. I admit I do not understand how to properly use stored procedures nor if they are the appropriate solution to this problem.

循环内的示例查询是:

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;

这会导致错误:每个派生表都必须有自己的别名."

It results in an error: "Every derived table must have its own alias."

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

The goal is to have a list of all devices that have had a given sensor value occur anywhere in their log (table).

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

Ultimately, should I just loop in my program once I've obtained a list of tables, or is there a query structure that would be more efficient?

推荐答案

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

Ultimately to solve this issue I get results from the separate tables and do my loops and comparisons in code. Later, it was made more efficient to combine all the sensor data in one table and optimize said table with indexes, which previously had been missing.

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

The moral of the story is that proper table structure is key to eliminating a lot of coding headaches!

这篇关于MySql:查询多个相同的动态表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！