Mac MySQL5.7 默认打开 ONLY_FULL_GROUP_BY 模式问题与解决方案

背景

MySQL5.7 后将 sql_modeONLY_FULL_GROUP_BY 模式默认设置为打开状态,这样一来,很多之前的 sql 语句可能会出现错误,错误信息如下:

Error Code: 1055. Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column '×××' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

描述:select 的列都要在 group 中。

解决方案一

这时我们可以通过 Navicat 或进入 MySQL 命令行终端输入 select @@global.sql_mode 来查看当前数据库的 sql_mode 属性值

Mac MySQL5.7 默认打开 ONLY_FULL_GROUP_BY 模式问题与解决方案

复制查询结果,去掉 ONLY_FULL_GROUP_BY,重新赋值,执行成功后,就可以将ONLY_FULL_GROUP_BY 模式默认设置为关闭状态,解决之前的 sql 报错问题

set sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'

但是这种方式设置的只是当前会话中的sql_model,服务器重启后,设置可能会失效。

解决方案二

通过在 my.cnf 配置文件中写入 sql_mode 实现关闭 ONLY_FULL_GROUP_BY 永久生效

首先要找到 my.cnf

但是官网说:从 5.7.18 开始不在二进制包中提供 my-default.cnf 文件。参考: MySQL 官网

这个 my.cnf 文件可以是自定义位置,也可以使用如下默认的位置,只要放在默认位置,MySQL 自动识别(通过 deb 或者 APT 源安装的,初始位置在下方列表):

FileName Purpose
/etc/my.cnf 全局选项
/etc/mysql/my.cnf 全局选项
SYSCONFDIR/my.cnf 全局选项
$MYSQL_HOME/my.cnf 服务器特定选项(仅限服务器)
defaults-extra-file 指定的文件 –defaults-extra-file,如果有的话
~/.my.cnf 用户特定选项
~/.mylogin.cnf 用户特定的登录路径选项(仅限客户端)

以上的详细说明可以参考官网解释:MySQL 官网

我选择创建在 /etc/mysql/my.cnf 位置

配置文件中我增加了一个 mysqld-5.7 组,并配置 sql_mode

配置文件语法也可参考 MySQL 官网

配置文件内容如下:

[mysqld-5.7] sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

最后重启 MySQL 服务器,在次执行 select @@global.sql_mode 就会发现 ONLY_FULL_GROUP_BY 没有了。

结尾

参考链接:
(34条消息) MySQL的my.cnf文件(解决5.7.18下没有my-default.cnf)_djCode-CSDN博客

(34条消息) MySQL5.7默认打开ONLY_FULL_GROUP_BY模式问题与解决方案_Peacock__的博客-CSDN博客_mysql5.7 only_full_group_by

本作品采用《CC 协议》,转载必须注明作者和本文链接
讨论数量: 0
(= ̄ω ̄=)··· 暂无内容!

讨论应以学习和精进为目的。请勿发布不友善或者负能量的内容,与人为善,比聪明更重要!