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.

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

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:

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

SHOW ENGINES;

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

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.

.

.

#
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:

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!