mysql插入空日期报错_MySQL 备份导入出错 and mysql添加DATETIME类型字段导致Invalid default value错误的问题...

出错信息：

1067 - Invalid default value for 'DATATIME'

[Msg] Importing Data...

[Msg] Rolling back...

[Msg] Finished - Stopped before completion

问题原因:MYSQL 5.6.5以下版本，不支持DATETIME日期类型，如果想要日期类型可以换成TIMESTAMP。

备注1：DATETIME字段必须要么留下一个空默认值，或根本没有默认值。

备注2：You should always be wary of migrating to an older version of any software, since it's often possible that your code is using new features that didn't exist previously. Updated versions usually provide backward compatibility (at least for a few releases), but there's no way for old versions to deal with unimplemented features from new versions.

执行sql如下：

CREATE TABLE `qcloud_sms_template` (

`id` int(11) unsigned NOT NULL AUTO_INCREMENT,

`ctime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,

PRIMARY KEY(`id`),

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

执行结果：

ERROR 1067 (42000): Invalid default value for 'ctime'

这段sql在本机测试没问题，放到开发环境下就报错了。

很奇怪，搜索一番后，发现DATETIME类型只支持mysql 5.6.5+.

解决方法：

将DATETIME改为TIMESTAMP。

参考资料：

http://stackoverflow.com/questions/168736/how-do-you-set-a-default-value-for-a-mysql-datetime-column/10603198