запускать несколько запросов в массиве с помощью multi_query [duplicate]

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

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

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

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

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

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

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

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

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

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

Несмотря на то, что старый вопрос мне очень помог, это мое решение, которое работает в моем классе 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();
}

Принятый ответ Герберта Балагтаса хорошо работает, когда массив $ 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();

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

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

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

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

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

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

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

$cumulativeArray += $rowArray; 

Вот мое решение: 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);

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

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

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

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

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

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

Обзор SQL

SQL будет выглядеть примерно так: предположим, что таблица с тремя столбцами вы хотите 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 $pdo->prepare($qry) и $stmt->execute($params).

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

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

Реализация

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

Предполагая:

• $tblName - имя строки таблицы INSERT для
• $colNames - 1-мерный массив имен столбцов таблицы. Эти имена столбцов должны быть действительными идентификаторами столбцов MySQL; избегать их с обратными шагами (``), если они не
• $dataVals - mutli-мерный массив, где каждый элемент представляет собой 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();

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

}
?>

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

Сначала определите имена колонок, которые вы будете использовать, или оставьте поле пустым, и 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 строк до сих пор, а время исполнения удручающее. Запустит еще несколько тестов и вернется сюда, если у меня есть что-то еще.

С уважением.

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

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

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

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

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