您现在的位置是:网站首页>MySQLMySQL

mysql5.7 导入DATETIME字段数据时遇到错误incorrect datetime value '0000-0-0 00:00:00' for column

左鹏2019-09-03 08:36:10MySQL251人已围观

简介mysql5.7 导入DATETIME字段数据时遇到错误incorrect datetime value '0000-0-0 00:00:00' for column创建数据库的时候CREATE TABLE `t_tag_post` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT,

mysql5.7 导入DATETIME字段数据时遇到错误incorrect datetime value '0000-0-0 00:00:00' for column

创建数据库的时候

CREATE TABLE `t_tag_post` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `pay_time` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '发布时间', PRIMARY KEY (`id`), KEY `tagid` (`tag_id`), KEY `postid` (`post_id`) ) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8;

报错:1292 - Incorrect datetime value: '0000-00-00 00:00:00' for column 'pay_time'

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.00 sec)
解决办法:去掉 NO_ZERO_IN_DATE,NO_ZERO_DATE
set global sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

如果不生效修改配置文件重新连接数据库 或者 重启数据库,就可以了










站点信息

  • 建站时间:2018-09-18
  • 网站程序:Laravel
  • 主题模板:《今夕何夕》
  • 文章统计:76条
  • 统计数据
  • 微信公众号:扫描二维码,关注我们