MySQL: Navigate through the databases and run the stored procedure on it

Question

I am very new to MySQL and I have a stored procedure that I would like to add to several legacy databases. I use SQLyog, and I would like to scroll through each database in the connection, and if it matches "application_%" (the databases are called application_clientName, there are dozens) to start the stored procedure.

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

I’m kind of looking at how to scroll through all the databases in SHOW DATABASES and run the statement if their name is LIKE 'application_%'. A shared stored procedure will be created in this database.

Answer 1

, , SCHEMATA information_scheme . , , , :

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

- . , MySQL SQL, . , -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 ;

:

CALL create_procedures();

, SQL, application_%. ( ) SQL.

SQLyog, , MySQL. input.sql, :

CALL create_procedures();

:

mysql -u <username> -p --database=<dbname> -N -r -B < input.sql > proc.sql
mysql -u <username> -p --database=<dbname> < proc.sql

<username> <dbname> (<dbname> ). - , , .