mysql 指定执行db_如何在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');