mysql5.7 sql_mode =_mysql5.7和mysql5.6默认的sql_mode说明

最新推荐文章于 2024-06-06 08:00:00 发布

王忠林俊杰

最新推荐文章于 2024-06-06 08:00:00 发布

阅读量320

点赞数

文章标签： mysql5.7 sql_mode =

本文链接：https://blog.csdn.net/weixin_33266588/article/details/113599569

版权

本文介绍了在将数据从MySQL5.6导入到5.7时遇到的因sql_mode差异导致的报错问题。MySQL5.7默认启用严格模式，不允许零日期插入，而5.6则可以。解决方法包括临时修改会话sql_mode或全局sql_mode，以及在配置文件中永久修改sql_mode设置。

摘要由CSDN通过智能技术生成

一.场景：

最近在部署新的项目，开发要求把原先旧项目的MySQL数据导入到新项目MySQL5.7的数据库上。(旧项目上运行的是mysql5.6)

不就是导入数据嘛，也没多想，那就导入呗。导入时，开始报错了，

报错如下：

MySQL 5.7 ERROR 1067 (42000): Invalid default value for ‘CREATE_TIME‘

MySQL 5.7 ERROR 1067 (42000): Invalid default value for ‘day‘

简单google了下，发现原来是mysql5.6和mysql5.7默认的sql_mode模式参数是不一样的，才导致的报错。

下面咱们就简单测试下，记录下问题的解决过程：

二.演示过程：

2.1建测试表

首先创建一个表，表中包含一个字段day,允许插入零日期 ‘0000-00-00‘ COMMENT ‘日期‘,

CREATE TABLE `zx_scores` (

`id` int(11) unsigned NOT NULL AUTO_INCREMENT,

`titles` char(15) NOT NULL,

`icon` smallint(6) unsigned DEFAULT ‘0‘,

`integral` int(10) NOT NULL DEFAULT ‘0‘,

`isdefault` tinyint(1) unsigned NOT NULL DEFAULT ‘0‘,

`create_time` varchar(20) COLLATE utf8_unicode_ci NOT NULL,

`day` date NOT NULL DEFAULT ‘0000-00-00‘ COMMENT ‘日期‘,

PRIMARY KEY (`id`),

KEY `integral` (`integral`)

) ENGINE=Innodb AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;

2.2登录数据库建表测试

登录mysql5.6服务，查看默认的sql_mode为：O_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

并且执行上面的建表语句，可以正常执行

于是又登录MySQL5.7服务查看默认的sql_mode为：

[email protected] [test01]> 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.01 sec)

或者采用如下查看也是一样的：

select @@sql_mode; ##当前mysql的session会话层查看

select @@global.sql_mode;##全局查看

于是执行上面的建表语句，执行报错了：

[email protected] [test01]>CREATE TABLE `zx_scores` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `titles` char(15) NOT NULL, `icon` smallint(6) unsigned DEFAULT ‘0‘, `integral` int(10) NOT

NULL DEFAULT ‘0‘, `isdefault` tinyint(1) unsigned NOT NULL DEFAULT ‘0‘, `create_time` varchar(20) COLLATE utf8_unicode_ci NOT NULL, `day` date NOT NULL DEFAULT ‘0000-00-00‘ COMMENT ‘日期‘,

PRIMARY KEY (`id`), KEY `integral` (`integral`) ) ENGINEE=Innodb AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;

ERROR 1067 (42000): Invalid default value for ‘day‘

2.3报错原因说明：

MySQL的sql_mode有两种，一种是空值，一种是严格模式，会给出很多默认设置。在MySQL5.7之后默认使用严格模式。

NO_ZERO_DATE：若设置该值，MySQL数据库不允许插入零日期，插入零日期会抛出错误而不是警告。

例如表中含字段TIMESTAMP列(如果未声明为NULL或显示DEFAULT子句)将自动分配DEFAULT ‘0000-00-00 00:00:00‘(零时间戳)，也或者是本测试的表day列默认允许插入零日期 ‘0000-00-00‘ COMMENT ‘日期‘；这些显然是不满足sql_mode中的NO_ZERO_DATE而报错。

2.4解决方式：

解决方式

方式一：先执行select @@sql_mode,复制查询出来的值并将其中的NO_ZERO_IN_DATE,NO_ZERO_DATE删除，然后执行set sql_mode = ‘修改后的值‘。

此方法只在当前会话中生效

方式二：先执行select @@global.sql_mode,复制查询出来的值并将其中的NO_ZERO_IN_DATE,NO_ZERO_DATE删除，然后执行set global sql_mode = ‘修改后的值‘。

此方法在当前服务中生效，重新MySQL服务后失效

方法三：在mysql的安装目录下，或my.cnf文件，新增 sql_mode = ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION，

添加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。

此方法永久生效.当然生产环境上是禁止重启MySQL服务的，所以采用方式二来解决线上的问题。

写此博文希望能够帮助更多的遇到此问题的小伙伴们