blocks|key|1360920|text|您可以使用mysqli_stmt类准备插入一行的查询，然后迭代数据数组。类似于：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1360921|$stmt+=++$db->stmt_init();
$stmt->prepare("INSERT+INTO+mytbl+(fld1,+fld2,+fld3,+fld4)+VALUES(?,+?,+?,+?)");
foreach($myarray+as+$row)
{
++++$stmt->bind_param('idsb',+$row['fld1'],+$row['fld2'],+$row['fld3'],+$row['fld4']);
++++$stmt->execute();
}
$stmt->close();|code-block|syntax|javascript|1360922|其中'idsb‘是要绑定的数据类型(int、double、string、blob)。|1360923|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

You could prepare the query for inserting one row using the mysqli_stmt class, and then iterate over the array of data. Something like:

<pre><code>$stmt = $db-&gt;stmt_init();
$stmt-&gt;prepare("INSERT INTO mytbl (fld1, fld2, fld3, fld4) VALUES(?, ?, ?, ?)");
foreach($myarray as $row)
{
 $stmt-&gt;bind_param('idsb', $row['fld1'], $row['fld2'], $row['fld3'], $row['fld4']);
 $stmt-&gt;execute();
}
$stmt-&gt;close();
</code></pre>

Where 'idsb' are the types of the data you're binding (int, double, string, blob).

blocks|key|1576203|text|虽然您不想执行1000个查询调用，但这样做也没问题：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1576204|$stmt=+array(+'array+of+statements'+);
$query=+'INSERT+INTO+yourtable+(col1,col2,col3)+VALUES+';
foreach(+$stmt+AS+$k+=>+$v+)+{
++$query.=+'('+.$v.+')';+//+NOTE:+you'll+have+to+change+to+suit
++if+(+$k+!==+sizeof($stmt)-1+)+$query.=+',+';
}
$r=+mysql_query($query);|code-block|syntax|javascript|1576205|根据数据源的不同，填充数组可能与通过file()打开文件并将内容转储到数组中一样简单。|offset|length|style|CODE|1576206|entityMap^0|0|0|I|6|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Q|8|@$I|R|J|S|K|L]]|9|@]|A|$]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

Well, you don't want to execute 1000 query calls, but doing this is fine:

<pre><code>$stmt= array( 'array of statements' );
$query= 'INSERT INTO yourtable (col1,col2,col3) VALUES ';
foreach( $stmt AS $k =&gt; $v ) {
 $query.= '(' .$v. ')'; // NOTE: you'll have to change to suit
 if ( $k !== sizeof($stmt)-1 ) $query.= ', ';
}
$r= mysql_query($query);
</code></pre>

Depending on your data source, populating the array might be as easy as opening a file and dumping the contents into an array via <code>file()</code>.

blocks|key|1576254|text|你可以随时使用mysql的LOAD+DATA|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1576255|LOAD+DATA+LOCAL+INFILE+'/full/path/to/file/foo.csv'+INTO+TABLE+`footable`+FIELDS+TERMINATED+BY+','+LINES+TERMINATED+BY+'\r\n'+|code-block|syntax|javascript|1576256|进行批量插入，而不是使用一堆INSERT语句。|1576257|entityMap^0|D|9|0|0|E|6|0^^$0|@$1|2|3|4|5|6|7|O|8|@$9|P|A|Q|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|R|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|S|8|@$9|T|A|U|B|C]]|D|@]|E|$]]|$1|M|3|-4|5|6|7|V|8|@]|D|@]|E|$]]]|N|$]]

You could always use mysql's <code>LOAD DATA</code>:

<pre><code>LOAD DATA LOCAL INFILE '/full/path/to/file/foo.csv' INTO TABLE `footable` FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n' 
</code></pre>

to do bulk inserts rather than using a bunch of <code>INSERT</code> statements.

blocks|key|1361067|text|PHP+5中的mysqli是一个对象，它具有一些很好的函数，可以帮助您加快上述答案的插入速度：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1361068|$mysqli->autocommit(FALSE);
$mysqli->multi_query($sqlCombined);
$mysqli->autocommit(TRUE);|code-block|syntax|javascript|1361069|当插入许多行时关闭自动提交会大大加快插入速度，所以关闭它，然后执行上面提到的，或者只生成一个字符串(sqlCombined)，它是由分号分隔的许多insert语句，多查询可以很好地处理它们。|1361070|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

