是否可以仅使用mysql命令环境对mysql数据库中的每个数据库执行查询并求和或合并结果? [英] Is it possible to execute a query for each database in mysql databases and sum or combine results using only mysql command environment?

问题描述

我在mysql 5.1.41中有以下查询:

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;

使得算法本质上是:

对于数据库中的每个数据库，计算疯狂的狗的数量，并对所有数据库的总数进行求和.但是，我不知道如何包装两个查询以从第一个查询中获取数据库名称，作为可迭代输入到mysql中的第二个查询中.

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.

有什么想法吗?

谢谢！

推荐答案

类似的事情可能会发生.我还没有很多带有日志的狗"来对此进行测试，但是我尝试了一个经过稍微编辑的版本，并且基本的想法似乎行得通.

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;

这篇关于是否可以仅使用mysql命令环境对mysql数据库中的每个数据库执行查询并求和或合并结果?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！