mysql - 错误的日期时间值 mysql57

Question

将我的 mysql 版本从 5.6 更新到 5.7 后，出现错误:

Incorect datetime value: 0000-00-00 00:00:00 for column 'create_time' at row 1

这是create_time的结构

`create_time` DATETIME DEFAULT '0000-00-00 00:00:00'

Answer 1

确保您运行的当前 MySQL 5.7 版本比 5.7.7 版本更新，或者禁用严格模式。 Versions 5.7.4 to 5.7.7 incorporated NO_ZERO_DATE SQL mode into strict mode.

As of MySQL 5.7.4, the ERROR_FOR_DIVISION_BY_ZERO, NO_ZERO_DATE, and NO_ZERO_IN_DATE SQL modes are deprecated. From MySQL 5.7.4 though 5.7.7, these modes do nothing when named explicitly. Instead, their effects are included in the effects of strict SQL mode (STRICT_ALL_TABLES or STRICT_TRANS_TABLES). In other words, strict mode means the same thing in those versions as the pre-5.7.4 meaning of strict mode plus ERROR_FOR_DIVISION_BY_ZERO, NO_ZERO_DATE, and NO_ZERO_IN_DATE.

The MySQL 5.7.4 change to make strict mode more strict by including ERROR_FOR_DIVISION_BY_ZERO, NO_ZERO_DATE, and NO_ZERO_IN_DATE caused some problems. For example, in MySQL 5.6 with strict mode but not NO_ZERO_DATE enabled, TIMESTAMP columns can be defined with DEFAULT '0000-00-00 00:00:00'. In MySQL 5.7.4 with the same mode settings, strict mode includes the effect of NO_ZERO_DATE and TIMESTAMP columns cannot be defined with DEFAULT '0000-00-00 00:00:00'. This causes replication of CREATE TABLE statements from 5.6 to 5.7.4 to fail if they contain such TIMESTAMP columns.