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

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

254158 次浏览
小开

您可以使用 mysqli _ stmt 类为插入一行准备查询，然后在数据数组上迭代。比如:

$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();

其中‘ idsb’是要绑定的数据类型(int、 double、 string、 blob)。

小开

嗯，您不想执行1000个查询调用，但是这样做是可以的:

$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);

根据数据源的不同，填充数组可能非常简单，只需打开一个文件并通过 file()将内容转储到一个数组中。

小开

你可以使用 mysql 的 LOAD DATA:

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

进行批量插入，而不是使用一堆 INSERT语句。

小开
最佳答案

在 MySQL 中，用多行组装一个 INSERT语句要比每行组装一个 INSERT语句快得多。

也就是说，您可能会遇到 PHP 中的字符串处理问题，这实际上是一个算法问题，而不是语言问题。基本上，在处理大字符串时，您希望尽量减少不必要的复制。首先，这意味着您希望避免连接。构建大型字符串(例如在一个字符串中插入数百行)的最快和最有效的内存方法是利用 implode()函数和数组分配。

$sql = array();
foreach( $data as $row ) {
$sql[] = '("'.mysql_real_escape_string($row['text']).'", '.$row['category_id'].')';
}
mysql_query('INSERT INTO table (text, category) VALUES '.implode(',', $sql));

这种方法的优点是不需要复制和重新复制到目前为止用每个串联组装的 SQL 语句; 相反，PHP 在 implode()语句中执行这个 一次。这是 很大的胜利。

如果有很多列要放在一起，而且一个或多个列很长，那么还可以构建一个内部循环来完成同样的工作，并使用 implode()将 value 子句分配给外部数组。

小开

PHP5中的 mysqli 是一个具有一些很好的函数的对象，这些函数允许您加快上述答案的插入时间:

$mysqli->autocommit(FALSE);
$mysqli->multi_query($sqlCombined);
$mysqli->autocommit(TRUE);

在插入很多行时关闭自动提交会大大加快插入速度，所以关闭自动提交，然后像上面提到的那样执行，或者只是创建一个字符串(sqlComposite) ，这是由分号和多查询分隔的很多插入语句，可以很好地处理它们。

小开

CodeIgniter 现在支持多重插入/批处理插入。

$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')
小开 
$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));
小开

我已经创建了一个执行多行的类，其用法如下:

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

类别定义如下:

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 (++$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('<br/>', $this->_bigInsertQuery->errorInfo());
return false;
}
$this->_insertCount++;


$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++) $singleInsertData[] = array_pop($this->_currentlyInsertingRows); array_reverse($singleInsertData);
for ($i = 0; $i < $this->_numberOfFields; $i++)     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('<br/>', $this->_singleInsertQuery->errorInfo());
return false;
}
$this->_currentlyInsertingCount--;
}
}


public function getError() {
return $this->_error;
}
}
小开

在编码符中使用插入批处理插入多行数据。

$this->db->insert_batch('tabname',$data_array); // $data_array holds the value to be inserted
小开

你可以用几种方法来解决这个问题。

首先 By loop

foreach($myarray as $row)
{
$data = array("first"=>$row->first,"second"=>$row->sec);
$this->db->insert('table_name',$data);
}

第二个 ——通过插入批处理

$data = array(
array(
'first' => $myarray[0]['first'] ,
'second' => $myarray[0]['sec'],
),
array(
'first' => $myarray[1]['first'] ,
'second' => $myarray[1]['sec'],
),
);


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

第三种方法——通过多重值传递

$sql = array();
foreach( $myarray as $row ) {
$sql[] = '("'.mysql_real_escape_string($row['first']).'", '.$row['sec'].')';
}
mysql_query('INSERT INTO table (first, second) VALUES '.implode(',', $sql));
小开

我已经创建了这个简单的函数，你们可以很容易地使用。您将需要传递表名 ($tbl)、表字段 ($insertFieldsArr)和插入数据数组 ($arr)。

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++;
}
$strVals=rtrim($strVals,',');
$sql[] = '('.$strVals.')';
}


$fields=implode(',',$insertFieldsArr);
mysql_query('INSERT INTO `'.$tbl.'` ('.$fields.') VALUES '.implode(',', $sql));
}
小开

虽然现在回答这个问题已经太晚了，但是我的答案是一样的。

如果使用 CodeIgniter，则可以使用 query _ Builder 类中定义的内置方法。

$this-> db-> insert _ batch ()

根据所提供的数据生成插入字符串，并运行查询。可以向函数传递数组或对象。下面是一个使用数组的例子:

$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')

第一个参数包含表名，第二个参数是值的关联数组。

您可以找到关于 query _ Builder 给你的更多详细信息

小开

我不得不向一个表中插入超过14000行，结果发现使用 Mysqli 准备语句的行需要超过10分钟，而使用相同的 Mysqli 准备语句的字符串参数的参数解包只需不到10秒。我的数据非常重复，因为它是 id 的倍数和一个常量整数。

10分钟代码:

            $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++) {
$arg_one = $k;
$arg_two = $ent;
if($stmt->execute()) {
$cnt++;
}
}
}

10秒代码:

            $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++) {
$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();

提交答案