statement to skip to the next row instead of aborting. (For nonignorable errors, an error occurs regardless of the IGNORE  keyword.) 

Example: If the table t  has a primary key column i  containing unique values, attempting to insert the same value of i  into multiple rows normally produces a duplicate-key error: 

mysql> CREATE TABLE t (i INT NOT NULL PRIMARY KEY); mysql> INSERT INTO t (i) VALUES(1),(1); ERROR 1062 (23000): Duplicate entry ’1’ for key ’t.PRIMARY’ 

With IGNORE , the row containing the duplicate key still is not inserted, but a warning occurs instead of an error: 

mysql> INSERT IGNORE INTO t (i) VALUES(1),(1); Query OK, 1 row affected, 1 warning (0.01 sec) Records: 2 Duplicates: 1 Warnings: 1 mysql> SHOW WARNINGS; +---------+------+-----------------------------------------+ | Level | Code | Message | +---------+------+-----------------------------------------+ | Warning | 1062 | Duplicate entry ’1’ for key ’t.PRIMARY’ | +---------+------+-----------------------------------------+ 1 row in set (0.00 sec) 

Example: If the table t2  has a NOT NULL  column id , attempting to insert NULL  produces an error in strict SQL mode: 

mysql> CREATE TABLE t2 (id INT NOT NULL); mysql> INSERT INTO t2 (id) VALUES(1),(NULL),(3); ERROR 1048 (23000): Column ’id’ cannot be null mysql> SELECT * FROM t2; Empty set (0.00 sec) 

If the SQL mode is not strict, IGNORE  causes the NULL  to be inserted as the column implicit default (0 in this case), which enables the row to be handled without skipping it: 

mysql> INSERT INTO t2 (id) VALUES(1),(NULL),(3); mysql> SELECT * FROM t2; +----+ | id | +----+ | 1 | | 0 | | 3 | +----+ 

These statements support the IGNORE  keyword: 

• CREATE TABLE ... SELECT : IGNORE  does not apply to the CREATE TABLE  or SELECT  parts of the statement but to inserts into the table of rows produced by the SELECT . Rows that duplicate an existing row on a unique key value are discarded. 

• DELETE : IGNORE  causes MySQL to ignore errors during the process of deleting rows. 

• INSERT : With IGNORE , rows that duplicate an existing row on a unique key value are discarded. Rows set to values that would cause data conversion errors are set to the closest valid values instead. 

 For partitioned tables where no partition matching a given value is found, IGNORE  causes the insert operation to fail silently for rows containing the unmatched value. 

• LOAD DATA , LOAD XML : With IGNORE , rows that duplicate an existing row on a unique key value are discarded. 

