PDO Prepared Вставляет несколько строк в один запрос

Вставка нескольких значений с помощью подготовленных заявлений PDO

Вставка нескольких значений в один оператор выполнения. Почему, потому что согласно этой странице это быстрее, чем обычные вставки.

 $datafields = array('fielda', 'fieldb', ... ); $data[] = array('fielda' => 'value', 'fieldb' => 'value' ....); $data[] = array('fielda' => 'value', 'fieldb' => 'value' ....); 

больше данных или вы, вероятно, имеете цикл, который заполняет данные.

С подготовленными вставками вам нужно знать поля, в которые вы вставляете, и количество полей для создания? заполнители для привязки ваших параметров.

 insert into table (fielda, fieldb, ... ) values (?,?...), (?,?...).... 

Это в основном то, как мы хотим, чтобы выражение insert выглядело.

Теперь, код:

 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(); // also helps speed up your inserts. $insert_values = array(); foreach($data as $d){ $question_marks[] = '(' . placeholders('?', sizeof($d)) . ')'; $insert_values = array_merge($insert_values, array_values($d)); } $sql = "INSERT INTO table (" . implode(",", $datafields ) . ") VALUES " . implode(',', $question_marks); $stmt = $pdo->prepare ($sql); try { $stmt->execute($insert_values); } catch (PDOException $e){ echo $e->getMessage(); } $pdo->commit(); 

Хотя в моем тесте разница в 1 с была при использовании нескольких вставок и регулярных подготовленных вставок с одним значением.

Тот же ответ, что и г-н Балагтас, немного яснее …

Последние версии MySQL и PHP PDO поддерживают многострочные INSERT .

Обзор SQL

SQL будет выглядеть примерно так, предполагая таблицу с тремя столбцами, в которую вы хотели бы INSERT .

 INSERT INTO tbl_name (colA, colB, colC) VALUES (?, ?, ?), (?, ?, ?), (?, ?, ?) [,...] 

ON DUPLICATE KEY UPDATE работает, как ожидалось, даже с многорядным INSERT; добавьте это:

 ON DUPLICATE KEY UPDATE colA = VALUES(colA), colB = VALUES(colB), colC = VALUES(colC) 

Обзор PHP

Ваш PHP-код будет следовать обычным $pdo->prepare($qry) и $stmt->execute($params) .

$params будет 1-мерным массивом всех значений, передаваемых в INSERT .

В приведенном выше примере он должен содержать 9 элементов; PDO будет использовать каждый набор из 3 как одну строку значений. (Вставка 3 строк по 3 столбца каждый = 9 массивов элементов.)

Реализация

Ниже код написан для ясности, а не эффективности. Работайте с функциями array_*() PHP array_*() для улучшения способов отображения или просмотра ваших данных, если хотите. Независимо от того, используете ли вы транзакции, очевидно, зависит от типа таблицы MySQL.

Предполагая, что:

• $tblName – имя строки таблицы для INSERT для
• $colNames – 1-мерный массив имен столбцов таблицы. Эти имена столбцов должны быть действительными идентификаторами столбцов MySQL; избегать их с обратными шагами (“), если они не
• $dataVals – $dataVals -мерный массив, где каждый элемент представляет собой 1-й массив строки значений для INSERT

Образец кода

 // setup data values for PDO // memory warning: this is creating a copy all of $dataVals $dataToInsert = array(); foreach ($dataVals as $row => $data) { foreach($data as $val) { $dataToInsert[] = $val; } } // (optional) setup the ON DUPLICATE column names $updateCols = array(); foreach ($colNames as $curCol) { $updateCols[] = $curCol . " = VALUES($curCol)"; } $onDup = implode(', ', $updateCols); // 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 (" . implode(', ', $colNames) . ") VALUES " . $allPlaces . " ON DUPLICATE KEY UPDATE $onDup"; // and then the PHP PDO boilerplate $stmt = $pdo->prepare ($sql); try { $stmt->execute($dataToInsert); } catch (PDOException $e){ echo $e->getMessage(); } $pdo->commit(); 

Для чего это стоит, я видел, что многие пользователи рекомендуют выполнять итерацию с помощью инструкций 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 раз быстрее и более. Если бы это было построено, чтобы сказать, отражают импорт тысяч строк на многие другие столбцы, разница может быть огромной.

