How to implement mysql remote cross-database joint query

Situation 1: Two libraries are on the same physical host

Joint query (two different libraries, myemployees library and shoppingCart library), these two libraries are on the same On each physical host, they are all on my local machine.

#联合查询(不同的2个库,myemployees库和shoppingCart库)
SELECT emp01.`employee_id`, emp01.`first_name` FROM myemployees.employees AS emp01 LIMIT 0, 5
UNION
SELECT emp02.`employee_id`, emp02.`first_name` FROM shoppingCart.`employees2` AS emp02;
#
SELECT emp01.`employee_id`, emp01.`first_name` FROM myemployees.employees AS emp01 LIMIT 0, 5
UNION ALL
SELECT emp02.`employee_id`, emp02.`first_name` FROM shoppingCart.`employees2` AS emp02;

Scenario 2: The two libraries are not on the same physical host (that is, the two libraries are on different physical hosts)

In order to demonstrate the effect, I am here Using my virtual machine, I installed the Linux system (centos) in the virtual machine. The MySql database has been installed in the Linux system, the MySql database service has been started, and all the environments have been taken care of.

The ip of my Linux system (centos) is 192.168.117.66.

#I plan to conduct a joint query between my local author table and the remote user table.

After entering SHOW CREATE TABLE `user`; in linux and getting the result, we copied

CREATE TABLE IF NOT EXISTS `user` (
  `id` INT(11) DEFAULT NULL,
  `name` VARCHAR(20) DEFAULT NULL
)

this code to my local database, and

Add

ENGINE =FEDERATED CONNECTION='mysql://root:root@192.168.117.66:3306/testDB/user';这句话。

CREATE TABLE IF NOT EXISTS `user` (
  `id` INT(11) DEFAULT NULL,
  `name` VARCHAR(20) DEFAULT NULL
)ENGINE =FEDERATED CONNECTION='mysql://root:root@192.168.117.66:3306/testDB/user';

at the end. In fact, the above statement, to put it bluntly, is to create a remote database connection in my local database. The shortcut (remote database connection shortcut), what is it similar to? It is similar to the desktop shortcut on the desktop in our window operating system. We can open a certain software icon on the desktop by double-clicking the software icon. It is the same principle.

Just execute the above statement.

By the way, there is one more thing to note:

You need to check whether the

FEDERATED engine of your local mysql database has Not turned on.

SHOW ENGINES;

If FEDERATED is NO, it means it is not enabled and you need to modify the configuration file of the mysql database.

Modify the configuration file of the local mysql database and add

federated at the end of the configuration file, as shown below:

If you are windows If you are using a Linux system, modify the

my.ini file. If you are using a Linux system, modify the my.cnf file.

#After modifying the configuration file, remember to restart the mysql service.

linux restarts the mysql service, service mysqld restart

.

Windows restart the mysql service. In the dos window, enter net stop mysql service name, and then enter net start mysql service name

.

OK, after everything is done, execute the following sql statement to see the query results of the cross-database query.

#
SELECT id, aname FROM author
UNION
SELECT id, `name` FROM `user`;

SELECT * FROM author INNER JOIN  `user`;

For the above cross-server and cross-database queries, you need to pay attention to the following points:

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. The remote database currently only supports MySQL and other databases do not support it.

4. The table structure must be completely consistent with the target database table.

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