INSERT INTO vs INSERT IGNORE
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)
As you can see, there's an 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)
Insert Duplicate Row within Length Limit
INSERT INTO
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'
INSERT IGNORE
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.
Insert Duplicate Row out of Length Limit
INSERT INTO
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.
INSERT IGNORE
To avoid interruption in a stored procedure, we may 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)
Successful but Truncated
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".