INSERT IGNORE in MySQL

When you try to insert a new record in MySQL and the primary key specified in the insert query already exists, then an error will occur and the query will be aborted. Using the "IGNORE" keyword prevents errors from occurring. The records containing duplicate primary keys are ignored and the rest of the records are added. In the former case, no record will be inserted if such an error occurs.


Although you shouldn't normally attempt to insert a record without first checking if the primary key you want to use has already been used, there may be times when this is required, such as when multiple developers need to update their own copies of a database, and a particular record may already exist in one or other of the databases.

The syntax is simple - just add "IGNORE" after "INSERT". Lets see an example:

INSERT IGNORE INTO tablename (id, field1, field2) VALUES (1, 'value1', 'value2');

In the above query, the id is assumed to be the primary key. If a record in the table 'tablename' has already a record with id = 1 then the above query will just be ignored instead of producing a warning.

Note: Use the IGNORE keyword only during the development phase. It could cause unexpected results as you cannot identify if the record has been inserted or not.