如何在MYSQL下所有指定数据库名下执行SQL

mysql下用户库比较多，都有统一的命名格式，希望在这些所有用户库执行脚本，更新数据，或者查询数据

可以采用以下存储过程实现

DROP PROCEDURE IF EXISTS `sp_execalldb`;

CREATE PROCEDURE `sp_execalldb`(p_sql varchar(4000),@p_dbname varchar(128))
BEGIN
drop temporary TABLE if EXISTS `temp_dblist`;
CREATE temporary TABLE `temp_dblist` (
`objid` tinyint NULL AUTO_INCREMENT ,
`dbname` varchar(50) NULL ,
PRIMARY KEY (`objid`)
)
;

insert into temp_dblist(dbname)
select SCHEMA_NAME from information_schema.SCHEMATA where SCHEMA_NAME like concat(@p_dbname,'%');

select count(*) into @rows from temp_dblist;

/*
call wfp.sp_execalldb('select max(EQ0722) from ?.wfpuser_t0404 ;')
*/
set @i=1;
WHILE @i <=@rows DO

-- select dbname from temp_dblist where objid=@i;
select REPLACE( p_sql,'?',dbname) into @tsql from temp_dblist where objid=@i ;
-- select @tsql;
prepare stmt from @tsql;
execute stmt;

set @i=@i+1;

END WHILE;

END;

 

调用示例

call sp_execalldb('select * from t','edb_a');