MySQL 5.7 timestamp类型设置default value为'0000-00-00 00:00:00'报错的解决方法

约定不等于承诺〃 2023-10-18 16:39 8阅读 0赞

mysql是使用docker安装的bitnami/mysql:5.7版本,在导入数据时报错，`Error Code: 1067 - Invalid default value for 'xxx'`。  
原因是因为这个字段为timestamp类型，而且默认值为’0000-00-00 00:00:00’,  
`'xxx' timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'`  
查了下资料才知道原来5.7及以上版本是不允许这么设置的，那怎么解决呢？  
是不是可以修改配置文件，查了下资料，有人说可以在`my.ini`中`mysqld`设置`sql_mode`。如：

[mysqld]
    sql_mode=NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

这个bitnami/mysql跟我们平时使用的mysql安装目录有些区别，于是搜索了下docker官网，找了下mysql的配置文件所在路径  
`/opt/bitnami/mysql/conf/bitnami/my.cnf`，把上面的配置加上了，重启了下docker，但发现没有效果，才发现原来重启之后刚才添加的配置不见了。  
那换一种方式，既然我能连上mysql，那我是不是可以直接修改`sql_mode`呢？  
查了下`sql_mode`

SHOW SESSION VARIABLES LIKE '%sql_mode%';

显示结果如下：

<table> 
 <thead> 
  <tr> 
   <th align="left">Variable_name</th> 
   <th align="left">Value</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="left">sql_mode</td> 
   <td align="left">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</td> 
  </tr> 
 </tbody> 
</table>

`NO_ZERO_IN_DATE,NO_ZERO_DATE`这两个值就是导致报错的根源，需要去除，重新设置一下`sql_mode`

SET sql_mode="ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

然后重新导入，依然报同样的问题，奇了怪了。  
最后查了下资料，需要设置为`global`

SET @@global.sql_mode ="ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

这样问题就解决了。