Shulou(Shulou.com)06/01 Report--

This article mainly shows you "how to realize mysql remote cross-library joint query", the content is simple and easy to understand, organized clearly, I hope to help you solve doubts, let Xiaobian lead you to study and learn "how to realize mysql remote cross-library joint query" this article bar.

Case 2 libraries on the same physical host

Joint query (2 different libraries,myemployees library and shoppingCart library), these 2 libraries are on the same physical host, both on my own machine.

#federated query (2 different libraries,myemployees library and shoppingCart library)SELECT emp01.` employee_id`, emp01.` first_name` FROM myemployees.employees AS emp01 LIMIT 0, 5UNIONSELECT 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, 5UNION ALLSELECT emp02.` employee_id`, emp02.` first_name` FROM shoppingCart.` employees2` AS emp02; Case 2: The two libraries are not on the same physical host (i.e., the two libraries are on different physical hosts)

To demonstrate the effect, I use my virtual machine here, my virtual machine installed Linux system (centos), Linux system has installed MySql database, MySql database service has been started, all the environment has been ready.

My Linux system (centos) IP is 192.168.117.66.

I intend to do a joint query of my local author table and remote user table.

After typing SHOW CREATE TABLE `user`; in linux, we put

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

Copy 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';

In fact, the above paragraph of the statement, to put it bluntly, is in my local database to build a remote database connection shortcut (remote database connection shortcut), similar to what? Similar to our window operating system desktop shortcut, we double-click on a desktop software icon can open the software, the same reason.

Just execute the above statement.

Oh, and one more thing to note:

You need to check if the FEDERATED engine in your local mysql database is turned on.

SHOW ENGINES;

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

Modify the configuration file of the local mysql database and add federated at the end of the configuration file, as shown in the following figure:

If you are running Windows, modify the my.ini file, and if you are running Linux, modify the my.cnf file.

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

Linux restart mysql service, service mysqld restart.

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

OK, after all done, execute the following sql statement, you can see the query results of cross-database query.

#SELECT id, aname FROM authorUNIONSELECT id, `name` FROM `user`;

SELECT * FROM author INNER JOIN `user`;

The above cross-server cross-database queries 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. Table structure cannot be modified.

MySQL uses this cross-database query method, remote databases currently only support MySQL, other databases do not support it.

4. The table structure must be identical to the target database tables.

The above is "how to achieve mysql remote cross-database joint query" all the content of this article, thank you for reading! I believe that everyone has a certain understanding, hope to share the content to help everyone, if you still want to learn more knowledge, welcome to pay attention to the industry information channel!

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.