MySQL Cross-Library Union table query

Business scenario: Queries that correlate tables in different databases

For example, the table to be associated with is: Table B in database A on machine a && b in database B on machine A.

In this case, you want to perform "select A.id,b.id from A left join B on ~ ~ ~;" That is not possible, but the business needs are not variable, the database design is immutable, this is the egg hurt.

Solution: Build a Table B ... in database A on machine a.

This is certainly not a joke with you, we are using the MySQL-based federated engine to build the table .

Example of a Build table statement:CREATE table ' table_name ' (...) ENGINE =federated connection= ' mysql://[username]:[password]@[location]:[port]/[db-name]/[table-name] '

Prerequisites: Your MySQL has to support the Federated engine (execute show engines; you can see if it is supported).

If there is a federated engine, but support is no, stating that your MySQL installed this engine, but not enabled, go to my.cnf file at the end of adding a line federated, restart MySQL can;

If there is no federated this line, stating that your MySQL does not install this engine, this can not be fun to play, it is best to find your home operation, because the next action is larger, and I do not know how to do;

Explanation: The table created by the Federated engine only has a table definition file locally, and the data file exists in the remote database, which enables remote data access similar to that of Oracle under Dblink. That is, this way of building a table will only create a table structure file in database A, the index of the table, the data, and so on machine B in database B, equivalent to just create a shortcut to table B in database A.

So, the egg will not hurt.

A few things to note:

1. The local table structure must be exactly the same as the remote.

2. The remote database is currently limited to MySQL

3. Do not support transactions

4. Table structure Modification not supported

MySQL Cross-Library Union table query