Introduction

INSERT IGNORE silently skips rows that would violate a unique or primary key constraint. INSERT ... ON DUPLICATE KEY UPDATE detects the conflict and updates the existing row instead. Choose INSERT IGNORE when duplicate rows can be safely discarded. Choose ON DUPLICATE KEY UPDATE when you need to merge new data into existing rows.

INSERT IGNORE: Skip Duplicates Silently

Syntax

INSERT IGNORE INTO table_name (col1, col2, col3)
VALUES (val1, val2, val3);

How it works

1. MySQL attempts the INSERT.
2. If a duplicate key violation occurs, the row is silently discarded.
3. No error is raised. The statement continues to the next row.
4. The auto-increment counter still advances, creating gaps in IDs.

Example

1CREATE TABLE users (
2    id INT PRIMARY KEY,
3    email VARCHAR(255) UNIQUE,
4    name VARCHAR(100)
5);
6
7INSERT INTO users VALUES (1, '[email protected]', 'Alice');
8
9-- This would normally throw: ERROR 1062 (Duplicate entry)
10-- With IGNORE, the row is silently skipped:
11INSERT IGNORE INTO users VALUES (1, '[email protected]', 'Bob');
12
13-- Alice is still in the table, unchanged
14SELECT * FROM users WHERE id = 1;
15-- +----+-------------------+-------+
16-- | id | email             | name  |
17-- +----+-------------------+-------+
18-- |  1 | [email protected] | Alice |
19-- +----+-------------------+-------+

Batch insert with IGNORE

1INSERT IGNORE INTO users (id, email, name) VALUES
2    (1, '[email protected]', 'Alice'),      -- skipped (duplicate id=1)
3    (2, '[email protected]', 'Bob'),           -- inserted
4    (3, '[email protected]', 'Charlie'),   -- inserted
5    (2, '[email protected]', 'Bob Duplicate');-- skipped (duplicate id=2)
6
7-- Result: rows 2 and 3 are inserted. Rows 1 and 4 are skipped.
8-- Check how many were actually inserted:
9SELECT ROW_COUNT();  -- Returns 2

INSERT ... ON DUPLICATE KEY UPDATE: Upsert

Syntax

INSERT INTO table_name (col1, col2, col3)
VALUES (val1, val2, val3)
ON DUPLICATE KEY UPDATE col2 = val2, col3 = val3;

How it works

1. MySQL attempts the INSERT.
2. If a duplicate key violation occurs, MySQL runs the UPDATE clause on the existing row.
3. The VALUES() function (deprecated in MySQL 8.0.20+) or row alias references the values from the attempted INSERT.
4. The affected-rows count returns 1 for an insert, 2 for an update, and 0 if the row existed but no values changed.

Example

1INSERT INTO users (id, email, name) VALUES (1, '[email protected]', 'Alice Updated')
2ON DUPLICATE KEY UPDATE email = VALUES(email), name = VALUES(name);
3
4-- The existing row with id=1 is updated:
5SELECT * FROM users WHERE id = 1;
6-- +----+-------------------------+---------------+
7-- | id | email                   | name          |
8-- +----+-------------------------+---------------+
9-- |  1 | [email protected]   | Alice Updated |
10-- +----+-------------------------+---------------+

MySQL 8.0.20+ syntax (row alias)

The VALUES() function in the UPDATE clause is deprecated since MySQL 8.0.20. Use a row alias instead:

INSERT INTO users (id, email, name) VALUES (1, '[email protected]', 'Alice Updated')
AS new_row
ON DUPLICATE KEY UPDATE email = new_row.email, name = new_row.name;

Conditional update (update only if value changed)

1INSERT INTO products (sku, price, last_updated) VALUES ('ABC-123', 29.99, NOW())
2AS new_row
3ON DUPLICATE KEY UPDATE
4    price = new_row.price,
5    last_updated = IF(price != new_row.price, NOW(), last_updated);

This updates the timestamp only when the price actually changes, avoiding unnecessary writes.

Counter/aggregation pattern

A common use case is incrementing a counter:

1INSERT INTO page_views (page_url, view_count, last_viewed) 
2VALUES ('/home', 1, NOW())
3ON DUPLICATE KEY UPDATE 
4    view_count = view_count + 1,
5    last_viewed = NOW();

Each call either creates the row with view_count = 1 or increments the existing count.

Side-by-Side Comparison

REPLACE INTO: A Third Option

MySQL also offers REPLACE INTO, which deletes the conflicting row and inserts the new one. This is different from both approaches above:

REPLACE INTO users (id, email, name) VALUES (1, '[email protected]', 'Alice Replaced');

Avoid REPLACE INTO when you have foreign keys or auto-increment IDs that other rows reference. The delete-then-insert behavior can trigger cascading deletes.

Real-World Use Cases

Use INSERT IGNORE for: idempotent data loading

