Shulou(Shulou.com)05/31 Report--

This article will explain in detail how to achieve cross-database association query in mysql. The content of the article is of high quality, so the editor will share it with you for reference. I hope you will have a certain understanding of the relevant knowledge after reading this article.

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 table-building sentence: 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: tables created by the FEDERATED engine only have table definition files locally, and data files exist in the remote database, which can be similar to Oracle.

The remote data access function of DBLINK. 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.

On how to achieve cross-database association query in mysql is shared here, I hope the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.