Oracle 数据库链接 - MySQL 等价物?

2021-11-20 00:00:00 mysql oracle

Oracle 的数据库链接允许用户查询多个物理数据库.

Oracle's database link allows user to query on multiple physical databases.

是否有任何 MySQL 等价物?解决方法?

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 ?

推荐答案

对于您的场景，我可以想到四种可能的解决方法:

I can think of four possible workarounds for your scenario:

  1. 在查询外部表时使用全限定表名.MySQL 支持 dbname.tablename 语法来访问当前数据库范围之外的表.这要求当前连接的用户具有从另一个物理数据库中请求的表中读取的适当权限.
  2. 如果您的外部数据库在不同的 MySQL 服务器上运行(在同一台机器上或通过网络连接)，您可以使用复制来不断更新远程表的只读副本.只有在运行两个单独的 MySQL 实例时才能进行复制.
  3. 使用FEDERATEDMySQL 存储引擎将表虚拟导入到您当前的数据库中.当使用 FEDERATED 存储引擎时，由于凭据是通过 CREATE TABLE 语句提供的，因此无需授予当前用户访问第二个数据库的权限.这也适用于运行在不同物理服务器或不同 MySQL 实例上的数据库.我认为这将是性能最差的选项，并且确实有一些限制 - 或多或少取决于您的使用场景和要求.
  4. 这是方法 1 的扩展.不必在每次从 外部 表中请求信息时都指定完全限定的表名，您只需创建一个 view 基于一个简单的 SELECT <<列>>FROM <<数据库>>.<<表>>.这类似于 FEDERATED 方法的工作方式，但仅限于同一 MySQL 实例上的表.
  1. use fully-qualified-table-names when querying for the external table. 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.
  2. 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.
  3. use the FEDERATED MySQL storage engine to virtually import the 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 the CREATE TABLE-statement when using the FEDERATED storage 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.
  4. This is an extension to method 1. Instead of having to specify the fully-qualified-table-names every time you request information from your external table, you simply can create a view inside your current database based on a simple SELECT <<columns>> FROM <<database>>.<<table>>. This resemble the way, the FEDERATED-method works, but is limited to tables on the same MySQL instance.

我个人认为方法 (4) 是最有用的 - 但其他方法也可能是可能的解决方法，具体取决于您的要求.

Personally I'd consider method (4) as the most useful - but the others could also be possible workarounds depending on your requirements.

相关文章