Вставить несколько строк с подготовленными инструкциями PDO

Ответ 1

Первое, что нужно сказать, это то, что вы можете вставлять несколько строк благодаря одному запросу INSERT

INSERT INTO Table (col1, col2, col3) 
VALUES ('abc', 'def', 'ghi'),
       ('abc', 'def', 'ghi'),
       ('abc', 'def', 'ghi'),
       ('abc', 'def', 'ghi'),
       ('abc', 'def', 'ghi')
       -- and so on...

Как только вы это знаете, вы можете получить хорошее решение с PDO (например).
Вы должны иметь в виду, что вам нужен полный процесс prepare и execute (с точки зрения безопасности вы должны передавать каждый параметр отдельно).

Скажем, у вас есть строки для вставки структурированных, как показано ниже:

$rows = array(
              array('abc', 'def', 'ghi'), // row 1 to insert
              array('abc', 'def', 'ghi'), // row 2 to insert
              array('abc', 'def', 'ghi')  // row 3 to insert
              // and so on ...
);

Ваша цель - получить этот результат в виде подготовленного запроса:

INSERT INTO Table (col1, col2, col3) 
VALUES (?, ?, ?),
       (?, ?, ?),
       (?, ?, ?)

С его соответствующим выполнить:

PDOStatement::execute(array('abc', 'def', 'ghi', 'abc', 'def', 'ghi', 'abc', 'def', 'ghi'));

Ну, вам нужно только сделать это сейчас:

$rows = array(
              array('abc', 'def', 'ghi'),
              array('abc', 'def', 'ghi'),
              array('abc', 'def', 'ghi')
);

$row_length = count($rows[0]);
$nb_rows = count($rows);
$length = $nb_rows * $row_length;

/* Fill in chunks with '?' and separate them by group of $row_length */
$args = implode(',', array_map(
                                function($el) { return '('.implode(',', $el).')'; },
                                array_chunk(array_fill(0, $length, '?'), $row_length)
                            ));

$params = array();
foreach($rows as $row)
{
   foreach($row as $value)
   {
      $params[] = $value;
   }
}

$query = "INSERT INTO Table (col1, col2, col3) VALUES ".$args;
$stmt = DB::getInstance()->prepare($query);
$stmt->execute($params);

И... Что это!

Таким образом, каждый параметр обрабатывается отдельно, что вам нужно (безопасность, безопасность, безопасность!) и все это динамически, только с одним запросом INSERT

Если у вас слишком много строк для вставки (см. this), вы должны execute поочередно

$rows = array(
              array('abc', 'def', 'ghi'), // row 1 to insert
              array('abc', 'def', 'ghi'), // row 2 to insert
              array('abc', 'def', 'ghi')  // row 3 to insert
              // and so on ...
);

$args = array_fill(0, count($rows[0]), '?');

$query = "INSERT INTO Table (col1, col2, col3) VALUES (".implode(',', $args).")";
$stmt = $pdo->prepare($query);

foreach ($rows as $row) 
{
   $stmt->execute($row);
}

Ответ 2

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

$rows = array(
              array('abc', 'def', 'ghi'), // row 1 to insert
              array('abc', 'def', 'ghi'), // row 2 to insert
              array('abc', 'def', 'ghi')  // row 3 to insert
              // and so on ...
);

$params = implode(",", array_fill(0, count($rows[0]), "?"));

$sql = "INSERT INTO mytable VALUES ($params)";

$stmt = $pdo->prepare($sql); // rely on exceptions for error detection

foreach ($rows as $row) {
    $stmt->execute($row);
}

MySQL действительно поддерживает многострочный синтаксис INSERT, конечно, поэтому вы можете попытаться собрать это вместе.

$params = implode(",", array_fill(0, count($rows[0]), "?"));

$tuples = "(" . implode("),(", array_fill(0, count($rows), $params)) . ")";

$sql = "INSERT INTO mytable VALUES $tuples";

$values = call_user_func_array("array_merge", $rows);

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

$stmt->execute($values);

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

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

Ответ 3

Если ваша таблица является транзакционной (например, InnoDB), вы можете использовать Transaction, чтобы ускорить ваши вставки. Сделка также имеет преимущество откат.

$pdo = DB::getInstance();
$stmt = $pdo->prepare('INSERT INTO table VALUES (col1, col2, col3) VALUES (:val1, :val2, :val3)');

$pdo->beginTransaction();

// The queries are not executed yet, but pushed to a transaction "stack"
foreach ($values as $value) {
    $stmt->execute([
        ':val1' => $value['val1'],
        ':val2' => $value['val2'],
        ':val3' => $value['val3'],
    ]);
}

// Executes all the queries "at once"
$pdo->commit();

Ответ 4

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

$array_params = array();
$params[':val1']="val1 1";
$params[':val2']="val1 2";
$params[':val3']="val1 3";
$array_params[] = $params;

$params[':val1']="val2 1";
$params[':val2']="val2 2";
$params[':val3']="val2 3";
$array_params[] = $params;

$sql="INSERT INTO table (col1,col2,col3) VALUES (:val1,:val2,:val3)";
$stmt=DB::getInstance()->prepare($sql);
foreach($array_params as $params)
{
  $stmt->execute($params);
}

Но его можно выполнить несколько вложений с одним запросом типа INSERT INTO table (col1,col2,col3) VALUES ("val1","val2","val3"),("val4","val5","val6"),("val7","val8,"val9"), используя что-то вроде этого для построения запроса:

$all_inserts = array( array('val1', 'val2', 'val3'),array('val4', 'val5', 'val6'));
$sql = 'INSERT INTO table (col1,col2,col3) VALUES ';
$rows = array();
foreach ($all_inserts as $one_insert)
{
   $rows[] = '('.implode(',', $pdo->quote($one_insert).')';
}
$sql .= ' '.implode(',', $rows);
$pdo->query($sql);