MySQL: Select multiple tables

Question

I want to select multiple tables with MySQL. But I don't know exactly the name of them, but they have all the same structure.

For example:

table_345:
id || row_1 || row_2 || row_3

I want to have something like that:

SELECT `id` FROM table_*

Answer 1

If you are using PHP with your MySQL database you can use mysql_list_tables() to get the list of tables in your database. That list can then be filtered easily using strpos(). Once you have your list of tables, you can use SQL UNION to combine the result sets from your tables.

I hope there's a really good reason for you to choose this kind of design since it could get really ugly, really fast.

Good luck.

Answer 2

In MySQL, you can use information_schema.TABLES (table names are case sensitive in MySQL) to retrieve the list of tables. You can then construct a dynamic query and run it with EXECUTE STMT. Here's an example:

SET @query = '';

SELECT *
FROM (
    SELECT @query := CONCAT(@query,if(@query = '','',' union all '),
        'select * from ', T.`TABLE_NAME`)
    FROM information_schema.TABLES T 
    WHERE TABLE_SCHEMA = 'YourDatabaseName'
) sub
WHERE 1=0;

PREPARE STMT FROM @query;
EXECUTE STMT;

P.S. Multi-line statements like this don't work in the query browser, but they do in the command line. Save the commands in a file called sqlcommands.txt and you can run them like:

mysql -u user -p password dbname < sqlcommands.txt