Is it possible to execute a query for each database in mysql databases and sum or combine results using only mysql command environment?

Question

I have the following query in mysql 5.1.41:

select distinct table_schema from information_schema.tables where table_schema like '%dog%';

I want to take the output of that command:

+-------------------+
| table_schema      |
+-------------------+
| dog_a             |
| dog_b             |
+-------------------+

and then use the database names as input to a query like the following:

select count(*) from (select * from dog_a.log where insane = 1 UNION ALL select * from dog_b.log where insane = 1) as total_count;

such that the algorithm is essentially:

For each database in databases, count the number of insane dogs and sum the total across all databases. However, I don't know how to wrap up the two queries to get the database names from the first query as iterable input into the second query within mysql.

I need to be able to do this entirely within the database.

Any ideas?

Thanks!

Answer 1

Something like this might do. I haven't got a bunch of 'dogs-with-logs' to test this against, but I tried a slightly edited version, and the basic idea seems to work.

Build up the query string in a variable, then use a prepared statement to execute it.

SELECT @query:=CONCAT(
      'select count(*) from ('
    , GROUP_CONCAT( CONCAT( y.prefix, x.table_schema, y.postfix ) SEPARATOR ' UNION ALL ' )
    , ') as total_count' )
FROM (
    SELECT  DISTINCT table_schema
    FROM    information_schema.tables
    WHERE   table_schema LIKE '%dog%'
    ) AS x
JOIN (
    SELECT
          'select * from '        AS prefix
        , '.log where insane = 1' AS postfix 
    ) AS y
;

-- SELECT @query AS Query;

PREPARE STMT FROM @query;
EXECUTE STMT;
DEALLOCATE PREPARE STMT;