圆月山庄资源网

MySQL5.7中的sql_mode默认值带来的坑及解决方法
数据库 2024/12/23 佚名
3 2
圆月山庄资源网 Design By www.vgjia.com

在正常项目开发过程中，如果MySQL版本从5.6升级到5.7版本。作为DBA在考虑数据库版本升级带来的影响时，一般会有几个注意点：

sql_mode
optimizer_switch

本文主要内容是MySQL升级到5.7版本之后，由于默认的 sql_mode 值带来的坑以及对应的解决方案。

案例一：ONLY_FULL_GROUP_BY

问题描述

MySQL版本从5.6升级至5.7之后，部分SQL执行报错，报错信息如下：

ERROR 1055 (42000): Expression #3 of XXXXXX list is not in GROUP BY clause and contains nonaggregated column ‘XXXXX.XXXXXX' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

这个问题原因在于从5.6升级至5.7版本后 sql_mode 默认值发生了改变，在5.7版本的 sql_mode 默认值中有意向 ONLY_FULL_GROUP_BY ，该选项的含义表示：对于使用 GROUP BY 进行查询的SQL，不允许 SELECT 部分出现 GROUP BY 中未出现的字段，也就是 SELECT 查询的字段必须是 GROUP BY 中出现的或者使用聚合函数的。

解决方案

方案一（不推荐）：修改5.7版本 sql_mode 值，将 ONLY_FULL_GROUP_BY 去掉

ONLY_FULL_GROUP_BY 是加强SQL规范的，其目的是让SQL查询出来的结果更符合规范，更准确。

如果没有 ONLY_FULL_GROUP_BY 规范限制，那么则能允许以下SQL的执行： SELECT a,b,c FROM t GROUP BY a 。SQL按照a字段值进行分组，当同一个a字段值对应多个b或者c值时，查询结果中的b,c值是不确定的。

方案二：改写SQL

案例二：NO_ZERO_DATE & NO_ZERO_IN_DATE & time_zone

问题描述

排错阶段一

MySQL版本从5.6升级至5.7之后，创建表的过程中失败：

