加快插入查询mysql [英] Speed up insert query mysql
本文介绍了加快插入查询mysql的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我想向现有表中插入大约10000个新行,该表已经包含10000行. 我需要获取查询的插入ID,并在循环内的另一个函数中使用该ID,如下代码所示.
I want to insert about 10000 new rows to an existing table which contain 10000 rows already. I need to get the insert id for the query and to use that id in another function inside the loop as in the code below.
foreach($values as $val){
$sql = "INSERT INTO wp_posts (`post_author`, `post_date`, `post_content`, `post_title`, `post_excerpt`, `post_status`,`post_modified`,`post_type`)
VALUES('".$val[author]."',NOW(),'".$val[content]."','".$val[title]."','".$val[excerpt]."','published',NOW(),'post')";
$result = mysql_query($sql);
$insertid = mysql_insert_id();
add_post_meta($insertid, $val[metakey], $val[metaval], true );
}
我正在从CSV文件中获取$ values数组的值.截至目前,我每3分钟只能插入20条新行. 无论如何,有什么办法可以加快插入速度?
I am fetching the values for $values array from a CSV file. As of now, I can insert only 20 new rows per 3 minutes. Is there anyway to speed up this insertion?
推荐答案
您可以尝试在PDO中使用准备好的语句,看看是否有帮助.
You could try using a prepared statement with PDO, see if it helps.
$sql = "INSERT INTO wp_posts (`post_author`, `post_date`, `post_content`, `post_title`, `post_excerpt`, `post_status`,`post_modified`,`post_type`)
VALUES(:author,NOW(),:content,:title,:excerpt,'published',NOW(),'post')";
$pdoStatement = $pdoConnexion->prepare($sql);
foreach($values as $val){
$pdoStatement->bindValue(':author', $val['author'], PDO::PARAM_STR);
$pdoStatement->bindValue(':content', $val['content'], PDO::PARAM_STR);
$pdoStatement->bindValue(':title', $val['title'], PDO::PARAM_STR);
$pdoStatement->bindValue(':excerpt', $val['excerpt'], PDO::PARAM_STR);
$pdoStatement->execute();
$insertid = $pdoConnexion->lastInsertId();
add_post_meta($insertid, $val[metakey], $val[metaval], true );
}
这篇关于加快插入查询mysql的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文