4. INSERT IGNORE

(explain the INSERT IGNORE statement that inserts rows into a table and ignores rows that cause errors.)

Summary: in this tutorial, you will learn how to use the MySQL INSERT IGNOREstatement to insert data into a table.

Introduction to MySQL INSERT IGNORE statement

When you use the INSERT statement to add multiple rows to a table and if an error occurs during the processing, MySQL terminates the statement and returns an error. Consequently, the table remains unchanged with no inserted rows.

The INSERT IGNORE statement allows you to disregard rows containing invalid data that would otherwise trigger an error and insert only rows that contain valid data.

Here’s the basic syntax of the INSERT IGNORE statement:

INSERT IGNORE INTO table(column_list)
VALUES(value_list),
      (value_list),
      ...;

Note that the IGNORE option is an extension of MySQL to the SQL standard.

MySQL INSERT IGNORE example

We will create a new table called subscribers for the demonstration.

CREATE TABLE subscribers (
    id INT PRIMARY KEY AUTO_INCREMENT,
    email VARCHAR(130) NOT NULL UNIQUE
);

The UNIQUE constraint ensures that no duplicate email exists in the emailcolumn.

The following statement inserts a new row into the subscribers table:

INSERT INTO subscribers(email)
VALUES('john.doe@gmail.com');

It worked as expected.

Let’s execute another statement that inserts two rows into the subscriberstable:

INSERT INTO subscribers(email)
VALUES('john.doe@gmail.com'),
      ('jane.smith@ibm.com');

It returns an error.

Error Code: 1062. Duplicate entry 'john.doe@gmail.com' for key 'email'Code language: SQL (Structured Query Language) (sql)

As indicated in the error message, the email john.doe@gmail.com violates the UNIQUE constraint.

However, if you use the INSERT IGNORE statement instead.

INSERT IGNORE INTO subscribers(email)
VALUES('john.doe@gmail.com'),
      ('jane.smith@ibm.com');

MySQL returned a message indicating that one row was inserted and the other row was ignored.

1 row(s) affected, 1 warning(s): 1062 Duplicate entry 'john.doe@gmail.com' for key 'email' Records: 2  Duplicates: 1  Warnings: 1

To find the detail of the warning, you can use the SHOW WARNINGS command as shown below:

SHOW WARNINGS;

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

If you query data from subscribers table, you will find that only one row was inserted and the row that caused the error was not.

MySQL INSERT IGNORE and STRICT mode

When the strict mode is on, MySQL returns an error and aborts the INSERTstatement if you try to insert invalid values into a table.

However, if you use the INSERT IGNORE statement, MySQL will issue a warning instead of an error. In addition, it will try to adjust the values to make them valid before adding the value to the table.

Consider the following example.

First, we create a new table named tokens:

CREATE TABLE tokens (
    s VARCHAR(6)
);Code language: SQL (Structured Query Language) (sql)

In this table, the column s accepts only strings whose lengths are less than or equal to six.

Second, insert a string whose length is seven into the tokens table.

INSERT INTO tokens VALUES('abcdefg');Code language: SQL (Structured Query Language) (sql)

MySQL issued the following error because the strict mode is on.

Error Code: 1406. Data too long for column 's' at row 1Code language: SQL (Structured Query Language) (sql)

Third, use the INSERT IGNORE statement to insert the same string.

INSERT IGNORE INTO tokens VALUES('abcdefg');Code language: SQL (Structured Query Language) (sql)

MySQL truncated data before inserting it into the tokens table. In addition, it issues a warning.

Summary

Use the MySQL INSERT IGNORE statement to insert rows into a table and ignore errors for rows that cause errors.