MySQL：ERROR 1067 - Invalid default value for ‘end_time‘

mysql版本：5.7.24

一、问题描述

创建表执行sql语句如下：

create table train_record (    
id int(11) not NULL AUTO_INCREMENT,    
user_name VARCHAR(20) NOT NULL COMMENT'用户名称',
train_name TINYINT(1) DEFAULT'2' NOT NULL COMMENT'训练项目，1主动训练 2引导训练',    
start_time TIMESTAMP NOT NULL COMMENT'开始时间',
end_time TIMESTAMP NOT NULL COMMENT'结束时间',
train_duration INT(4) NOT NULL COMMENT'训练时长',
train_times int(4) DEFAULT'1' NOT NULL COMMENT'训练次数，1-16次 2-32次 3-48次 4-64次 5-80次 6-96次',
repeat_time TINYINT(1) DEFAULT'1' NOT NULL COMMENT'点击后重复播放次数，1-1次 2-2次 3-3次',
volume int(3) DEFAULT'0' NOT NULL COMMENT'音量',
play_random TINYINT(1) DEFAULT'1' NOT NULL COMMENT'是否随机播放，1是0否',
PRIMARY KEY(id)    
) COMMENT'训练记录表';

执行sql报错结果：

1067 - Invalid default value for 'end_time', Time: 0.000000s

原因：mysql从5.7开始，默认是严格模式，严格遵从SQL92规范。

mysql> show variables like 'explicit_defaults_for_timestamp'; 

执行结果：变量explicit_defaults_for_timestamp的value值为off。

mysql> show variables like 'sql_mode';

执行结果如下：

ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,
ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

 1. 亲测，创建表如果只有一个字段设为TIMESTAMP类型，默认情况下，没有指明null属性，该字段就会自动加上NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP属性，如果给该字段插入null值，会自动给该字段设置为CURRENT_TIMESTAMP（当前时间）值。

说明：DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP表示：在创建新记录和修改现有记录的时候都对这个数据列刷新。

2. 亲测，创建表如果有一个以上的字段指定TIMESTAMP类型，如果没有指定null属性或者没有设置默认值，则第二个TIMESTAMP字段报Invalid错误。
> 这是因为：
  第一个TIMESTAMP字段会自动被加上DEFAULT CURRENT_TIMESTAMP和ON UPDATE CURRENT_TIMESTAMP属性。
  第一个TIMESTAMP之后的字段，会被自动加上DEFAULT ‘0000-00-00 00:00:00’属性。而5.7版本的sql_mode变量中含有NO_ZERO_DATE，表示'0000-00-00 00:00:00'格式非法，这与严格模式有关。

二、解决办法

修改全局变量explicit_defaults_for_timestamp

mysql> set global explicit_defaults_for_timestamp = ON;

说明：需要退出重新进入mysql，该变量才生效。

此时，执行创建表sql语句，亲测执行成功。

1. 由于没有指定默认值，假设start_time和end_time值为空，执行insert操作，受严格模式影响，执行语句报错，报错语句如下：

1364 - Field 'start_time' doesn't have a default value, Time: 0.000000s

如果将值设为0000-00-00 00:00:00，执行insert操作，报错如下：

1292 - Incorrect datetime value: '0000-00-00 00:00:00' for column 'start_time' at row 1, Time: 0.001000s

这时候就需要修改全局变量sql_mode，去掉NO_ZERO_IN_DATE,NO_ZERO_DATE，执行sql语句如下：

mysql> set global sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

再执行insert操作，亲测成功。

insert into train_record (user_name, train_name, start_time, end_time, train_duration, train_times, repeat_time, volume, play_random) VALUES (
liulin', 2, '0000-00-00 00:00:00', '0000-00-00 00:00:00', 30, 4, 3, 50, 1);

因此可以在创建sql语句时，将start_time和end_time字段的not null属性后面加上default '0000-00-00 00:00:00'，如下所示：

start_time timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '创建时间',
end_time timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '创建时间',

这样当TIMESTAMP字段值为空时，默认插入0000-00-00 00:00:00，执行语句不会再报错。

2. 如果将start_time和end_time这两个字段的not null去掉，执行sql语句，两个字段会被自动加上DEFAULT DULL的属性,执行insert操作的时候，若TIMESTAMP字段值为空，则记录为null，不再是CURRENT_TIMESTAMP（当前时间）值。

三、总结
1. 之前公司项目使用MySQL版本为5.6.42，在备份迁移到本地数据库就遇到了日期0000-00-00 00:00:00格式非法的问题，因为是导入SQL文件，批量替换有点麻烦，所以只要将sql_mode的NO_ZERO_IN_DATE, NO_ZERO_DATE去掉即可。

2. 关于explicit_default_for_timestamp的解释可参考mysql官网的 Server System Variables 一文 ，右上角可切换版本查看不同mysql版本的系统变量介绍。

3. 业务需要精确到秒时，也可以考虑DateTime类型。