How to associate queries across libraries in mysql

Editor to share with you how to cross-database related query in mysql, I believe most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!

Business scenario: queries that correlate tables in different databases

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

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

Solution: create a table B in database An on machine A.

This is no joke with you, of course, we are using the federated engine based on MySQL to build tables.

Example of a table-building 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 supports it or not).

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

If there is no FEDERATED line at all, it means that your mysql does not have this engine installed, so you can't play happily. You'd better go to your home operation and maintenance staff to fix it, because the next action is relatively big, and I don't know how to do it.

Explanation: the table created by the FEDERATED engine only has the table definition file locally, and the data file exists in the remote database. Through this engine, the remote data access function similar to DBLINK under Oracle can be realized. That is to say, this method of creating a table will only create a table structure file of table B in database A, and the index, data and other files of the table are still in database B on machine B. it is equivalent to creating a shortcut to table B in database A.

As a result, the egg doesn't hurt any more.

A few points to pay attention to:

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

two。 Remote database is currently limited to MySQL

3. Transactions are not supported

4. Table structure modification is not supported

Additional comments from other netizens:

CREATE TABLE IF NOT EXISTS `logintoken` (`id` int (11) NOT NULL AUTO_INCREMENT, `type` char (1) NOT NULL DEFAULT '0mm, `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 5.12 logintoken table, you only need to open the FEDERATED yourself, not 5.12.

These are all the contents of the article "how to correlate queries across databases in mysql". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!