mysql> CREATE TABLE `t_manager` (
  .....
  ->  `CREATE_DATETIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  ->  `MODIFIER` varchar(32) DEFAULT NULL COMMENT '更新人',
  ->  `MODIFY_DATETIME` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
  ->  `IS_DELETED` bit(1) DEFAULT b'0' COMMENT '删除状态 1：删除 0:未删除',
  ->  `IS_ENABLE` bit(1) DEFAULT b'1' COMMENT '启用状态 1:启用 0:禁用',
  ->  PRIMARY KEY (`CACHE_ID`)
  -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
ERROR 1067 (42000): Invalid default value for 'MODIFY_DATETIME'

错误提示 MODIFY_DATETIME 字段设置的默认值是无效的，考虑到刚从5.6版本升级到5.7版本，于是又去翻了翻5.7中默认的 sql_mode 值。结果发现了两个可能存在影响的选项：

NO_ZERO_DATE
NO_ZERO_IN_DATE

排错阶段二

于是解决方案就是按照 NO_ZERO_DATE 以及 NO_ZERO_IN_DATE 的要求设置默认值，将 MODIFY_DATETIME 字段默认值设置为'1001-01-01 01:01:01'，结果发现还是无法成功创建表：

mysql>CREATE TABLE `t_manager` (
  .....
  ->  `CREATE_DATETIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  ->  `MODIFIER` varchar(32) DEFAULT NULL COMMENT '更新人',
  ->  `MODIFY_DATETIME` timestamp NOT NULL DEFAULT '1001-01-01 01:01:01' ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
  ->  `IS_DELETED` bit(1) DEFAULT b'0' COMMENT '删除状态 1：删除 0:未删除',
  ->  `IS_ENABLE` bit(1) DEFAULT b'1' COMMENT '启用状态 1:启用 0:禁用',
  ->  PRIMARY KEY (`CACHE_ID`)
  -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
ERROR 1067 (42000): Invalid default value for 'MODIFY_DATETIME'

查看了所有的 sql_mode 值，都符合规范，但是表还是创建不成功。只好去官方手册上找找timestamp介绍：

The TIMESTAMP data type is used for values that contain both date and time parts. TIMESTAMP has a range of ‘1970-01-01 00:00:01' UTC to ‘2038-01-19 03:14:07' UTC.

排错阶段三

可以看到官方定义中timestamp字段值的范围是'1970-01-01 00:00:01'到'2038-01-19 03：14：07'，原来是我们设置的默认值不在timestamp范围之内。于是再次修改默认值：

mysql>CREATE TABLE `t_manager` (
  .....
  ->  `CREATE_DATETIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  ->  `MODIFIER` varchar(32) DEFAULT NULL COMMENT '更新人',
  ->  `MODIFY_DATETIME` timestamp NOT NULL DEFAULT '1970-01-01 00:00:01' ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
  ->  `IS_DELETED` bit(1) DEFAULT b'0' COMMENT '删除状态 1：删除 0:未删除',
  ->  `IS_ENABLE` bit(1) DEFAULT b'1' COMMENT '启用状态 1:启用 0:禁用',
  ->  PRIMARY KEY (`CACHE_ID`)
  -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
ERROR 1067 (42000): Invalid default value for 'MODIFY_DATETIME'

邪了门，居然还是无法成功创建表。实在是没辙了，向同事求救，同事说他在机器上试试，结果同样的语句在他的MySQL上执行成功，同样是5.7.23版本。

百思不得其解。

一气之下将两边的参数值拿出来对比了一下，果然找到了不同的根本。

测试环境 同事环境 system_time_zone=CST system_time_zone UTC time_zone='+08:00' time_zone=SYSTEM

回过头来看timestamp字段定义的范围：

The TIMESTAMP data type is used for values that contain both date and time parts. TIMESTAMP has a range of ‘1970-01-01 00:00:01' UTC to ‘2038-01-19 03:14:07' UTC.

这个时间范围指的是UTC时区的时间范围，测试环境设置了CST东八区的时区，则对应的时间范围上也需要对应的加8小时。所以将timestamp字段默认值修改为'1970-01-01 08:00:01'，表终于创建成功。

mysql>CREATE TABLE `mn_cache_refresh_manager` (
  ......
  ->  `CREATE_DATETIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  ->  `MODIFIER` varchar(32) DEFAULT NULL COMMENT '更新人',
  ->  `MODIFY_DATETIME` timestamp NOT NULL DEFAULT '1970-01-01 08:00:01' ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
  ->  `IS_DELETED` bit(1) DEFAULT b'0' COMMENT '删除状态 1：删除 0:未删除',
  ->  `IS_ENABLE` bit(1) DEFAULT b'1' COMMENT '启用状态 1:启用 0:禁用',
  ->  PRIMARY KEY (`CACHE_ID`)
  -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.02 sec)

总结

以上所述是小编给大家介绍的MySQL5.7中的sql_mode默认值带来的坑及解决方法，希望对大家有所帮助，如果大家有任何疑问请给我留言，小编会及时回复大家的。在此也非常感谢大家对网站的支持！

标签：
mysql5.7,默认值,sql_mode默认值
圆月山庄资源网 Design By www.vgjia.com
广告合作：本站广告合作请联系QQ：858582 申请时备注：广告合作（否则不回）
免责声明：本站文章均来自网站采集或用户投稿，网站不提供任何软件下载或自行开发的软件！ 如有用户或公司发现本站内容信息存在侵权行为，请邮件告知！ 858582#qq.com
圆月山庄资源网 Design By www.vgjia.com
www.vgjia.com 圆月山庄资源网
139,976互联网资源
144,792高清电影
21,817无损音乐
631,128技术资源

更新日志

友情链接

杰晶网络 DDR爱好者之家 桃源资源网 杰网资源 富贵资源网 南强小屋 铁雪资源网 幽灵资源网 万梅资源网 狼山资源网 白云岛资源网 昆仑资源网 相思资源网 明霞山资源网 内蒙古资源网 黑松山资源网 茶园资源网 饿虎岗资源网 大旗谷资源网 常春岛资源网 岱庙资源网 兴国资源网 快活林资源网 蝙蝠岛资源网 帝王谷资源网 白云城资源网 伏龙阁资源网 清风细雨楼 天枫庄资源网 圆月山庄资源网 无争山庄资源网 神水资源网 移花宫资源网 神剑山庄资源网 无为清净楼资源网 金钱帮资源网 丐帮资源网 华山资源网 极乐门资源网 小李飞刀资源网 凤求凰客栈 风云阁资源网 金狮镖局 鸳鸯亭资源网 千金楼资源网 更多链接