Example of cross-database association query method in mysql

This article mainly introduces the cross-database related query method in mysql. Friends who need it can refer to it. I hope it can help everyone.

Business scenario: Query to associate tables in different databases

For example, the tables to be associated are: Table A in database A on machine A && in database B on machine B Table 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. This is so painful. .

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 the federated engine of MySQL.

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; you can see whether it is supported).

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

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

Explanation: The tables created through the FEDERATED engine only have table definition files locally, and the data files exist in the remote database. Through this engine, something similar to Oracle can be achieved Download the remote data access function of DBLINK. 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 creating the table in database A. A shortcut to B.

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. It does not need to be turned on for 5.12.

Related recommendations:

How Oracle implements cross-database query

Solution to cross-database transaction consistency problem (example)

[MSSQL]SQLServer cross-database query

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