我目前正在使用这种类型的SQL在MySQL上插入多行值:
INSERT INTO `tbl` (`key1`,`key2`) VALUES ('r1v1','r1v2'),('r2v1','r2v2'),...
根据PDO的读取,使用预处理语句应该比静态查询更安全。
因此,我想知道是否有可能使用预处理语句生成“使用一条查询插入多行值”的功能。
如果是,请问我该如何实现?
我目前正在使用这种类型的SQL在MySQL上插入多行值:
INSERT INTO `tbl` (`key1`,`key2`) VALUES ('r1v1','r1v2'),('r2v1','r2v2'),...
根据PDO的读取,使用预处理语句应该比静态查询更安全。
因此,我想知道是否有可能使用预处理语句生成“使用一条查询插入多行值”的功能。
如果是,请问我该如何实现?
$data[] = ['valueA1', 'valueB1'];
$data[] = ['valueA2', 'valueB2'];
insert into table (fielda, fieldb, ... ) values (?,?...), (?,?...)....
// create the ?,? sequence for a single row
$values = str_repeat('?,', count($data[0]) - 1) . '?';
// construct the entire query
$sql = "INSERT INTO table (columnA, columnB) VALUES " .
// repeat the (?,?) sequence for each row
str_repeat("($values),", count($data) - 1) . "($values)";
$stmt = $pdo->prepare ($sql);
// execute with all values from $data
$stmt->execute(array_merge(...$data));
placeholders()
。在循环之前只需调用一次并使用 sizeof($datafields)
,然后将结果字符串附加到循环中的 $question_marks[]
中。 - AVIDeveloperINSERT INTO tbl_name
(colA, colB, colC)
VALUES (?, ?, ?), (?, ?, ?), (?, ?, ?) [,...]
ON DUPLICATE KEY UPDATE
即使在多行插入时也能按预期工作;追加此内容:
ON DUPLICATE KEY UPDATE colA = VALUES(colA), colB = VALUES(colB), colC = VALUES(colC)
您的PHP代码将遵循通常的$pdo->prepare($qry)
和$stmt->execute($params)
PDO调用。
$params
将是一个一维数组,包含要传递给INSERT
的所有值。
在上面的示例中,它应该包含9个元素;PDO将使用每组3个元素作为单个值行。(插入3行,每行3列= 9个元素的数组。)
下面的代码是为了清晰起见而编写的,而不是为了效率。如果需要,可以使用PHP的array_*()
函数来更好地映射或遍历数据。假设每个查询都是独立的事务,因此不需要显式事务。
假设:
$dataVals
- 多维数组,其中每个元素是要插入的一行值的一维数组// setup data values for PDO. No memory overhead thanks to copy-on-write
$dataToInsert = array();
foreach ($dataVals as $row) {
foreach($row as $val) {
$dataToInsert[] = $val;
}
}
$onDup = "ON DUPLICATE KEY UPDATE colA=VALUES(colA)"; // optional
// setup the placeholders - a fancy way to make the long "(?, ?, ?)..." string
$rowPlaces = '(' . implode(', ', array_fill(0, count($colNames), '?')) . ')';
$allPlaces = implode(', ', array_fill(0, count($dataVals), $rowPlaces));
$sql = "INSERT INTO `tblName` (`colA`, `colB, colC)" .
" VALUES $allPlaces ON DUPLICATE KEY UPDATE $onDup";
// and then the PHP PDO boilerplate
$stmt = $pdo->prepare ($sql);
$stmt->execute($dataToInsert);
$rowPlaces
不再必要:$allPlaces = implode(',', array_fill(0, count($dataVals), '('.str_pad('', (count($colNames)*2)-1, '?,').')'));
- Philvotes
ADD UNIQUE unique_index
(user
, email
, address
);中所示。 - Giuseppearray_push($dataToInsert, ...array_values($dataVals));
比foreach ($dataVals as $row => $data) {}
要快得多。 - Anis就我所知,许多用户建议通过迭代INSERT语句而不是构建单个字符串查询来完成操作。我决定运行一个只有两个字段和非常基本的插入语句的简单测试:
<?php
require('conn.php');
$fname = 'J';
$lname = 'M';
$time_start = microtime(true);
$stmt = $db->prepare('INSERT INTO table (FirstName, LastName) VALUES (:fname, :lname)');
for($i = 1; $i <= 10; $i++ ) {
$stmt->bindParam(':fname', $fname);
$stmt->bindParam(':lname', $lname);
$stmt->execute();
$fname .= 'O';
$lname .= 'A';
}
$time_end = microtime(true);
$time = $time_end - $time_start;
echo "Completed in ". $time ." seconds <hr>";
$fname2 = 'J';
$lname2 = 'M';
$time_start2 = microtime(true);
$qry = 'INSERT INTO table (FirstName, LastName) VALUES ';
$qry .= "(?,?), ";
$qry .= "(?,?), ";
$qry .= "(?,?), ";
$qry .= "(?,?), ";
$qry .= "(?,?), ";
$qry .= "(?,?), ";
$qry .= "(?,?), ";
$qry .= "(?,?), ";
$qry .= "(?,?), ";
$qry .= "(?,?)";
$stmt2 = $db->prepare($qry);
$values = array();
for($j = 1; $j<=10; $j++) {
$values2 = array($fname2, $lname2);
$values = array_merge($values,$values2);
$fname2 .= 'O';
$lname2 .= 'A';
}
$stmt2->execute($values);
$time_end2 = microtime(true);
$time2 = $time_end2 - $time_start2;
echo "Completed in ". $time2 ." seconds <hr>";
?>
虽然整个查询本身只需要毫秒级别的时间,但是后一个(单字符串)查询总是比较快,速度通常快8倍或更多。如果在更多的列上导入数千行数据,这种差异可能会变得非常大。
foreach($json_content as $datarow) { $id = $datarow[id]; $date = $datarow[date]; $row3 = $datarow[row3]; $row4 = $datarow[row4]; $row5 = $datarow[row5]; $row6 = $datarow[row6]; $row7= $datarow[row7]; // 现在执行 $databaseinsert->execute(); } // 结束循环
- Peterbind_param
语句?” - Peter(?,?)
,对吧? - NoobishProarray_merge(...$data)
一次性合并所有数组,而无需使用任何循环。$insert_values = array();
foreach($data as $d){
$question_marks[] = '(' . placeholders('?', sizeof($d)) . ')';
$insert_values = array_merge($insert_values, array_values($d));
}
//Note that these fields are empty, but the field count should match the fields in $datafields.
$data[] = array('','','','',... n );
//getting rid of array_merge()
array_push($insert_values, $value1, $value2, $value3 ... n );
function placeholders($text, $count=0, $separator=","){
$result = array();
if($count > 0){
for($x=0; $x<$count; $x++){
$result[] = $text;
}
}
return implode($separator, $result);
}
$pdo->beginTransaction();
foreach($data as $d){
$question_marks[] = '(' . placeholders('?', sizeof($d)) . ')';
}
$sql = "INSERT INTO table (" . implode(",", array_keys($datafield) ) . ") VALUES " . implode(',', $question_marks);
$stmt = $pdo->prepare($sql);
$stmt->execute($insert_values);
$pdo->commit();
array_push($data, ...array_values($row))
代替 $data = array_merge($data, array_values($row));
。速度更快。 - mpenarray_push()
在 PHP 4 中都可用。 - ZurabWebarray_push()
,而是因为 @Mark 使用了参数解包。注意那里的 ...array_values()
调用了吗? - mariano.iglesiasarray_values()
在 PHP 4 中也是可用的。不确定您所说的 argument unpacking
是否指的是它。 - ZurabWeb$stmt = $pdo->prepare('INSERT INTO foo VALUES(:v1_1, :v1_2, :v1_3),
(:v2_1, :v2_2, :v2_3),
(:v2_1, :v2_2, :v2_3)');
$stmt->bindValue(':v1_1', $data[0][0]);
$stmt->bindValue(':v1_2', $data[0][1]);
$stmt->bindValue(':v1_3', $data[0][2]);
// etc...
$stmt->execute();
或者:
$pdo->beginTransaction();
$stmt = $pdo->prepare('INSERT INTO foo VALUES(:a, :b, :c)');
foreach($data as $item)
{
$stmt->bindValue(':a', $item[0]);
$stmt->bindValue(':b', $item[1]);
$stmt->bindValue(':c', $item[2]);
$stmt->execute();
}
$pdo->commit();
$stmt->execute();
放在foreach循环之外吗? - bafromca你并不是用预处理语句的正确方式。
一个查询语句只能插入一行数据是可以接受的,因为你可以执行一个预处理语句,使用不同的参数多次插入数据。事实上,这是最大的优点之一,它允许你以高效、安全和方便的方式插入大量数据。
所以,你所提出的方案可能可以实现,至少对于固定数量的行来说是可以的,但几乎可以肯定的是,这不是你真正想要的。
//$array = array( '1','2','3','4','5', '1','2','3','4','5');
$arCount = count($array);
$rCount = ($arCount ? $arCount - 1 : 0);
$criteria = sprintf("(?,?,?,?,?)%s", str_repeat(",(?,?,?,?,?)", $rCount));
$sql = "INSERT INTO table(c1,c2,c3,c4,c5) VALUES$criteria";
当插入大约1000条记录时,您不想在只需要值计数时循环遍历每个记录进行插入。
$values = array();
foreach($workouts_id as $value){
$_value = "(".$value.",".$plan_id.")";
array_push($values,$_value);
}
$values_ = implode(",",$values);
$sql = "INSERT INTO plan_days(id,name) VALUES" . $values_."";
$stmt = $this->conn->prepare($sql);
$stmt->execute();
$workouts_id
,它可能具有意外数据的 $value
。你无法保证另一个开发人员在未来不会使这些数据不安全。因此,我认为最好使用PDO准备查询。 - Nikita_kharkov_ua这是我编写的一个类,可以进行多次插入并带有清除选项:
<?php
/**
* $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;
function __construct(\PDO $pdo, $tableName, $fieldsAsArray, $bigInsertCount = 100) {
$this->_numberOfFields = count($fieldsAsArray);
$insertIntoPortion = "INSERT 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;
}
}
根据我的实验,我发现在单个事务中使用多行值的mysql插入语句是最快的。
然而,如果数据太多,则mysql的max_allowed_packet
设置可能会限制具有多个值行的单个事务插入。因此,当数据大于mysql的max_allowed_packet
大小时,以下函数将失败:
singleTransactionInsertWithRollback
singleTransactionInsertWithPlaceholders
singleTransactionInsert
在插入大量数据的情况下,最成功的方法是transactionSpeed
方法,但它消耗的时间比上述方法更长。因此,为了解决这个问题,您可以将数据分成较小的块并多次调用单个事务插入,或者通过使用transactionSpeed
方法放弃执行速度。
以下是我的研究结果。
<?php
class SpeedTestClass
{
private $data;
private $pdo;
public function __construct()
{
$this->data = [];
$this->pdo = new \PDO('mysql:dbname=test_data', 'admin', 'admin');
if (!$this->pdo) {
die('Failed to connect to database');
}
}
public function createData()
{
$prefix = 'test';
$postfix = 'unicourt.com';
$salutations = ['Mr.', 'Ms.', 'Dr.', 'Mrs.'];
$csv[] = ['Salutation', 'First Name', 'Last Name', 'Email Address'];
for ($i = 0; $i < 100000; ++$i) {
$csv[] = [
$salutations[$i % \count($salutations)],
$prefix.$i,
$prefix.$i,
$prefix.$i.'@'.$postfix,
];
}
$this->data = $csv;
}
public function truncateTable()
{
$this->pdo->query('TRUNCATE TABLE `name`');
}
public function transactionSpeed()
{
$timer1 = microtime(true);
$this->pdo->beginTransaction();
$sql = 'INSERT INTO `name` (`first_name`, `last_name`) VALUES (:first_name, :last_name)';
$sth = $this->pdo->prepare($sql);
foreach (\array_slice($this->data, 1) as $values) {
$sth->execute([
':first_name' => $values[1],
':last_name' => $values[2],
]);
}
// $timer2 = microtime(true);
// echo 'Prepare Time: '.($timer2 - $timer1).PHP_EOL;
// $timer3 = microtime(true);
if (!$this->pdo->commit()) {
echo "Commit failed\n";
}
$timer4 = microtime(true);
// echo 'Commit Time: '.($timer4 - $timer3).PHP_EOL;
return $timer4 - $timer1;
}
public function autoCommitSpeed()
{
$timer1 = microtime(true);
$sql = 'INSERT INTO `name` (`first_name`, `last_name`) VALUES (:first_name, :last_name)';
$sth = $this->pdo->prepare($sql);
foreach (\array_slice($this->data, 1) as $values) {
$sth->execute([
':first_name' => $values[1],
':last_name' => $values[2],
]);
}
$timer2 = microtime(true);
return $timer2 - $timer1;
}
public function noBindAutoCommitSpeed()
{
$timer1 = microtime(true);
foreach (\array_slice($this->data, 1) as $values) {
$sth = $this->pdo->prepare("INSERT INTO `name` (`first_name`, `last_name`) VALUES ('{$values[1]}', '{$values[2]}')");
$sth->execute();
}
$timer2 = microtime(true);
return $timer2 - $timer1;
}
public function singleTransactionInsert()
{
$timer1 = microtime(true);
foreach (\array_slice($this->data, 1) as $values) {
$arr[] = "('{$values[1]}', '{$values[2]}')";
}
$sth = $this->pdo->prepare('INSERT INTO `name` (`first_name`, `last_name`) VALUES '.implode(', ', $arr));
$sth->execute();
$timer2 = microtime(true);
return $timer2 - $timer1;
}
public function singleTransactionInsertWithPlaceholders()
{
$placeholders = [];
$timer1 = microtime(true);
$sql = 'INSERT INTO `name` (`first_name`, `last_name`) VALUES ';
foreach (\array_slice($this->data, 1) as $values) {
$placeholders[] = '(?, ?)';
$arr[] = $values[1];
$arr[] = $values[2];
}
$sql .= implode(', ', $placeholders);
$sth = $this->pdo->prepare($sql);
$sth->execute($arr);
$timer2 = microtime(true);
return $timer2 - $timer1;
}
public function singleTransactionInsertWithRollback()
{
$placeholders = [];
$timer1 = microtime(true);
$sql = 'INSERT INTO `name` (`first_name`, `last_name`) VALUES ';
foreach (\array_slice($this->data, 1) as $values) {
$placeholders[] = '(?, ?)';
$arr[] = $values[1];
$arr[] = $values[2];
}
$sql .= implode(', ', $placeholders);
$this->pdo->beginTransaction();
$sth = $this->pdo->prepare($sql);
$sth->execute($arr);
$this->pdo->commit();
$timer2 = microtime(true);
return $timer2 - $timer1;
}
}
$s = new SpeedTestClass();
$s->createData();
$s->truncateTable();
echo "Time Spent for singleTransactionInsertWithRollback: {$s->singleTransactionInsertWithRollback()}".PHP_EOL;
$s->truncateTable();
echo "Time Spent for single Transaction Insert: {$s->singleTransactionInsert()}".PHP_EOL;
$s->truncateTable();
echo "Time Spent for single Transaction Insert With Placeholders: {$s->singleTransactionInsertWithPlaceholders()}".PHP_EOL;
$s->truncateTable();
echo "Time Spent for transaction: {$s->transactionSpeed()}".PHP_EOL;
$s->truncateTable();
echo "Time Spent for AutoCommit: {$s->noBindAutoCommitSpeed()}".PHP_EOL;
$s->truncateTable();
echo "Time Spent for autocommit with bind: {$s->autoCommitSpeed()}".PHP_EOL;
$s->truncateTable();
一个只包含两列的表格有 100,000 个条目的结果如下:
Original Answer 翻译成 "最初的回答"
$ php data.php
Time Spent for singleTransactionInsertWithRollback: 0.75147604942322
Time Spent for single Transaction Insert: 0.67445182800293
Time Spent for single Transaction Insert With Placeholders: 0.71131205558777
Time Spent for transaction: 8.0056409835815
Time Spent for AutoCommit: 35.4979159832
Time Spent for autocommit with bind: 33.303519010544
$stmt->execute($data);
的答案时要小心,详见 http://php.net/manual/en/pdostatement.execute.php#refsect1-pdostatement.execute-parameters 。基本上所有的参数都被作为字符串传递并验证。在构建查询后,只需循环遍历数据,并手动使用bindValue
或bindParam
方法,将类型作为第三个参数传递即可。 - MrMesees