08/24: MySQL > Overriding Input Data Restrictions
Category: Study
Posted by: hajime osako
Using INSERT IGNORE or UPDATE IGNORE causes MySQL to use non-strict behavior for the statement (producing warnings rather than errors).
Before MySQL 5, date values were required only to have month and day values in the range from 1 to 12 and 1 to 31, respectively. This means that MySQL accepted dates such as '2009-02-31'.
MySQL 5 requires that month and day values correspond to an actual legal date, so '2009-02-31' is not considered a valid date. MySQL 5 converts it to '0000-00-00' and generates a warning (in strict mode, error).
Enabling ALLOW_INVALID_DATES allows you to use the respective ranges of 1 to 12 and 1 to 31.
SET sql_mode = 'STRICT_ALL_TABLES,ALLOW_INVALID_DATES';
Before MySQL 5, date values were required only to have month and day values in the range from 1 to 12 and 1 to 31, respectively. This means that MySQL accepted dates such as '2009-02-31'.
MySQL 5 requires that month and day values correspond to an actual legal date, so '2009-02-31' is not considered a valid date. MySQL 5 converts it to '0000-00-00' and generates a warning (in strict mode, error).
Enabling ALLOW_INVALID_DATES allows you to use the respective ranges of 1 to 12 and 1 to 31.
SET sql_mode = 'STRICT_ALL_TABLES,ALLOW_INVALID_DATES';
