MySQL INSERT IGNORE: Skip Duplicates

Normally one bad row makes the whole INSERT fail. With INSERT IGNORE, MySQL skips the rows that cause certain errors, turns the errors into warnings, and inserts the rest. This is MySQL-specific.

query.sql

SQL

CREATE TABLE subscribers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(100) NOT NULL UNIQUE,
    name VARCHAR(20)
);
INSERT INTO subscribers (email, name) VALUES ('asha@example.com', 'Asha');

Without IGNORE, a duplicate email stops the statement:

query.sql

SQL

INSERT INTO subscribers (email, name) VALUES ('ben@example.com', 'Ben'), ('asha@example.com', 'Asha again');

Output

OUTPUT

ERROR 1062 (23000): Duplicate entry 'asha@example.com' for key 'subscribers.email'

Even ben@example.com, which was fine, was not inserted. With IGNORE:

query.sql

SQL

1
2
3

INSERT IGNORE INTO subscribers (email, name) VALUES ('ben@example.com', 'Ben'), ('asha@example.com', 'Asha again');
SHOW WARNINGS;
SELECT id, email, name FROM subscribers ORDER BY id;

Output

OUTPUT

+---------+------+----------------------------------------------------------------+
| Level   | Code | Message                                                        |
+---------+------+----------------------------------------------------------------+
| Warning | 1062 | Duplicate entry 'asha@example.com' for key 'subscribers.email' |
+---------+------+----------------------------------------------------------------+
1 row in set

+----+------------------+------+
| id | email            | name |
+----+------------------+------+
|  1 | asha@example.com | Asha |
|  4 | ben@example.com  | Ben  |
+----+------------------+------+
2 rows in set

The duplicate row was skipped with a warning. ben was inserted.

What IGNORE changes

IGNORE downgrades these errors to warnings:

Problem	Without `IGNORE`	With `IGNORE`
Duplicate `PRIMARY KEY` / `UNIQUE` value	Error, statement fails	Row skipped
Data too long for a column	Error	Value cut to fit
Wrong data type (text into a number column)	Error	Converted to the nearest value (often `0`)
`NOT NULL` column given `NULL`	Error	Replaced by the type’s implicit default
Value out of range	Error	Clipped to the nearest valid value

query.sql

SQL

1
2
3

INSERT IGNORE INTO subscribers (email, name) VALUES ('chitra@example.com', 'Chitra from a very long name');
SHOW WARNINGS;
SELECT email, name FROM subscribers WHERE email = 'chitra@example.com';

Output

OUTPUT

+---------+------+-------------------------------------------+
| Level   | Code | Message                                   |
+---------+------+-------------------------------------------+
| Warning | 1265 | Data truncated for column 'name' at row 1 |
+---------+------+-------------------------------------------+
1 row in set

+--------------------+----------------------+
| email              | name                 |
+--------------------+----------------------+
| chitra@example.com | Chitra from a very l |
+--------------------+----------------------+
1 row in set

The name was cut to 20 characters (Chitra from a very l). You asked MySQL to ignore the problem, and it silently damaged the data. That is the danger.

When to use it

Loading data where duplicates are expected and harmless, such as importing a list of emails that may overlap with the table.
Idempotent jobs (“insert if not already there”) when you are sure that the only possible error is the duplicate.

When not to use it

When you cannot afford wrong or truncated data. IGNORE hides every kind of error, not only duplicates.
When you need to know which rows were skipped. Use ON DUPLICATE KEY UPDATE or check the warnings.

A safer pattern for “insert only if new” is to be explicit:

query.sql

SQL

1
2
3

INSERT INTO subscribers (email, name)
SELECT 'dev@example.com', 'Dev'
WHERE NOT EXISTS (SELECT 1 FROM subscribers WHERE email = 'dev@example.com');

This still needs the UNIQUE key to be safe under concurrency, since two connections could both find “not exists” at the same moment. The UNIQUE index makes the second one fail rather than create a duplicate.

dangerous

Keep strict mode

IGNORE overrides strict mode for that statement only. Treat every use as a deliberate decision, and read the warnings.