以编程方式更新多个数据库中的相同过程

Question

I have procedure MY_OWN_PROC with parameters _DATE, _ID and its body is:

BEGIN
SELECT h.* 
FROM `my` h 
WHERE     
    h.DATE>=_DATE /*comment*/;
END

I have the same procedure in databases db1 - db100. Now I would like to update body of procedure in all databases at once (e.g. I want to replace "h.DATE<_DATE" part or the whole body). How can this be done with mysql by native sql command, or by adminer (I am using adminer on the server) or by php script please?

Update: First my thought - I will make sql command for the 100 databases (copy/paste) and the copy this huge command to adminer and run it.

Answer 1

If your database user has access to all of the databases, the you can create a PHP for loop

$conn = new mysqli ( '172.17.0.3', 'root', 'root', 'test');
for ( $i = 1; $i < 101; $i++ )  {
        if ($conn->query("CREATE DEFINER=`root`@`%` PROCEDURE `db$i`.`MY_OWN_PROC`(IN `_DATE` INT)
        NO SQL
    SELECT h.* 
FROM `my` h 
WHERE     
    h.DATE>=_DATE") === false )  {
            echo "error:".$conn->error;
        }
    }

The main thing here is the db$i.MY_OWN_PROC bit which puts the increment into the database name.

You will of course have to put the correct source for your procedure into the code and the correct database credentials.