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

How to implement cross-database query in MySQL? in view of this problem, this article introduces the corresponding analysis and solution in detail, hoping to help more partners who want to solve this problem to find a more simple and feasible method.

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) the cross-library query method does not support transactions, 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.

This is the answer to the question about how to realize cross-database query in MySQL. I hope the above content can be of some help to you. If you still have a lot of doubts to be solved, you can follow the industry information channel to learn more about it.

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.