mysql - repetir - optimización de consultas bases datos

Consultando múltiples bases de datos a la vez

Question

Tengo instancias de WordPress con cada una en su propia base de datos. Para una actualización necesito consultar todos los complementos activos, que están almacenados en una tabla ''wp_options'' y accesibles a través de

WHERE option_name=''active_plugins''

¿Cómo puedo acceder a todas las configuraciones de los complementos activos (repartidas en múltiples bases de datos) y darles salida en un único resultado de SQL? Conozco la sintaxis database.tablename , pero ¿cómo puedo continuar desde allí con la declaración Where anterior?

Una solicitud en una única base de datos se vería así:

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

Answer 1

La respuesta de Gruber funciona muy bien, pero tiene un error de sintaxis --- hay una coma espuria al final de la línea 10. Aquí está el código, con el error de sintaxis corregido:

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

Answer 2

La solución de Pentium10 es buena, pero su inconveniente es que debe ampliar la consulta para cada esquema que se incluirá. La siguiente solución usa una declaración preparada para producir un conjunto de resultados para todos los esquemas en su servidor MySQL que tienen la tabla wp_options . Esto debería ser más conveniente para ti.

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

Answer 3

Otro ejemplo más de consultar bases de datos múltiples usando procedimiento, cursor, unión de todo y estado preparado. No requiere eliminar y eliminar permisos:

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

Answer 4

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"