A Pitfall of INSERT IGNORE

  • by
INSERT IGNORE is primarily used for preventing statements stopped from unique constraint violations by ignoring current duplicated rows. That is to say, the duplicated rows will not be inserted into the table.

But actually, INSERT IGNORE will try everything it can do to insert the table without errors during statement execution. Let's see an example. Suppose we have a table named fruits to store all kind of fruits in the supermarket.
mysql> show columns in fruits;
+-------+---------------------+------+-----+---------+----------------+
| Field | Type                | Null | Key | Default | Extra          |
+-------+---------------------+------+-----+---------+----------------+
| id    | tinyint(3) unsigned | NO   | PRI | NULL    | auto_increment |
| name  | varchar(8)          | YES  | UNI | NULL    |                |
+-------+---------------------+------+-----+---------+----------------+
2 rows in set (0.01 sec)

You can see there's a unique constraint and 8 characters in length on column name.

List all fruits.
mysql> select * from fruits;
+----+--------+
| id | name   |
+----+--------+
|  1 | apple  |
|  2 | banana |
|  3 | cherry |
|  4 | mango  |
+----+--------+
4 rows in set (0.00 sec)

Undoubtedly, we can't insert a duplicated value to this table.
mysql> insert into fruits (name) values ('apple');
ERROR 1062 (23000): Duplicate entry 'apple' for key 'name_unique'

We can use INSERT IGNORE to prevent errors.
mysql> insert ignore fruits (name) values ('apple');
Query OK, 0 rows affected (0.00 sec)

Please notice that, the number of rows affected is 0, which means no row inserted and no errors. This is a regular way to use INSERT IGNORE to prevent statements to be interrupted.

Now, we'd like to add a fruit named watermelon which is longer than 8 characters.
mysql> insert into fruits (name) values ('watermelon');
ERROR 1406 (22001): Data too long for column 'name' at row 1

Apparently, the length limit was taking effect. The statement was stopped because strings larger than 8 are not allowed on this column.

We use IGNORE instead of INTO like the following:
mysql> insert ignore fruits (name) values ('watermelon');
Query OK, 1 row affected, 1 warning (0.01 sec)

This time, watermelon was inserted without errors. Let's see the content of the table.
mysql> select * from fruits;
+----+----------+
| id | name     |
+----+----------+
|  1 | apple    |
|  2 | banana   |
|  3 | cherry   |
|  4 | mango    |
|  5 | watermel |
+----+----------+
5 rows in set (0.00 sec)

Oh, the value was truncated for fitting into the table no matter what SQL mode is currently in. This is what I want? No, I would rather get an error to notify me some of the values must be taken care of in advance than get an incomplete table without errors. INSERT IGNORE is very useful, but it might overkill the function. We as developers should be aware of this.

One alternative of INSERT IGNORE that can also avoid interruption of execution is to use an error handler in stored procedures.
declare continue handler for 1062 select 'Duplicated! please check it out.';
The error handler will continue to work when condition 1062 is met, which is the error for "Duplicate entry '%s' for key %d".

Leave a Reply

Your email address will not be published. Required fields are marked *