PDO Prepared Insère plusieurs lignes dans une seule requête

Question

J'utilise actuellement ce type de SQL sur MySQL pour insérer plusieurs lignes de valeurs en une seule requête :

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

D'après les lectures sur PDO, l'utilisation d'instructions préparées devrait me donner une meilleure sécurité que les requêtes statiques.

Je voudrais donc savoir s'il est possible de générer "l'insertion de plusieurs lignes de valeurs par l'utilisation d'une seule requête" en utilisant des instructions préparées.

Si oui, puis-je savoir comment le mettre en œuvre ?

Answer 1

Insertion de valeurs multiples avec des instructions préparées PDO

Insertion de plusieurs valeurs dans une instruction d'exécution. Pourquoi, car selon cette page il est plus rapide que les inserts ordinaires.

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

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

plus de valeurs de données ou vous avez probablement une boucle qui alimente les données.

Avec les insertions préparées, vous devez connaître les champs dans lesquels vous effectuez l'insertion, ainsi que le nombre de champs pour créer les caractères génériques ? afin de lier vos paramètres.

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

C'est en gros ce à quoi nous voulons que l'instruction d'insertion ressemble.

Maintenant, le code :

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);
$stmt->execute($insert_values);
$pdo->commit();

Bien que dans mon test, il n'y avait qu'une différence d'une seconde entre l'utilisation d'encarts multiples et d'encarts ordinaires préparés avec une seule valeur.

Answer 2

Même réponse que M. Balagtas, légèrement plus claire...

Versions récentes de MySQL et PHP PDO faire supporter plusieurs rangées INSERT déclarations.

Aperçu de SQL

Le SQL ressemblera à quelque chose comme ceci, en supposant un tableau à 3 colonnes que vous souhaitez INSERT à.

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

ON DUPLICATE KEY UPDATE fonctionne comme prévu, même avec un INSERT à plusieurs rangs ; ajoutez ceci :

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

Présentation de PHP

Votre code PHP suivra le schéma habituel $pdo->prepare($qry) et $stmt->execute($params) Appels PDO.

$params sera un tableau unidimensionnel de tous les valeurs à transmettre à la INSERT .

Dans l'exemple ci-dessus, il devrait contenir 9 éléments ; PDO utilisera chaque ensemble de 3 comme une seule ligne de valeurs. (Insertion de 3 rangées de 3 colonnes chacune = tableau de 9 éléments).

Mise en œuvre

Le code ci-dessous est écrit pour la clarté, pas pour l'efficacité. Travaillez avec le PHP array_*() des fonctions pour de meilleures façons de cartographier ou de parcourir vos données si vous le souhaitez. La possibilité d'utiliser des transactions dépend évidemment du type de votre table MySQL.

Assumant :

• $tblName - le nom en chaîne de la table à INSÉRER
• $colNames - Tableau unidimensionnel des noms de colonnes de la table. Ces noms de colonne doivent être des identifiants de colonne MySQL valides ; échappez-les avec des barres obliques inversées (``) s'ils ne le sont pas.
• $dataVals - tableau mutli-dimensionnel, où chaque élément est un tableau 1-d d'une rangée de valeurs à INSÉRER

Exemple de code

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

$stmt->execute($dataToInsert);

$pdo->commit();

Answer 3

Pour ce que cela vaut, j'ai vu beaucoup d'utilisateurs recommander d'itérer à travers des déclarations INSERT au lieu de construire une seule requête de chaîne comme la réponse sélectionnée l'a fait. J'ai décidé d'effectuer un test simple avec seulement deux champs et une instruction d'insertion très basique :

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

Alors que la requête globale elle-même prenait quelques millisecondes ou moins, la dernière requête (chaîne unique) était systématiquement 8 fois plus rapide ou plus. Si cette requête était développée pour refléter une importation de milliers de lignes sur beaucoup plus de colonnes, la différence pourrait être énorme.

Answer 4

La réponse acceptée par Herbert Balagtas fonctionne bien lorsque le tableau $data est petit. Avec des tableaux de $data plus grands, la fonction array_merge devient prohibitivement lente. Mon fichier de test pour créer le tableau $data a 28 cols et fait environ 80 000 lignes. Le script final a pris 41s à compléter.

Utilisation de array_push() pour créer $insert_values à la place de array_merge() a donné lieu à une Vitesse multipliée par 100 avec un temps d'exécution de 0.41s .

La problématique array_merge() :

$insert_values = array();

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

Pour éliminer le besoin de array_merge(), vous pouvez construire les deux tableaux suivants à la place :

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

Ces tableaux peuvent ensuite être utilisés comme suit :

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

Answer 5

Ce n'est tout simplement pas la façon dont vous utilisez les déclarations préparées.

Il est parfaitement acceptable d'insérer une ligne par requête car vous pouvez exécuter une instruction préparée plusieurs fois avec des paramètres différents. En fait, c'est l'un de ses plus grands avantages, car il vous permet d'insérer un grand nombre de lignes de manière efficace, sûre et confortable.

Il est donc peut-être possible de mettre en œuvre le schéma que vous proposez, au moins pour un nombre fixe de lignes, mais il est presque garanti que ce n'est pas vraiment ce que vous voulez.