Is it possible to execute a query for each database in mysql databases and summarize or combine the results using only the 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 this command:

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

and then use the database names as input to the query, for example:

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

so the algorithm is essentially:

. , , , mysql.

.

?

!

Answer 1

, - . " ", , , , , .

, .

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;