mysqli in PHP 5 is an object with some good functions that will allow you to speed up the insertion time for the answer above:
<pre><code>$mysqli-&gt;autocommit(FALSE);
$mysqli-&gt;multi_query($sqlCombined);
$mysqli-&gt;autocommit(TRUE);
</code></pre>
Turning off autocommit when inserting many rows greatly speeds up insertion, so turn it off, then execute as mentioned above, or just make a string (sqlCombined) which is many insert statements separated by semi-colons and multi-query will handle them fine.

blocks|key|1361102|text|CodeIgniter现在支持多个插入/批插入。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1361103|$data+=+array(
+++array(
++++++'title'+=>+'My+title'+,
++++++'name'+=>+'My+Name'+,
++++++'date'+=>+'My+date'
+++),
+++array(
++++++'title'+=>+'Another+title'+,
++++++'name'+=>+'Another+Name'+,
++++++'date'+=>+'Another+date'
+++)
);

$this->db->insert_batch('mytable',+$data);

//+Produces:+INSERT+INTO+mytable+(title,+name,+date)+VALUES+('My+title',+'My+name',+'My+date'),+('Another+title',+'Another+name',+'Another+date')|code-block|syntax|javascript|1361104|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Multiple insert/ batch insert is now supported by CodeIgniter.
<pre class="lang-php prettyprint-override"><code>$data = array(
 array(
 'title' =&gt; 'My title' ,
 'name' =&gt; 'My Name' ,
 'date' =&gt; 'My date'
 ),
 array(
 'title' =&gt; 'Another title' ,
 'name' =&gt; 'Another Name' ,
 'date' =&gt; 'Another date'
 )
);

$this-&gt;db-&gt;insert_batch('mytable', $data);

// Produces: INSERT INTO mytable (title, name, date) VALUES ('My title', 'My name', 'My date'), ('Another title', 'Another name', 'Another date')
</code></pre>

blocks|key|1361114|text|$query=+array();+
foreach(+$your_data+as+$row+)+{
++++$query[]+=+'("'.mysql_real_escape_string($row['text']).'",+'.$row['category_id'].')';
}
mysql_query('INSERT+INTO+table+(text,+category)+VALUES+'.implode(',',+$query));|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1361115|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>$query= array(); 
foreach( $your_data as $row ) {
 $query[] = '("'.mysql_real_escape_string($row['text']).'", '.$row['category_id'].')';
}
mysql_query('INSERT INTO table (text, category) VALUES '.implode(',', $query));
</code></pre>

