How to solve mysql cross-database query problem

1. The first type of cross-database query is a joint query between two different databases under the same mysql server. The relationship is as shown below

in the same Under a mysql server, two different data can be directly added to the database name to achieve cross-database query

select * from t_test1 t1, test2.t_test2 t2 where t1.id = t2.id

Execute sql query to get the results

2. The second type of cross-database query is a mysql server installed on two different servers (physical servers) to implement cross-database query. The implementation principle is similar to a virtual mapping and needs to be used Federated, another storage engine of mysql, FEDERATED storage engine accesses data in tables in remote databases instead of local tables.

This feature brings convenience to some development applications. You can directly build a federated table locally to connect to the remote data table. After configuration, the data in the local table can be directly synchronized with the remote data table. In fact, this engine does not actually store data. The data it needs is obtained by connecting to other MySQL servers.

mysql does not enable the federated storage engine by default and needs to be enabled in the configuration file;

#在[mysqld] 下 添加 federated 然后重启mysql服务就可以了
[mysqld]
federated

As shown below:

#输入命令查看引擎开启状态
show engines;

can be seen , has been turned on

Then you can create a virtual table on the test1 database, map the t_test2 table on the test2 library to the test1 library

CREATE TABLE `t_test2` (
  `id` int NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=FEDERATED DEFAULT CHARSET=utf8mb3 COMMENT='远程测试表t_test2' CONNECTION='mysql://root:123456@127.0.0.1:3306/test2/t_test2' ;
/**

CONNECTION='mysql://root:123456$@127.0.0.1:3306/test2/t_test2' 这个配置是关键
root:代表远程数据库的用户
123456:代表远程数据的密码
127.0.0.1:代表远程数据库的ip地址,域名也可以
test2:代表远程数据库的数据库名称
t_test2:代表远程数据库中的哪一张表
**/

Execute sql statement

You can see that a table t_test2 is created. This table does not actually exist, it is just a remote mapping

You can see using the FEDERATED engine

After the table is created, you can write sql

select * from t_test1 t1, t_test2 t2 where t1.id = t2.id

You can see the execution results

federated usage notes:

1. The locally created table name must exist on the remote server, and the created fields must also be fields in the remote table, which can be compared to The remote table has fewer fields, but not more. Select the local storage engine

2. Modifying the structure of the local virtual table will not modify the structure of the remote table

3. The truncate command will Clear remote table data

4. The drop command will only delete the virtual table and not the remote table

5. Execute statements such as select count(*), select * from limit M, N The efficiency is very low, and there are serious problems when the amount of data is large. However, querying by primary key or index column is very fast. For example, the following query is very slow (assuming id is the main index)

select id from db.tablea where id >100 limit 10 ;

The above is the detailed content of How to solve mysql cross-database query problem. For more information, please follow other related articles on the PHP Chinese website!