0

For example， One mysql server machine A has One table called "tableA" under in a database called databaseA, another mysql server machine B has one table called "tableB" under in a database called databaseB. how to join tableA and tableB? Is there any good solution?

Superman
  • 285
  • 5
  • 17
  • This is NOT a duplicate of https://stackoverflow.com/questions/5698378/mysql-join-between-tables-in-2-different-databases - that refers to 2 databases on the SAME server. This is actually similar to a question I just answered: https://stackoverflow.com/questions/44380223/mysql-copy-data-of-tables-from-2-databases-both-database-are-in-different-ser/44380277#44380277 but actually it is not a duplicate of that either because that is about copying data between tables and not about an actual JOIN. – manassehkatz-Moving 2 Codidact Jun 06 '17 at 02:21
  • This is not a dup of the question about joining across databases within the same server. – ysth Jun 06 '17 at 02:22
  • @ysth - You're correct. I realized that and updated my comment. – manassehkatz-Moving 2 Codidact Jun 06 '17 at 02:25
  • could you provide one solution?Thanks – Superman Jun 06 '17 at 02:30
  • @Tim Biegeleisen, My problem is in two different mysql server, but your provide answer is in the same mysql server. – Superman Jun 06 '17 at 02:35
  • @Superman - I don't know of any solution, short of a brute force/ugly "manual join" - i.e., do 2 separate queries and simulate a join in the scripting language of your choice. The nature of MySQL (probably most other SQL as well) is that the optimization, foreign key checks, caching, etc. needed to handle JOINs well is all based on a single server (or server cluster, etc.) – manassehkatz-Moving 2 Codidact Jun 06 '17 at 02:46
  • @manassehkatz, thank you very much! – Superman Jun 06 '17 at 02:57

1 Answers1

1

You can write a JOIN tables in two databases hosted in the same MySQL instance by using qualified table names:

SELECT ...
FROM databaseA.tableA
JOIN databaseB.tableB
  ON <join-condition>

But you can't write a query to join across different MySQL instances.

MySQL parses and executes queries within the process of one server instance. If you were to try to reference tables in different instances, which instance would execute the query? Neither one has the ability to access tables in both instances.

You might fake it by using the FEDERATED storage engine, but I don't have confidence in that storage engine.

The general solution is to query the tables in each instance, fetch all the rows from each, and matching the rows using application code.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828