1-- Loading data from a CSV where some rows may already exist
2LOAD DATA INFILE '/tmp/users.csv'
3INTO TABLE users
4FIELDS TERMINATED BY ','
5LINES TERMINATED BY '\n'
6IGNORE 1 ROWS;
7
8-- Or with INSERT IGNORE for smaller datasets:
9INSERT IGNORE INTO tags (name) VALUES
10    ('python'), ('javascript'), ('rust'), ('python');  -- 'python' duplicate handled

Use ON DUPLICATE KEY UPDATE for: syncing external data

1-- Syncing product prices from an external feed
2INSERT INTO products (sku, name, price, stock, updated_at)
3VALUES ('WIDGET-01', 'Blue Widget', 14.99, 100, NOW())
4AS src
5ON DUPLICATE KEY UPDATE
6    price = src.price,
7    stock = src.stock,
8    updated_at = src.updated_at;

Use ON DUPLICATE KEY UPDATE for: session management

1-- Create or refresh a user session
2INSERT INTO sessions (session_id, user_id, last_active, data)
3VALUES ('abc123', 42, NOW(), '{"cart": []}')
4AS new_session
5ON DUPLICATE KEY UPDATE
6    last_active = NOW(),
7    data = new_session.data;

Use INSERT IGNORE for: deduplication during migration

1-- Merge two user tables, keeping the first occurrence
2INSERT IGNORE INTO users_merged (email, name, created_at)
3SELECT email, name, created_at FROM users_table_a;
4
5INSERT IGNORE INTO users_merged (email, name, created_at)
6SELECT email, name, created_at FROM users_table_b;

Performance Considerations

For bulk operations with many conflicts, the performance difference matters:

1-- Benchmarking 100,000 rows with 50% duplicates:
2-- INSERT IGNORE:              ~1.2 seconds
3-- ON DUPLICATE KEY UPDATE:    ~1.8 seconds
4-- REPLACE INTO:               ~2.5 seconds

These are approximate numbers. Actual performance depends on table structure, index count, and hardware. The key insight: INSERT IGNORE is faster because it skips the update write I/O on conflicts.

For very high throughput, consider batching:

1-- Batch insert with IGNORE (faster than individual statements)
2INSERT IGNORE INTO events (event_id, type, timestamp) VALUES
3    (1, 'click', '2026-06-18 10:00:00'),
4    (2, 'view', '2026-06-18 10:00:01'),
5    -- ... hundreds more rows
6    (500, 'click', '2026-06-18 10:05:00');

PostgreSQL Equivalent

If you work with PostgreSQL, the equivalent syntax uses ON CONFLICT:

1-- PostgreSQL equivalent of INSERT IGNORE
2INSERT INTO users (id, email, name) VALUES (1, '[email protected]', 'Alice')
3ON CONFLICT (id) DO NOTHING;
4
5-- PostgreSQL equivalent of ON DUPLICATE KEY UPDATE
6INSERT INTO users (id, email, name) VALUES (1, '[email protected]', 'Alice Updated')
7ON CONFLICT (id) DO UPDATE SET
8    email = EXCLUDED.email,
9    name = EXCLUDED.name;

Common Pitfalls

• INSERT IGNORE silences all errors, not just duplicates. Type mismatches, NOT NULL violations, and out-of-range values are also converted to warnings. Check SHOW WARNINGS after the statement to catch silent failures.
• Auto-increment gaps. Both statements advance the auto-increment counter even when no row is inserted. This creates gaps in IDs. If sequential IDs matter to your application, investigate before using either.
• VALUES() is deprecated. In MySQL 8.0.20+, use the row alias syntax (AS new_row ... ON DUPLICATE KEY UPDATE col = new_row.col) instead of VALUES(col).
• REPLACE INTO deletes the old row. This fires ON DELETE triggers, cascades foreign keys, and assigns a new auto-increment ID. It is almost never what you want.
• Lock contention. ON DUPLICATE KEY UPDATE holds a write lock on the conflicting row during the update. Under very high concurrency, this can cause lock-wait timeouts. Consider batching or reducing conflict frequency.
• Checking affected rows. ROW_COUNT() returns 1 for insert, 2 for update, 0 for no-change. Application ORMs may interpret "2 rows affected" incorrectly. Test your ORM behavior.

Summary

• Use INSERT IGNORE when duplicates can be safely discarded without modifying existing data. It is simpler and faster.
• Use INSERT ... ON DUPLICATE KEY UPDATE when you need to merge or upsert: keep existing rows but update them with new values.
• Avoid REPLACE INTO unless you specifically need delete-then-insert semantics and understand the foreign key implications.
• In MySQL 8.0.20+, use the row alias syntax (AS new_row) instead of the deprecated VALUES() function.
• In PostgreSQL, the equivalent is ON CONFLICT DO NOTHING / ON CONFLICT DO UPDATE.
• Always check SHOW WARNINGS after INSERT IGNORE to catch silently swallowed errors beyond just duplicate keys.