[乐意黎]MySQL 5.7.27中 datetime字段 0000-00-00 00:00:00 抛错的处理

如题，在ECS（Centos 7.2）上数据库MySQL新装了 5.7.27版本，在导入旧库数据时，在抛错

[2018-05-21 10:20:43]：准备数据库连接信息...
[2018-05-21 10:20:43]：数据库连接准备完成...
[2018-05-21 10:20:43]：【注意】：压缩文件的行数预测是采样方式，未必准确....
[2018-05-21 10:20:43]：初步打开Zip文件，内在文件名为：110087_all.sql
[2018-05-21 10:20:43]：解压缩文件大小为：276.20MB
[2018-05-21 10:20:43]：解压缩前大小为：92.14MB
[2018-05-21 10:20:43]：界面设置采用自动识别字符集模式...
[2018-05-21 10:20:43]：自动识别字符集将采用内部文件前：5M 大小为采样，进行字符集自动识别...
[2018-05-21 10:20:43]：自动识别字符集结果：UTF-8
[2018-05-21 10:20:43]：识别字符集耗时：14ms
[2018-05-21 10:20:43]：现在开始解析内部文件内容.....
[2018-05-21 10:20:43]：根据界面选择，将按照SQL格式解析Zip文件中的内容....
[2018-05-21 10:20:43]：估算导入SQL的数量为：6406,该值仅提供进度参考，不代表真实值。
[2018-05-21 10:20:44]：======================================
执行的SQL语句出错：

错误信息：Invalid default value for 'comment_date'

究其原因，是 comment_date 字段 为datetime 类型, 默认值是"0000-00-00 00:00:00"。

经查询得知:

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 |
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+

解决办法: 

CREATE TABLE `aerchi.com_comments` (
  `comment_ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `comment_post_ID` bigint(20) unsigned NOT NULL DEFAULT '0',
  `comment_author` tinytext NOT NULL,
  `comment_author_email` varchar(100) NOT NULL DEFAULT '',
  `comment_author_url` varchar(200) NOT NULL DEFAULT '',
  `comment_author_IP` varchar(100) NOT NULL DEFAULT '',
  `comment_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `comment_date_gmt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `comment_content` text NOT NULL,
  `comment_karma` int(11) NOT NULL DEFAULT '0',
  `comment_approved` varchar(20) NOT NULL DEFAULT '1',
  `comment_agent` varchar(255) NOT NULL DEFAULT '',
  `comment_type` varchar(20) NOT NULL DEFAULT '',
  `comment_parent` bigint(20) unsigned NOT NULL DEFAULT '0',
  `user_id` bigint(20) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`comment_ID`),
  KEY `comment_post_ID` (`comment_post_ID`),
  KEY `comment_approved_date_gmt` (`comment_approved`,`comment_date_gmt`),
  KEY `comment_date_gmt` (`comment_date_gmt`),
  KEY `comment_parent` (`comment_parent`),
  KEY `comment_author_email` (`comment_author_email`(10))
) ENGINE=MyISAM AUTO_INCREMENT=25 DEFAULT CHARSET=utf8

方法一：临时修改方法

mysql> set global  sql_mode=‘ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION‘ ;
mysql> set session  sql_mode=‘ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION‘ ;

方法二：修改datetime的默认值

如datetime类型值'0000-00-00 00:00:00' 修改成'0000-01-01 00:00:00' 即可。

方法三：永久修改方法,需要重启mysql服务:修改mysql的配置文件my.cnf,添加以下参数

#实际是去除NO_ZERO_IN_DATE,NO_ZERO_DATE
sql_mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION 

 乐意用的是方法三，因为表太多，不可能一一修改.

使用  vim /etc/my.cnf 打开，添加

#实际是去除NO_ZERO_IN_DATE,NO_ZERO_DATE
sql_mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_

后， :wq 保存。

"service mysqld restart" 重启MySQL

mysql> show variables like 'sql_mode';
+---------------+--------------------------------------------------------------------------------------------------------------+
| Variable_name | Value                                                                                                        |
+---------------+--------------------------------------------------------------------------------------------------------------+
| sql_mode      | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+---------------+--------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

如图: 

测试下：

CREATE TABLE `aerchi.com_comments` (
  `comment_ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `comment_post_ID` bigint(20) unsigned NOT NULL DEFAULT '0',
  `comment_author` tinytext NOT NULL,
  `comment_author_email` varchar(100) NOT NULL DEFAULT '',
  `comment_author_url` varchar(200) NOT NULL DEFAULT '',
  `comment_author_IP` varchar(100) NOT NULL DEFAULT '',
  `comment_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `comment_date_gmt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `comment_content` text NOT NULL,
  `comment_karma` int(11) NOT NULL DEFAULT '0',
  `comment_approved` varchar(20) NOT NULL DEFAULT '1',
  `comment_agent` varchar(255) NOT NULL DEFAULT '',
  `comment_type` varchar(20) NOT NULL DEFAULT '',
  `comment_parent` bigint(20) unsigned NOT NULL DEFAULT '0',
  `user_id` bigint(20) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`comment_ID`),
  KEY `comment_post_ID` (`comment_post_ID`),
  KEY `comment_approved_date_gmt` (`comment_approved`,`comment_date_gmt`),
  KEY `comment_date_gmt` (`comment_date_gmt`),
  KEY `comment_parent` (`comment_parent`),
  KEY `comment_author_email` (`comment_author_email`(10))
) ENGINE=MyISAM AUTO_INCREMENT=25 DEFAULT CHARSET=utf8

执行成功，耗时：[73ms.]

OK, 终于算解决了。

乐意黎原创

2018-05-21

原文地址: https://blog.csdn.net/aerchi/article/details/80389602