Example of cross-database query in MySQL

Preface

In MySQL, cross-database queries are mainly divided into two situations. One is cross-database queries within the same service; the other is cross-database queries between different services. The cross-database queries are different for each of them. The following will introduce these two types of cross-database queries in detail.

1. Cross-database query of the same service

Cross-database queries of the same service only need to include the data name when doing associated queries. The SQL is written as follows: SELECT * FROM database1.table1 x JOIN database2.table2 y ON x.field1=y.field2; for example:

2. Cross-database query of different services

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

Requirement: Table A in database X on service A needs to be associated with table B in database Y on service B to query the required data;

1. First check whether the FEDERATED engine is installed in the MySQL database by using the command show engines; as shown below:

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 to the end of the my.cnf file and restart MySQL. If the FEDERATED engine is not found, 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]'

Note: The table created by the FEDERATED engine only has a local table definition file, and the data file exists in the remote database. This engine can realize remote data access function. In other words, this table creation method will create a table structure file in database X on service A (that is, the table structure file of table B in database Y on service B). The table index, data and other files are also in database Y on service B, which is equivalent to a shortcut for easy association.

3. In database X on service A, directly associate table A with table B to query the required data.

There are a few things to note about this approach:

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. Currently, the remote database only supports MySQL, and other databases are not supported.

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

Summarize

This is the end of this article about cross-database query in MySQL. For more relevant MySQL cross-database query content, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!