您如何在 php 中有效地连接到 mysql 而无需重新连接每个查询

How do you efficiently connect to mysql in php without reconnecting on every query

提问by Gal

I'm pretty sick of having to rewrite my code every time I learn something new about php (like the fact that mysql connections cannot be passed around in a session as a handle).

每次我学习关于 php 的新东西时，我都不得不重写我的代码（比如 mysql 连接不能作为句柄在会话中传递这一事实），我感到非常厌烦。

How do you implement mysql connection in your projects? A lot of people have proposed "connection pooling", but after reading the manual i'm still lost. It's like: "connection pooling is mysql_pconnect!" - me: "and...? how is that any different in reality? can you pass around a mysql_pconnect in a session? why is this seemingly mysterious aura??"

你是如何在你的项目中实现mysql连接的？很多人提出了“连接池”，但在阅读手册后我仍然迷路。就像：“连接池是mysql_pconnect！” - 我：“还有……？这在现实中有什么不同？你能在会话中传递一个 mysql_pconnect 吗？为什么这看起来很神秘？？”

Let me explain my situation. I have a function called "query1":

让我解释一下我的情况。我有一个名为“query1”的函数：

function query1($query)
{
    $db = new mysql(HOST,USER,PASS,DBNAME);
    $result = $db->query($query);
    $db->close();
    return $result;
} 

This is seems like a squanderous and inefficient way of querying a db (especiallysince you need a mysql handle for functions like mysql_real_escape_string). What is the correct form to do it? Can someone please help me?

这似乎是查询数据库的一种浪费且低效的方式（特别是因为您需要一个 mysql 句柄来处理诸如 mysql_real_escape_string 之类的函数）。这样做的正确形式是什么？有人可以帮帮我吗？

Thank you I'd really appreciate a good honest answer.

谢谢你，我真的很感激一个好的诚实的答案。

回答by Tyler Carter

Normally connections happen once a page load. AKA

通常连接发生在页面加载后。又名

class Database{
    public function connect()
    {
         $this->connection = mysql_connect();
    }

    // This will be called at the end of the script.
    public function __destruct()
    {
        mysql_close($this->connection);
    }

    public function function query($query)
    {
        return mysql_query($query, $this->connection);
    }
}
$database = new Database;
$database->connect();

$database->query("INSERT INTO TABLE (`Name`) VALUES('Chacha')");

Basically, you open the connection in the beginning of the page, close it at the end page. Then, you can make various queries during the page and don't have to do anything to the connection.

基本上，您在页面开头打开连接，在结束页面关闭它。然后，您可以在页面期间进行各种查询，而不必对连接做任何事情。

You could even do the mysql_connect in the constructor as Erik suggest.

您甚至可以按照 Erik 的建议在构造函数中执行 mysql_connect。

---

---

To use the above using global variables (not suggested as it creates global state), you would do something like

要使用全局变量使用上述内容（不建议，因为它会创建全局状态），您可以执行类似的操作

Global $db;

$db = new Database;
// ... do startup stuff

function doSomething()
{
    Global $db;
    $db->query("Do Something");
}

---

---

Oh, and no one mentioned you don't have to pass around a parameter. Just connect

哦，没有人提到您不必传递参数。只需连接

mysql_connect();

Then, mysql_query will just use the last connection no matter what the scope is.

然后，无论作用域是什么，mysql_query 都将使用最后一个连接。

mysql_connect();

function doSomething()
{
    mysql_query("Do something");
}

---

---

Per the comments:

根据评论：

I think you should use mysql_pconnect() instead of mysql_connect(), because mysql_connect() doesn't use connection pooling. – nightcoder

我认为您应该使用 mysql_pconnect() 而不是 mysql_connect()，因为 mysql_connect() 不使用连接池。– 夜间编码器

You might want to consider whether you use mysql_connector mysql_pconnect. However, you should still only connect once per script.

您可能需要考虑是否使用mysql_connect或mysql_pconnect。但是，您仍然应该每个脚本只连接一次。

回答by nightcoder

You don't need to connect to the database in every function. You need to connect to the database when the script starts running and save connection object in global state. In any function you can use that connection object to query the database. Your connection object will be recreated for every time script is executed but it will be very fast, because special connection pool is used behind the scene, so connection will be done immediately (matter of microseconds, because actually connection was not even broken, it was saved in connection pool).

您不需要在每个函数中都连接到数据库。您需要在脚本开始运行时连接到数据库并将连接对象保存在全局状态。在任何函数中，您都可以使用该连接对象来查询数据库。每次执行脚本时都会重新创建您的连接对象，但它会非常快，因为在幕后使用了特殊的连接池，所以连接将立即完成（微秒的问题，因为实际上连接甚至没有断开，它是保存在连接池中）。

Here is the example you asked for:

这是您要求的示例：

// this code should be executed on every page/script load:
$adoConn = ADONewConnection(...);
$adoConn->PConnect(...);

// ...

//And then in any place you can just write:
global $adoConn;
$adoConn->ExecuteNonQuery("INSERT INTO test SET Value = 'Hello, world!'");

As for your question "how do I implement connection pool". You don't. It's maintained by the server behind the scene and used if you (or the PHP library for work with PHP) use mysql_pconnect() function.

至于你的问题“我如何实现连接池”。你没有。它由后台服务器维护，如果您（或用于 PHP 的 PHP 库）使用 mysql_pconnect() 函数，则使用它。

PS. If you are afraid to keep $adoConn as a global variable (I'm not) then you can create a class with a static property:

附注。如果您害怕将 $adoConn 保留为全局变量（我不是），那么您可以创建一个具有静态属性的类：

class DB
{
  public static $adoConn;
}

// ...

DB::$adoConn->ExecuteNonQuery(...);