MySQL MariaDB

MySQL INSERT IGNORE Clause

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:

INSERT IGNORE INTO table(column_list)
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:

CREATE TABLE products (
    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.

INSERT INTO products (product_name, category, price, quantity, expiration_date, barcode)
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:

SQL Error [1064] [42000]: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ','bakery', 92.53, 10, '2023-09-22', 8704051853058)' at line 5

 
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:

INSERT IGNORE INTO products (product_name, category, price, quantity, expiration_date, barcode)
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.

About the author

John Otieno

My name is John and am a fellow geek like you. I am passionate about all things computers from Hardware, Operating systems to Programming. My dream is to share my knowledge with the world and help out fellow geeks. Follow my content by subscribing to LinuxHint mailing list