错误1067(42000):"end_time"的默认值无效 [英] ERROR 1067 (42000): Invalid default value for '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='秒杀库存表';

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

错误1067(42000):"end_time"的默认值无效

我该如何解决这个问题?

解决方案

MySQL以特殊方式对待timestamp，这在

那些非标准行为仍然是TIMESTAMP的默认行为，但自MySQL 5.6.6起已弃用，并且此警告在启动时出现:

 [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'，与 --explicit_defaults_for_timestamp .它将按您期望的方式处理timestamp列(并且无论如何将成为某些将来的MySQL版本中的默认行为)，或允许它们为null.

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.

这篇关于错误1067(42000):"end_time"的默认值无效的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！