php - tag - title attribute html



PDO Preparado Insere várias linhas em uma única consulta (14)

Atualmente, estou usando esse tipo de SQL no MySQL para inserir várias linhas de valores em uma única consulta:

INSERT INTO `tbl` (`key1`,`key2`) VALUES ('r1v1','r1v2'),('r2v1','r2v2'),...

Nas leituras do PDO, as declarações de uso preparadas devem me dar uma segurança melhor do que as consultas estáticas.

Portanto, gostaria de saber se é possível gerar "inserir várias linhas de valores pelo uso de uma consulta" usando instruções preparadas.

Se sim, posso saber como posso implementá-lo?

https://src-bin.com


Answer #1

A Resposta Aceita por Herbert Balagtas funciona bem quando o array $ data é pequeno. Com matrizes de dados $ maiores, a função array_merge torna-se proibitivamente lenta. Meu arquivo de teste para criar o array $ data tem 28 cols e tem cerca de 80.000 linhas. O roteiro final levou 41s para completar.

Usar array_push () para criar $ insert_values ​​em vez de array_merge () resultou em uma aceleração de 100X com tempo de execução de 0.41s .

O problemático array_merge ():

$insert_values = array();

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

Para eliminar a necessidade de array_merge (), você pode construir as seguintes duas matrizes:

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

Esses arrays podem ser usados ​​da seguinte maneira:

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

Answer #2

A mesma resposta do Sr. Balagtas, um pouco mais clara ...

Versões recentes MySQL e PHP PDO suportam instruções INSERT multi-row.

Visão Geral do SQL

O SQL será algo como isto, assumindo uma tabela de 3 colunas que você gostaria de INSERT .

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

ON DUPLICATE KEY UPDATE funciona como esperado, mesmo com um INSERT de várias linhas; acrescente isto:

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

Visão Geral do PHP

Seu código PHP seguirá as habituais $pdo->prepare($qry) e $stmt->execute($params) .

$params será uma matriz unidimensional de todos os valores para passar para o INSERT .

No exemplo acima, ele deve conter 9 elementos; O PDO usará cada conjunto de 3 como uma única linha de valores. (Inserindo 3 linhas de 3 colunas cada = 9 matriz de elementos.)

Implementação

O código abaixo foi escrito para maior clareza e não para eficiência. Trabalhe com as funções da array_*() PHP array_*() para encontrar maneiras melhores de mapear ou percorrer seus dados, se desejar. Se você pode usar transações, obviamente, depende do seu tipo de tabela do MySQL.

Assumindo:

  • $tblName - o nome da string da tabela para INSERT to
  • $colNames - matriz unidimensional dos nomes das colunas da tabela Estes nomes de coluna devem ser identificadores de colunas válidos do MySQL; escape-os com backticks (``) se eles não estiverem
  • $dataVals - array $dataVals -dimensional, onde cada elemento é um array 1-d de uma linha de valores para INSERT

Código de amostra

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

Answer #3

Aqui está a minha abordagem simples.

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

Answer #4

Aqui está a minha solução: https://github.com/sasha-ch/Aura.Sql base na biblioteca auraphp / Aura.Sql.

Exemplo de uso:

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

Os relatórios de erros são bem-vindos.


Answer #5

Como ainda não foi sugerido, tenho certeza de que LOAD DATA INFILE ainda é o modo mais rápido de carregar dados, pois desativa a indexação, insere todos os dados e reativa os índices, tudo em uma única solicitação.

Salvando os dados como um csv deve ser bastante trivial, tendo em mente fputcsv. O MyISAM é o mais rápido, mas você ainda obtém um grande desempenho no InnoDB. Existem outras desvantagens, embora eu siga este caminho se você estiver inserindo muitos dados e não se preocupar com menos de 100 linhas.


Answer #6

Duas abordagens possíveis:

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

Ou:

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

Se os dados de todas as linhas estiverem em uma única matriz, eu usaria a segunda solução.


Answer #7

Foi assim que eu fiz:

Primeiro defina os nomes das colunas que você usará, ou deixe em branco e pdo assumirá que você quer usar todas as colunas da tabela - nesse caso, você precisará informar os valores das linhas na ordem exata em que aparecem na tabela. .

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

Agora, suponha que você tenha uma matriz bidimensional já preparada. Iterar e construir uma string com seus valores de linha, como tal:

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

Agora, o que você acabou de fazer foi verificar se $ rows já estava definido e, caso contrário, criá-lo e armazenar valores de linha e a sintaxe SQL necessária para que seja uma declaração válida. Observe que as strings devem ficar entre aspas duplas e aspas simples, para que sejam prontamente reconhecidas como tal.

Tudo o que resta fazer é preparar a declaração e executar, como tal:

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

Testado com até 2000 linhas até o momento e o tempo de execução é desanimador. Farei mais alguns testes e voltarei aqui caso tenha algo a contribuir.

Saudações.


Answer #8

Isso funcionou para mim

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

Answer #9

Por que vale a pena, eu vi muitos usuários recomendam iterar através de instruções INSERT em vez de construir como uma única consulta de string como a resposta selecionada fez. Eu decidi executar um teste simples com apenas dois campos e uma instrução de inserção muito básica:

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

Enquanto a consulta geral em si levou milissegundos ou menos, a última consulta (string única) foi consistentemente 8 vezes mais rápida ou mais. Se isso fosse construído para refletir uma importação de milhares de linhas em muitas outras colunas, a diferença poderia ser enorme.


Answer #10

Simplesmente não é assim que você usa declarações preparadas.

Não há problema em inserir uma linha por consulta, pois você pode executar uma instrução preparada várias vezes com parâmetros diferentes. Na verdade, essa é uma das maiores vantagens, pois permite que você insira um grande número de linhas de maneira eficiente, segura e confortável.

Por isso, talvez seja possível implementar o esquema que você propõe, pelo menos para um número fixo de linhas, mas é quase garantido que isso não é realmente o que você deseja.


Answer #11

Você pode inserir várias linhas em uma única consulta com esta função:

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 é uma matriz de matrizes de valores. No seu caso, você chamaria a função com

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

Isso tem o benefício de usar instruções preparadas ao inserir várias linhas com uma única consulta. Segurança!


Answer #12

Valores Múltiplos Inseridos com Instruções Preparadas PDO

Inserindo vários valores em uma instrução de execução. Por que porque, de acordo com esta página , é mais rápido que as inserções regulares.

$datafields = array('fielda', 'fieldb', ... );

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

mais valores de dados ou você provavelmente tem um loop que preenche os dados.

Com inserções preparadas, você precisa conhecer os campos para os quais está inserindo e o número de campos para criar o campo? espaços reservados para vincular seus parâmetros.

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

É basicamente assim que queremos que a instrução insert seja semelhante.

Agora, o código:

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

Embora no meu teste, houvesse apenas uma diferença de 1 segundo ao usar várias inserções e inserções preparadas regularmente com valor único.


Answer #13

A maioria das soluções dadas aqui para criar a consulta preparada é mais complexa do que precisa ser. Usando as funções internas do PHP, você pode facilmente criar a instrução SQL sem sobrecarga significativa.

Dado $records, uma matriz de registros em que cada registro é em si um array indexado (na forma de field => value), a seguinte função inserirá os registros na tabela especificada $table, em uma conexão PDO $connection, usando apenas uma única instrução preparada. Note que esta é uma solução PHP 5.6+ devido ao uso do argumento de desempacotamento na chamada para 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);
}

Answer #14

A união de matriz deve ser ainda mais rápida do que array_push, por isso, algo como:

$cumulativeArray += $rowArray; 




prepared-statement