一次性查询多个数据库

Question

我有几个独立的WordPress实例，每个实例都有自己的数据库。为了进行更新，我需要查询所有活动插件，这些插件存储在名为'wp_options'的表中并可通过以下方式访问:

SELECT option_value FROM wp_options WHERE option_name = 'active_plugins'

WHERE option_name='active_plugins'

我如何访问所有活动插件设置（分布在多个数据库中），并将它们输出为一个单独的SQL结果？我知道database.tablename语法，但如何在上述Where语句之后继续操作？

在单个数据库中的请求看起来像这样：

SELECT option_value
  FROM `database`.`wp_options`
 WHERE option_name="active_plugins"

- Boldewyn

Answer 1

SELECT option_value
 FROM `database1`.`wp_options`
  WHERE option_name="active_plugins"
UNION
SELECT option_value
 FROM `database2`.`wp_options`
  WHERE option_name="active_plugins"

- Pentium10

---

如果您有多个数据库和表，是否可以将它们放在循环或函数中，以避免重复工作？ - Dexter

如果每个数据库有不同的用户/密码，那么这种方法行不通，对吧？ - рüффп

Answer 2

Pentium10的解决方案很好，但它的缺点是您必须扩展查询以包含每个模式。下面的解决方案使用准备语句为MySQL服务器上具有wp_options表的所有模式生成结果集。这对您应该更方便。

DROP PROCEDURE IF EXISTS `MultipleSchemaQuery`;

DELIMITER $$

CREATE PROCEDURE `MultipleSchemaQuery`()
BEGIN
    declare scName varchar(250);
    declare q varchar(2000);

    DROP TABLE IF EXISTS ResultSet;
    create temporary table ResultSet (
     option_value varchar(200)
    );

    DROP TABLE IF EXISTS MySchemaNames;
    create temporary table MySchemaNames (
        schemaName varchar(250)
    );

    insert into MySchemaNames
    SELECT distinct
        TABLE_SCHEMA as SchemaName
    FROM 
        `information_schema`.`TABLES`  
    where 
        TABLE_NAME = 'wp_options';

label1:
    LOOP
        set scName = (select schemaName from MySchemaNames limit 1);
        set @q = concat('select option_value from ', scName, '.wp_options where option_name=\'active_plugins\'');
        PREPARE stmt1 FROM @q;
        EXECUTE stmt1;
        DEALLOCATE PREPARE stmt1;

        delete from MySchemaNames where schemaName = scName;
        IF ((select count(*) from MySchemaNames) > 0) THEN
            ITERATE label1;
        END IF;
        LEAVE label1;

    END LOOP label1;

    SELECT * FROM ResultSet;

    DROP TABLE IF EXISTS MySchemaNames;
    DROP TABLE IF EXISTS ResultSet;
END
$$

DELIMITER ;

CALL MultipleSchemaQuery();

- Gruber

---

这是一个可重用解决方案的好开始。 但请记得用分隔符子句将其包装起来，否则很可能会失败，特别是在MySQL WorkBench SQL编辑器中。 - Joey T

@Joey T：谢谢，我添加了分隔符子句。一般来说，我尽量避免使用单租户设置，因为这会涉及到模式同步和查询相关的问题。如果必须使用这种设置，我发现使用预处理语句是解决查询问题的好方法。通过这种技术，您可以获得模拟多租户设置的查询。 - Gruber

为什么需要使用表格ResultSet？ - java dev

Answer 3

Gruber的答案很好，但是它有一个语法错误——第10行末尾有一个多余的逗号。这里是修复语法错误后的代码：

DELIMITER $$

CREATE PROCEDURE `MultipleSchemaQuery`()
BEGIN
    declare scName varchar(250);
    declare q varchar(2000);

    DROP TABLE IF EXISTS ResultSet;
    create temporary table ResultSet (
     option_value varchar(200)
    );

    DROP TABLE IF EXISTS MySchemaNames;
    create temporary table MySchemaNames (
        schemaName varchar(250)
    );

    insert into MySchemaNames
    SELECT distinct
        TABLE_SCHEMA as SchemaName
    FROM 
        `information_schema`.`TABLES`  
    where 
        TABLE_NAME = 'wp_options';

label1:
    LOOP
        set scName = (select schemaName from MySchemaNames limit 1);
        set @q = concat('select option_value from ', scName, '.wp_options where option_name=\'active_plugins\'');
        PREPARE stmt1 FROM @q;
        EXECUTE stmt1;
        DEALLOCATE PREPARE stmt1;

        delete from MySchemaNames where schemaName = scName;
        IF ((select count(*) from MySchemaNames) > 0) THEN
            ITERATE label1;
        END IF;
        LEAVE label1;

    END LOOP label1;

    SELECT * FROM ResultSet;

    DROP TABLE IF EXISTS MySchemaNames;
    DROP TABLE IF EXISTS ResultSet;
END
$$

- Tom Kerswill

---

欢迎来到 Stack Overflow！修改已有的答案比发布新的答案更好。您是否已经具备编辑特权？（我知道对于新来的用户来说有时候会陷入进退两难的境地......“正确的做法实际上是这样的......而你没有足够的声望来这么做。”）如果您拥有足够的声望，应该编辑答案并删除此答案。 - Keith Pinson

非常感谢您对此的回复。是的，完全正确；我陷入了一个“进退两难”的境地，在那篇帖子上我无法添加评论或编辑原始答案 --- 所以除了创建新答案外没有其他方式来提醒人们。我尝试的所有方法都被删除了 --- 因此，感谢您没有删除这个答案；一旦我有足够的声望，我会回来把它改成一个合适的答案！ - Tom Kerswill

Answer 4

以下是使用存储过程、游标、union all 和预处理语句查询多个数据库的另一个示例。不需要删除和清除权限：

USE `my_db`;
DROP PROCEDURE IF EXISTS `CountAll`;
DELIMITER $$

CREATE PROCEDURE `CountAll`(IN tableName VARCHAR(255))
BEGIN

    DECLARE db_name         VARCHAR(250);
    DECLARE exit_loop       BOOLEAN;
    DECLARE union_query     TEXT DEFAULT '';

    DECLARE my_databases CURSOR FOR 
        SELECT DISTINCT `table_schema`
        FROM `information_schema`.`tables`
        WHERE 
            `table_schema` LIKE 'myprefix\_%' AND
            `table_name` = tableName;

    DECLARE CONTINUE HANDLER
        FOR NOT FOUND SET exit_loop = TRUE;

    OPEN my_databases;

    get_database: LOOP

        FETCH my_databases INTO db_name;

        IF exit_loop THEN
            -- remove trailing UNION ALL statement
            SET union_query = TRIM(TRAILING ' UNION ALL ' FROM union_query);
            LEAVE get_database;
        END IF;

        SET union_query = concat(union_query, 'SELECT COUNT(*) AS qty FROM ', 
                                     db_name, '.', tableName, ' UNION ALL ');

    END LOOP get_database;

    CLOSE my_databases;

    SET @final_query = concat('SELECT SUM(qty) FROM (', union_query, 
                                                    ') AS total;');
    PREPARE stmt1 FROM @final_query;
    EXECUTE stmt1;
    DEALLOCATE PREPARE stmt1;

END$$

DELIMITER ;

CALL CountAll('wp_options');

- bancer