Querying multiple databases at once

Question

I have WordPress instances with each in its own database. For an update I need to query all active plugins, that are stored in a table 'wp_options' and accessible via

WHERE option_name='active_plugins'

How can I access all active plugin settings (spread over multiple databases) and output them in one single SQL result? I know the database.tablename syntax, but how do I go on from there with the above Where statement?

A request in a single database would look like this:

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

score 57 · Accepted Answer · answered Jan 25 '10 at 13:48

57

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"

answered Jan 25 '10 at 13:48

Pentium10

204,586
122
423
502

If you have multiple database and tables, is it possible to place it inside a loop or a function to avoid repetitive work. – Dexter Jun 28 '18 at 13:48
This does not work if you have different user/password for each database no? – рüффп Oct 30 '20 at 12:53

score 12 · Answer 2 · edited Apr 22 '17 at 12:53

The solution by Pentium10 is good but its drawback is that you have to extend the query for every schema to be included. The below solution uses a prepared statement to produce a result set for all schemas on your MySQL server which have the wp_options table. This should be more convenient for you.

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();

This is a great start to a reusable solution. But remember to wrap this with the delimiter clauses or it will likely fail, esp. in MySQL WorkBench SQL Editor — Joey T, Feb 25 '13 at 07:06
@Joey T: Thanks, I added delimiter clauses. As a general note, I try avoiding single-tenant setups because of the problems related to schema synching and querying. If one is stuck with such a setup, I have found that using prepared statements is a great way of remedying the querying problems. With this technique, you essentially get queries that emulate a multi-tenant setup. — Gruber, Feb 25 '13 at 09:49

score 8 · Answer 3 · answered Aug 21 '14 at 20:11

Gruber's answer works great, but it has a syntax error --- there's a spurious comma at the end of line 10. Here is the code, with syntax error fixed:

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

Welcome to Stack Overflow! It would be better to edit the existing answer than post a new one. Do you have edit privileges yet? (I know that they people who are new to the site can be in a catch-22 that way for a while... "The correct way to do this is actually this...and you don't have rep for that.") If you have enough rep, you should edit the answer and delete this one. — Keith Pinson, Aug 21 '14 at 20:36
Thanks so much for the reply to this. Yes - that's exactly right; I had a catch-22 where I wasn't able to add a comment to that post, or to edit the original answer --- so there wasn't any way to alert people to this other than to create a new answer. Everything I tried was just deleted --- so, thanks for not deleting this one; and I'll come back and make this a proper answer as soon as I have enough reputation to do that! — Tom Kerswill, Aug 18 '15 at 15:25

score 3 · Answer 4 · answered Mar 19 '14 at 08:32

Yet another example of querying multiple databases using procedure, cursor, union all and prepared statement. Does not require drop and delete permissions:

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');

Querying multiple databases at once

4 Answers4

Linked

Related