MySQL Insert Multiple

Inserting Many Rows Efficiently

In simple terms: Importing a product catalog, a CSV of students, or a day's worth of log entries means inserting many rows. Sending one INSERT per row works, but every statement is a round trip to the database server — thousands of them make imports slow. There are better ways.

Technically: MySQL and MySQLi offer three approaches:

The examples start with an empty products table.

Approach 1: One Multi-Row INSERT

INSERT INTO products (sku, name, price, stock, category) VALUES
    ('PEN-01', 'Gel Pen', 20.00, 120, 'stationery'),
    ('NB-A5', 'Notebook A5', 60.00, 45, 'stationery');

From PHP, generate one placeholder group per row and pass all values together:

<?php
require __DIR__ . '/src/db.php';

$db = db();

$products = [
    ['PEN-01', 'Gel Pen', '20.00', 120, 'stationery'],
    ['NB-A5', 'Notebook A5', '60.00', 45, 'stationery'],
    ['LAMP-01', 'Desk Lamp', '1299.00', 8, 'electronics'],
    ['BAG-02', 'Backpack', '899.00', 0, 'bags'],
    ['MOUSE-01', 'Wireless Mouse', '599.00', 25, 'electronics'],
];

$placeholders = implode(', ', array_fill(0, count($products), '(?, ?, ?, ?, ?)'));

$db->execute_query(
    "INSERT INTO products (sku, name, price, stock, category) VALUES $placeholders",
    array_merge(...$products)
);

$firstId = $db->insert_id;
$count = $db->affected_rows;

echo "Inserted $count products with IDs $firstId to " . ($firstId + $count - 1) . "\n";

Output

Inserted 5 products with IDs 1 to 5

How It Works

1. array_fill() creates five copies of (?, ?, ?, ?, ?); implode() joins them with commas. Only placeholders are built dynamically — no data is ever placed into the SQL text.
2. array_merge(...$products) flattens the rows into one list of 25 values, in the same order as the placeholders.
3. MySQL inserts all five rows in a single statement and a single round trip.
4. A multi-row insert is atomic: if one row violates a constraint (for example, a duplicate SKU), none of the rows are inserted.
5. insert_id returns the first generated ID; affected_rows the number of rows (see MySQL Get Last ID).

These five products are used in the following lessons.

Limits

A single statement is limited by MySQL's max_allowed_packet setting (64 MB by default in MySQL 8) and by the maximum of 65,535 placeholders per prepared statement. For large imports, insert in batches of a few hundred rows (shown below).

Approach 2: Reusing a Prepared Statement in a Transaction

When each row must be validated individually, or bad rows should be skipped and reported, prepare the statement once and execute it for every row:

<?php
require __DIR__ . '/src/db.php';

$db = db();

$rows = [
    ['Gopi', 'gopi@example.com', 'Salem'],
    ['Hema', 'not-an-email', 'Erode'],
    ['Indu', 'indu@example.com', null],
    ['Jaya', 'jaya@example.com', 'Thanjavur'],
];

$stmt = $db->prepare('INSERT INTO customers (name, email, city) VALUES (?, ?, ?)');

$imported = 0;
$errors = [];

$db->begin_transaction();

try {
    foreach ($rows as $index => [$name, $email, $city]) {
        if (filter_var($email, FILTER_VALIDATE_EMAIL) === false) {
            $errors[] = 'Row ' . ($index + 1) . ": invalid email '$email'";
            continue;
        }

        $stmt->execute([$name, strtolower($email), $city]);
        $imported++;
    }

    $db->commit();
} catch (Throwable $e) {
    $db->rollback();
    throw $e;
}

echo "Imported $imported customers\n";
echo implode("\n", $errors) . "\n";

Output

Imported 3 customers
Row 2: invalid email 'not-an-email'

Why the Transaction Matters

