dsjlqkbpn029473708
2018-03-24 07:47 阅读 23
已采纳

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

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.

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享

1条回答 默认 最新

  • 已采纳
    dongxian1921 dongxian1921 2018-03-24 09:08

    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.

    点赞 评论 复制链接分享

相关推荐