mysql 1067 42000,错误1067（42000）：“ end_time”的默认值无效

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='秒杀库存表';

When I try to create the table with the previous query, this is the error I get:

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

How can I solve this issue?

解决方案

MySQL treats timestamp in a special way, that is a little hard to find in the documentation when you don't know what you are looking for:

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).

That means your second timestamp not null column will get an implicit default value of '0000-00-00 00:00:00', which is not allowed in combination with the NO ZERO DATE and strict sql mode (which is by default enabled in MySQL 5.7) and results in your error.

To solve your problem, enable the option --explicit_defaults_for_timestamp. It treats the timestamp columns as you expected (and will be the default behaviour in some future MySQL release anyway), or allow them to be null.