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

In this issue, the editor will bring you about how to achieve cross-database query in MySQL. The article is rich in content and analyzes and describes for you from a professional point of view. I hope you can get something after reading this article.

1. Cross-database query for the same service

Cross-database queries of the same service only need to be associated with the data name. SQL is written as follows: SELECT * FROM database 1.table1 x JOIN database 2.table2 y ON x.field1fields. Field2; for example:

Second, cross-database queries for different services

Cross-database queries for different services cannot be associated directly by adding data names. Here, you need to use the federated engine in the MySQL database. The specific process is as follows:

Requirements: table An of database X on service A needs to associate table B of database Y on service B to query the required data

1. Check whether the FEDERATED engine is installed in the MySQL database, as shown in the following figure with the command show engines;:

As shown in the figure above, MySQL has installed the FEDERATED engine, but Support is No, which means it is not enabled. Add a line of FEDERATED at the end of the my.cnf file and restart MySQL. If you can't find the FEDERATED engine, you need to install it.

2. Create a table B in database X on service A with the following statement: CREATE TABLE table_name (...) ENGINE=FEDERATED CONNECTION='mysql:// [username]: [password] @ [localtion]: [port] / [db-name] / [table-name]'

Description: the table created by the FEDERATED engine only has a table definition file locally, and the data file exists in the remote database. The remote data access function can be realized through this engine. In other words, this table creation method creates a table structure file in database X on service A (that is, the table structure file of database Y on service B), and the index, data and other files of the table are also in database Y on service B. it is equivalent to a shortcut to facilitate association.

3. By directly associating table A with table B in database X on service A, you can query the required data.

This approach has the following points to pay attention to:

1) this cross-database query method does not support transactions, so it is best not to use transactions.

2) the table structure cannot be modified.

3) MySQL uses this cross-database query method. Remote databases only support MySQL, but other databases do not.

4) the table structure must be exactly the same as the target database table.

The above is how to achieve cross-database query in the MySQL shared by the editor. If you happen to have similar doubts, you might as well refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

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.