Run MySQL queries on different databases -

Run MySQL queries on different databases -

we have scheme have each partition database , have 20+ divisions.

so when have update / delete / alter / new table have go threw of database , run queries .

sometime people don't follow procedures (always ?) , end having structures aren't updated .

i looking way lunch same queries on database without having utilize bash or external scripts .

so here of stuff found :

call each("select databases `database` 'division_%'" , ${1});

where come in query in ${1}

or (less dynamic):

call $('{a, b}' , 'alter table division_${1}.caching add together column notes varchar(4096) charset utf8'');

but gives me "no database selected"

any thought on how proceed situation ?

this solution found , 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 myschemanames select distinct table_schema schemaname `information_schema`.`tables` table_schema 'division_%'; label1: loop set scname = (select schemaname myschemanames limit 1); // query set @q = concat('truncate table ', scname, '.caching'); prepare stmt1 @q; execute stmt1; deallocate prepare stmt1; delete myschemanames schemaname = scname; if ((select count(*) myschemanames) > 0) iterate label1; end if; leave label1; end loop label1; select * resultset; drop table if exists myschemanames; drop table if exists resultset; end $$

inspired :

querying multiple databases @ once

mysql multiple-databases dynamic-queries