MySQL IGNORE Clause Explained

In MySQL, we can use the IGNORE clause in statements that change data in order to ignore certain errors that might occur had we not used it. When IGNORE is used, such errors are downgraded to warnings.

For example, we can use IGNORE in an INSERT statement to ignore any errors we might normally get if we tried to insert a NULL value into a NOT NULL column. In such a case, MySQL won’t return an error. Instead, it will deal with the issue in another way, and provide us with a warning.

If we have strict mode enabled, we can use IGNORE to force MySQL to act as though strict mode is disabled. However, IGNORE can also be used to downgrade certain errors regardless of the strict mode setting.

Example

Suppose we create a table with NOT NULL columns:

CREATE TABLE Products (
    ProductId int NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ProductName varchar(255) NOT NULL,
    ProductDescription varchar(1000) NOT NULL,
    Price decimal(8,2) NOT NULL
);

And now let’s try to insert data that contains NULL into one of those NOT NULL columns:

INSERT INTO Products ( ProductName, ProductDescription, Price )
VALUES 
    ( 'Hammer', 'Left handed edition', 5.45 ),
    ( 'Saw', NULL, 7.50 );

Result:

ERROR 1048 (23000): Column 'ProductDescription' cannot be null

We get an error that tells us the column cannot be NULL.

This is to be expected because the column is defined as NOT NULL. I have strict mode enabled, and so we get an error. Strict mode ensures that we get errors whenever we try to do stuff like this. If we disabled strict mode, we would find that the error would disappear and we’d get a warning instead.

However, we can alternatively add the IGNORE keyword to our INSERT statement to get the same effect.

Example:

INSERT IGNORE INTO Products ( ProductName, ProductDescription, Price )
VALUES 
    ( 'Hammer', 'Left handed edition', 5.45 ),
    ( 'Saw', NULL, 7.50 );

Result:

