mysql报错：1067 - Invalid default value for create_time解决方案

痛定思痛。 2022-12-28 01:32 135阅读 0赞

### 解决mysql报错：1067 - Invalid default value for 'create\_time' ###

*   *  报错
     *  解决方案
     *   *  临时修改
         *  永久修改

## 报错 ##

CREATE TABLE `ent_admin_user` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `username` varchar(255) NOT NULL,
      `password` varchar(255) NOT NULL,
      `last_login_ip` varchar(255) DEFAULT NULL,
      `last_login_time` datetime DEFAULT NULL,
      `listorder` varchar(255) DEFAULT NULL,
      `status` varchar(255) DEFAULT NULL,
        `create_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '创建时间',
      `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2d1b19xaWFuZ3FpYW5n_size_16_color_FFFFFF_t_70]原因在于时间的默认值不兼容，查看sql\_mode

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 |
    +---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
    1 row in set (0.00 sec)

![在这里插入图片描述][20201213140537127.png]其中NO\_ZERO\_IN\_DATE,NO\_ZERO\_DATE这两个参数限制时间不能为0。

## 解决方案 ##

### 临时修改 ###

set session sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

### 永久修改 ###

编辑mysql的配配置文件 my.cnf

在\[mysqld\]下面添加如下列：

sql_mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

保存退出，重启mysql服务：

systemctl restart mysqld.service

再去执行sql语句，就不报错了  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2d1b19xaWFuZ3FpYW5n_size_16_color_FFFFFF_t_70 1]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2d1b19xaWFuZ3FpYW5n_size_16_color_FFFFFF_t_70]: /images/20221120/695582c0f329406897061c7debd4342f.png
[20201213140537127.png]: https://img-blog.csdnimg.cn/20201213140537127.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2d1b19xaWFuZ3FpYW5n_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20201213140809321.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2d1b19xaWFuZ3FpYW5n,size_16,color_FFFFFF,t_70