错误:ERROR 1067 (42000) at line 728: Invalid default value for ‘SEND_TIME’
CREATE TABLE `etl_errtask_sendlog` (
`LOG_ID` int(11) NOT NULL,
`TASK_ID` int(11) NULL DEFAULT NULL,
`IS_SEND` int(11) NULL DEFAULT NULL,
`CREATE_TIME` timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP(0),
`SEND_TIME` timestamp(0) NOT NULL DEFAULT '0000-00-00 00:00:00',
`TYPE` int(11) NULL DEFAULT NULL,
`INST_ID` int(11) NULL DEFAULT NULL,
`MODEL` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`DATE_FIELD` varchar(8) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
PRIMARY KEY (`LOG_ID`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
问题分析:
对TIMESTAMP 类型的字段如果不设置缺省值或没有标志not null时候在创建表时会报这个错误,这是因为sql_mode中的NO_ZEROR_DATE导制的,在strict mode中不允许'0000-00-00'作为合法日期
sql_mode有两种,一种是空值,一种是严格模式,会给出很多默认设置。在MySQL5.7之后默认使用严格模式。NO_ZERO_DATE:若设置该值,MySQL数据库不允许插入零日期,插入零日期会抛出错误而不是警告。
解决方法:
mysql> show variables like "sql_mode";
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value |
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
| 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 row in set (0.09 sec)
方法一:
mysql> set global sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,ALLOW_INVALID_DATES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
方法二:
删除NO_ZERO_DATE配置
mysql> set global sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';