Query OK, 2 rows affected, 1 warning (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 1

This time we didn’t get the error. Instead, we can see that both rows were inserted with a warning.

Let’s take a look at the warning:

SHOW WARNINGS;

Result:

+---------+------+--------------------------------------------+
| Level   | Code | Message                                    |
+---------+------+--------------------------------------------+
| Warning | 1048 | Column 'ProductDescription' cannot be null |
+---------+------+--------------------------------------------+

So the warning says the same thing that the error told us – the column cannot be NULL.

So if the column can’t be NULL, how did the insert succeed? Surely it didn’t insert a NULL value into the column?

That’s right, it didn’t.

When we use IGNORE to ignore the error, MySQL follows certain rules that depend on the situation. In our case, we were trying to insert a NULL value into a NOT NULL column. In this case, MySQL inserted the implicit default value for the column. The column is string column (a varchar(1000) to be precise), and so it inserted an empty string (which is the implicit default value for a string column).

Let’s take a look:

SELECT * FROM Products;

Result:

+-----------+-------------+---------------------+-------+
| ProductId | ProductName | ProductDescription  | Price |
+-----------+-------------+---------------------+-------+
|         1 | Hammer      | Left handed edition |  5.45 |
|         2 | Saw         |                     |  7.50 |
+-----------+-------------+---------------------+-------+

So in our case, the ProductDescription column contains an empty string.

But this isn’t the only scenario. There are other scenarios that cause errors, and the way MySQL resolves it will depend on the scenario. Generally, it will either insert adjusted values or skip the offending row/s.

Numeric Column with No Default Value

Our price column has a decimal(8,2) data type. Here’s what happens when we omit the Price column from an insert operation (without using the IGNORE clause):

INSERT INTO Products ( ProductName, ProductDescription )
VALUES 
    ( 'Screw Driver', 'Right handed edition' );

Result:

ERROR 1364 (HY000): Field 'Price' doesn't have a default value

In this case, we get an error that tells us there’s no default value for the Price column. Here, MySQL doesn’t know what to insert, because we didn’t specify a value, and we didn’t specify a default value.

Let’s add the IGNORE keyword:

INSERT IGNORE INTO Products ( ProductName, ProductDescription )
VALUES 
    ( 'Screw Driver', 'Right handed edition' );

Result:

Query OK, 1 row affected, 1 warning (0.00 sec)

This time the insert operation succeeded, and we got a warning.

Let’s check the warning:

SHOW WARNINGS;

Result:

+---------+------+--------------------------------------------+
| Level   | Code | Message                                    |
+---------+------+--------------------------------------------+
| Warning | 1364 | Field 'Price' doesn't have a default value |
+---------+------+--------------------------------------------+

As expected, the warning says the same thing as the error.

Let’s take a look at our table’s data:

SELECT * FROM Products;

Result:

+-----------+--------------+----------------------+-------+
| ProductId | ProductName  | ProductDescription   | Price |
+-----------+--------------+----------------------+-------+
|         1 | Hammer       | Left handed edition  |  5.45 |
|         2 | Saw          |                      |  7.50 |
|         3 | Screw Driver | Right handed edition |  0.00 |
+-----------+--------------+----------------------+-------+

The Price column contains 0.00 for the screw driver, which is the implicit default value for the decimal(8,2) data type. If it had been an int column it would’ve been set to 0.

Primary Key Column

Now let’s try to insert a duplicate value into our primary key column (without using the IGNORE keyword):

INSERT INTO Products ( ProductId, ProductName, ProductDescription, Price )
VALUES 
    ( 4, 'Wrench', 'The monkey type', 9.20 ),
    ( 1, 'Axe', 'Sharpest tool in the shed', 15.00 );

Result:

ERROR 1062 (23000): Duplicate entry '1' for key 'products.PRIMARY'

As expected, we get an error because a primary key column can’t contain duplicate values. Our second row contains 1, which would be a duplicate of the value in the first row.

Let’s check the data:

SELECT * FROM Products;

Result:

+-----------+--------------+----------------------+-------+
| ProductId | ProductName  | ProductDescription   | Price |
+-----------+--------------+----------------------+-------+
|         1 | Hammer       | Left handed edition  |  5.45 |
|         2 | Saw          |                      |  7.50 |
|         3 | Screw Driver | Right handed edition |  0.00 |
+-----------+--------------+----------------------+-------+

Nothing was inserted. Even our first attempted row wasn’t inserted, even though there’s no problem with that row. So our error prevented any rows from being inserted.

Now let’s use the IGNORE clause:

INSERT IGNORE INTO Products ( ProductId, ProductName, ProductDescription, Price )
VALUES 
    ( 4, 'Wrench', 'The monkey type', 9.20 ),
    ( 1, 'Axe', 'Sharpest tool in the shed', 15.00 );

Result:

Query OK, 1 row affected, 1 warning (0.00 sec)
Records: 2  Duplicates: 1  Warnings: 1

Check the warning:

SHOW WARNINGS;

Result:

+---------+------+------------------------------------------------+
| Level   | Code | Message                                        |
+---------+------+------------------------------------------------+
| Warning | 1062 | Duplicate entry '1' for key 'products.PRIMARY' |
+---------+------+------------------------------------------------+

As expected, the same message as the error.

However, in this case, one row was inserted. So let’s take a look at the data:

SELECT * FROM Products;

Result:

+-----------+--------------+----------------------+-------+
| ProductId | ProductName  | ProductDescription   | Price |
+-----------+--------------+----------------------+-------+
|         1 | Hammer       | Left handed edition  |  5.45 |
|         2 | Saw          |                      |  7.50 |
|         3 | Screw Driver | Right handed edition |  0.00 |
|         4 | Wrench       | The monkey type      |  9.20 |
+-----------+--------------+----------------------+-------+

We can see that the wrench was inserted successfully. Only the row with the duplicate value wasn’t inserted.

So the above examples demonstrate that when we use the IGNORE keyword, we can get different results depending on the issue that was causing the error. We need to keep this in mind whenever we try to use the IGNORE clause to downgrade errors to just warnings.

Statements that Support the `IGNORE` Clause

We can use the IGNORE clause with the following statements:

INSERT [ IGNORE ]
UPDATE [ IGNORE ]
DELETE [ IGNORE ]
LOAD DATA [ IGNORE ]
LOAD XML [ IGNORE ]
CREATE TABLE… [ IGNORE ] SELECT

More Information

See the MySQL documentation for more detail on implicit default handling.

Also see this blog post on the MySQL website for a more detailed explanation of how the IGNORE clause works, and for which errors it caters for.

And here’s the actual worklog where the IGNORE clause was redefined and reimplemented in MySQL 5.7.

Example

Numeric Column with No Default Value

Primary Key Column

Statements that Support the IGNORE Clause

More Information

Statements that Support the `IGNORE` Clause