存储过程可以与两个不同的数据库一起工作吗?两台服务器怎么样? [英] Can a stored procedure work with two different databases? How about two servers?

问题描述

我想知道MySQL的存储过程是否可以在同一台机器上使用两个不同的数据库?如果它们在不同的服务器上呢?

I am wondering if MySQL's stored procedures can work with two different databases on the same machine? How about if they are on different servers?

推荐答案

如果我们要讨论的是同一台服务器上的两个数据库:是的，存储过程可以访问另一个数据库.您必须确保以其特权运行该过程的用户在每个数据库上都具有必需的特权.

If we're talking about two databases on the same server: yes, a stored procedure can access another database. You have to make sure that the user under whose privileges the procedure is being run has the necessary privileges on each database.

例如，假设您在同一服务器上有两个数据库mydb1和mydb2，并且每个数据库都包含一个名为messages的具有相同结构的表.假设您要向mydb2添加一个存储过程，以清空mydb2中的messages表并在mydb1中复制messages表的内容.您可以这样做:

For example, suppose you have two databases on the same server, mydb1 and mydb2, and that each contains a table named messages with the same structure. Suppose you want to add a stored procedure to mydb2 that empties the messages table in mydb2 and copies the contents of the messages table in mydb1. You could do this:

CREATE PROCEDURE `SynchroniseMessages` ()
LANGUAGE SQL
NOT DETERMINISTIC
MODIFIES SQL DATA
SQL SECURITY DEFINER
BEGIN

DELETE FROM `mydb2`.`messages`;

INSERT INTO
    `mydb2`.`messages`
    SELECT * FROM `mydb1`.`messages`;

END

看看我如何用表所属的数据库对表进行完全限定.实际上，您可能会说我在这里太热心了，因为我们指定此存储过程将属于mydb2.我不需要添加 mydb2.限定词.如果存储过程在mydb1数据库中，则需要那些限定符，但反之，则不需要 mydb1.出现的地方.

See how I've fully qualified the tables with the databases to which they belong. In fact you could argue that I'm being over-zealous here, because we specified that this stored procedure will belong in mydb2. I don't need to add the mydb2. qualifier. If the stored procedure were in the mydb1 database, I would need those qualifiers, but conversely I wouldn't need the mydb1. where it appears.

为了能够运行此过程(可能是为了对其进行定义?)，我需要确保我的用户对mydb2具有DELETE和INSERT特权，并且mydb1的SELECT特权.

In order to be able to run this procedure (possibly in order to be able to define it?), I'd need to make sure my user has DELETE and INSERT privileges on mydb2, and also SELECT privileges on mydb1.

不同服务器上的数据库听起来要复杂得多.

Databases on different servers sounds rather more complicated.

这篇关于存储过程可以与两个不同的数据库一起工作吗?两台服务器怎么样?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！