在远程数据库中插入 134675 个值的最快方法

2022-01-09 00:00:00 insert php mysql

我有一个包含超过 134675+ 个值的数组，我需要将它们插入到我的 mySQL 表中.我知道使用 PHP 和 mySQL 数据插入所需的所有内容.有没有一种快速的方法可以让我在 30-60 秒内将所有这些值插入到远程服务器上?因为当我尝试使用 foreach 方法时，页面超时.远程服务器不允许数据库连接持续超过 60 秒.我不知道为什么.所以请帮助我快速逻辑.

I have an array with more than 134675+ values, I need to insert them to my mySQL table. I know all the things needed in this to work with PHP and mySQL data insertion. Is there a fast method that would let me insert all these values on a remote server within 30-60 seconds? because when i am trying it with the foreach method, the page is timing out. The remote server is not allowing DB connections to persist for more than 60 seconds. I dont know why. So please help me with a fast logic.

这是我尝试过的一些代码:

Here is some code i tried:

foreach($array as $value)
{
    $sql="insert into collected values('".$value."')";
    $res=mysql_query($sql);
    //then some extra code.
}

注意我在服务器上没有这么多的访问权限.我的数据库帐户只能插入值，仅此而已.它是对 mySQL DB 的约束.我不能使用 CSV 或任何其他东西.

NOTE I dont have so many access privileges on the server. My DB account can only insert values and nothing more than that. And its a constraint on the mySQL DB. I cannot use CSV or any other thing.

推荐答案

您可以在循环中包含 mysql_ping() 函数.此函数检查以确保连接已打开，如果未打开，则重新连接.

You could include in your loop the mysql_ping() function. This function checks to make sure that the connection is open, and if it is not, it re-connects.

使用您自己的示例，您可以执行以下操作:

Using your own example, you could do something like:

foreach($array as $value) {
    mysql_ping($dbconn);
    $sql="insert into collected values('".$value."')";
    $res=mysql_query($sql);
    //then some extra code.
}

编辑:需要注意的是，根据文档，在 MySQL 5.0.14 之后，PHP 不会自动重新连接.如果您使用较新版本的 MySQL，则必须放入自己的连接逻辑，可能是这样的(我没有测试过):

Edit: It should be noted that according to the docs, after MySQL 5.0.14, PHP does not automatically reconnect. If you use a newer version of MySQL you will have to put in your own connection logic, maybe like this (I haven't tested):

function check_dbconn($connection) {
    if (!mysql_ping($connection)) {
        mysql_close($connection);
        $connection = mysql_connect('server', 'username', 'password');
        mysql_select_db('db',$connection);
    } 
    return $connection;
}

foreach($array as $value) {
    $dbconn = check_dbconn($dbconn);
    $sql="insert into collected values('".$value."')";
    $res=mysql_query($sql, $dbconn);
    //then some extra code.
}

相关文章