MySQL INSERT IGNORE – A MySQL Syntax Tip

Overview – MySQL INSERT IGNORE

In MySQL, when you perform a mysql insert query, it is very common to come across errors with one of the insert queries. Once an issue is encountered with a mysql insert, the rest of the mysql inserts will not be executed since the MySQL query won’t continue. This can be annoying since you will have to figure out which rows were inserted and which ones weren’t so you can manually insert the remaining rows.

Fortunately, you can use mysql insert ignore syntax to avoid this frustration by using the keyword ‘ignore’.

MySQL INSERT IGNORE

Consider the following mysql insert statement that would insert two records:

INSERT INTO `table` ('name','surname','email') VALUES ('Mark','Ringo','mark@example.com'),('Sarah','Ainsley','sarah@example.com');

If the first insert (mark ringo) had to fail for some reason, then Sarah would not be inserted UNLESS you add the word ‘ignore’ after mysql insert statement, making the above statement:

INSERT IGNORE INTO `table` .......

Now if the first entry fails, then Sarah will still get inserted. Pretty nifty eh’ and can save countless hours of frustration while trying to import partial records and missing rows from an incomplete mysql insert.

Use With Caution

Please use the mysql insert ignore with caution. Errors and warnings that creep up are there for a reason. MySQL INSERT IGNORE can save a lot of frustration and simplify inserting records however it can end up masking some issues that exist.