Incorrect datetime value: ‘0000-00-00 00:00:00‘ for column xxxx

小灰灰 2022-08-31 15:23 156阅读 0赞

### 目录 ###

*   *  背景
     *  问题分析
     *  解决方案

## 背景 ##

*  从测试环境数据导入到本地数据库报`Incorrect datetime value: '0000-00-00 00:00:00'`，报错详情如下所示。  
    \[DTF\] Data Transfer started  
    \[DTF\] 0> Getting tables  
    \[DTF\] 1> xx: Getting table structure  
    \[DTF\] 1> xxx: Fetching records  
    \[DTF\] 1> xx: Drop table  
    \[DTF\] 1> xx: Create table  
    \[DTF\] 1> xx: Transferring records  
    \[ERR\] 1> INSERT INTO `xxx` VALUES (xxxx  
    \[ERR\] 1> 1292 - Incorrect datetime value: ‘0000-00-00 00:00:00’ for column ‘create\_time’ at row 373  
    \[DTF\] Process terminated

## 问题分析 ##

*  为什么会有`0000-00-00 00:00:00`呢？

> 官方文档上说明MySQL允许将’0000-00-00’保存为“伪日期”(如果不使用NO\_ZERO\_DATE SQL模式)。这在某些情况下比使用NULL值更方便(并且数据和索引占用的空间更小)。

## 解决方案 ##

*  解决方案就是设置`sql_mode`，取消`NO_ZERO_DATE`。
 *  我们先来看本地环境的sql\_mode设置。使用`show variables like '%sql_mode%';`，结果如下。

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2JhaWR1XzE5NDczNTI5_size_16_color_FFFFFF_t_70]

> Value：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

*  然后再看测试环境的。  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2JhaWR1XzE5NDczNTI5_size_16_color_FFFFFF_t_70 1]
 *  对比下很容易发现，的确是没有`NO_ZERO_DATE`的。
 *  现在我们修改mysql的`my.cnf`文件，修改`sql_mode`（如果没有就新增，放在\[mysqld\]下面），去掉`NO_ZERO_DATE`。代码如下所示。

sql_mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2JhaWR1XzE5NDczNTI5_size_16_color_FFFFFF_t_70 2]

*  然后再导入，就能成功了，结果如下所示。  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2JhaWR1XzE5NDczNTI5_size_16_color_FFFFFF_t_70 3]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2JhaWR1XzE5NDczNTI5_size_16_color_FFFFFF_t_70]: /images/20220829/46ce75f0b2a442e4aab39f8121e68116.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2JhaWR1XzE5NDczNTI5_size_16_color_FFFFFF_t_70 1]: /images/20220829/f275b6e036884d688d26ead3cfb96e00.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2JhaWR1XzE5NDczNTI5_size_16_color_FFFFFF_t_70 2]: /images/20220829/96b35c9c8ea14deb8cb0448526ad15fe.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2JhaWR1XzE5NDczNTI5_size_16_color_FFFFFF_t_70 3]: /images/20220829/fd923a044942406dab88ea0371f9e21f.png