Detailed explanation of cross-database related query method in mysql

This article mainly introduces the cross-database association query method in mysql. Friends who need it can refer to the

business scenario: correlating data in different databases Table query

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 , the database design is immutable, which is a pain in the ass. .

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

Of course this is not a joke. We use the table creation method 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 federated engine (execute show engines; you can 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 it is not enabled, go to the end of the my.cnf file Add a federated line and restart mysql;

If there is no FEDERATED line at all, it means that your mysql does not have this engine installed, so you cannot play happily. It is best to go to your home to get it done. Well, because the next action is relatively big, and I don’t know how to do it;

Explanation: The table created through the FEDERATED engine only has a table definition file locally, and the data file exists in the remote database. This engine can 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 index, data and other files of the table are still in database B on machine B, which is equivalent to just A shortcut to table B is created in database A.

As a result, the egg no longer hurts. .

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. Transactions are not supported

4. Table structure modification is not supported

Additions from 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';

To use the remote logintoken table of 5.12, you only need to turn on FEDERATED yourself. There is no need to turn it on for 5.12

The above is the detailed content of Detailed explanation of cross-database related query method in mysql. For more information, please follow other related articles on the PHP Chinese website!