通过php数组插入多行到mysql。

I'm passing a large dataset into a MySQL table via PHP using insert commands and I'm wondering if its 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.

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

13 个解决方案

#1

Assembling one INSERT statement with multiple rows is much faster in MySQL than one INSERT statement per row.

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

That said, it sounds like you might be running into string-handling problems in PHP, which is really an algorithm problem, not a language one. Basically, when working with large strings, you want to minimize unnecessary copying. Primarily, this means you want to avoid concatenation. The fastest and most memory efficient way to build a large string, such as for inserting hundreds of rows at one, is to take advantage of the implode() function and array assignment.

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

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

The advantage of this approach is that you don't copy and re-copy the SQL statement you've so far assembled with each concatenation; instead, PHP does this once in the implode() statement. This is a big win.

这种方法的优点是，您不必复制和重新复制到目前为止为每个连接组装的SQL语句;相反，PHP在内爆()语句中只执行一次。这是一个巨大的胜利。

If you have lots of columns to put together, and one or more are very long, you could also build an inner loop to do the same thing and use implode() to assign the values clause to the outer array.

如果有很多列要放在一起，并且一个或多个列非常长，您还可以构建一个内部循环来做同样的事情，并使用内爆()将values子句分配给外部数组。

#2

Multiple insert/ batch insert is now supported by codeigniter. I had same problem. Though it is very late for answering question, it will help somebody. That's why answering this question.

codeigniter现在支持多个insert/ batch insert。我有同样的问题。虽然现在回答问题已经很晚了，但它会对某些人有所帮助。这就是为什么要回答这个问题。

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

#3

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

您可以准备使用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();

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

其中'idsb'是要绑定的数据类型(int, double, string, blob)。

#4

I know this is an old query, but I was just reading and thought I'd add what I found elsewhere:

我知道这是一个老问题，但我只是读了一下，然后想把我在别处找到的东西加进去:

mysqli in PHP 5 is an ojbect with some good functions that will allow you to speed up the insertion time for the answer above:

PHP 5中的mysqli是一个具有一些很好的函数的ojbect，它将允许您加快插入时间，以便于上面的答案:

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

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.

当插入许多行时关闭autocommit会极大地加快插入速度，因此关闭它，然后执行上面提到的操作，或者创建一个字符串(sqlcombination)，其中有许多由半冒号分隔的insert语句，多查询可以很好地处理它们。

Hope this helps someone save time (searching and inserting!)

希望这能帮助人们节省时间(搜索和插入!)

R

R

#5

You could always use mysql's LOAD DATA:

你可以一直使用mysql的负载数据:

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

to do bulk inserts rather than using a bunch of INSERT statements.

做批量插入，而不是使用一堆插入语句。

#6

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

你不希望执行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);

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

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

#7

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

#8

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

我创建了一个执行多行的类，它使用如下:

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

where the class is defined as follows:

类的定义如下:

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;
    }
}

#9

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

在codeigniter中使用insert batch来插入多行数据。

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

#10

You can do it with several ways in codeigniter e.g.

你可以用codeigniter中的几种方法来做。

First By loop

首先循环

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

Second -- By insert batch

第二，插入批量

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

Third way -- By multiple value pass

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

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

#11

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

我已经创建了这个简单的函数，你们可以很容易地使用它。您将需要向插入数据传递表名($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));
}

#12

Although it is too late to answer this question. Here are my answer on the same.

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

If you are using CodeIgniter then you can use inbuilt methods defined in query_builder class.

如果您正在使用CodeIgniter，那么您可以使用在query_builder类中定义的内置方法。

$this->db->insert_batch()

$ this - > db - > insert_batch()

Generates an insert string based on the data you supply, and runs the query. You can either pass an array or an object to the function. Here is an example using an array:

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

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

The first parameter will contain the table name, the second is an associative array of values.

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

You can find more details about query_builder here

您可以在这里找到关于query_builder的更多细节

#13

use this in codeigniter for multiple data insertion


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