I'm very new to MySQL and I have a stored procedure that I'd like to add to several legacy databases. I'm using SQLyog and I would like to loop through each database on the connection and if it matches 'application_%' (databases are called application_clientName
, there are dozens) to run the stored procedure.
我对MySQL非常陌生,我有一个存储过程,我想将它添加到几个遗留数据库中。我正在使用SQLyog,我希望循环遍历连接上的每个数据库,如果它匹配“application_%”(数据库称为application_clientName,有许多)来运行存储过程。
A script I can save and run through SQLyog would be ideal.
我可以保存并运行SQLyog的脚本是理想的。
I'm kind of looking to loop through all the databases in SHOW DATABASES and run a statement if their name is LIKE 'application_%'. The statement will create a generic stored procedure in that database.
我希望循环遍历SHOW database中的所有数据库,如果它们的名称类似于“application_%”,则运行一条语句。语句将在该数据库中创建一个通用存储过程。
1 个解决方案
#1
4
Okay, it looks like the SCHEMATA table in the information_scheme
database contains a list of all databases. Thus, in order to get a list of all the databases you want to run the procedure on, you can do:
看起来information_scheme数据库中的SCHEMATA表包含所有数据库的列表。因此,为了获得要运行该过程的所有数据库的列表,您可以:
SELECT schema_name FROM information_schema.schemata
WHERE schema_name LIKE 'application_%';
The next step is to work this into some kind of procedure. Unfortunately, MySQL doesn't do well with executing dynamically generated SQL if it involves creating procedures. Thus, the purely-SQL version I came up with is a bit messy. It comes down to first creating the 'generator' procedure, then calling it, and finally executing the result of the generator:
下一步是把它变成某种程序。不幸的是,如果MySQL涉及到创建过程,那么它就不能很好地执行动态生成的SQL。因此,我提出的purely-SQL版本有点混乱。首先创建“生成器”过程,然后调用它,最后执行生成器的结果:
delimiter //
DROP PROCEDURE IF EXISTS create_procedures//
CREATE PROCEDURE create_procedures()
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE db VARCHAR(255);
DECLARE appDBs CURSOR FOR SELECT schema_name FROM information_schema.schemata WHERE schema_name LIKE 'application_%';
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
SET @procName = "simpleproc"; -- Change this to your proc name
SET @output = "delimiter //";
OPEN appDBs;
REPEAT
FETCH appDBs INTO db;
IF NOT done THEN
-- Replace this procedure declaration with your procedure.
-- Make sure to keep the ',db,' syntax there.
-- You should really only have to change the parameters
-- and the stuff between the BEGIN and END clauses.
SET @output = CONCAT(@output,'
DROP PROCEDURE IF EXISTS ',db,'.',@procName,'//
CREATE PROCEDURE ',db,'.',@procName,'()
BEGIN
SELECT 1;
END//');
END IF;
UNTIL done END REPEAT;
CLOSE appDBs;
SET @output = CONCAT(@output,'\ndelimiter ;');
SELECT @output AS procs;
END//
delimiter ;
After this procedure is generated, call the procedure:
生成此过程后,调用该过程:
CALL create_procedures();
This will output a single column that contains the SQL necessary to create procedures for all the application_%
tables. Select the entire column (it'll be rather long), and execute it as a new SQL query.
这将输出一个列,其中包含为所有application_%表创建过程所需的SQL。选择整个列(它将相当长),并将其作为新的SQL查询执行。
I've never used SQLyog, but if that doesn't work properly, then you may need to use MySQL's command line interface. First, generate a file input.sql
containing:
我从来没有使用过SQLyog,但是如果它不能正常工作,那么您可能需要使用MySQL的命令行接口。首先,生成文件输入。sql包含:
CALL create_procedures();
Then execute the following commands:
然后执行以下命令:
mysql -u <username> -p --database=<dbname> -N -r -B < input.sql > proc.sql
mysql -u <username> -p --database=<dbname> < proc.sql
changing <username>
and <dbname>
to appropriate values (<dbname>
can be any database you have permission to access). If you don't run into any errors, then you should have stored procedures defined for every database.
将
#1
4
Okay, it looks like the SCHEMATA table in the information_scheme
database contains a list of all databases. Thus, in order to get a list of all the databases you want to run the procedure on, you can do:
看起来information_scheme数据库中的SCHEMATA表包含所有数据库的列表。因此,为了获得要运行该过程的所有数据库的列表,您可以:
SELECT schema_name FROM information_schema.schemata
WHERE schema_name LIKE 'application_%';
The next step is to work this into some kind of procedure. Unfortunately, MySQL doesn't do well with executing dynamically generated SQL if it involves creating procedures. Thus, the purely-SQL version I came up with is a bit messy. It comes down to first creating the 'generator' procedure, then calling it, and finally executing the result of the generator:
下一步是把它变成某种程序。不幸的是,如果MySQL涉及到创建过程,那么它就不能很好地执行动态生成的SQL。因此,我提出的purely-SQL版本有点混乱。首先创建“生成器”过程,然后调用它,最后执行生成器的结果:
delimiter //
DROP PROCEDURE IF EXISTS create_procedures//
CREATE PROCEDURE create_procedures()
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE db VARCHAR(255);
DECLARE appDBs CURSOR FOR SELECT schema_name FROM information_schema.schemata WHERE schema_name LIKE 'application_%';
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
SET @procName = "simpleproc"; -- Change this to your proc name
SET @output = "delimiter //";
OPEN appDBs;
REPEAT
FETCH appDBs INTO db;
IF NOT done THEN
-- Replace this procedure declaration with your procedure.
-- Make sure to keep the ',db,' syntax there.
-- You should really only have to change the parameters
-- and the stuff between the BEGIN and END clauses.
SET @output = CONCAT(@output,'
DROP PROCEDURE IF EXISTS ',db,'.',@procName,'//
CREATE PROCEDURE ',db,'.',@procName,'()
BEGIN
SELECT 1;
END//');
END IF;
UNTIL done END REPEAT;
CLOSE appDBs;
SET @output = CONCAT(@output,'\ndelimiter ;');
SELECT @output AS procs;
END//
delimiter ;
After this procedure is generated, call the procedure:
生成此过程后,调用该过程:
CALL create_procedures();
This will output a single column that contains the SQL necessary to create procedures for all the application_%
tables. Select the entire column (it'll be rather long), and execute it as a new SQL query.
这将输出一个列,其中包含为所有application_%表创建过程所需的SQL。选择整个列(它将相当长),并将其作为新的SQL查询执行。
I've never used SQLyog, but if that doesn't work properly, then you may need to use MySQL's command line interface. First, generate a file input.sql
containing:
我从来没有使用过SQLyog,但是如果它不能正常工作,那么您可能需要使用MySQL的命令行接口。首先,生成文件输入。sql包含:
CALL create_procedures();
Then execute the following commands:
然后执行以下命令:
mysql -u <username> -p --database=<dbname> -N -r -B < input.sql > proc.sql
mysql -u <username> -p --database=<dbname> < proc.sql
changing <username>
and <dbname>
to appropriate values (<dbname>
can be any database you have permission to access). If you don't run into any errors, then you should have stored procedures defined for every database.
将