MySQL从INFORMATION_SCHEMA中检索到的一组表中进行选择

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:

等等。所有的表都具有相同的结构，我想做的是一次从所有表中选择东西。我不能使用合并表，因为我正在运行InnoDB。无论如何，我使用以下语句从我的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.

我想把所有的桌子都退回去。我还可以group_concat返回的结果，以获得以逗号作为分隔符的列表。现在我陷入的困境是运行一个select查询，它实际上会从这些表中检索数据。

Any hints are appreciated. Thanks! ~ K

任何暗示感激。谢谢!~ K

1 个解决方案

#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.

您必须使用动态SQL来构建查询(或查询)，以从这些表中获得结果。也就是说，您将information_schema查询中的表作为字符串返回，然后将这些字符串插入到进一步的查询中。

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.

但是我质疑你每天创建一个单独的表格的设计。乍一看，这似乎是一件方便的事情，但表的传播失控了，最终您在执行普通查询时遇到了困难，就像您现在面临的困难一样。这是一个反模式，我称之为元数据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.

不能在SQL查询(包括视图定义)中动态地设置表名。可以将表名插入到字符串中，然后将字符串准备为SQL查询。

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.

您可以使用CONCAT()函数构建一个字符串，然后使用PREPARE和EXECUTE作为查询运行该字符串。但是由于mysql客户机中没有循环结构，所以如果不能用Python或PHP或其他宿主语言编写脚本，就必须编写一个存储过程。

#1