在单个脚本中连接多个数据库的优缺点

Let's say user have two databases hosted on single host and I need to connect to both of them so that I can use any table anytime without adding connection code multiple times.

假设用户有两个数据库托管在单个主机上，我需要连接它们，这样我就可以在不添加连接代码的情况下使用任何表。

I have implemented this in CodeIgniter with adding authorization details of both databases in database.php file and to load required database with $this->load->database('dbname'); in script.

我在CodeIgniter中实现了这一点，并在数据库中添加了两个数据库的授权细节。使用$this->load->数据库('dbname')加载所需的数据库;在脚本。

Now, for core PHP, we can do this like:

对于核心PHP，我们可以这样做:

mysql_connect ('host','user','password','port','dbname'); // connection with one database.   

It was connected with my first database.

它与我的第一个数据库相连。

Now, I want to connect with second database:

现在我想要连接到第二个数据库:

1) I have not closed above connection and connected with second one with

1)我没有关闭上面的连接，并与第二个连接。

mysql_connect ('host','user','password','port','dbname1');.

2) Would it be bad practice to do so ? Would it consume more objects ? Should we be required to close first one anyhow?

这样做是不是不好?它会消耗更多的物体吗?我们是否应该要求关闭第一个?

14 个解决方案

#1

It is not neccessary to open 2 connection just to use tables from 2 databases on the same server. You just need to use the database.table notation. Doing that means that you can even join tables from different databases in the same query

在同一台服务器上使用两个数据库的表来打开两个连接并不是必要的。您只需要使用数据库。表表示法。这样做意味着您甚至可以在同一个查询中连接来自不同数据库的表

SELECT t1.col1, t1.col2, t2.col2, t2.col2
FROM db1.table1 AS t1 JOIN db2.table1 AS t2 ON t1.col1 = t2.col3

So if you have connected to db1 initially you can use db2 tables and the same if you connected to db2 you can use db1 tables.

因此，如果您最初连接到db1，那么您可以使用db2表，如果连接到db2，也可以使用db1表。

#2

Have you tried this?

你有试过吗?

$mysqli1 = new mysqli("example.com", "user", "password", "database1");

$mysqli2 = new mysqli("example.com", "user", "password", "database2");

#3

You can do this by following object oriented approach

您可以通过遵循面向对象的方法来实现这一点

First of all create connection with two databases:

首先创建与两个数据库的连接:

$Db1 = new mysqli('localhost','root','','database1'); // this is object of database 1
$Db2 = new mysqli('localhost','root','','database2'); // this is object of database 2

$query1 = 'select * from `table_name_of_database1`';  // Query to be run on DB1
$query2 = 'select * from `table_name_of_database2`';   // Query to be run on DB2

$result1 = $Db1->query($query1); // Executing query on database1 by using $Db1
$result2 = $Db2->query($query2); // Executing query on database2 by using $Db2

echo "<pre>";

/* Print result of $query1 */

if ($result1->num_rows > 0) {
    while($row = $result1->fetch_assoc()) {
        print_r($row);
    }
} else {
    echo "0 results";
}


/*========================================================*/


/* Print result of $query2 */

if ($result2->num_rows > 0) {
    while($row = $result2->fetch_assoc()) {
         print_r($row);
    }
} else {
    echo "0 results";
}

Conclusion: When you want to use database1 use $Db1 object and if you want to use database2 then use $DB2.

结论:当您想使用database1时使用$Db1对象，如果您想使用database2，则使用$DB2。

#4

Why do you need two connections? The pros/advantages of two databases are actually primarily performance issues. But if you are on the same machine actually the only advantage you have, would be a cleaner separation. So it would be better to use one DB with two different prefixes for the table. So you seperate the diffent data by prefix and not by DB.

为什么需要两个连接?两个数据库的优点实际上主要是性能问题。但是如果你在同一台机器上实际上你唯一的优势是，一个更干净的分离。因此，最好使用带有两个不同前缀的一个DB。所以你用前缀而不是DB来分离数据。

