MySQL INSERT Add Rows

Prepared Statements in Application Code

Application code should never concatenate raw user input into INSERT statements. Prepared statements separate SQL structure from values, reducing injection risk and improving clarity.

Validate input before inserting.
Bind values through the database driver.
Handle database errors without exposing internals to users.

LAST_INSERT_ID and Child Rows

After inserting a parent row with an auto-increment key, LAST_INSERT_ID can be used in the same connection to insert child rows. This is common for orders, invoices, posts with tags, and user profiles.

Use one transaction for parent and child writes.
Read the generated ID immediately.
Rollback if a child insert fails.

Bulk Import Strategy

Bulk inserts are faster than one query per row, but they need batching, validation, and failure handling. For large imports, log rejected rows so the user can fix data instead of guessing.

Batch large imports.
Validate before the database step.
Use transactions according to the acceptable failure model.

Make Inserts Atomic and Retry-Safe

Use one multi-row INSERT when all rows share the same statement and should succeed together. For a workflow spanning several statements, begin a transaction, check every result, and roll back the entire unit on failure. Do not report success before the commit completes.

Retries can create duplicates after a client loses the response to a successful commit. Protect a natural request identifier with a UNIQUE constraint and choose explicit duplicate behavior. INSERT IGNORE can hide data problems; ON DUPLICATE KEY UPDATE is appropriate only when updating the existing row is the intended domain rule.

List columns explicitly so schema order changes cannot remap values.
Use parameterized statements for every untrusted value.
Test duplicate keys, missing required values, and transaction rollback.

Single and Bulk Insert

INSERT INTO users (name, email, created_at)
VALUES ('Meera', 'meera@example.com', NOW());

INSERT INTO order_items (order_id, product_id, quantity)
VALUES
  (101, 7, 2),
  (101, 12, 1),
  (101, 19, 4);

Transaction for Related Inserts

START TRANSACTION;

INSERT INTO orders (customer_id, status, created_at)
VALUES (42, 'pending', NOW());

SET @order_id = LAST_INSERT_ID();

INSERT INTO order_items (order_id, product_id, quantity)
VALUES (@order_id, 5, 2);

COMMIT;

Prepared INSERT in PHP PDO

<?php
$stmt = $pdo->prepare('INSERT INTO users (name, email) VALUES (:name, :email)');
$stmt->execute([
    'name' => $name,
    'email' => $email,
]);

Insert Child Rows with Generated ID

START TRANSACTION;

INSERT INTO invoices (customer_id, created_at)
VALUES (42, NOW());

SET @invoice_id = LAST_INSERT_ID();

INSERT INTO invoice_items (invoice_id, label, amount)
VALUES (@invoice_id, 'Hosting', 999.00);

COMMIT;

Before you move on