目录

MySQL5.7 严格模式引发的问题及解决办法

问题

新安装的 MySQL5.7，跑老项目的时（使用 FlyWay 管理数据库版本）出现了如下错误：

Invalid default value for 'audit_at'

出错 SQL 语句：

alter table supervise_note add audit_at TIMESTAMP;

原因

MySQL5.7将默认的 sql_mode 从“松散模式”更改为“严格模式”。

我们通过SQL语句

mysql> select @@sql_mode;

查询出 sql_mode ，发现其中有 NO_ZERO_IN_DATE,NO_ZERO_DATE。

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

即为不允许 TIMESTAMP 值为 0000-00-00 00:00:00。

我们再通过 SQL 语句

mysql> show variables;

查询出 MySQL5.7 的 explicit_defaults_for_timestamp 默认值为 OFF ，这与 sql_mode 的 NO_ZERO_DATE 也是相冲突的。

explicit_defaults_for_timestamp = OFF

解决方案

mysql> set @@explicit_defaults_for_timestamp = 1;

1.此时如果 TIMESTAMP 列没有显示的指定 not null 属性，那么默认的该列可以为 null ，此时向该列中插入 null 值时，会直接记录 null 。

2.如果 TIMESTAMP 列被加上了 not null 属性，并且没有指定默认值。这时如果向表中插入记录，但是没有给该 TIMESTAMP 列指定值的时候，如果 strict sql_mode 被指定了，那么会直接报错。如果 strict sql_mode 没有被指定，那么会向该列中插入 ’0000-00-00 00:00:00’ 并且产生一个 warning 。

去掉sql_mode中的 NO_ZERO_IN_DATE,NO_ZERO_DATE 。

SQL语句：

mysql> set @@sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

这里也可修改 MySQL 配置文件来去除规则，没有配置文件在 /etc/mysql 下创建 my.cnf 配置文件

sudo vi /etc/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"

如：

[mysqld]
sql_mode="ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

最后重启服务器，即可正常执行创建语句。

参考资料