Running the same query on multiple databases in one query (MySQL)

Question

I have multiple databases with the same structure but different unrelated names, and I want to run this query on all of them:

SELECT FROM <dbname>.`cms_users` WHERE `email` LIKE '%admin.something%'

I searched a bit in the information_schema and found a table called SCHEMATA that contains the databases' names which is ultimately what I need to run the query above and I can do so manually by just replacing the database name myself and creating the query. However I want to do it automatically using a mysql loop but I'm not very certain how can I do this and how can I concatenate the database name to the query and run it.

My pseudo code for this would be as follows:

array dbnames= select `SCHEMA_NAME` from `information_schema`.`SCHEMATA`;

loop start on dbnames
SELECT FROM dbnames[index].`cms_users` WHERE `email` LIKE '%admin.bilsi%';
loop end

Any help to put this or a better logic into mysql syntax? Thanks.

Well, there's no way to dynamically construct a query from a MySQL console, but you could build it somewhere else and run it in the console, perhaps with the queries joined by `UNION ALL`. Any reason you can't do that? — siride, Dec 17 '14 at 03:32
@siride nope, I guess I can try that, but I thought that I can get the whole thing in one query — Naguib Ihab, Dec 17 '14 at 03:53

score 13 · Accepted Answer · edited Apr 02 '20 at 08:46

SQL Server and other RDBMS products allow you do scripting in the console. You can use anything you can use in stored procedures. MySQL is unfortunately much more limited and does not allow flow control constructs outside of stored procedures and functions. That means no loops, no if-statements, no cursors. You can use variables, but only the ones that start with @.

Furthermore, if you do a loop, you'll be sending multiple result-sets back to the client. If you're just running queries from a console, this is fine. If the results are something you intend for a program to use, this may not be desirable (it may not be desirable in either case).

If you are doing this in a one-off sort of way, get a list of databases manually, and then use copy and paste to build a query using UNION ALL, like so:

SELECT FROM `first_db`.`cms_users` WHERE `email` LIKE '%admin.bilsi%'
UNION ALL
SELECT FROM `second_db`.`cms_users` WHERE `email` LIKE '%admin.bilsi%'
UNION ALL
SELECT FROM `third_db`.`cms_users` WHERE `email` LIKE '%admin.bilsi%';

If you expect the number of databases to be changing and you don't want to have to update your query, or you are sending it from a program, you can use dynamic SQL. This means building a query in a string variable and then submitting it using MySQL's prepared statement functionality.

On the console, you can use something like this (see: http://dev.mysql.com/doc/refman/5.0/en/sql-syntax-prepared-statements.html):

SELECT GROUP_CONCAT(CONCAT("SELECT FROM `", SCHEMA_NAME, "`.`cms_users` WHERE `email` LIKE '%admin.bilsi%'") SEPARATOR ' UNION ALL ')
  INTO @stmt_sql 
  FROM INFORMATION_SCHEMA.SCHEMATA 
 WHERE SCHEMA_NAME NOT IN('mysql', 'test', 'tmp', 'information_schema', 'sys', 'performance_schema');
PREPARE stmt FROM @stmt_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

This generates the query I showed above using information from the INFORMATION_SCHEMA pseudo-database, namely, the list of databases by name (which MySQL incorrectly calls schemas). The rest is just the boilerplate code needed to prepare and execute a prepared statement, as per the linked documentation.

There are other ways, but they are even more tedious and won't buy you much.

That prepared statement with `GROUP_CONCAT` of a `UNION` is a work of art! Perfect for what I needed to do - thanks. — OsakaWebbie, Sep 04 '17 at 04:04
Keep in mind that if the GROUP_COCANT produces a string that's longer than 1024 characters it will get truncated. To get around this run `SET @@group_concat_max_len = value_numeric;` at the start of the query. More details in this question: https://stackoverflow.com/questions/2567000/mysql-and-group-concat-maximum-length — Tony Vlcek, Sep 23 '21 at 15:03

Running the same query on multiple databases in one query (MySQL)

1 Answers1