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

爱被打了一巴掌 2022-09-12 13:57 237阅读 0赞

# 出现场景 #

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)