blocks|key|1361179|text|我已经创建了一个执行多行的类，它的用法如下：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1361180|$pdo->beginTransaction();
$pmi+=+new+PDOMultiLineInserter($pdo,+"foo",+array("a","b","c","e"),+10);
$pmi->insertRow($data);
//+....
$pmi->insertRow($data);
$pmi->purgeRemainingInserts();
$pdo->commit();|code-block|syntax|javascript|1361181|其中类的定义如下：|1361182|class+PDOMultiLineInserter+{
++++private+$_purgeAtCount;
++++private+$_bigInsertQuery,+$_singleInsertQuery;
++++private+$_currentlyInsertingRows++=+array();
++++private+$_currentlyInsertingCount+=+0;
++++private+$_numberOfFields;
++++private+$_error;
++++private+$_insertCount+=+0;

++++/**
+++++*+Create+a+PDOMultiLine+Insert+object.
+++++*
+++++*+@param+PDO+$pdo++++++++++++++The+PDO+connection
+++++*+@param+type+$tableName+++++++The+table+name
+++++*+@param+type+$fieldsAsArray+++An+array+of+the+fields+being+inserted
+++++*+@param+type+$bigInsertCount++How+many+rows+to+collect+before+performing+an+insert.
+++++*/
++++function+__construct(PDO+$pdo,+$tableName,+$fieldsAsArray,+$bigInsertCount+=+100)+{
++++++++$this->_numberOfFields+=+count($fieldsAsArray);
++++++++$insertIntoPortion+=+"REPLACE+INTO+`$tableName`+(`".implode("`,`",+$fieldsAsArray)."`)+VALUES";
++++++++$questionMarks++=+"+(?".str_repeat(",?",+$this->_numberOfFields+-+1).")";

++++++++$this->_purgeAtCount+=+$bigInsertCount;
++++++++$this->_bigInsertQuery++++=+$pdo->prepare($insertIntoPortion.$questionMarks.str_repeat(",+".$questionMarks,+$bigInsertCount+-+1));
++++++++$this->_singleInsertQuery+=+$pdo->prepare($insertIntoPortion.$questionMarks);
++++}

++++function+insertRow($rowData)+{
++++++++//+@todo+Compare+speed
++++++++//+$this->_currentlyInsertingRows+=+array_merge($this->_currentlyInsertingRows,+$rowData);
++++++++foreach($rowData+as+$v)+array_push($this->_currentlyInsertingRows,+$v);
++++++++//
++++++++if+(%2B%2B$this->_currentlyInsertingCount+==+$this->_purgeAtCount)+{
++++++++++++if+($this->_bigInsertQuery->execute($this->_currentlyInsertingRows)+===+FALSE)+{
++++++++++++++++$this->_error+=+"Failed+to+perform+a+multi-insert+(after+{$this->_insertCount}+inserts),+the+following+errors+occurred:".implode(' ',+$this->_bigInsertQuery->errorInfo());
++++++++++++++++return+false;
++++++++++++}
++++++++++++$this->_insertCount%2B%2B;

++++++++++++$this->_currentlyInsertingCount+=+0;
++++++++++++$this->_currentlyInsertingRows+=+array();
++++++++}
++++++++return+true;
++++}

++++function+purgeRemainingInserts()+{
++++++++while+($this->_currentlyInsertingCount+>+0)+{
++++++++++++$singleInsertData+=+array();
++++++++++++//+@todo+Compare+speed+-+http://www.evardsson.com/blog/2010/02/05/comparing-php-array_shift-to-array_pop/
++++++++++++//+for+($i+=+0;+$i+<+$this->_numberOfFields;+$i%2B%2B)+$singleInsertData[]+=+array_pop($this->_currentlyInsertingRows);+array_reverse($singleInsertData);
++++++++++++for+($i+=+0;+$i+<+$this->_numberOfFields;+$i%2B%2B)+++++array_unshift($singleInsertData,+array_pop($this->_currentlyInsertingRows));

++++++++++++if+($this->_singleInsertQuery->execute($singleInsertData)+===+FALSE)+{
++++++++++++++++$this->_error+=+"Failed+to+perform+a+small-insert+(whilst+purging+the+remaining+rows;+the+following+errors+occurred:".implode(' ',+$this->_singleInsertQuery->errorInfo());
++++++++++++++++return+false;
++++++++++++}
++++++++++++$this->_currentlyInsertingCount--;
++++++++}
++++}

++++public+function+getError()+{
++++++++return+$this->_error;
++++}
}|1361183|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

I have created a class that performs multi-line that is used as follows:

<pre><code>$pdo-&gt;beginTransaction();
$pmi = new PDOMultiLineInserter($pdo, "foo", array("a","b","c","e"), 10);
$pmi-&gt;insertRow($data);
// ....
$pmi-&gt;insertRow($data);
$pmi-&gt;purgeRemainingInserts();
$pdo-&gt;commit();
</code></pre>

where the class is defined as follows:

