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

浅浅的花香味﹌ 2022-05-24 04:57 104阅读 0赞

如题，在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)

如图:  ![2018052111334054][]

测试下：

OK, 终于算解决了。

乐意黎原创

2018-05-21

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

[2018052111334054]: /images/20220524/73f14971acef41d0a69248344a4cc6f1.png