如何列出来自多个MySQL数据库的表数据？

Question

我在MySQL中有几十个数据库，它们都有具有相同结构的表log。是否有简单的方法(一行或两行SQL)在所有数据库中列出来自所有这些相似表的数据？而不需要写所有的数据库名称？

谢谢你提前提供帮助。

Answer 1

系统表information_schema.TABLES保存表信息，包括拥有表的架构(数据库)。通过巧妙地与UNION ALL连接，您可以使用它来构造一个结合所有log表的GROUP_CONCAT()查询。

但是，您将使用一个,片段UNION ALL SELECT col, col2, col3..作为分隔符，而不是默认的SQL连接分隔符。结果将是一个如下所示的查询：

SELECT col, col2, col3 FROM db1.log 
UNION ALL SELECT col, col2, col3 FROM db2.log
UNION ALL SELECT col, col2, col3 FROM db3.log...

查询的效果如下：

SELECT CONCAT('SELECT col1, col2, col3 FROM ', 
  GROUP_CONCAT(CONCAT(TABLE_SCHEMA, '.log') SEPARATOR ' UNION ALL SELECT col1, col2, col3 FROM '))
FROM information_schema.TABLES
WHERE TABLE_NAME = 'log';

外部CONCAT()确保SELECT cols... FROM开始查询。GROUP_CONCAT()将所有数据库集中在一个由UNION ALL组件连接的行中，CONCAT(TABLE_SCHEMA, '.log')用log表名对动态检索的数据库名称进行限定。

使用SELECT *可能很有诱惑力，但如果表的列顺序不一致，我会避免使用它。

这将输出一个完整的SQL字符串，然后您可以在任何客户机中执行该字符串。还可以将其放在CREATE PROCEDURE的主体中，后者将创建字符串，然后在其上调用PREPARE和EXECUTE。一般来说，这采取的形式是：

SET @qry = 'SELECT CONCAT(\'SELECT col1, col2, col3 FROM \', GROUP_CONCAT(....'
PREPARE stmt FROM @qry;
EXECUTE stmt;

请记住，在这个上下文中，SQL是一个字符串，它必须是完整的单引号，您必须反斜杠转义其中的引号。

最后，一个关于标识符引用的注释。这里的代码假设您的数据库名不需要回勾引用。根据数据库的命名方式，可能需要进行CONCAT()回退，如

CONCAT('`', TABLE_SCHEMA, '`')