Introduction

In MySQL, ON DUPLICATE KEY IGNORE is not a real clause. When developers ask for it, they usually want one of two actual behaviors: skip rows that violate a unique key, or update the existing row when a conflict happens.

The Two Real MySQL Options

MySQL gives you two main conflict-handling patterns:

• 'INSERT IGNORE'
• 'INSERT ... ON DUPLICATE KEY UPDATE'

They solve different problems, so choosing between them is a business-rule decision, not just a syntax preference.

Use INSERT IGNORE to Skip Conflicts

If the requirement is “try to insert these rows, but keep going when a duplicate appears”, INSERT IGNORE is the closest match:

1CREATE TABLE users (
2    id INT PRIMARY KEY,
3    email VARCHAR(255) UNIQUE
4);
5
6INSERT IGNORE INTO users (id, email)
7VALUES
8    (1, '[email protected]'),
9    (1, '[email protected]'),
10    (2, '[email protected]');

In this example, the duplicate id value conflicts with an existing unique key. MySQL skips the offending row and inserts the rows that do not violate constraints.

That sounds convenient, but the tradeoff is important: the rejected row is not retried or merged. It is simply ignored, usually with a warning instead of a hard error.

Use ON DUPLICATE KEY UPDATE to Merge

If the requirement is “insert when new, otherwise modify the existing row”, use ON DUPLICATE KEY UPDATE:

1INSERT INTO users (id, email)
2VALUES (1, '[email protected]')
3ON DUPLICATE KEY UPDATE
4    email = '[email protected]';

This is not an ignore operation. The conflicting row becomes an update. That distinction matters because an update can overwrite good data if you apply it too casually.

You can update multiple columns:

1INSERT INTO users (id, email, last_seen_at)
2VALUES (1, '[email protected]', NOW()) AS incoming
3ON DUPLICATE KEY UPDATE
4    email = incoming.email,
5    last_seen_at = incoming.last_seen_at;

This pattern is often called an upsert, even though MySQL uses different syntax than some other databases. In current MySQL examples, the row-alias style is preferred over older VALUES(column) references.

Why MySQL Does Not Have ... IGNORE in That Clause

The missing syntax is actually helpful because ignore and update mean very different things:

• ignore means keep the existing row untouched
• update means mutate the existing row

Those are not small implementation details. They are two separate data-management policies. A database should make you choose clearly.

Compare with Other Databases

Confusion often comes from switching between SQL dialects. PostgreSQL, for example, uses ON CONFLICT DO NOTHING and ON CONFLICT DO UPDATE. Developers then look for a MySQL phrase that sounds similar and guess ON DUPLICATE KEY IGNORE.

In MySQL, the mapping is roughly:

• PostgreSQL DO NOTHING maps to INSERT IGNORE
• PostgreSQL DO UPDATE maps to ON DUPLICATE KEY UPDATE

The wording is different, but the decision is the same: skip the conflicting row or handle it explicitly.

Check Affected Rows and Warnings

If you use INSERT IGNORE, do not assume success means every row was written. You should inspect the affected row count or warnings, especially in batch ingestion jobs:

1INSERT IGNORE INTO users (id, email)
2VALUES (1, '[email protected]'), (2, '[email protected]');
3
4SHOW WARNINGS;

That helps you detect whether ignored duplicates are expected noise or a sign that upstream data quality is deteriorating.

Be Careful with IGNORE

IGNORE can suppress more than just duplicate-key failures by converting some errors into warnings. That means it should not be used as a generic “make the insert stop failing” switch.

If duplicates are supposed to be rare, it can be safer to let the statement fail, log the problem, and fix the source. Silent tolerance is only a good idea when it matches the data contract.

Common Pitfalls

• Writing ON DUPLICATE KEY IGNORE and expecting MySQL to parse it.
• Using INSERT IGNORE when the real requirement was to update the existing row.
• Using ON DUPLICATE KEY UPDATE when the existing row should have remained unchanged.
• Ignoring warnings and affected row counts, which hides skipped rows during batch imports.
• Treating duplicate-key conflicts as harmless when they may indicate broken upstream logic.

Summary

• 'ON DUPLICATE KEY IGNORE is not valid MySQL syntax.'
• Use INSERT IGNORE when duplicate rows should be skipped.
• Use ON DUPLICATE KEY UPDATE when duplicates should update existing rows.
• Choose the statement based on the real data rule, not just on whether you want to avoid an error.
• Monitor warnings and row counts so duplicate handling does not hide data-quality issues.