Run MySQL queries on different databases

Question

We have a system where we have for each division a database , currently we have 20+ divisions.

So when we have to update / delete / alter / new table we have to go threw all of those database and run the queries .

Sometime people don't follow procedures (always ?) and we end up having structures that aren't updated .

I was looking into a way to lunch the same queries on all database without having to use bash or external scripts .

So here is some of the stuff that i found :

CALL FOR EACH("SELECT databases WHERE `DATABASE` LIKE 'division_%'" , ${1});

where i could enter a query in the ${1}

or this (less dynamic):

call $('{a, b}'   , 'ALTER TABLE division_${1}.caching ADD COLUMN notes VARCHAR(4096) CHARSET utf8'');

But this gives me "No database Selected"

Any idea on how to proceed with this situation ?

Answer 1

This is a solution i found and it works :

USE division_global;

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_SCHEMA LIKE 'division_%';

label1:
    LOOP
        set scName = (select schemaName from MySchemaNames limit 1);
        // The Query
        set @q = concat('TRUNCATE TABLE ', scName, '.caching');
        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
$$

Inspired by this :

Querying multiple databases at once