每次插入一条数据速度很慢，一次全部插入SQL语句过大，个人解决办法是每次插入指定条数

来源：互联网发布：微信埋雷软件多少钱编辑：程序博客网时间：2024/04/28 00:23

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<title>每次插入一条数据速度很慢，一次全部插入SQL语句过大，个人解决办法是每次插入指定条数</title>
</head>
<body>

<?php

$conn = mysql_connect ( "localhost", "root", "" );

if (! $conn) {
 echo "Unable to connect to DB: " . mysql_error ();
 exit ();
}

if (! mysql_select_db ( "dealdata" )) {
 echo "Unable to select mydbname: " . mysql_error ();
 exit ();
}
mysql_query("set names utf8");

$sql = "INSERT INTO `dealdata`.`a_tempdata` (`dataid` ,`city` ,`ptype` ,`type`,`title`,`tel`,`people`)VALUES ";

$sql_field=array();
 $str = file_get_contents ( "data/baixing.txt" );
 $str=str_replace("'", "''", $str);

 $arrlist = data2array ( $str );
 $len = count($arrlist);

 $index=intval($len);
 $per=10000;
 $index=0;
 $j=$k=$temp=0;
 //per控制一次插入多小数据，temp记录每次插入时的序号，j记录多小条数据，k相当是j%per
 for( $i=$index;$i<$len;$i++) {

 $pieces = explode ( "@---@", $arrlist[$i] );
 if(empty($pieces[0])){
 continue;
 }
 if(strlen($pieces[4])!=7){
 continue;
 }
 $j++;
 $k++;

 foreach ( $pieces as $key=>$item ) {
 $pieces[$key]=mysql_real_escape_string($pieces[$key]);
 }
 $tel=$pieces[4].$pieces[5];
 $sql_field[]= "(NULL , '".$pieces[0]."', '".$pieces[1]."', '".$pieces[2]."', '".$pieces[3]."', '".$tel."', '".$pieces[6]."')";
 if(1 && $k>=$per){
 $temp++;
 $k=0;
 $sql .= implode(",", $sql_field);

 $result = mysql_query ( $sql );
 if(!$result){
 echo "fail-----> $temp ";
 }

 $sql = "INSERT INTO `dealdata`.`a_tempdata` (`dataid` ,`city` ,`ptype` ,`type`,`title`,`tel`,`people`)VALUES ";
 $sql_field=array();
 }
 }
 echo "per控制一次插入多小数据，temp记录每次插入时的序号，j记录多小条数据，k相当是j%per ";
 echo "i:$i j:$j k:$k temp:$temp per:$per ";
 exit;

function data2array($data) {
 $arrlist = explode ( "\n", $data );
 if (! end ( $arrlist )) {
 array_pop ( $arrlist );
 }
 $arr = array ();
 $arr=$arrlist;
 return $arr;
}

?>

</body>

</html>

baixing.txt的数据格式如下