当前位置: 首页 > 编程笔记 >

MySQL5.7中的sql_mode默认值带来的坑及解决方法

段干恺
2023-03-14
本文向大家介绍MySQL5.7中的sql_mode默认值带来的坑及解决方法,包括了MySQL5.7中的sql_mode默认值带来的坑及解决方法的使用技巧和注意事项,需要的朋友参考一下

在正常项目开发过程中,如果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默认值带来的坑及解决方法,希望对大家有所帮助,如果大家有任何疑问请给我留言,小编会及时回复大家的。在此也非常感谢大家对小牛知识库网站的支持!

 类似资料:
  • 本文向大家介绍总结Pyinstaller的坑及终极解决方法(小结),包括了总结Pyinstaller的坑及终极解决方法(小结)的使用技巧和注意事项,需要的朋友参考一下 一. 首先要有个稳定环境 下面是博主经测试的觉得坑比较少的环境搭配 1.Python3.4 + PyQt5.4 + Pyinstaller3.2.1 2.Python3.5 + PyQt5.8 + Pyinstaller3.2.1

  • 本文向大家介绍Centos7安装docker compse踩过的坑及解决方法,包括了Centos7安装docker compse踩过的坑及解决方法的使用技巧和注意事项,需要的朋友参考一下 一、安装方式 1.官方安装方式 docker-compose --version 测试安装是否成功,成功的话打印出docker-compose的版本信息 这种方式貌似需要FQ,能连接到外网才行。 2.使用pyth

  • 伙计们!我正在开发一个web应用程序,我决定使用Jackson作为JSON处理框架。 我愿意发送请求数据;假设POJO看起来像这样: 将其序列化如下: 但是我需要向该对象添加一些元数据,比如字段和。我以为会解决我的问题,但是我正在使用Jackson和泽西一起使用,所以我没有手动序列化对象,所以我不能使用。 我知道这个线程,但是没有一个答案可以满足我的需求,因为我认为,编写一个自定义序列化程序是有点

  • 本文向大家介绍Eclipse中改变默认的workspace的方法及说明详解,包括了Eclipse中改变默认的workspace的方法及说明详解的使用技巧和注意事项,需要的朋友参考一下 eclipse中改变默然的workspace的方法可以有以下几种: 1.在创建project的时候,手动选择使用新的workspace,如创建一个web project,在向导中的Location选项,取消使用"Us

  • 主要内容:1.SimpleDateFormat线程不安全,2.双重检查锁的漏洞,3.volatile的原子性,4.死锁,5.没释放锁,6.HashMap导致内存溢出,7.使用默认线程池,8.@Async注解的陷阱,9.自旋锁浪费cpu资源,10.ThreadLocal用完没清空1.SimpleDateFormat线程不安全 如果dataFormat 抽取成常量 dateFormat对象被定义成了静态常量,这样就能被所有对象共用。 如果只有一个线程调用time方法,也不会出现问题。 但Serivc

  • 新安装后不能像其他旧版本的MySQL那样使用root ID和空/无密码登录MySQL数据库