mysql - 错误 1067 (42000) : Invalid default value for 'end_time'

Question

CREATE TABLE seckill (
  `seckill_id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '商品库存id',
  `name` VARCHAR(120) NOT NULL COMMENT '商品名称',
  `number` INT NOT NULL COMMENT '库存数量',
  `start_time` TIMESTAMP NOT NULL COMMENT '开始时间',
  `end_time` TIMESTAMP NOT NULL COMMENT '结束时间',
  `create_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (seckill_id),
  KEY idx_start_time(start_time),
  KEY idx_end_time(end_time),
  KEY idx_create_time(create_time)
) ENGINE=InnoDB AUTO_INCREMENT=1000 DEFAULT CHARSET=utf8 COMMENT='秒杀库存表';

当我尝试使用之前的查询创建表时，这是我得到的错误:

ERROR 1067 (42000): Invalid default value for 'end_time'

我该如何解决这个问题？

Answer 1

MySQL 以一种特殊的方式处理timestamp，这在documentation 中很难找到。当您不知道要查找什么时:

In MySQL, the TIMESTAMP data type differs in nonstandard ways from other data types:

• TIMESTAMP columns not explicitly declared with the NULL attribute are assigned the NOT NULL attribute. (Columns of other data types, if not explicitly declared as NOT NULL, permit NULL values.) Setting such a column to NULL sets it to the current timestamp.

• The first TIMESTAMP column in a table, if not declared with the NULL attribute or an explicit DEFAULT or ON UPDATE clause, is automatically assigned the DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP attributes.

• TIMESTAMP columns following the first one, if not declared with the NULL attribute or an explicit DEFAULT clause, are automatically assigned DEFAULT '0000-00-00 00:00:00' (the “zero” timestamp). For inserted rows that specify no explicit value for such a column, the column is assigned '0000-00-00 00:00:00' and no warning occurs.

Those nonstandard behaviors remain the default for TIMESTAMP but as of MySQL 5.6.6 are deprecated and this warning appears at startup:

[Warning] TIMESTAMP with implicit DEFAULT value is deprecated.
Please use --explicit_defaults_for_timestamp server option 
(see documentation for more details).

这意味着您的第二个 timestamp not null 列将获得隐式默认值 '0000-00-00 00:00:00'，这在与 NO ZERO DATE 组合和 strict sql 模式(默认情况下在 MySQL 5.7 中启用)并导致错误。

要解决您的问题，请启用选项 --explicit_defaults_for_timestamp .它按照您的预期处理 timestamp 列(并且无论如何将成为 future MySQL 版本中的默认行为)，或者允许它们为 null。