MySQL SELECT from a group of tables retrieved from INFORMATION_SCHEMA

Question

Hey all. I've got the following set of tables which is variable and adds up every day:

data-2010-10-10
data-2010-10-11
data-2010-10-12
data-2010-10-13

And so on. All the tables have the same structure and what I'd like to do is select stuff from all tables at once. I'm unable to use a MERGE table since I'm running InnoDB. Anyways, I'm using the following statement to select the table names from my information_schema:

select table_name from `information_schema`.`tables` 
  where `table_schema` = 'mydb2' and `table_name` like 'data-%'

Which return all the tables I'd like to unite. I can also group_concat the returned results to get the listed with a comma as a separator. Now where I'm stuck is running a select query that would actually retrieve the data from these tables.

Any hints are appreciated. Thanks! ~ K

Answer 1

You would have to use dynamic SQL to build the query (or queries) to get results from these tables. That is, you get the tables back from your information_schema query as strings, then you interpolate these strings into a further query.

Something involving UNION of individual queries against all the tables:

SELECT ...
FROM (SELECT * FROM `data-2010-10-10`
      UNION ALL SELECT * FROM `data-2010-10-11`
      UNION ALL SELECT * FROM `data-2010-10-12`
      UNION ALL SELECT * FROM `data-2010-10-13`) AS u

But I question your design of creating a separate table per day. It seems like a convenient thing to do at first, but the tables propagate out of control, and you end up having difficulty doing ordinary queries, like the difficulty you are facing now. This is an antipattern I call Metadata Tribbles.

It's likely that your data could be stored in a single table, with a date column to let you distinguish data from different days. You probably just need to create indexes to assist the queries you need to run against the total data.

---

Re your comment:

You can't make table names dynamic in an SQL query (including a view definition). You can interpolate table names into a string, and then prepare the string as an SQL query.

You can build a string with the CONCAT() function, and then use PREPARE and EXECUTE to run the string as a query. But since there are no loop structures in the mysql client, you'd have to write a stored procedure to do this if you can't write a script in Python or PHP or some such host language.