Cross-database association query method in MySQL

Business scenario: querying tables in different databases

For example, the tables to be associated are: Table A in database A on machine A && Table B in database B on machine B.

In this case, it is impossible to execute "select A.id,B.id from A left join B on ~~~;", but the business requirements are immutable and the database design is immutable, which is a pain in the ass. .

Solution: Create a table B in database A on machine A. . .

I'm not kidding you, we use the method of building tables based on MySQL's federated engine.

Example of table creation statement:

CREATE TABLE `table_name`(......) ENGINE = FEDERATED CONNECTION = 'mysql://[username]:[password]@[location]:[port]/[db-name]/[table-name]'

Prerequisite: Your MySQL must support the federated engine (execute show engines; to see whether it is supported).

If there is a FEDERATED engine, but Support is NO, it means that your MySQL has installed this engine but has not enabled it. Add a line of federated to the end of the my.cnf file and restart MySQL.

If there is no FEDERATED line at all, it means that your MySQL engine is not installed, and you can't play happily. It's best to ask your operation and maintenance to fix it, because the next step is relatively large, and I don't know how to do it;

Explanation: The table created by the FEDERATED engine only has a local table definition file, and the data file exists in the remote database. This engine can be used to implement remote data access functions similar to DBLINK under Oracle. That is to say, this table creation method will only create a table structure file of Table B in Database A. The table index, data and other files are still in Database B on Machine B. It is equivalent to just creating a shortcut to Table B in Database A.

So, the pain in the balls is gone. .

A few points to note:

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

2. The remote database is currently limited to MySQL

3. No support for transactions

4. Table structure modification is not supported

Other netizens added:

CREATE TABLE IF NOT EXISTS `logintoken` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`type` char(1) NOT NULL DEFAULT '0',
`loginName` varchar(20) DEFAULT NULL,
`token` varchar(2000) DEFAULT NULL,
`tokenExpiredTime` timestamp NULL DEFAULT NULL,
PRIMARY KEY (`id`)
)ENGINE=FEDERATED CONNECTION='mysql://root:[email protected]:3306/zysso/logintoken';

To use the remote 5.12 logintoken table, you only need to enable FEDERATED yourself. 5.12 does not need to enable it.