什么是在PHP中保存多个MySQL数据库连接的解决方案

Question

I need a solution for save every MySQL connections in PHP and use it in sql transactions between connections.

I know that I can't do transactions between different connections but I want to handle in PHP or a solution. (I don't know "what is the solution?")

Is that right, about :

• security?
• performance?
• transaction?
• ...

completing question :

I have multiple databases that connected with different users. One or more database are common between other databases that use it on the joins, for example :

db_common with common_user and tables: common_1, common_2, ...
db_specific_1 with user_1 and tables: s1_1, s1_2, ...
db_specific_2 with user_2 and tables: s2_1, s2_2, ...

May be join db_specific_1 with common or even with db_specific_2.

I want to remove joins between databases. Because, db_specific_1 is for a project and db_specific_2 is for other project. Since the database server is the same one, I granted select access to user_1 to db_specific_2, that is wrong, since db_specific_2 must be transfer to other server.

I need transaction between db_specific_1 and db_specific_2 and db_common.

Answer 1

I setup the access rights so that each project needs only one database connection. Usually each project has a main part of a database. For example:

grant all on `database`.* to `username`@localhost

If the same user have full access to another database2, I add

grant all on `database2`.* to `username`@localhost

But usually, only some tables or columns must be accessed, and sometime only readonly:

grant select on `database2`.stats to `username`@localhost

In PHP use database.table instead of table to select foreign tables:

SELECT a.name, b.stat 
FROM `mytable` a 
LEFT JOIN `database2`.`stats` b ON b.id = a.id