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

тот же ответ, что и Г-Н Балагтас, немного яснее...

последние версии MySQL и PHP PDO do поддержка multi-строку INSERT заявления.

обзор SQL

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

INSERT 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

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

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

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

реализация

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

предположим:

• $tblName - строковое имя таблицы для вставки в
• $colNames - 1-мерный массив имен столбцов таблицы Эти имена столбцов должны быть допустимыми идентификаторами столбцов MySQL; экранируйте их с помощью обратных кавычек ( ` ), если они не
• $dataVals - mutli-мерный массив, где каждый элемент представляет собой 1-d массив строки значений для вставки

Пример Кода

// 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 вместо построения в виде одного строкового запроса, как это сделал выбранный ответ. Я решил запустить простой тест всего с двумя полями и очень простой оператор 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 мал. С большими массивами $data функция array_merge становится запредельно медленной. Мой тестовый файл для создания массива $data имеет 28 cols и составляет около 80 000 строк. Окончательный сценарий взял 41s завершить.

используя array_push () чтобы создать $insert_values вместо array_merge () в результате получилось 100Х скорость до С временем выполнения 0.41 s.

проблемные 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();
}

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

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

//$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'" . ')';
}

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

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

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

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

С уважением.

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

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

хотя старый вопрос все вклады помогли мне много так вот мое решение, которое работает в моем собственном DbContext класса. Элемент

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

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).

У меня была та же проблема, и это то, как я выполняю для себя, и я сделал функцию для себя для нее ( и вы можете использовать ее, если это поможет вам).

пример:

вставить в значения стран (Страна, город) (Германия, Берлин), (Франция, Париж);

$arr1 = Array("Germany", "Berlin");
$arr2 = Array("France", "France");

insertMultipleData("countries", Array($arr1, $arr2));


// Inserting multiple data to the Database.
public function insertMultipleData($table, $multi_params){
    try{
        $db = $this->connect();

        $beforeParams = "";
        $paramsStr = "";
        $valuesStr = "";

        for ($i=0; $i < count($multi_params); $i++) { 

            foreach ($multi_params[$i] as $j => $value) {                   

                if ($i == 0) {
                    $beforeParams .=  " " . $j . ",";
                }

                $paramsStr .= " :"  . $j . "_" . $i .",";                                       
            }

            $paramsStr = substr_replace($paramsStr, "", -1);
            $valuesStr .=  "(" . $paramsStr . "),"; 
            $paramsStr = "";
        }


        $beforeParams = substr_replace($beforeParams, "", -1);
        $valuesStr = substr_replace($valuesStr, "", -1);


        $sql = "INSERT INTO " . $table . " (" . $beforeParams . ") VALUES " . $valuesStr . ";";

        $stmt = $db->prepare($sql);


        for ($i=0; $i < count($multi_params); $i++) { 
            foreach ($multi_params[$i] as $j => &$value) {
                $stmt->bindParam(":" . $j . "_" . $i, $value);                                      
            }
        }

        $this->close($db);
        $stmt->execute();                       

        return true;

    }catch(PDOException $e){            
        return false;
    }

    return false;
}

// Making connection to the Database 
    public function connect(){
        $host = Constants::DB_HOST;
        $dbname = Constants::DB_NAME;
        $user = Constants::DB_USER;
        $pass = Constants::DB_PASS;

        $mysql_connect_str = 'mysql:host='. $host . ';dbname=' .$dbname;

        $dbConnection = new PDO($mysql_connect_str, $user, $pass);
        $dbConnection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

        return $dbConnection;
    }

    // Closing the connection
    public function close($db){
        $db = null;
    }

Если insertMultipleData ($table, $multi_params) возвращает TRUE ваши данные будут вставлены в базу данных.

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

дано $records, массив записей, где каждая запись представляет собой индексный массив (в виде field => value), следующая функция вставит записи в данную таблицу $table, при подключении 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;