<pre><code>class PDOMultiLineInserter {
 private $_purgeAtCount;
 private $_bigInsertQuery, $_singleInsertQuery;
 private $_currentlyInsertingRows = array();
 private $_currentlyInsertingCount = 0;
 private $_numberOfFields;
 private $_error;
 private $_insertCount = 0;

 /**
 * Create a PDOMultiLine Insert object.
 *
 * @param PDO $pdo The PDO connection
 * @param type $tableName The table name
 * @param type $fieldsAsArray An array of the fields being inserted
 * @param type $bigInsertCount How many rows to collect before performing an insert.
 */
 function __construct(PDO $pdo, $tableName, $fieldsAsArray, $bigInsertCount = 100) {
 $this-&gt;_numberOfFields = count($fieldsAsArray);
 $insertIntoPortion = "REPLACE INTO `$tableName` (`".implode("`,`", $fieldsAsArray)."`) VALUES";
 $questionMarks = " (?".str_repeat(",?", $this-&gt;_numberOfFields - 1).")";

 $this-&gt;_purgeAtCount = $bigInsertCount;
 $this-&gt;_bigInsertQuery = $pdo-&gt;prepare($insertIntoPortion.$questionMarks.str_repeat(", ".$questionMarks, $bigInsertCount - 1));
 $this-&gt;_singleInsertQuery = $pdo-&gt;prepare($insertIntoPortion.$questionMarks);
 }

 function insertRow($rowData) {
 // @todo Compare speed
 // $this-&gt;_currentlyInsertingRows = array_merge($this-&gt;_currentlyInsertingRows, $rowData);
 foreach($rowData as $v) array_push($this-&gt;_currentlyInsertingRows, $v);
 //
 if (++$this-&gt;_currentlyInsertingCount == $this-&gt;_purgeAtCount) {
 if ($this-&gt;_bigInsertQuery-&gt;execute($this-&gt;_currentlyInsertingRows) === FALSE) {
 $this-&gt;_error = "Failed to perform a multi-insert (after {$this-&gt;_insertCount} inserts), the following errors occurred:".implode('&lt;br/&gt;', $this-&gt;_bigInsertQuery-&gt;errorInfo());
 return false;
 }
 $this-&gt;_insertCount++;

 $this-&gt;_currentlyInsertingCount = 0;
 $this-&gt;_currentlyInsertingRows = array();
 }
 return true;
 }

 function purgeRemainingInserts() {
 while ($this-&gt;_currentlyInsertingCount &gt; 0) {
 $singleInsertData = array();
 // @todo Compare speed - http://www.evardsson.com/blog/2010/02/05/comparing-php-array_shift-to-array_pop/
 // for ($i = 0; $i &lt; $this-&gt;_numberOfFields; $i++) $singleInsertData[] = array_pop($this-&gt;_currentlyInsertingRows); array_reverse($singleInsertData);
 for ($i = 0; $i &lt; $this-&gt;_numberOfFields; $i++) array_unshift($singleInsertData, array_pop($this-&gt;_currentlyInsertingRows));

 if ($this-&gt;_singleInsertQuery-&gt;execute($singleInsertData) === FALSE) {
 $this-&gt;_error = "Failed to perform a small-insert (whilst purging the remaining rows; the following errors occurred:".implode('&lt;br/&gt;', $this-&gt;_singleInsertQuery-&gt;errorInfo());
 return false;
 }
 $this-&gt;_currentlyInsertingCount--;
 }
 }

 public function getError() {
 return $this-&gt;_error;
 }
}
</code></pre>

blocks|key|1361231|text|在codeigniter中使用insert+batch插入多行数据。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1361232|$this->db->insert_batch('tabname',$data_array);+//+$data_array+holds+the+value+to+be+inserted|code-block|syntax|javascript|1361233|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Use insert batch in codeigniter to insert multiple row of data.

<pre><code>$this-&gt;db-&gt;insert_batch('tabname',$data_array); // $data_array holds the value to be inserted
</code></pre>

blocks|key|1361286|text|我已经创建了这个简单的函数，你们可以很容易地使用。您将需要针对插入数据、数据数组($arr)传递表名($tbl)、表字段($insertFieldsArr)。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1361287|insert_batch('table',array('field1','field2'),$dataArray);

++++function+insert_batch($tbl,$insertFieldsArr,$arr){+$sql+=+array();+
++++foreach(+$arr+as+$row+)+{
++++++++$strVals='';
++++++++$cnt=0;
++++++++foreach($insertFieldsArr+as+$key=>$val){
++++++++++++if(is_array($row)){
++++++++++++++++$strVals.="'".mysql_real_escape_string($row[$cnt]).'\',';
++++++++++++}
++++++++++++else{
++++++++++++++++$strVals.="'".mysql_real_escape_string($row).'\',';
++++++++++++}
++++++++++++$cnt%2B%2B;
++++++++}
++++++++$strVals=rtrim($strVals,',');
++++++++$sql[]+=+'('.$strVals.')';
++++}

++++$fields=implode(',',$insertFieldsArr);
++++mysql_query('INSERT+INTO+`'.$tbl.'`+('.$fields.')+VALUES+'.implode(',',+$sql));
}|code-block|syntax|javascript|1361288|entityMap^0|14|6|1E|6|1O|I|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@$9|N|A|O|B|C]|$9|P|A|Q|B|C]|$9|R|A|S|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|T|8|@]|D|@]|E|$I|J]]|$1|K|3|-4|5|6|7|U|8|@]|D|@]|E|$]]]|L|$]]

