在远程数据库中插入134675值的最快方法 [英] Fastest way to insert 134675 values in remote database
问题描述
我有一个包含超过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.
}
这篇关于在远程数据库中插入134675值的最快方法的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!