The INSERT statement is one of the most fundamental and useful statements in MySQL and any other database.
It allows us to insert the records into an existing table by referencing the table name and providing the layout of the table columns. We then provide the values that we wish to insert into the table.
However, when it comes to the INSERT statement, we need to ensure that every specified row does not contain an error. Otherwise, the entire clause will fail. This can be incredibly inefficient when dealing with multiple insert records.
It is good to have a fallback that allows MySQL to selectively insert the rows that contain valid values and ignore the ones that do not fit the requirements. This is where there INSERT IGNORE clause comes into play.
In this tutorial, we will learn how to use the INSERT IGNORE clause to allow MySQL to disregard any row that contains an invalid data instead of terminating the entire insert block.
Syntax:
The following shows the basic syntax of the INSERT IGNORE statement in the MySQL database:
VALUES(value_list),
(value_list),
The INSERT IGNORE clause is specific to the MySQL database and is not supported in other database systems as it is not part of the SQL standard.
However, other database engines provide a similar implementation using clauses such as INSERT ON DUPLICATE KEY UPDATE, INSERT INTO ON CONFLICT DO NOTHING (PostgreSQL) and more.
MySQL INSERT IGNORE Clause
Let us explore an example that demonstrates how to use the INSERT IGNORE clause in MySQL.
Consider the example table which creates a basic table that contains the product information:
product_id INT PRIMARY KEY AUTO_INCREMENT,
product_name VARCHAR(255),
category VARCHAR(255),
price DECIMAL(10, 2),
quantity INT,
expiration_date DATE,
barcode BIGINT
);
Once we created the table, let us attempt to insert the rows into the table with some rows containing an error.
VALUES
('Chef Hat 25cm', 'bakery', 24.67, 57, '2023-09-09', 2854509564204),
('Quail Eggs - Canned', 'pantry', 17.99, 67, '2023-09-29', 1708039594250),
(,'bakery', 92.53, 10, '2023-09-22', 8704051853058);
You might notice that the last row contains an error. This is deliberate to demonstrate what happens when we attempt to insert the data as provided in the previous example.
Once we run the previous query, it should return the error message as follows:
As a result, MySQL will terminate the entire INSERT statements even though the first two rows are valid. To overcome this, we can use the INSERT IGNORE clause as follows:
VALUES
('Chef Hat 25cm', 'bakery', 24.67, 57, '2023-09-09', 2854509564204),
('Quail Eggs - Canned', 'pantry', 17.99, 67, '2023-09-29', 1708039594250),
(,'bakery', 92.53, 10, '2023-09-22', 8704051853058);
MySQL will insert the valid records and ignore the invalid one.
Conclusion
In this tutorial, we learned how to use the INSERT IGNORE clause in MySQL to skip any row that contains an error during an INSERT statement while applying the valid ones.
