MySQL INSERT IGNORE

Insert Ignore statement in MySQL has a special feature that ignores the invalid rows whenever we are inserting single or multiple rows into a table. We can understand it with the following explanation, where a table contains a primary key column.

The primary key column cannot stores duplicate values into a table. For example, student_roll_number should always be unique for every student. Similarly, the employee_id in the company should always be distinct into the employee table. When we try to insert a duplicate record into a table with a primary key column, it produces an error message. However, if we use the INSERT IGNORE statement to add duplicate rows into a table with a primary key column, MySQL does not produce any error. This statement is preferred when we are trying to insert records in bulk, and resulting errors can interrupt the execution process. As a result, it does not store any record into a table. In such a case, the INSERT IGNORE statement only generates the warnings.

Below are the cases where an INSERT IGNORE statement avoids error:

• When we will try to insert a duplicate key where the column of a table has a PRIMARY or UNIQUE KEY constraint.
• When we will try to add a NULL value where the column of a table has a NOT NULL constraint.
• When we will try to insert a record to a partitioned table where the entered values do not match the format of listed partitions.

Syntax

The following are a syntax to use the INSERT IGNORE statement in MySQL:

INSERT IGNORE INTO table_name (column_names)  

VALUES ( value_list), ( value_list) .....;

MySQL INSERT IGNORE Example

Let us understand how INSERT IGNORE statement works in MySQL with the help of an example. First, we need to create a table named “Student” using the following statement:

CREATE TABLE Student (  

  Stud_ID int AUTO_INCREMENT PRIMARY KEY,  

  Name varchar(45) DEFAULT NULL,  

  Email varchar(45) NOT NULL UNIQUE,  

  City varchar(25) DEFAULT NULL  

);

The UNIQUE constraint ensures that we cannot insert duplicate values into the email column. Next, it is required to insert the records into the table. We can execute the below statement to add data into a table:

INSERT INTO Student(Stud_ID, Name, Email, City)   

VALUES (1,'Stephen', '[email protected]', 'Texax'),   

(2, 'Joseph', '[email protected]', 'Alaska'),   

(3, 'Peter', '[email protected]', 'california');

Finally, execute the SELECT statement to verify the insert operation:

SELECT * FROM Student;  

We can see the below output where we have three rows into the table:

Let us execute the below statement that will try to add two records into the table:

INSERT INTO Student(Stud_ID, Name, Email, City)   

VALUES (4,'Donald', '[email protected]', 'New York'),   

(5, 'Joseph', '[email protected]', 'Chicago');

It will produce an error: ERROR 1062 (23000): Duplicate entry ‘[email protected]’ for key ‘student.Email’ because of the email violets the UNIQUE constraint.

Now, let us see what happened if we use the INSERT IGNORE statement into the above query:

INSERT IGNORE INTO Student(Stud_ID, Name, Email, City)   

VALUES (4,'Donald', '[email protected]', 'New York'),   

(5, 'Joseph', '[email protected]', 'Chicago');

MySQL will produce a message: one row added, and the other row was ignored.

1 row affected, 1 warning(s): 1062 Duplicate entry for key email.  

Records: 2  Duplicates: 1  Warning: 1

We can see the detailed warning using the SHOW WARNINGS command:

Thus, we can say that if we use the INSERT IGNORE statement, MySQL gives a warning instead of issuing an error.

MySQL INSERT IGNORE and STRICT mode

In MySQL, STRICT MODE handles invalid or missing values that are going to be added into a table using INSERT OR UPDATE statement. If the strict mode is ON, and we are trying to add invalid values into a table using the INSERT statement, the statement is aborted, and we will get an error message.

However, if we use the INSERT IGNORE command, MySQL produces a warning message instead of throwing an error. Also, this statement tries to truncate values to make them valid before inserting it into the table.Let us understand it with the help of an example. First, we are going to create a table named “Test” using the below statement:

CREATE TABLE Test (  
    ID int AUTO_INCREMENT PRIMARY KEY,  
    Name varchar(5) NOT NULL  
); 

In the above table, the name column only accepts the string whose length is less than or equal to five characters. Now, execute the below statement to insert the records into a table.

INSERT INTO Test(Name)  

VALUES ('Peter'), ('John');

We can see that the specified name validates the name column constraint so it will be added successfully. Execute the SELECT statement to verify the result. It will give the output as below:

Next, insert the name whose length is greater than five:

INSERT INTO Test(Name) VALUES ('Stephen');

MySQL does not add values and gives an error message because the strict mode is ON. However, if we use the INSERT IGNORE statement to insert the same string, it will give the warning message instead of throwing an error.

INSERT IGNORE INTO Test(Name) VALUES ('Stephen');  

Finally, we have executed the SHOW WARNINGS command to check the warning message. The below output explains it more clearly that shows MySQL tries to truncate data before inserting it into a table.