IGNORE option tells the server to ignore some common errors.
IGNORE can be used with the following statements:
- INSERT (see also INSERT IGNORE)
- LOAD DATA INFILE
- ALTER TABLE
- CREATE TABLE ... SELECT
- INSERT ... SELECT
The logic used:
- Variables out of ranges are replaced with the maximum/minimum value.
NOT NULLfield will insert 0 ( in a numerical field), 0000-00-00 ( in a date field) or an empty string ( in a character field).
- Rows that cause a duplicate key error or break a foreign key constraint are not inserted, updated, or deleted.
The following errors are ignored:
|Error number||Symbolic error name||Description|
|Can't write; duplicate key in table '%s'|
|Column '%s' cannot be null|
|Duplicate entry '%s' for key %d|
|Subquery returns more than 1 row|
|Out of range value for column '%s' at row %ld|
|Data truncated for column '%s' at row %ld|
|Truncated incorrect %s value: '%s'|
|Incorrect integer value|
|Cannot delete or update a parent row|
|Cannot add or update a child row: a foreign key constraint fails (%s)|
|Table has no partition for value %s|
|Duplicate entry '%s' for key '%s'|
|Table has no partition for some existing values|
|Found a row not matching the given partition set|
Ignored errors normally generate a warning.
A property of the
IGNORE clause consists in causing transactional engines and non-transactional engines (like XtraDB and Aria) to behave the same way. For example, normally a multi-row insert which tries to violate a
UNIQUE contraint is completely rolled back on XtraDB/InnoDB, but might be partially executed on Aria. With the
IGNORE clause, the statement will be partially executed in both engines.
Duplicate key errors also generate warnings. The OLD_MODE server variable can be used to prevent this.