php 通过php数组将多行插入到mysql中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/779986/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
insert multiple rows via a php array into mysql
提问by toofarsideways
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.
我正在使用插入命令通过 PHP 将一个大数据集传递到 MySQL 表中，我想知道是否可以通过查询一次插入大约 1000 行，而不是将每个值附加到一英里长的字符串的末尾然后执行它。我正在使用 CodeIgniter 框架，因此它的功能也可供我使用。
回答by staticsan
Assembling one INSERTstatement with multiple rows is much faster in MySQL than one INSERTstatement per row.
INSERT在 MySQL 中，将一条语句与多行组合在一起比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 中的字符串处理问题，这实际上是一个算法问题，而不是语言问题。基本上，在处理大字符串时，您希望尽量减少不必要的复制。首先，这意味着您要避免串联。构建大字符串（例如一次插入数百行）的最快且内存效率最高的方法是利用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));
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 oncein the implode()statement. This is a bigwin.
这种方法的优点是您不必复制和重新复制迄今为止用每个连接组装的 SQL 语句；相反，PHP在语句中执行一次implode()。这是一个巨大的胜利。
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.
如果您有很多列要放在一起，并且一个或多个列很长，您还可以构建一个内部循环来做同样的事情，并使用implode()将 values 子句分配给外部数组。
回答by Somnath Muluk
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 现在支持多插入/批量插入。我有同样的问题。虽然回答问题很晚，但它会帮助某人。这就是为什么要回答这个问题。
$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')
回答by Espresso_Boy
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）。
回答by Ross Carver
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.
插入多行时关闭自动提交会大大加快插入速度，因此将其关闭，然后如上所述执行，或者只是创建一个字符串（sqlCombined），它是由分号分隔的许多插入语句，多查询会很好地处理它们。
Hope this helps someone save time (searching and inserting!)
希望这可以帮助某人节省时间（搜索和插入！）
R
电阻
回答by vezult
You could always use mysql's LOAD DATA:
你总是可以使用 mysql 的LOAD DATA：
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 INSERTstatements.
进行批量插入而不是使用一堆INSERT语句。
回答by bdl
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().
回答by Nikunj Dhimar
$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));
回答by Abhishek Singh
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 的更多详细信息
回答by Kumar Rakesh
You can do it with several ways in codeigniter e.g.
您可以在 codeigniter 中使用多种方法来完成，例如
FirstBy 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));
回答by Waqas
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).
我创建了这个简单的功能，你们可以轻松使用。您需要将 table-name ($tbl), table-field传递($insertFieldsArr)给插入数据 data array ($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));
}
