Querying multiple MySQL tables

What is the best thing to approach somethi开发者_如何学Cng like:

select * from (show tables like "T_DATA___") // Invalid

There are over 600 tables with the name T_DATAxy where x and y are letters

Something went seriously wrong with this design. Accessing 600 tables at once means accessing as much as 1800 files on disk. You should've partitioned this data instead.

As far as th question goes, Im afraid that you will need to use a stored procedure or external application, to build a multiple UNION query statement. Still, I seem to remember that there's a limit of 32 tables merged in a UNION.

You could get the list of tables whose data you want (show tables like __) and then use mysql dump, passing in that list.

http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html

If you are determined to get it from SQL queries, you could generate appropriate sql queries using macros and execute them all at once. e.g. get the list of tables, replace newline with "; (newline) select * from ", execute all queries. (The emacs mysql mode makes this super easy).

As the other commenter says, you won't be able to do it in a single query due to #-table limits.