如何对数据库中的多个表运行相同的查询 [英] How to run the same query against multiple tables in the database

问题描述

在运行以下查询MYSQL时，抱怨:表'DB.tableName'不存在.

While running following query MYSQL complains that : Table 'DB.tableName' doesn't exist.

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变量值会正确打印:

While if I use the following query then tableName variable value is printed correctly :

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$$

推荐答案

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

The FROM part of the SELECT statement has to have actual table names, not a CHAR(100) variable that contains the name of the table. It just doesn't work like this.

您似乎想对数据库中具有相似结构的许多表运行特定查询.通常，这意味着可以改进数据库架构.但是，如果您必须处理现有的内容，则必须使用动态SQL .到MySQL文档的链接有一个示例该示例正是您所需要的，它通过将表名存储为用户变量来演示如何选择要在运行时在其上执行查询的表".

It looks like you want to run a particular query against many tables with similar structure in your database. Quite often it means that the database schema could be improved. But, if you have to deal with what you have you'll have to use dynamic SQL. This link to MySQL documentation has an example "that demonstrates how to choose the table on which to perform a query at runtime, by storing the name of the table as a user variable", which is exactly what you need.

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

Inside your loop you need to build a string with the SQL query and use 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%".

As far as I understand, the result of the EXECUTE is sent to the caller of the stored procedure as if it was a normal SELECT, so in this example the caller will receive multiple result sets if your database has more than one table where table_name like "%FAULT_20150320%".

此处是指向有关MySQL动态SQL 如何在MySQL存储过程中使用动态SQL ，并提供一些示例.

Here is a link to another SO question about MySQL dynamic SQL How To have Dynamic SQL in MySQL Stored Procedure with some examples.

您似乎想要这样的东西.它应该汇总signatureCount变量中几个表的计数.

It looks like you want something like this. It should sum up counts from several tables in signatureCount variable.

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.

Another variant, if the number of tables that you need to process is not much, is to build dynamically one big SQL statement that includes all tables inside your loop and then EXECUTE it in one go:

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

这篇关于如何对数据库中的多个表运行相同的查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！