MySQL：循环访问数据库并对其运行存储过程 [英] MySQL: Loop through databases and run a stored procedure on it

问题描述

我是MySQL的新手，我有一个存储过程，我想添加到几个遗留的数据库。我使用SQLyog和我想循环通过每个数据库的连接，如果它匹配'application_％'（数据库称为 application_clientName ，有几十个）运行存储过程。

我可以保存并通过SQLyog运行的脚本将是理想的。

寻找循环遍历SHOW DATABASES中的所有数据库，并运行一个语句，如果他们的名字是LIKE'application_％'。该语句将在该数据库中创建一个通用的存储过程。

解决方案

好吧，它看起来像 SCHEMATA 表中的 information_scheme 数据库包含所有数据库的列表。因此，为了得到您要运行该过程的所有数据库的列表，您可以：

  SELECT schema_name FROM information_schema.schemata 
 WHERE schema_name LIKE'application_％'; 
  

下一步是将此工作转换为某种程序。不幸的是，如果涉及创建过程，MySQL不能很好地执行动态生成的SQL。因此，我提出的纯SQL版本是有点凌乱。首先创建'generator'过程，然后调用它，最后执行生成器的结果：

 分隔符// 
 DROP PROCEDURE IF EXISTS create_procedures // 
 CREATE PROCEDURE create_procedures（）
 BEGIN 
 DECLARE done INT DEFAULT 0; 
 DECLARE db VARCHAR（255）; 
 DECLARE appDB 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; - 将此更改为您的proc名称
 
 SET @output =delimiter //; 
 
 OPEN appDBs; 
 REPEAT 
 FETCH appDBs INTO db; 
如果不完成
  - 用您的过程替换此过程声明。 
  - 确保保持'，db，'语法。 
  - 你真的只需要改变参数
  - 和BEGIN和END子句之间的东西。 
 SET @output = CONCAT（@output，'
 DROP PROCEDURE IF EXISTS'，db，'。'，@ procName，'// 
 CREATE PROCEDURE'，db，'。 @procName，'（）
 BEGIN 
 SELECT 1; 
 END //'）; 
 
 END IF; 
 UNTIL done END REPEAT; 
 
关闭appDBs; 
 
 SET @output = CONCAT（@ output，'\\\
delimiter;'）; 
 
 SELECT @output AS procs; 
 END // 
 delimiter; 
  

生成此过程后，调用以下过程：

  CALL create_procedures（）; 
  

这将输出一个包含创建所有 application _％表。选择整个列（它会很长），并将其作为一个新的SQL查询执行。

我从来没有使用过SQLyog，但如果没有工作正常，那么你可能需要使用MySQL的命令行界面。首先，生成一个文件 input.sql 包含：

  CALL create_procedures ）;然后执行以下命令：
 
 
 <$ p 
 $ p>  mysql -u< username> -p --database =< dbname> -N-r -B < input.sql> proc.sql 
 mysql -u< username> -p --database =< dbname> < proc.sql 
  

更改< username> 和< dbname> 到适当的值（< dbname> 可以是您有权访问的任何数据库）。如果你不会遇到任何错误，那么你应该为每个数据库定义存储过程。

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.

A script I can save and run through SQLyog would be ideal.

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.

解决方案

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:

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:

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.

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:

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.

这篇关于MySQL：循环访问数据库并对其运行存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！