MySql: Query multiple identical dynamic tables.
- by JYelton
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.
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';
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.
An example query inside the loop would be:
SELECT device_name, sensor_value
FROM device_table
WHERE sensor_value > 10;
Trying the following does not work:
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?
