博客
关于我
强烈建议你试试无所不能的chatGPT,快点击我
mysql5.7和mysql5.6默认的sql_mode说明
阅读量:6274 次
发布时间:2019-06-22

本文共 3479 字,大约阅读时间需要 11 分钟。

一.场景:

最近在部署新的项目,开发要求把原先旧项目的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服务的,所以采用方式二来解决线上的问题。
写此博文希望能够帮助更多的遇到此问题的小伙伴们

转载于:https://blog.51cto.com/wujianwei/2315320

你可能感兴趣的文章
为网页添加留言功能
查看>>
JavaScript—数组(17)
查看>>
Android 密钥保护和 C/S 网络传输安全理论指南
查看>>
以太坊ERC20代币合约优化版
查看>>
Why I Began
查看>>
同一台电脑上Windows 7和Ubuntu 14.04的CPU温度和GPU温度对比
查看>>
linux下查看和添加PATH环境变量
查看>>
js数组的操作
查看>>
springmvc Could not write content: No serializer
查看>>
Python系语言发展综述
查看>>
新手 开博
查看>>
借助开源工具高效完成Java应用的运行分析
查看>>
163 yum
查看>>
nginx 限速
查看>>
html5 聊天机器人
查看>>
第三章:Shiro的配置——深入浅出学Shiro细粒度权限开发框架
查看>>
openstack虚拟机修改IP地址
查看>>
80后创业的经验谈(转,朴实但实用!推荐)
查看>>
初识 lex
查看>>
让Windows图片查看器和windows资源管理器显示WebP格式
查看>>