{"id":6091,"date":"2017-07-06T20:41:46","date_gmt":"2017-07-07T03:41:46","guid":{"rendered":"http:\/\/www.mysqltutorial.org\/?page_id=6091"},"modified":"2024-01-04T20:24:56","modified_gmt":"2024-01-05T03:24:56","slug":"mysql-insert-ingore","status":"publish","type":"page","link":"https:\/\/www.mysqltutorial.org\/mysql-basics\/mysql-insert-ingore\/","title":{"rendered":"MySQL INSERT IGNORE Statement"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use the MySQL INSERT IGNORE<\/code> statement to insert data into a table.<\/p>\n\n\n\n

Introduction to MySQL INSERT IGNORE statement<\/h2>\n\n\n\n

When you use the INSERT<\/code><\/a> statement to add multiple rows to a table and if an error occurs during the processing, MySQL terminates the statement\u00a0and returns an error. Consequently, the table remains unchanged with no inserted rows.<\/p>\n\n\n\n

The INSERT IGNORE<\/code> statement allows you to disregard rows containing invalid data that would otherwise trigger an error and insert only rows that contain valid data.<\/p>\n\n\n\n

Here’s the basic syntax of the INSERT IGNORE<\/code> statement:<\/p>\n\n\n

INSERT<\/span> IGNORE<\/span> INTO<\/span> table<\/span>(column_list)\nVALUES<\/span>(value_list),\n      (value_list),\n      ...;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Note that the IGNORE<\/code> option is an extension of MySQL to the SQL standard.<\/p>\n\n\n\n
MySQL INSERT IGNORE example<\/h2>\n\n\n\n
We will create a new table<\/a> called subscribers<\/code> for the demonstration.<\/p>\n\n\n
CREATE<\/span> TABLE<\/span> subscribers (\n    id<\/span> INT<\/span> PRIMARY KEY<\/span> AUTO_INCREMENT,\n    email VARCHAR<\/span>(130<\/span>) NOT<\/span> NULL<\/span> UNIQUE<\/span>\n);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The following statement inserts a new row into the  subscribers<\/code> table:<\/p>\n\n\n
INSERT<\/span> INTO<\/span> subscribers(email)\nVALUES<\/span>('john.doe@gmail.com'<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
It worked as expected.<\/p>\n\n\n\n
Let’s execute another statement that inserts two rows into the  subscribers<\/code> table:<\/p>\n\n\n
INSERT<\/span> INTO<\/span> subscribers(email)\nVALUES<\/span>('john.doe@gmail.com'<\/span>), \n      ('jane.smith@ibm.com'<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
It returns an error.<\/p>\n\n\n
Error Code: 1062. Duplicate entry 'john.doe@gmail.com' for key 'email'<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
As indicated in the error message, the email john.doe@gmail.com<\/code> violates the UNIQUE<\/code> constraint.<\/p>\n\n\n\n
However, if you use the INSERT IGNORE<\/code> statement instead.<\/p>\n\n\n
INSERT<\/span> IGNORE<\/span> INTO<\/span> subscribers(email)\nVALUES<\/span>('john.doe@gmail.com'<\/span>), \n      ('jane.smith@ibm.com'<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
MySQL returned a message indicating that one row was inserted and the other row was ignored.<\/p>\n\n\n
1 row(s) affected, 1 warning(s): 1062 Duplicate entry 'john.doe@gmail.com' for key 'email' Records: 2  Duplicates: 1  Warnings: 1<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
To find the detail of the warning, you can use the SHOW WARNINGS<\/code> command as shown below:<\/p>\n\n\n
SHOW<\/span> WARNINGS<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"MySQL<\/figure>\n\n\n\n
In conclusion, when you use the INSERT IGNORE<\/code> statement, instead of issuing an error, MySQL issues a warning in case an error occurs.<\/p>\n\n\n\n
If you query data from subscribers<\/code> table, you will find that only one row was inserted and the row that\u00a0caused the error was not.<\/p>\n\n\n\n
\"MySQL<\/figure>\n\n\n\n
MySQL INSERT IGNORE and STRICT\u00a0mode<\/h2>\n\n\n\n
When the strict mode<\/a> is on, MySQL returns an error and aborts the INSERT<\/code> statement if you try to insert invalid values into a table.<\/p>\n\n\n\n
However, if you use the INSERT IGNORE<\/code> 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.<\/p>\n\n\n\n
Consider the following example.<\/p>\n\n\n\n
First, we create a new table<\/a> named tokens<\/code>:<\/p>\n\n\n
CREATE<\/span> TABLE<\/span> tokens (\n    s VARCHAR<\/span>(6<\/span>)\n);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this table, the column s<\/code> accepts only strings whose lengths\u00a0are less than or equal to six.<\/p>\n\n\n\n
Second, insert a string whose length is seven into the tokens<\/code> table.<\/p>\n\n\n
INSERT<\/span> INTO<\/span> tokens VALUES<\/span>('abcdefg'<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
MySQL issued the following error because the strict mode is on.<\/p>\n\n\n
Error Code: 1406. Data too long for column 's' at row 1<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Third, use the INSERT IGNORE<\/code> statement to insert the same string.<\/p>\n\n\n
INSERT<\/span> IGNORE<\/span> INTO<\/span> tokens VALUES<\/span>('abcdefg'<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
MySQL truncated data before inserting it into the tokens<\/code> table. In addition, it issues a warning.<\/p>\n\n\n\n
\"MySQL<\/figure>\n\n\n\n
Summary<\/h2>\n\n\n\n
    \n
  • Use the MySQL INSERT IGNORE<\/code> statement to insert rows into a table and ignore errors for rows that cause errors.<\/li>\n<\/ul>\n
    \n\t
    \n\t\t
    Was this tutorial helpful? <\/div>\n\t\t
    \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
    \n\t\t
    \n\t\t\t
    <\/div>\n\t\t\t\n\t\t\t
The UNIQUE<\/code><\/a> constraint ensures that no duplicate email exists in the email<\/code> column.<\/p>\n\n\n\n