Oracle Database Link – MySQL Equivalent?

by

I can think of four possible workarounds for your scenario:

  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.

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

More Related Contents:

  1. In SQL Server how to get the count of child if both parent and child are available [closed]
  2. how sql join works on 3 tables with example
  3. How to select the nth row in a SQL database table?
  4. SQL: Repeat a result row multiple times, and number the rows
  5. Migrate from Oracle to MySQL
  6. How do I decide when to use right joins/left joins or inner joins Or how to determine which table is on which side?
  7. Relationship between catalog, schema, user, and database instance
  8. Does mysql have the equivalent of Oracle’s “analytic functions”?
  9. What resources exist for Database performance-tuning? [closed]
  10. Is there a way to get the row number in Mysql like the rownum in oracle [duplicate]
  11. Use Composite Primary Key as Foreign Key
  12. What does SQL Select symbol || mean?
  13. MySQL equivalent of Oracle’s SEQUENCE.NEXTVAL
  14. MySQL: how to do row-level security (like Oracle’s Virtual Private Database)?
  15. What are the max number of allowable parameters per database provider type?
  16. Why in SQL NULL can’t match with NULL?
  17. Recreating a query using INNER JOIN? [duplicate]
  18. MySQL: Enable LOAD DATA LOCAL INFILE
  19. MySQL root access from all hosts
  20. Setting up MySQL and importing dump within Dockerfile
  21. SQL order string as number
  22. MySQL: Fastest way to count number of rows
  23. Find most frequent value in SQL column
  24. Rails 3 ActiveRecord: Order by count on association
  25. MySQL ENUM type vs join tables
  26. MySQL – How to SUM times?
  27. How do I UPDATE a row in a table or INSERT it if it doesn’t exist?
  28. How to make join queries using Sequelize on Node.js
  29. MySQL: Can I do a left join and pull only one row from the join table?
  30. Trouble with GROUP_CONCAT and Longtext in MySQL

Leave a Comment