解决MySQL insert出现Incorrect datetime value: ‘0000-00-00 00:00:00‘ for column ‘xxx‘ at row 1

出现场景

MySQL版本是5.7，执行一条MySQL插入命令时，出现以下错误：

mysql> INSERT INTO `mds_site` VALUES ('0', '*', '默认站点', '*', '1', '', '0000-00-00 00:00:00');
ERROR 1292 (22007): Incorrect datetime value: '0000-00-00 00:00:00' for column 'create_time' at row 1

解决方案

错误提示为不正确的datetime值，明明格式是对的，为什么不行呢。

我们先查看建表语句，create_time字段类型为datetime。

mysql> show create table mds_site;
+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table    | Create Table                                                                                                                                                                                                                                                                                                                                                                    |
+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| mds_site | CREATE TABLE `mds_site` (
  `id` varchar(64) NOT NULL,
  `site` varchar(255) NOT NULL,
  `site_description` varchar(255) DEFAULT NULL,
  `site_name` varchar(255) NOT NULL,
  `status` int(1) DEFAULT NULL COMMENT '-1不可用，1可用',
  `create_user` varchar(255) NOT NULL,
  `create_time` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8       |
+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

通过查询MySQL 5.7的文档得知，5.7以后默认情况不能插入无效的日期格式0000-00-00。所以我们只需要插入有效的日期即可。

mysql> INSERT INTO `mds_site` VALUES ('0', '*', '默认站点', '*', '1', '', now());
Query OK, 1 row affected (0.00 sec)