Mastering MySQL Upserts: INSERT IGNORE, REPLACE INTO, and ON DUPLICATE KEY UPDATE Explained

Background

When importing large batches of data, encountering duplicate primary‑key or unique‑index values can cause a standard INSERT statement to abort, forcing developers to resort to inefficient "SELECT‑then‑INSERT/UPDATE" loops that dramatically increase round‑trips to the database.

Standard INSERT

The basic INSERT INTO … VALUES … statement adds new rows and aborts on any primary‑key or unique‑key conflict, guaranteeing strict data integrity but often being too rigid for bulk‑import scenarios.

-- Insert a single row
INSERT INTO users (id, name, email)
VALUES (1, 'FunTester', '[email protected]');

-- Insert multiple rows
INSERT INTO users (id, name, email)
VALUES
    (2, 'Alice', '[email protected]'),
    (3, 'Bob', '[email protected]'),
    (4, 'FunTester_User', '[email protected]');

INSERT IGNORE

Syntax

INSERT IGNORE INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);

How It Works

Silently skips rows that would cause a primary‑key or unique‑key conflict.

Continues inserting subsequent rows without raising an error.

No transaction rollback occurs for the ignored rows.

Typical Use Cases

Data‑deduplication during import.

Idempotent operations such as logging user login events.

Merging data from multiple sources where duplicates should be ignored.

Advantages

Concise SQL eliminates the need for a prior SELECT check, reducing code complexity and network overhead.

Bulk operations are not halted by a single conflicting row, improving stability.

Fewer round‑trips to the database lead to better performance under high concurrency.

Disadvantages

Ignored rows are silent; you cannot easily tell which records were skipped, making monitoring and auditing harder.

All errors—including data‑type mismatches or foreign‑key violations—are suppressed, potentially hiding real problems.

Not suitable for strict domains (e.g., finance or order processing) where every failure must be visible.

REPLACE INTO

Syntax

REPLACE INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);

How It Works

MySQL first attempts a normal INSERT. If a primary‑key or unique‑key conflict occurs, the existing row is deleted (triggering any DELETE triggers) and the new row is inserted, causing both DELETE and INSERT triggers to fire.

Typical Use Cases

Updating configuration tables where the new row must completely replace the old one.

Refreshing cache tables with the latest snapshot.

Key Differences from INSERT IGNORE

INSERT IGNORE

skips the conflicting row, preserving the old data. REPLACE INTO removes the old row and inserts the new one, affecting row count (1 or 2 rows affected).

Warnings

Deletes the entire old record; missing columns in the new row become NULL or default values.

Triggers associated with DELETE and INSERT will execute.

Auto‑increment values may change.

INSERT … ON DUPLICATE KEY UPDATE

Syntax

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...)
ON DUPLICATE KEY UPDATE
    column1 = new_value1,
    column2 = new_value2;

How It Works

The statement first tries a normal INSERT. If a conflict occurs, it executes an UPDATE on the specified columns instead of deleting the row, preserving all other fields.

Typical Use Cases

Accumulate points: insert a new user or add points to an existing user.

Track page views: insert a new record or increment the view counter.

Update inventory while keeping unchanged columns.

Advantages

Precise control: only the designated columns are updated.

No data loss: other columns remain untouched, making it safer for critical tables.

Supports complex expressions such as increments, concatenations, or conditional logic.

Generally the recommended solution for "insert‑or‑update" scenarios.

Practical Comparison

Choosing the right method depends on the specific requirement:

Data import where duplicates may be ignored – use INSERT IGNORE for simplicity and speed.

Configuration updates that must fully replace old rows – use REPLACE INTO to ensure the new record overwrites the old one.

Scenarios needing incremental updates (e.g., points, counters) – use INSERT … ON DUPLICATE KEY UPDATE to add or modify values without losing other data.

Strict uniqueness enforcement – stick with the standard INSERT to surface conflicts as errors.