MySQL INSERT IGNORE

When you use the INSERT statement to add multiple rows to a table and if an error occurs during processing, MySQL terminates the statement and returns an error. As a result, no rows are inserted into the table.

However, if you use the INSERT IGNORE statement, rows containing invalid data that caused the error are ignored and rows containing valid data are inserted into the table.
 

 

Example using the INSERT IGNORE statement

We will create a new table called “Contact” for this example.

CREATE TABLE Contact (
    id INT PRIMARY KEY AUTO_INCREMENT,
    phoneNbr VARCHAR(50) NOT NULL UNIQUE
);

The UNIQUE constraint ensures that no duplicate phone numbers exist in “phoneNbr” column.

The following statement inserts a new row in the “Contact” table:

INSERT INTO Contact(phoneNbr) VALUES('0825480036');

 
This works as expected.

Now let’s execute another statement that inserts two rows into the “Contact” table:

INSERT INTO Contact(phoneNbr) VALUES('0825480036'), ('0288903471');

This returns an error.
 

 
As shown in the error message, the phone number ‘0825480036’ violates the UNIQUE constraint. Therefore the second number is not inserted in “Contact” table.
 

 

 
If you use the INSERT IGNORE statement instead, the second number will be inserted into “Contact” table.

INSERT IGNORE INTO Contact(phoneNbr) VALUES('0725480036'), ('0788903471');

 

 
To check if the second number is inserted in “Contact” table, we run the following query:
 

 
In conclusion, when you use the INSERT IGNORE statement instead of issuing an error, MySQL issues a warning if an error occurs.
 

MCQPractice competitive and technical Multiple Choice Questions and Answers (MCQs) with simple and logical explanations to prepare for tests and interviews.Read More