Принятый ответ Герберта Балагтаса хорошо работает, когда массив $ data мал. При больших массивах данных функция array_merge становится слишком медленной. Мой тестовый файл для создания массива $ data имеет 28 столбцов и составляет около 80 000 строк. Окончательный сценарий занял 41-е место .

Использование array_push () для создания $ insert_values ​​вместо array_merge () привело к 100-кратному ускорению с временем выполнения 0,41 .

Проблемная array_merge ():

 $insert_values = array(); foreach($data as $d){ $question_marks[] = '(' . placeholders('?', sizeof($d)) . ')'; $insert_values = array_merge($insert_values, array_values($d)); } 

Чтобы устранить необходимость в array_merge (), вместо этого вы можете построить следующие два массива:

 //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); try { $stmt->execute($insert_values); } catch (PDOException $e){ echo $e->getMessage(); } $pdo->commit(); 

Это просто не так, как вы используете подготовленные заявления.

Вполне нормально вставлять одну строку для каждого запроса, потому что вы можете выполнить один подготовленный оператор несколько раз с разными параметрами. На самом деле это одно из самых больших преимуществ, так как позволяет вам вставлять вам большое количество рядов эффективным, безопасным и удобным способом.

Поэтому возможно реализовать схему, которую вы предлагаете, по крайней мере для фиксированного количества строк, но почти гарантировано, что это не то, что вы хотите.

Два возможных подхода:

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

Или:

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

Если данные для всех строк находятся в одном массиве, я бы использовал второе решение.

 $stmt = $db->prepare('INSERT INTO ids VALUES (0, :url)'); try { $db->beginTransaction(); foreach ($ursl as $url) { $stmt->bindValue(':url', $url); $stmt->execute(); } $db->commit(); } catch (PDOException $e) { $db->rollBack(); } 

Более короткий ответ: сгладить массив данных, упорядоченных по столбцам, тогда

 //$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 или около того записей вам не нужно прокручивать каждую запись, чтобы вставить их, когда все, что вам нужно, – это количество значений.

Вот класс, который я написал, делаю несколько вложений с опцией очистки:

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

Вот мой простой подход.

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

Вот как я это сделал:

Сначала определите имена колонок, которые вы будете использовать, или оставьте поле пустым, и pdo предположит, что вы хотите использовать все столбцы в таблице – в этом случае вам нужно будет сообщить значения строк в том порядке, в котором они отображаются в таблице ,

 $cols = 'name', 'middleName', 'eMail'; $table = 'people'; 

