Mysql get data from two table from different database in storeprocedure-CodePudding

I have two databases on same server with the same SERVER IP but both database user and its password is different. My problem is i want to create a procedure in which one table is used from db1 and one table is used form db2 like :

select * from customer; // db1
select * from orders inner join customer on order.customer_id = customer.id; // db2

I don't know how can i connect this two DB in one store procedure. Basically how to create the procedure for that.

CodePudding user response：

We can access other databases on the same MySQL instance by prefixing database names to tables.

select * 
from db2.orders o
inner join db1.customer c
on o.customer_id = c.id;

but both database user and its password is different

We might grant a role or account which can access both databases to access those resources, the user used to run the query will need SELECT access to all databases at least.

CodePudding user response：

You can use mysql federated storage engine to load one of the databases required table into the other one, and then treat it like normal table.