MySQL INSERT IGNORE: Skip Duplicates and Read Warnings

INSERT IGNORE tells MySQL to continue past certain row errors instead of stopping the insert statement. It is often used to skip rows that conflict with a PRIMARY KEY or UNIQUE index. However, it can also turn some invalid values into warnings and insert adjusted values, so IGNORE does more than skip duplicates.

For the standard INSERT syntax, see MySQL INSERT. For multi-row VALUES statements, see MySQL INSERT multiple rows.

MySQL INSERT IGNORE syntax

INSERT IGNORE INTO table_name (column_1, column_2)
VALUES
    (value_1a, value_2a),
    (value_1b, value_2b);

The IGNORE modifier can be used with a single row or multiple rows. MySQL skips rows with ignorable errors, reports warnings, and continues with other rows where it can.

Skip a duplicate key row

The following example creates a table whose email column must be unique:

CREATE TABLE subscriber_demo (
    email VARCHAR(100) NOT NULL,
    source VARCHAR(20) NOT NULL,
    PRIMARY KEY (email)
);

INSERT INTO subscriber_demo (email, source)
VALUES ('[email protected]', 'website');

Now try to insert Ava’s address again together with a new subscriber:

INSERT IGNORE INTO subscriber_demo (email, source)
VALUES
    ('[email protected]', 'import'),
    ('[email protected]', 'import');

MySQL skips the row that duplicates the primary key and inserts the new address. Run SHOW WARNINGS immediately after the insert to inspect the duplicate warning:

SHOW WARNINGS;

Then check the stored rows:

SELECT email, source
FROM subscriber_demo
ORDER BY email;

The existing Ava row remains unchanged; INSERT IGNORE does not update duplicate rows:

+------------------+---------+
| email            | source  |
+------------------+---------+
| [email protected]  | website |
| [email protected] | import  |
+------------------+---------+

IGNORE can change invalid values

IGNORE can also convert some data errors into warnings and adjust the inserted value. For example, a signed TINYINT column cannot store 300. With INSERT IGNORE, MySQL can clip an out-of-range value to the closest value the column can store, rather than aborting the statement:

CREATE TABLE quantity_demo (
    quantity TINYINT NOT NULL
);

INSERT IGNORE INTO quantity_demo (quantity)
VALUES (300);

SHOW WARNINGS;

SELECT quantity
FROM quantity_demo;

For a signed TINYINT, the stored value is 127. Inspect warnings and validate inserted data instead of assuming IGNORE only discards duplicates.

INSERT IGNORE vs. ON DUPLICATE KEY UPDATE

Choose the statement that matches the desired result when a key already exists:

• Use INSERT IGNORE to leave the existing row unchanged and skip the conflicting row.
• Use INSERT ... ON DUPLICATE KEY UPDATE to change the existing row when a duplicate key is found. See the MySQL upsert syntax.
• Use plain INSERT when a duplicate key should be treated as an error and reviewed by the application.

INSERT IGNORE does not ignore every possible error. Syntax errors and other non-ignorable failures can still stop the statement. Review the warnings after the insert, especially when input comes from an import or application-generated batch.

Summary

• INSERT IGNORE skips rows with certain errors, including duplicate primary or unique keys, and reports warnings.
• Existing duplicate rows are not updated.
• Some invalid values can be adjusted and inserted with a warning, so check SHOW WARNINGS and validate the data.

For exact behavior and supported forms, see the MySQL 8.4 Reference Manual: INSERT Statement.