IGNORE
The IGNORE
option tells the server to ignore some common errors.
IGNORE
can be used with the following statements:
- DELETE
- INSERT (see also INSERT IGNORE)
- LOAD DATA INFILE
- UPDATE
- ALTER TABLE
- CREATE TABLE ... SELECT
- INSERT ... SELECT
The logic used:
- Variables out of ranges are replaced with the maximum/minimum value.
- SQL_MODEs
STRICT_TRANS_TABLES
,STRICT_ALL_TABLES
,NO_ZERO_IN_DATE
,NO_ZERO_DATE
are ignored.
- Inserting
NULL
in aNOT NULL
field 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 |
---|---|---|
1022 | ER_DUP_KEY | Can't write; duplicate key in table '%s' |
1048 | ER_BAD_NULL_ERROR | Column '%s' cannot be null |
1062 | ER_DUP_ENTRY | Duplicate entry '%s' for key %d |
1242 | ER_SUBQUERY_NO_1_ROW | Subquery returns more than 1 row |
1264 | ER_WARN_DATA_OUT_OF_RANGE | Out of range value for column '%s' at row %ld |
1265 | WARN_DATA_TRUNCATED | Data truncated for column '%s' at row %ld |
1292 | ER_TRUNCATED_WRONG_VALUE | Truncated incorrect %s value: '%s' |
1366 | ER_TRUNCATED_WRONG_VALUE_FOR_FIELD | Incorrect integer value |
1369 | ER_VIEW_CHECK_FAILED | CHECK OPTION failed '%s.%s' |
1451 | ER_ROW_IS_REFERENCED_2 | Cannot delete or update a parent row |
1452 | ER_NO_REFERENCED_ROW_2 | Cannot add or update a child row: a foreign key constraint fails (%s) |
1526 | ER_NO_PARTITION_FOR_GIVEN_VALUE | Table has no partition for value %s |
1586 | ER_DUP_ENTRY_WITH_KEY_NAME | Duplicate entry '%s' for key '%s' |
1591 | ER_NO_PARTITION_FOR_GIVEN_VALUE_SILENT | Table has no partition for some existing values |
1748 | ER_ROW_DOES_NOT_MATCH_GIVEN_PARTITION_SET | 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.