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

目录

MySQL-strict-mode.png

问题

新安装的 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_modeNO_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"

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

参考资料

文章最后编辑时间为:2019-02-28 17:27:42

标签: mysql

声明:本博客如无特殊说明皆为原创,转载请注明来源:MySQL5.7 严格模式引发的问题及解决办法谢谢!

相关文章

发表评论:

icon_question.pngicon_razz.pngicon_sad.pngicon_evil.pngicon_exclaim.pngicon_smile.pngicon_redface.pngicon_biggrin.pngicon_surprised.pngicon_eek.pngicon_confused.pngicon_cool.pngicon_lol.pngicon_mad.pngicon_twisted.pngicon_rolleyes.pngicon_wink.pngicon_idea.pngicon_arrow.pngicon_neutral.pngicon_cry.pngicon_mrgreen.png