#5

I don't think how to connect to 2 DBs simultaneously is the problem, as you have successfully done it ( or know how to do it ). I can gather that from your question. So I won't show how to do this. See other answers if you need to.

我不认为如何同时连接到两个DBs是问题，因为您已经成功地完成了它(或知道如何做到)。我可以从你的问题中得出这个结论。我就不讲怎么做了。如果需要，请查看其他答案。

But to address your issues directly:

但要直接解决你的问题:

1. Would it be bad practice to do so ? Generally, you should avoid 2 simultaneous DB connection handles as much as possible. If you only need to get data from one DB and use them to do something on the other, your best bet is to put the data from DB1 into appropriate PHP variables, close the connection; then make the second connection. That would be cheaper than keeping 2 DB connections open at the same time. However, if you are doing something like INSERT INTO db1.table SELECT FROM db2.table AND ALSO NEED TO COMMIT OR ROLLBACK depending on success or failure of some queries, then AFAIK, you need to keep both connections open until your processes are over. You see, there always trade-offs. So you decide based on the need of your application and bear the cost.
2. 这样做不好吗?通常，您应该尽可能避免同时使用两个DB连接句柄。如果您只需要从一个DB获取数据并使用它们在另一个DB上做一些事情，那么最好的方法是将来自DB1的数据放入适当的PHP变量中，关闭连接;然后进行第二次连接。这比同时打开两个DB连接要便宜。但是，如果您正在执行插入到db1之类的操作。从db2表选择。表和还需要提交或回滚，这取决于某些查询的成功或失败，然后AFAIK，您需要保持两个连接的打开，直到您的进程结束。你看，总是有权衡取舍的。因此，您可以根据应用程序的需要来决定并承担成本。

As a practical example of this scenario, I once worked on a project where I needed to SELECT a table1, INSERT INTO a table2, if the INSERT succeeds, I delete all the rows from table1, if the DELETE fails, I rollback the INSERT operation because the data cannot live in the two tables at the same time.

作为一个实际的例子,这个场景中,我曾经做的一个项目,我需要选择一个表,插入一个表,如果插入成功,我从表1删除所有的行,如果删除失败,回滚插入操作,因为我不能生活在两个表的数据在同一时间。

Of course, my own case involved only one DB, so no need of a second connection. But assuming the two tables were on different DBs, then that may be similar to your situation.

当然，我自己的情况只涉及一个DB，所以不需要第二个连接。但假设这两个表位于不同的DBs上，那么这可能与您的情况类似。

2. Would it consume more objects ? No other objects other than the ones pointed out in 1 above, namely the DB connection handles according to your question.

   它会消耗更多的物体吗?除了上面1中指出的对象之外，没有其他对象，也就是根据您的问题处理DB连接。

3. Should we compulsory require to close first one anyhow ? Once again, depending on your application needs.

   我们是否应该强制要求关闭第一个?同样，这取决于应用程序的需要。

#6

Instead of mysql_connect use mysqli_connect.

使用mysql_connect而不是mysqli_connect。

mysqli is provide a functionality for connect multiple database at a time.

mysqli提供了一次连接多个数据库的功能。

#7

1) Is it possible to connect with more than one database in one script ?

1)是否可以在一个脚本中连接多个数据库?

Yes we can create multiple MySQL link identifier in a same script.

是的，我们可以在同一个脚本中创建多个MySQL链接标识符。

2) It should be not like to close one connection with mysql_close and open new one,rather both connection should open at a time and user can use any table from any of the database ?

2)不应该关闭与mysql_close和open new one的连接，这两个连接应该同时打开，用户可以使用任何数据库中的任何表?

Use Persistent Database Connections like mysql_pconnect

使用像mysql_pconnect这样的持久数据库连接

3) If it is possible,what can be disadvantage of this ? Will there create two object and this will going to create issue ?

3)如果可能的话，有什么缺点呢?是否会创建两个对象，这会产生问题?

I don't think so it create any issue other than increasing some load on server.