I have created this simple function which you guys can use easily. You will need to pass the table-name <code>($tbl)</code>, table-field <code>($insertFieldsArr)</code> against your inserting data, data array <code>($arr)</code>.

<pre><code>insert_batch('table',array('field1','field2'),$dataArray);

 function insert_batch($tbl,$insertFieldsArr,$arr){ $sql = array(); 
 foreach( $arr as $row ) {
 $strVals='';
 $cnt=0;
 foreach($insertFieldsArr as $key=&gt;$val){
 if(is_array($row)){
 $strVals.="'".mysql_real_escape_string($row[$cnt]).'\',';
 }
 else{
 $strVals.="'".mysql_real_escape_string($row).'\',';
 }
 $cnt++;
 }
 $strVals=rtrim($strVals,',');
 $sql[] = '('.$strVals.')';
 }

 $fields=implode(',',$insertFieldsArr);
 mysql_query('INSERT INTO `'.$tbl.'` ('.$fields.') VALUES '.implode(',', $sql));
}
</code></pre>

blocks|key|1576517|text|我不得不将超过14000行插入到一个表中，并发现使用Mysqli预准备语句进行行对行需要花费十多分钟，而对于相同的Mysqli预准备语句，使用字符串参数进行参数解包只需不到10秒。我的数据非常重复，因为它是id的倍数和一个常量整数。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1576518|10分钟代码：|1576519|++++++++++++$num+=+1000;
++++++++++++$ent+=+4;
++++++++++++$value+=+['id'+=>+1,
++++++++++++++++++++++'id'+=>+2,
++++++++++++++++++++++'id'+=>+3,
++++++++++++++++++++++'id'+=>+4,
++++++++++++++++++++++'id'+=>+5,
++++++++++++++++++++++'id'+=>+6,
++++++++++++++++++++++'id'+=>+7,
++++++++++++++++++++++'id'+=>+8,
++++++++++++++++++++++'id'+=>+9,
++++++++++++++++++++++'id'+=>+10,
++++++++++++++++++++++'id'+=>+11,
++++++++++++++++++++++'id'+=>+12,
++++++++++++++++++++++'id'+=>+13,
++++++++++++++++++++++'id'+=>+14];
++++++++++++$cnt+=+0;
++++++++++++$query+=+"INSERT+INTO+table+(col1,+col2)+VALUES+(?,?)";
++++++++++++$stmt+=+$this->db->prepare($query);
++++++++++++$stmt->bind_param('ii',+$arg_one,$arg_two);
++++++++++++++++++++foreach+($value+as+$k+=>+$val)+{
+++++++++++++++++++++++++for+($i=0;+$i+<+$num;+$i%2B%2B)+{+
++++++++++++++++++++++++++++$arg_one+=+$k;
++++++++++++++++++++++++++++$arg_two+=+$ent;
++++++++++++++++++++++++++++if($stmt->execute())+{
++++++++++++++++++++++++++++++++$cnt%2B%2B;
++++++++++++++++++++++++++++}
++++++++++++++++++++++++}
++++++++++++++++++++}|code-block|syntax|javascript|1576520|10秒代码：|1576521|++++++++++++$ent+=+4;
++++++++++++$num+=+1000;
++++++++++++$value+=+['id'+=>+1,
++++++++++++++++++++++'id'+=>+2,
++++++++++++++++++++++'id'+=>+3,
++++++++++++++++++++++'id'+=>+4,
++++++++++++++++++++++'id'+=>+5,
++++++++++++++++++++++'id'+=>+6,
++++++++++++++++++++++'id'+=>+7,
++++++++++++++++++++++'id'+=>+8,
++++++++++++++++++++++'id'+=>+9,
++++++++++++++++++++++'id'+=>+10,
++++++++++++++++++++++'id'+=>+11,
++++++++++++++++++++++'id'+=>+12,
++++++++++++++++++++++'id'+=>+13,
++++++++++++++++++++++'id'+=>+14];
+++++++++++++$newdat+=+[];
+++++++++++++foreach+($value+as+$k+=>+$val)+{
+++++++++++++++++for+($i=0;+$i+<+$num;+$i%2B%2B)+{
++++++++++++++++++++$newdat[]+=+$val;
++++++++++++++++++++$newdat[]+=+$ent;
+++++++++++++++++}
+++++++++++++}
++++++++++++//+create+string+of+data+types
++++++++++++$cnt+=+count($newdat);
++++++++++++$param+=+str_repeat('i',$cnt);
++++++++++++//+create+string+of+question+marks
++++++++++++$rec+=+(count($newdat)+==+0)+?+0+:+$cnt+/+2+-+1;
++++++++++++$id_q+=+str_repeat('(?,?),',+$rec)+.+'(?,?)';
++++++++++++//+insert
++++++++++++$query+=+"INSERT+INTO+table+(col1,+col2)+VALUES+$id_q";
++++++++++++$stmt+=+$db->prepare($query);
++++++++++++$stmt->bind_param($param,+...$newdat);
++++++++++++$stmt->execute();|1576522|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|P|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|Q|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|R|8|@]|9|@]|A|$]]|$1|K|3|L|5|F|7|S|8|@]|9|@]|A|$G|H]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

