MySQL 一个存储过程可以处理两个不同的数据库吗？两台服务器怎么样？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4014780/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Can a stored procedure work with two different databases? How about two servers?
提问by Pentium10
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?
我想知道 MySQL 的存储过程是否可以在同一台机器上使用两个不同的数据库？如果他们在不同的服务器上呢？
回答by Hammerite
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.
如果我们谈论的是同一台服务器上的两个数据库：是的，一个存储过程可以访问另一个数据库。您必须确保在其权限下运行该过程的用户对每个数据库都具有必要的权限。
For example, suppose you have two databases on the same server, mydb1and mydb2, and that each contains a table named messageswith the same structure. Suppose you want to add a stored procedure to mydb2that empties the messagestable in mydb2and copies the contents of the messagestable in mydb1. You could do this:
例如，假设您在同一台服务器上有两个数据库mydb1和mydb2，并且每个数据库都包含一个以messages相同结构命名的表。假设您要添加一个存储过程来mydb2清空 中的messages表mydb2并复制 中的messages表的内容mydb1。你可以这样做：
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
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.mydb1database, I would need those qualifiers, but conversely I wouldn't need the mydb1.
看看我是如何用它们所属的数据库完全限定这些表的。事实上，您可能会争辩说我在这里过于热情，因为我们指定此存储过程将属于mydb2. 我不需要添加mydb2.mydb1数据库中，我将需要这些限定符，但相反，我不需要mydb1.
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 DELETEand INSERTprivileges on mydb2, and also SELECTprivileges on mydb1.
为了能够运行这个程序（可能是为了能够定义吗？），我需要确保我的用户DELETE和INSERT权限上mydb2，也SELECT特权上mydb1。
Databases on different servers sounds rather more complicated.
不同服务器上的数据库听起来相当复杂。
回答by Hadi Forghani
You Can Also use Different Databases on different servers but you need to link them and use a same account (for example an active directory account) for both of them to connect
您也可以在不同的服务器上使用不同的数据库，但您需要链接它们并使用相同的帐户（例如活动目录帐户）让它们连接
回答by Yasen Zhelev
IMHO it can work with 2 databases on one server, but only if they have the same login details It is not possible on 2 different servers.
恕我直言，它可以在一台服务器上使用 2 个数据库，但前提是它们具有相同的登录详细信息 在 2 个不同的服务器上是不可能的。
The stored procedure is executed under one database connection. All the databases that the connection's login has access to, are available in stored procedure.
存储过程在一个数据库连接下执行。连接的登录名可以访问的所有数据库都在存储过程中可用。
回答by Seva Alekseyev
Just use the databasename.tablename notation. Remote databases - I don't know of such a way in MySQL.. It's possible in MS SQL Server.
只需使用 databasename.tablename 表示法。远程数据库 - 我不知道 MySQL 中的这种方式.. 在 MS SQL Server 中是可能的。