我不认为这会产生任何问题，除了增加服务器的负载。

#8

You can use like this

你可以这样使用

$db1 = mysql_connect($hostname, $username, $password); 
$db2 = mysql_connect($hostname, $username, $password, true); 

mysql_select_db('abc', $db1);
mysql_select_db('def', $db2);

For Database 1

对于数据库1

mysql_query('select * from table1', $db1);

For Database 2

对于数据库2

mysql_query('select * from table2', $db2);

#9

The best way to use multiple databases is to use PDO functions

使用多个数据库的最佳方式是使用PDO函数

EXAMPLE

例子

// database cobfigurations
$config= array(
    // first database
    array(
        'type'=>'mysql',                    // DB type
        'host'=>'localhost',                // DB host
        'dbname'=>'database1',      // DB name
        'user'=>'root',                 // DB username
        'pass'=>'12345',                // DB password
    ),
    // second database
    array(
        'type'=>'mysql',                    // DB type
        'host'=>'localhost',                // DB host
        'dbname'=>'database2',      // DB name
        'user'=>'root',                 // DB username
        'pass'=>'987654',               // DB password
    ),
);
// database connections
$mysql=array();
foreach($config as $con)
{
    $con=(object)$con;
    $start= new PDO($con->type.':host='.$con->host.';dbname='.$con->dbname.'', $con->user, $con->pass, array(
            // pdo setup
            PDO::ATTR_PERSISTENT            => FALSE,
            PDO::ATTR_DEFAULT_FETCH_MODE    => PDO::FETCH_OBJ,
            PDO::ATTR_ERRMODE               => PDO::ERRMODE_EXCEPTION,
            PDO::MYSQL_ATTR_INIT_COMMAND    => 'SET NAMES UTF8'
    ));

    if ($start && !empty($start) && !is_resource($start))
        $mysql[]=$start;    // connection is OK prepare objects
    else
        $mysql[]=false; // connection is NOT OK, return false
}

/**********************
 ****  HOW TO USE ****
**********************/ 

// fetch data from database 1
$data1 = $mysql[0]->query("SELECT id, title, text FROM content1")->fetchAll();
if(count($data1)>0)
{
    foreach($data1 as $i=>$result)
    {
        echo $result->id.' '.$result->title.' '.$result->text.'<br>'
    }
}

// fetch data from database 2
$data2 = $mysql[1]->query("SELECT id, title, text FROM content2")->fetchAll();
if(count($data2)>0)
{
    foreach($data2 as $i=>$result)
    {
        echo $result->id.' '.$result->title.' '.$result->text.'<br>'
    }
}

If you not use PDO before, please read this short tutorial:

如果您以前没有使用过PDO，请阅读下面的教程:

http://www.mysqltutorial.org/php-querying-data-from-mysql-table/

http://www.mysqltutorial.org/php-querying-data-from-mysql-table/

Is practicly same like mysql and mysqli connections but is more advanced, fast and secure.

与mysql和mysqli连接类似，但更高级、更快、更安全。

Read this documentations: http://php.net/manual/en/book.pdo.php

读这个文件:http://php.net/manual/en/book.pdo.php

And you can add more then 2 databases

你可以添加两个以上的数据库

#10

Q: What cons are there to connect with other database without closing previous database?

问:在不关闭之前的数据库的情况下，有什么缺点可以连接到其他数据库?

A: When you connect to a database server physically are assigning resources to interact with you, if two databases are on the same server you would unnecessarily using resources that could be used to address other connections or other activities. Therefore you would be right close connections that do not need to continue using.

答:当您物理地连接到数据库服务器时，正在分配资源与您进行交互，如果两个数据库位于同一服务器上，您将不必要地使用可以用于处理其他连接或其他活动的资源。因此，您将是正确的密切联系，不需要继续使用。

Q: Is this a appropriate practice to do so ? What is the best way to do so without opening this connection in every script multiple times ? I want this to get done in core php only as I have already know this in codeigniter.

