Cross-database association query method in mysql, mysql cross-database Association

Cross-database association query method in mysql, mysql cross-database Association

Business scenario: Associate queries for tables in different databases

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

In this case, you want to execute "select A. id, B. id from A left join B on ~~~; "That's impossible, but the business needs are immutable, and the database design is immutable. This is a headache ..

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

This is certainly not a joke. We use the federated engine of MySQL to create tables.

Table creation statement example:

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

Prerequisites: Your mysql database must support the federated engine (run show engines; to see if it is supported ).

If the FEDERATED engine is available but the Support is NO, it indicates that your mysql has installed this engine, but it is not enabled. Add a federated line at the end of my. cnf file and restart mysql;

If there is no FEDERATED line at all, it means that your mysql has not installed this engine, which won't be able to play happily. You 'd better go to your home for O & M, because the next steps are relatively large, and I don't know how to do it;

Explanation: The tables created through the FEDERATED engine only have a table definition file locally, and the data file is stored in a remote database. Through this engine, Remote Data Access similar to DBLINK in Oracle can be achieved. That is to say, this table creation method only creates A table structure file for table B in database A. The table index, data, and other files are still in database B on machine B, it is equivalent to creating A shortcut for table B in database.

So the egg does not hurt ..

Notes:

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

2. Currently, the remote database is only available for MySQL.

3. transactions are not supported.

4. Table Structure Modification is not supported

Supplemented by other netizens:

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:root@192.168.5.102:3306/zysso/logintoken';

You only need to enable FEDERATED on your own to use the remote 5.12 logisticken table. 5.12 does not need to be enabled.