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

原创

运维之美 2018-11-10 11:47:28 博主文章分类：MySQL ©著作权

文章标签 sql mode 说明 文章分类 运维

©著作权归作者所有：来自51CTO博客作者运维之美的原创作品，请联系作者获取转载授权，否则将追究法律责任

一.场景：

最近在部署新的项目，开发要求把原先旧项目的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为：

root@localhost [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;##全局查看

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

root@localhost [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服务的，所以采用方式二来解决线上的问题。写此博文希望能够帮助更多的遇到此问题的小伙伴们