如何在不重新连接每个查询的情况下有效地连接到php中的mysql

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 (especially since 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.

谢谢，我真的很感谢你的回答。

2 个解决方案

#1

Normally connections happen once a page load. AKA

通常，一旦页面加载就会发生连接。 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_connect or mysql_pconnect. However, you should still only connect once per script.

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

#2

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:

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

class DB
{
  public static $adoConn;
}

// ...

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

#1