I had to INSERT more than 14000 rows into a table and found that line for line with Mysqli prepared statements took more than ten minutes, while argument unpacking with string parameters for the same Mysqli prepared statements did it in less than 10 seconds. My data was very repetitive as it was multiples of id's and one constant integer.
10 minutes code:
<pre class="lang-php prettyprint-override"><code> $num = 1000;
 $ent = 4;
 $value = ['id' =&gt; 1,
 'id' =&gt; 2,
 'id' =&gt; 3,
 'id' =&gt; 4,
 'id' =&gt; 5,
 'id' =&gt; 6,
 'id' =&gt; 7,
 'id' =&gt; 8,
 'id' =&gt; 9,
 'id' =&gt; 10,
 'id' =&gt; 11,
 'id' =&gt; 12,
 'id' =&gt; 13,
 'id' =&gt; 14];
 $cnt = 0;
 $query = &quot;INSERT INTO table (col1, col2) VALUES (?,?)&quot;;
 $stmt = $this-&gt;db-&gt;prepare($query);
 $stmt-&gt;bind_param('ii', $arg_one,$arg_two);
 foreach ($value as $k =&gt; $val) {
 for ($i=0; $i &lt; $num; $i++) { 
 $arg_one = $k;
 $arg_two = $ent;
 if($stmt-&gt;execute()) {
 $cnt++;
 }
 }
 }
</code></pre>
10 second code:
<pre class="lang-php prettyprint-override"><code> $ent = 4;
 $num = 1000;
 $value = ['id' =&gt; 1,
 'id' =&gt; 2,
 'id' =&gt; 3,
 'id' =&gt; 4,
 'id' =&gt; 5,
 'id' =&gt; 6,
 'id' =&gt; 7,
 'id' =&gt; 8,
 'id' =&gt; 9,
 'id' =&gt; 10,
 'id' =&gt; 11,
 'id' =&gt; 12,
 'id' =&gt; 13,
 'id' =&gt; 14];
 $newdat = [];
 foreach ($value as $k =&gt; $val) {
 for ($i=0; $i &lt; $num; $i++) {
 $newdat[] = $val;
 $newdat[] = $ent;
 }
 }
 // create string of data types
 $cnt = count($newdat);
 $param = str_repeat('i',$cnt);
 // create string of question marks
 $rec = (count($newdat) == 0) ? 0 : $cnt / 2 - 1;
 $id_q = str_repeat('(?,?),', $rec) . '(?,?)';
 // insert
 $query = &quot;INSERT INTO table (col1, col2) VALUES $id_q&quot;;
 $stmt = $db-&gt;prepare($query);
 $stmt-&gt;bind_param($param, ...$newdat);
 $stmt-&gt;execute();
</code></pre>

I'm passing a large dataset into a MySQL table via PHP using insert commands and I'm wondering if it's possible to insert approximately 1000 rows at a time via a query other than appending each value on the end of a mile-long string and then executing it. I am using the CodeIgniter framework so its functions are also available to me.

How to insert multiple rows from array using CodeIgniter framework?

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

我正在使用insert命令通过PHP将一个大型数据集传递到一个MySQL表中，我想知道是否可以通过一个查询一次插入大约1000行，而不是将每个值附加到一个一英里长的字符串的末尾，然后执行它。我使用的是CodeIgniter框架，所以我也可以使用它的功能。

问如何使用CodeIgniter框架从数组中插入多行？
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何使用CodeIgniter框架从数组中插入多行？EN