PHP数据库连接实践 [英] PHP Database connection practice

问题描述

我有一个连接到多个数据库（Oracle，MySQL和MSSQL）的脚本，每次脚本运行时可能不会使用每个数据库连接，但所有脚本都可以在单个脚本执行中使用。我的问题是，最好连接到所有的数据库一次在脚本的开头，即使所有的连接可能不使用，或者是更好地连接到他们的需要，唯一的抓住是，我需要使连接调用在循环中（因此数据库连接在循环中将是新的X次数）。

I have a script that connects to multiple databases (Oracle, MySQL and MSSQL), each database connection might not be used each time the script runs but all could be used in a single script execution. My question is, "Is it better to connect to all the databases once in the beginning of the script even though all the connections might not be used. Or is it better to connect to them as needed, the only catch is that I would need to have the connection call in a loop (so the database connection would be new for X amount of times in the loop).

示例代码＃1：

// Connections at the beginning of the script
$dbh_oracle = connect2db();
$dbh_mysql  = connect2db();
$dbh_mssql  = connect2db();

for ($i=1; $i<=5; $i++) {
   // NOTE: might not use all the connections
   $rs = queryDb($query,$dbh_*); // $dbh can be any of the 3 connections
}

示例代码＃2：

// Connections in the loop
for ($i=1; $i<=5; $i++) {
   // NOTE: Would use all the connections but connecting multiple times
   $dbh_oracle = connect2db();
   $dbh_mysql  = connect2db();
   $dbh_mssql  = connect2db();

   $rs_oracle = queryDb($query,$dbh_oracle);
   $rs_mysql  = queryDb($query,$dbh_mysql);
   $rs_mssql  = queryDb($query,$dbh_mssql);
}

现在我知道你可以使用持久连接，为每个数据库在循环以及？像 mysql_pconnect（）， mssql_pconnect（）和 adodb for Oracle持久连接方法。我知道持续连接也可以是资源池，因为我正在寻找最好的性能/实践。

now I know you could use a persistent connection but would that be one connection open for each database in the loop as well? Like mysql_pconnect(), mssql_pconnect() and adodb for Oracle persistent connection method. I know that persistent connection can also be resource hogs and as I'm looking for best performance/practice.

这是一个很好的帖子在持续连接可能会导致问题

Here is a good post on why persistent connections could cause problems

推荐答案

使用懒惰连接包装类：

class Connection
{
    private $pdo;
    private $dsn;

    public __construct($dsn)
    {
        $this->dsn = $dsn;
    }

    public query($sql)
    {
        //the connection will get established here if it hasn't been already
        if (is_null($this->pdo))
            $this->pdo = new PDO($this->dsn);

        //use pdo to do a query here

    }
}

我希望你已经使用PDO了。如果没有，你应该是。 PDO是独立于数据库的。如果你使用过程函数，你必须为每个数据库类型创建一个新类。

I hope you're already using PDO. If not, you should be. PDO is database independent. If you did this using procedural functions, you'd have to create a new class for each database type.

无论如何，这只是一个骨架例如，在 query（）中添加 $ params 选项，但您应该能够得到想法。仅当调用 query（）时才尝试连接。构建对象不会建立连接。

Anyways, this is just a skeleton (you'd want to add a $params option in query(), for example), but you should be able to get the idea. The connection is only attempted when you call query(). Constructing the object does not make a connection.

另外，考虑使用 Doctrine 。它有懒惰的连接，使生活更容易一般。

As an aside, consider using Doctrine. It has lazy connections and makes life easier in general.

这篇关于PHP数据库连接实践的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！