Pits Collection of MySQL

Posted by Wang Zhihao on 2017-03-30

Invalid default value for 'TIMESTAMP'

DROP TABLE IF EXISTS `mapping`;
CREATE TABLE `mapping` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`start_time` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
`end_time` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
`create_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
`update_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

执行这一段SQL语句的时候,报错如下:

Invalid default value for 'start_time'

出错的原因是NO_ZERO_DATE mode

  • The NO_ZERO_DATE mode affects whether the server permits ‘0000-00-00’ as a valid date. Its effect also depends on whether strict SQL mode is enabled.

    • If this mode is not enabled, ‘0000-00-00’ is permitted and inserts produce no warning.
    • If this mode is enabled, ‘0000-00-00’ is permitted and inserts produce a warning.
    • If this mode and strict mode are enabled, ‘0000-00-00’ is not permitted and inserts produce an error, unless IGNORE is given as well. For INSERT IGNORE and UPDATE IGNORE, ‘0000-00-00’ is permitted and inserts produce a warning.

为了避免报错,可以执行下面这句:

SET SQL_MODE='ALLOW_INVALID_DATES';