通过单一连接从两个不同的数据库获取数据，这是一种有效的方法

Question

When I first read about mysqli_connect(), I thought we can only perform the sql operations only on the database that was passed as an argument to mysqli_connect() but today I just gave a try that, is it possible to perform join operations on tables of two different databases on my local server. So I have created another database (db_2). Amazingly it worked.

I tried googling on this topic and I have found very old answers related to mysql_connect() and mysql_select_db(). Just take a look at my code

$host="localhost";
$username="root";
$password="";
$db_name="db_1";  

$con = mysqli_connect($host,$username,$password,$db_name); 

$sql_query = "SELECT name FROM db_1.user as u inner join
                       db_2.user as us ON us.id = u.id
                       WHERE u.id = 102 LIMIT 1";

$result = mysqli_query($con,$sql_query);

$row = mysqli_fetch_array($result, MYSQL_ASSOC);

echo $row['name'];

Initially I thought we should make two connections to two databases, something like this to make use of two databases

 $con1 = mysqli_connect($host,$username,$password,"db_1"); 
 $con2 = mysqli_connect($host,$username,$password,"db_2"); 

but only using single connection It is easily able to fetch data between two databases.

My question is is this right way of joining two databases ? I know PDO is better way but out of curiosity just want to know.

Also are there any limitations? Is this prefered way when I have huge data.

Does it really slow down the performance when you grab data between two databases or is it little. Any help is greatly appreciated. Thanks

Answer 1

If both of the databases are on the same server and are accessible by the user you're using. You should be able to put together a query like this...

SELECT db1.table1.column1, db2.table2.column2 FROM db1.table1 JOIN db2.table2 ON db1.table1.column1 = db2.table2.column2;