问:这是一个合适的做法吗?没有在每个脚本中多次打开这个连接的最好方法是什么?我只希望在core php中完成这一点，因为我在codeigniter中已经知道这一点。

One way SESSIONS, but you can't store database conections in sessions. Read in PHP.net this Warning: "Some types of data can not be serialized thus stored in sessions. It includes resource variables or objects with circular references (i.e. objects which passes a reference to itself to another object)." MySQL connections are one such kind of resource.

单向会话，但是不能在会话中存储数据库连接。在PHP.net中读取此警告:“某些类型的数据不能序列化，因此不能存储在会话中。它包括具有循环引用的资源变量或对象(即将引用传递到另一个对象的对象)。MySQL连接就是这样一种资源。

You have to reconnect on each page run.

你必须在每次页面运行时重新连接。

This is not as bad as it sounds if you can rely on connection pooling via mysql_pconnect(). When connecting, the function would first try to find a (persistent) link that's already open with the same host, username and password. If one is found, an identifier for it will be returned instead of opening a new connection. The connection to the SQL server will not be closed when the execution of the script ends. Instead, the link will remain open for future use (mysql_close() will not close links established by mysql_pconnect()).

如果您可以通过mysql_pconnect()依赖连接池，这并不像听起来那么糟糕。当连接时，函数将首先尝试找到一个(持久的)链接，该链接已经用相同的主机、用户名和密码打开。如果找到一个，它的标识符将被返回，而不是打开一个新的连接。当脚本执行结束时，不会关闭到SQL服务器的连接。相反，该链接将继续开放以供将来使用(mysql_close()将不会关闭由mysql_pconnect()建立的链接)。

Reference:

参考:

http://php.net/manual/en/function.mysql-pconnect.php

http://php.net/manual/en/function.mysql-pconnect.php

http://www.php.net/manual/en/intro.session.php

http://www.php.net/manual/en/intro.session.php

Can't pass mysqli connection in session in php

不能在php会话中传递sqmyli连接

#11

Use PDO supported by php 5 version instead mysql connect

使用php 5版本支持的PDO代替mysql连接

#12

Here is a simple class that selects the required database automatically when needed.

这是一个简单的类，在需要时自动选择所需的数据库。

class Database 
{
    private $host   = 'host';
    private $user   = 'root';
    private $pass   = 'pass';
    private $dbname = '';

    private $mysqli = null;

    function __construct() 
    {
        // dbname is not defined in constructor
        $this->mysqli = new mysqli( $this->host, $this->user, $this->pass );    
    }

    function __get( $dbname ) 
    {
        // if dbname is different, and select_db() is succesfull, save current dbname
        if ( $this->dbname !== $dbname && $this->mysqli->select_db( $dbname ) ) {
            $this->dbname = $dbname;
        }

        // return connection
        return $this->mysqli;
    }
}


// examples
$db = new Database();

$result = $db->db1->query( "SELECT DATABASE()" );
print_r( $result->fetch_row() );

$result = $db->db2->query( "SELECT DATABASE()" );
print_r( $result->fetch_row() );

$result = $db->{'dbname with spaces'}->query( "SELECT DATABASE()" );
print_r( $result->fetch_row() );

#13

$con1 = mysql_connect($hostname, $username, $password); 
$con2 = mysql_connect($hostname, $username, $password, true); 

mysql_select_db('database1', $con1);
mysql_select_db('database2', $con2);

Then to query database 1 pass the first link identifier:

然后查询数据库1，通过第一个链接标识符:

mysql_query('select * from tablename', $con1);

and for database 2 pass the second:

对于数据库2，通过第二个:

mysql_query('select * from tablename', $con2);

#14

if mysql's user have permission to two database , you can join two table from two database etc:

如果mysql用户有两个数据库的权限，您可以从两个数据库连接两个表，等等:

SELECT database1.table.title title1,database2.table.title title2 
FROM database1.table
INNER JOIN database2.table 
ON (database1.table.id=database2.table.id)