Insert Multiple

Inserting Multiple Rows into MySQL with PHP PDO

When importing CSV files or saving order line items in PHP, inserting rows one by one with separate auto-committed queries is slow because MySQL flushes disk logs on every single insert. Wrapping a prepared statement inside a single transaction—or building chunked multi-row INSERT batches—speeds up bulk inserts by 10x or more.

Method 1: One Prepared Statement Inside a Transaction

Prepare the SQL template once before your foreach loop, start a transaction with beginTransaction(), execute each row inside the loop, and commit() once at the end.

<?php
declare(strict_types=1);

function importProducts(PDO $pdo, array $products): int
{
    if ($products === []) {
        return 0;
    }

    $pdo->beginTransaction();

    try {
        $stmt = $pdo->prepare(
            'INSERT INTO products (sku, title, unit_price) VALUES (:sku, :title, :price)'
        );

        $inserted = 0;
        foreach ($products as $product) {
            $stmt->execute([
                ':sku' => $product['sku'],
                ':title' => $product['title'],
                ':price' => $product['price'],
            ]);
            $inserted += $stmt->rowCount();
        }

        $pdo->commit();
        return $inserted;
    } catch (Throwable $e) {
        if ($pdo->inTransaction()) {
            $pdo->rollBack();
        }
        throw $e;
    }
}

Method 2: Chunked Multi-Row VALUES (...), (...) Queries

For very large imports (5,000+ rows), batching 200 to 500 rows into a single parameterized INSERT INTO ... VALUES (?, ?, ?), (?, ?, ?) statement cuts network round-trips dramatically. Always split large arrays with array_chunk($rows, 250) so you never exceed MySQL's max_allowed_packet or parameter limits.

Common Mistakes to Avoid

• Inserting 10,000 rows without a transaction: Auto-commit forces a disk sync on every row, turning a 0.2-second import into a 20-second timeout.
• Putting 50,000 placeholders in one INSERT query: Huge single queries can exceed MySQL's 65,535 prepared placeholder limit or max_allowed_packet. Always chunk batches to 250–500 rows.
• Forgetting rollBack() in the catch block: Leaving half-imported records after row 400 fails corrupts import state. Transactions guarantee all-or-nothing atomicity.

Frequently Asked Questions

Should I use beginTransaction() or multi-row VALUES?

For up to a few hundred rows, a single prepared statement inside a transaction is clean and very fast. For thousands of rows, combine a transaction with array_chunk() multi-row inserts.

How do I skip duplicate rows during a batch import?

Use INSERT IGNORE INTO ... or INSERT INTO ... ON DUPLICATE KEY UPDATE ... depending on whether duplicates should be skipped or updated.

How do I avoid running out of memory on huge CSV imports?

Read the CSV lazily with fgetcsv(), buffer 250 rows at a time, flush each batch inside a transaction, and unset the buffer array.