Теперь предположим, что у вас уже подготовлен двухмерный массив. Итерируйте его и постройте строку со значениями в строке:

 foreach ( $people as $person ) { if(! $rowVals ) { $rows = '(' . "'$name'" . ',' . "'$middleName'" . ',' . "'$eMail'" . ')'; } else { $rowVals = '(' . "'$name'" . ',' . "'$middleName'" . ',' . "'$eMail'" . ')'; } 

Теперь то, что вы только что сделали, это проверить, были ли уже определены строки $, а если нет, создайте его и сохраните значения строк и необходимый синтаксис SQL, чтобы он был действительным. Обратите внимание, что строки должны входить в двойные кавычки и одинарные кавычки, поэтому они будут незамедлительно распознаваться как таковые.

Все, что осталось сделать, это подготовить заявление и выполнить, как таковой:

 $stmt = $db->prepare ( "INSERT INTO $table $cols VALUES $rowVals" ); $stmt->execute (); 

Протестировано до 2000 строк до сих пор, а время исполнения удручающее. Запустит еще несколько тестов и вернется сюда, если у меня есть что-то еще, чтобы внести свой вклад.

С уважением.

Вы можете вставить несколько строк в один запрос с помощью этой функции:

 function insertMultiple($query,$rows) { if (count($rows)>0) { $args = array_fill(0, count($rows[0]), '?'); $params = array(); foreach($rows as $row) { $values[] = "(".implode(',', $args).")"; foreach($row as $value) { $params[] = $value; } } $query = $query." VALUES ".implode(',', $values); $stmt = $PDO->prepare($query); $stmt->execute($params); } } 

$ row – массив массивов значений. В вашем случае вы вызываете функцию с помощью

 insertMultiple("INSERT INTO tbl (`key1`,`key2`)",array(array('r1v1','r1v2'),array('r2v1','r2v2'))); 

Это имеет то преимущество, что вы используете подготовленные инструкции , вставляя несколько строк с одним запросом. Безопасность!

Это сработало для меня

  $sql = 'INSERT INTO table(pk_pk1,pk_pk2,date,pk_3) VALUES '; $qPart = array_fill(0, count($array), "(?, ?,UTC_TIMESTAMP(),?)"); $sql .= implode(",", $qPart); $stmt = DB::prepare('base', $sql); $i = 1; foreach ($array as $value) { $stmt->bindValue($i++, $value); $stmt->bindValue($i++, $pk_pk1); $stmt->bindValue($i++, $pk_pk2); $stmt->bindValue($i++, $pk_pk3); } $stmt->execute(); 

Вот мое решение: https://github.com/sasha-ch/Aura.Sql на основе библиотеки auraphp / Aura.Sql.

Пример использования:

 $q = "insert into t2(id,name) values (?,?), ... on duplicate key update name=name"; $bind_values = [ [[1,'str1'],[2,'str2']] ]; $pdo->perform($q, $bind_values); 

Бугрэпорты приветствуются.

Пример моего реального мира, чтобы вставить все немецкие почтовые индексы в пустую таблицу (чтобы добавить названия городов позже):

 // obtain column template $stmt = $db->prepare('SHOW COLUMNS FROM towns'); $stmt->execute(); $columns = array_fill_keys(array_values($stmt->fetchAll(PDO::FETCH_COLUMN)), null); // multiple INSERT $postcode = '01000';// smallest german postcode while ($postcode <= 99999) {// highest german postcode $values = array(); while ($postcode <= 99999) { // reset row $row = $columns; // now fill our row with data $row['postcode'] = sprintf('%05d', $postcode); // build INSERT array foreach ($row as $value) { $values[] = $value; } $postcode++; // avoid memory kill if (!($postcode % 10000)) { break; } } // build query $count_columns = count($columns); $placeholder = ',(' . substr(str_repeat(',?', $count_columns), 1) . ')';//,(?,?,?) $placeholder_group = substr(str_repeat($placeholder, count($values) / $count_columns), 1);//(?,?,?),(?,?,?)... $into_columns = implode(',', array_keys($columns));//col1,col2,col3 // this part is optional: $on_duplicate = array(); foreach ($columns as $column => $row) { $on_duplicate[] = $column; $on_duplicate[] = $column; } $on_duplicate = ' ON DUPLICATE KEY UPDATE' . vsprintf(substr(str_repeat(', %s = VALUES(%s)', $count_columns), 1), $on_duplicate); // execute query $stmt = $db->prepare('INSERT INTO towns (' . $into_columns . ') VALUES' . $placeholder_group . $on_duplicate);//INSERT INTO towns (col1,col2,col3) VALUES(?,?,?),(?,?,?)... {ON DUPLICATE...} $stmt->execute($values); } 

Как вы можете видеть, он полностью гибкий. Вам не нужно проверять количество столбцов или проверять, в какой позиции находится ваша колонка. Вам нужно только установить данные вставки:

  $row['postcode'] = sprintf('%05d', $postcode); 

Я горжусь некоторыми конструкторами строкой запроса, поскольку они работают без тяжелых массивных функций, таких как array_merge. Особенно vsprintf () была хорошей находкой.

Наконец, мне нужно было добавить 2x while (), чтобы избежать превышения предела памяти. Это зависит от вашего предела памяти, но при этом его общее общее решение, чтобы избежать проблем (и 10 запросов по-прежнему намного лучше, чем 10.000).

Поскольку это еще не было предложено, я уверен, что LOAD DATA INFILE по-прежнему является самым быстрым способом загрузки данных, поскольку он отключает индексирование, вставляет все данные и затем снова включает индексы – все в одном запросе.

Сохранение данных как csv должно быть довольно тривиальным, имея в виду fputcsv. MyISAM работает быстрее, но вы все равно получаете большую производительность в InnoDB. Есть и другие недостатки, хотя я бы пошел по этому маршруту, если вы вставляете много данных и не беспокоитесь о том, что у вас меньше 100 строк.

Большинство решений, приведенных здесь для создания подготовленного запроса, являются более сложными, чем они должны быть. Используя встроенные функции PHP, вы можете легко просмотреть инструкцию SQL без значительных накладных расходов.

Учитывая $records , массив записей, где каждая запись сама является индексированным массивом (в виде значения field => value ), следующая функция будет вставлять записи в таблицу таблицы $table в соединение $connection PDO $connection , используя только одно подготовленное заявление. Обратите внимание, что это решение PHP 5.6+ из-за использования распаковки аргументов в вызове array_push :

 private function import(PDO $connection, $table, array $records) { $fields = array_keys($records[0]); $placeHolders = substr(str_repeat(',?', count($fields)), 1); $values = []; foreach ($records as $record) { array_push($values, ...array_values($record)); } $query = 'INSERT INTO ' . $table . ' ('; $query .= implode(',', $fields); $query .= ') VALUES ('; $query .= implode('),(', array_fill(0, count($records), $placeHolders)); $query .= ')'; $statement = $connection->prepare($query); $statement->execute($values); } 

Объединение массива должно быть еще быстрее, чем array_push , поэтому что-то вроде:

 $cumulativeArray += $rowArray; 

Несмотря на старый вопрос, все вклады очень помогли мне, поэтому вот мое решение, которое работает в моем собственном классе DbContext . Параметр $rows – это просто массив ассоциативных массивов, представляющих строки или модели: field name => insert value .

Если вы используете шаблон, который использует модели, это прекрасно подходит при передаче данных модели в виде массива, скажем, из метода ToRowArray в классе модели.

Примечание . Это должно быть само собой разумеющимся, но никогда не позволять аргументам, переданным этому методу, подвергаться воздействию пользователя или полагаться на любой пользовательский ввод, кроме значений вставки, которые были проверены и дезинфицированы. Аргумент $tableName и имена столбцов должны быть определены логикой вызова; например, модель User может быть сопоставлена ​​с таблицей пользователя, которая имеет список столбцов, сопоставленных с полями членов модели.

 public function InsertRange($tableName, $rows) { // Get column list $columnList = array_keys($rows[0]); $numColumns = count($columnList); $columnListString = implode(",", $columnList); // Generate pdo param placeholders $placeHolders = array(); foreach($rows as $row) { $temp = array(); for($i = 0; $i < count($row); $i++) $temp[] = "?"; $placeHolders[] = "(" . implode(",", $temp) . ")"; } $placeHolders = implode(",", $placeHolders); // Construct the query $sql = "insert into $tableName ($columnListString) values $placeHolders"; $stmt = $this->pdo->prepare($sql); $j = 1; foreach($rows as $row) { for($i = 0; $i < $numColumns; $i++) { $stmt->bindParam($j, $row[$columnList[$i]]); $j++; } } $stmt->execute(); } 

test.php

 <?php require_once('Database.php'); $obj = new Database(); $table = "test"; $rows = array( array( 'name' => 'balasubramani', 'status' => 1 ), array( 'name' => 'balakumar', 'status' => 1 ), array( 'name' => 'mani', 'status' => 1 ) ); var_dump($obj->insertMultiple($table,$rows)); ?> 

database.php

 <?php class Database { /* Initializing Database Information */ var $host = 'localhost'; var $user = 'root'; var $pass = ''; var $database = "database"; var $dbh; /* Connecting Datbase */ public function __construct(){ try { $this->dbh = new PDO('mysql:host='.$this->host.';dbname='.$this->database.'', $this->user, $this->pass); //print "Connected Successfully"; } catch (PDOException $e) { print "Error!: " . $e->getMessage() . "<br/>"; die(); } } /* Insert Multiple Rows in a table */ public function insertMultiple($table,$rows){ $this->dbh->beginTransaction(); // also helps speed up your inserts. $insert_values = array(); foreach($rows as $d){ $question_marks[] = '(' . $this->placeholders('?', sizeof($d)) . ')'; $insert_values = array_merge($insert_values, array_values($d)); $datafields = array_keys($d); } $sql = "INSERT INTO $table (" . implode(",", $datafields ) . ") VALUES " . implode(',', $question_marks); $stmt = $this->dbh->prepare ($sql); try { $stmt->execute($insert_values); } catch (PDOException $e){ echo $e->getMessage(); } return $this->dbh->commit(); } /* placeholders for prepared statements like (?,?,?) */ function placeholders($text, $count=0, $separator=","){ $result = array(); if($count > 0){ for($x=0; $x<$count; $x++){ $result[] = $text; } } return implode($separator, $result); } } ?>