Oracle 数据库链接 - MySQL 等价物？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1565993/
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
Oracle Database Link - MySQL Equivalent?
提问by Palani
Oracle's database linkallows user to query on multiple physical databases.
Oracle 的数据库链接允许用户查询多个物理数据库。
Is there any MySQL equivalent ? Workaround ?
是否有任何 MySQL 等价物？解决方法？
I want to run a join query on two tables , which are in two physical databases. Is it possible in MySQL ?
我想在两个物理数据库中的两个表上运行连接查询。在 MySQL 中可能吗？
回答by Stefan Gehrig
I can think of four possible workarounds for your scenario:
我可以为您的场景想到四种可能的解决方法：
- use fully-qualified-table-names when querying for the externaltable. MySQL supports the
dbname.tablename-syntax to access tables outside the current database scope. This requires that the currently connected user has the appropriate rights to read from the requested table in another physical db. - if your external database is running on a different MySQL server (either on the same machine or via a network connection) you could use replication to constantly update a read-only copy of the remote table. Replication is only possible if you're running two separate MySQL instances.
- use the
FEDERATEDMySQL storage engineto virtually importthe table into your current database. This lifts the requirement of giving the current user access rights into the second database as the credentials are given with theCREATE TABLE-statement when using theFEDERATEDstorage engine. This also works with the databases running on different physical servers or different MySQL instances. I think that this will be the poorest performing option and does have some limitations- more or less important depending on your usage scenario and your requirements. - This is an extension to method 1. Instead of having to specify the fully-qualified-table-names every time you request information from your externaltable, you simply can create a viewinside your current database based on a simple
SELECT <<columns>> FROM <<database>>.<<table>>. This resemble the way, theFEDERATED-method works, but is limited to tables on the same MySQL instance.
- 查询外部表时使用全限定表名。MySQL 支持
dbname.tablename-syntax 来访问当前数据库范围之外的表。这要求当前连接的用户具有从另一个物理数据库中请求的表中读取的适当权限。 - 如果您的外部数据库在不同的 MySQL 服务器上运行（在同一台机器上或通过网络连接），您可以使用复制来不断更新远程表的只读副本。仅当您运行两个单独的 MySQL 实例时才可能进行复制。
- 使用
FEDERATEDMySQL 存储引擎将表虚拟导入到您当前的数据库中。这CREATE TABLE取消了授予当前用户访问第二个数据库的权限的要求，因为在使用FEDERATED存储引擎时，凭据是通过 -语句提供的。这也适用于运行在不同物理服务器或不同 MySQL 实例上的数据库。我认为这将是性能最差的选项，并且确实有一些限制- 或多或少取决于您的使用场景和您的要求。 - 这是一个扩展方法1，而不必指定完全限定表，每名从您的请求信息时的外部表，你根本就创建一个视图基于一个简单当前的数据库中
SELECT <<columns>> FROM <<database>>.<<table>>。这类似于FEDERATED- 方法的工作方式，但仅限于同一 MySQL 实例上的表。
Personally I'd consider method (4) as the most useful - but the others could also be possible workarounds depending on your requirements.
我个人认为方法 (4) 是最有用的 - 但其他方法也可能是可能的解决方法，具体取决于您的要求。
回答by Filip Ekberg
There's no MySQL equavilent method at the moment, see this post. However as the poster suggest you can do a work-around ifthe databases are on the same machine, by just adding the database-name in front of the table-name.
目前没有 MySQL 等效方法，请参阅此帖子。但是，正如海报所建议的，如果数据库在同一台机器上，您可以通过在表名前面添加数据库名来解决问题。
Also see this, it's 6 years old, but still not resolved. It's closed and probably not on their todo-list anymore.
也看到这个，已经6年了，但还是没有解决。它已关闭，可能不再出现在他们的待办事项列表中。
