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

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.

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

Here is some code i tried:

下面是我试过的一些代码:

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

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.

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

4 个解决方案

#1

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.

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

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.
}

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):

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

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.
}

#2

I think it would be better if you put your values within a csv file and you use load data syntax.

我认为如果你把你的值放在csv文件中并且使用load数据语法会更好。

edit. Example

编辑。例子

Let's suppose you have a txt file with all your values

假设您有一个带有所有值的txt文件。

value1
value2
value3
and so on

Once you create your table structure

一旦创建了表结构

create table mytest (
id int not null auto_increment primary key,
myvalue varchar(50)
) engine = myisam;

and upload your txt file you can do something like this

上传你的txt文件你可以这样做

load data infile 'myfile.txt'
into table mytest (myvalue);

#3

If you can use MySQL transactions and fire off queries in batches of, say, several hundred at a time, it may improve the reliability and speed of the insert.

如果您可以使用MySQL事务，并在每次数百次的情况下触发查询，它可能会提高插入的可靠性和速度。

#4

I think @nick's answer is best - but an alternative maybe is to write SQL to create an SP that lists all the inserts, i.e. as a large string, send that across the wire, execute the SP, and delete it.

我认为@nick的答案是最好的——但另一种可能是编写SQL来创建一个SP，它列出所有的插入，例如，作为一个大字符串，通过网络发送，执行SP，并删除它。

I'm a MSSQL person myself and I would never recommend this - but it's the kind of crazy hack I've had to use in the pass when I couldn't rely on MSSQL's own brand of bulk upload functionality.

我自己就是一个MSSQL用户，我永远不会推荐这个——但是当我不能依赖MSSQL自己的批量上传功能时，我不得不在pass上使用这种疯狂的技巧。

Alternatively can you build multiple insert statements in SQL and send it as a single command to the DB? Again sorry not a mysql expert but this is very possible in other databases.

或者，您可以在SQL中构建多个insert语句并将其作为单个命令发送给DB吗?抱歉，我不是mysql专家，但这在其他数据库中是很可能的。

#1