mysql - 如何对数据库中的多个表运行相同的查询

Question

在运行以下查询时 MYSQL 提示:表 'DB.tableName' 不存在。

CREATE PROCEDURE CountSignatures()
  BEGIN
     DECLARE done INT DEFAULT FALSE;
     DECLARE signatureCount INT;
     DECLARE tableName CHAR(100);
     DECLARE tableList CURSOR FOR Select table_name from information_schema.tables where table_name like "%FAULT_20150320%";
     DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
     SET signatureCount = 1;
     OPEN tableList;
     tableListLoop: LOOP
       SET done = FALSE ;
       FETCH tableList INTO tableName;
       IF done THEN
         LEAVE tableListLoop;
       END IF;

     **Select count(distinct signature) from tableName;**

     END LOOP;
     CLOSE tableList;
  END$$

如果我使用以下查询，则 tableName 变量值会正确打印:

CREATE PROCEDURE CountSignatures()
  BEGIN
     DECLARE done INT DEFAULT FALSE;
     DECLARE signatureCount INT;
     DECLARE tableName CHAR(100);
     DECLARE tableList CURSOR FOR Select table_name from information_schema.tables where table_schema="LogData" and table_name like "%FAULT_20150320%";
     DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
     SET signatureCount = 1;
     OPEN tableList;
     tableListLoop: LOOP
       SET done = FALSE ;
       FETCH tableList INTO tableName;
       IF done THEN
         LEAVE tableListLoop;
       END IF;

       **Select tableName;**

     END LOOP;
     CLOSE tableList;
  END$$

Answer 1

SELECT 语句的 FROM 部分必须有实际的表名，而不是包含名称的 CHAR(100) 变量 table 。它只是不能像这样工作。

您似乎想对数据库中具有相似结构的许多表运行特定查询。通常这意味着可以改进数据库模式。但是，如果你必须处理你拥有的东西，你将不得不使用 dynamic SQL . MySQL 文档的此链接有一个示例“演示了如何通过将表的名称存储为用户变量来选择在运行时执行查询的表”，这正是您所需要的。

在您的循环中，您需要使用 SQL 查询构建一个字符串并使用 EXECUTE。

SET @s = CONCAT('select count(distinct signature) from ', tableName);

PREPARE stmt FROM @s;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

据我所知，EXECUTE 的结果被发送到存储过程的调用者，就好像它是一个普通的 SELECT，所以在这个例子中如果您的数据库有多个表where table_name like "%FAULT_20150320%"，调用者将收到多个结果集。

这是另一个关于 MySQL 动态 SQL 的 SO 问题的链接 How To have Dynamic SQL in MySQL Stored Procedure一些例子。

看起来你想要这样的东西。它应该在 signatureCount 变量中汇总来自多个表的计数。

CREATE PROCEDURE CountSignatures()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE signatureCount INT;
    DECLARE tableName CHAR(100);
    DECLARE tableList CURSOR FOR SELECT table_name FROM information_schema.tables WHERE table_name LIKE "%FAULT_20150320%";
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    SET signatureCount = 0;
    OPEN tableList;
    tableListLoop: LOOP
        SET done = FALSE;
        FETCH tableList INTO tableName;
        IF done THEN
            LEAVE tableListLoop;
        END IF;

        SET @VarCount = 0;
        SET @VarSQL = CONCAT('SET @VarCount = (SELECT COUNT(DISTINCT signature) FROM ', tableName, ')');

        PREPARE stmt FROM @VarSQL;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;

        SET signatureCount = signatureCount + @VarCount;
    END LOOP;
    CLOSE tableList;

    SELECT signatureCount;
END$$

另一种变体，如果您需要处理的表数量不多，则动态构建一个包含循环内所有表的大型 SQL 语句，然后一次性EXECUTE:

SELECT 
(COUNT(DISTINCT signature) FROM Table1) +
(COUNT(DISTINCT signature) FROM Table2) +
...
(COUNT(DISTINCT signature) FROM TableN) AS TotalCount