• Speed: Without a transaction, InnoDB commits (and flushes to disk) after every row. Committing once at the end makes large imports many times faster.
• Consistency: If a database error occurs midway (for example, a duplicate email), rollback() undoes the whole batch, so the import can be fixed and repeated without leaving half the data behind.
• Efficiency: The statement is parsed once by MySQL and executed many times with different values.

($stmt->execute([...]) with an array requires PHP 8.1+; on older versions, use bind_param().)

Approach 3: multi_query() for Fixed Scripts

multi_query() sends several statements separated by semicolons in one call. It is useful for running a fixed SQL script, such as seed data for a development database:

<?php
require __DIR__ . '/src/db.php';

$db = db();

$seedSql = "
    INSERT INTO customers (name, email, city) VALUES ('Test User', 'test@example.com', 'Chennai');
    UPDATE customers SET city = 'Coimbatore' WHERE email = 'test@example.com';
    SELECT COUNT(*) FROM products;
";

$db->multi_query($seedSql);

$statement = 1;
do {
    if ($result = $db->store_result()) {
        echo "Statement $statement returned " . $result->fetch_row()[0] . "\n";
        $result->free();
    } else {
        echo "Statement $statement affected {$db->affected_rows} row(s)\n";
    }
    $statement++;
} while ($db->next_result());

Output

Statement 1 affected 1 row(s)
Statement 2 affected 1 row(s)
Statement 3 returned 5

Every result must be consumed with store_result() / next_result() before the connection can be used for other queries.

Warning: multi_query() does not support placeholders. If user input ever reached it, an attacker could append entire additional statements ('; DROP TABLE customers; --). Use it only for fixed, trusted SQL. For data, use approaches 1 or 2.

Practical Example: Importing in Batches

Large imports combine both ideas: multi-row inserts in batches, all inside a transaction.

<?php
function insertInBatches(mysqli $db, string $table, array $columns, iterable $rows, int $batchSize = 500): int
{
    $columnList = implode(', ', $columns);
    $group = '(' . implode(', ', array_fill(0, count($columns), '?')) . ')';
    $inserted = 0;
    $batch = [];

    $flush = function () use ($db, $table, $columnList, $group, &$batch, &$inserted): void {
        if ($batch === []) {
            return;
        }
        $placeholders = implode(', ', array_fill(0, count($batch), $group));
        $db->execute_query("INSERT INTO $table ($columnList) VALUES $placeholders", array_merge(...$batch));
        $inserted += $db->affected_rows;
        $batch = [];
    };

    $db->begin_transaction();
    try {
        foreach ($rows as $row) {
            $batch[] = $row;
            if (count($batch) === $batchSize) {
                $flush();
            }
        }
        $flush();
        $db->commit();
    } catch (Throwable $e) {
        $db->rollback();
        throw $e;
    }

    return $inserted;
}

How It Works

1. Rows are collected into batches (500 by default); each full batch is sent as one multi-row INSERT.
2. $rows can be a generator reading a large CSV file line by line (see PHP CSV Files), so memory use stays small regardless of file size.
3. The whole import is one transaction: all rows are inserted, or none are.
4. $table and $columns are inserted into the SQL as identifiers, so they must come from your code, never from user input.

Common Mistakes

One INSERT per Row Without a Transaction

Effect: Very slow imports (a disk flush per row).

Fix: Use multi-row inserts or a single transaction.

Building Value Lists by Concatenating Data

Effect: SQL injection and quoting errors.

Fix: Generate placeholders, pass values separately.

Using multi_query() With User Data

Effect: Attackers can inject additional statements.

Fix: Use it only for fixed SQL scripts.

Giant Single Statements

Effect: "Packet too large" or placeholder-limit errors.

Fix: Insert in batches.

Best Practices

• Use multi-row INSERT statements with generated placeholders for sets of rows.
• Wrap bulk inserts in a transaction for speed and consistency.
• Reuse prepared statements when rows need individual validation.
• Process large imports in batches, ideally from a generator.
• Reserve multi_query() for trusted, fixed SQL scripts.

Official reference: https\://www.php.net/manual/en/mysqli.multi-query.php