MySQL报错：sql

阿里云国内75折回扣微信号：monov8

阿里云国际，腾讯云国际，低至75折。AWS 93折免费开户实名账号代冲值优惠多多微信号：monov8 飞机：@monov6

作为初学者我们在使用MySQL的时候总是会遇到各种各样的报错让人头痛不已。其中有一种报错sql_mode=only_full_group_by十分常见每次都是老长的一串出现然后带走你所有的好心情。

在这里插入图片描述

出现这样的报错并不是因为你的代码写得不好而是因为在MySQL 5.7后MySQL默认开启了SQL_MODE严格模式对数据进行严格校验。如果代码中含有group by聚合操作那么select中的列除了使用聚合函数之外的如max()、min()等都必须出现在group by中。

比如说出现下面这种情况就会报错

select Beijing,Shanghai from city group by Beijing

如果改成这个样子

select Beijing,Shanghai from city group by Beijing,Shanghai

或者这个样子

select Beijing from city group by Beijing

就不会报错了。

当然这样子随意的改动代码我们可能就无法得到想要的信息了。

我们可以通过以下四种方法解决该问题

方法一直接修改数据库配置

首先打开数据库输入

select @@global.sql_mode;

这个时候就会返回得到以下的信息不同电脑返回的信息可能不同

ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION

如果里面包含 ONLY_FULL_GROUP_BY那么就重新设置在数据库中输入以下代码去掉ONLY_FULL_GROUP_BY即可

SET GLOBAL sql_mode='STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION';

但是当我们再一次重新启动数据库时可能会恢复原样还是会出现ONLY_FULL_GROUP_BY的报错这就需要我们再一次修改数据库配置。

那有没有可以永久生效的办法呢当然有参考方法二就可以使修改数据库配置永久生效

方法二修改数据库配置永久生效

修改配置文件my.ini

在[mysqld]模块下新增一行配置

sql_mode='STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION';

运行后重启即可生效

方法三使用 any_value() 或 group_concat()

1. any_value()将分到同一组的数据里第一条数据的指定列值作为返回数据。 any_value()函数就是MySQL提供的用来抑制ONLY_FULL_GROUP_BY值被拒绝的

select Beijing,any_value(Shanghai) from city group by Beijing

2. group_concat()将分到同一组的数据默认用逗号隔开作为返回数据

在这里插入图片描述

select Beijing,group_concat(Shanghai) from city group by Beijing

方法四开动脑筋修改代码

举个例子在牛客的第206题中就出现了类似的情况

https://www.nowcoder.com/practice/4a052e3e1df5435880d4353eb18a91c6?tpId=82&tqId=29764&rp=1&ru=/exam/oj&qru=/exam/oj&sourceUrl=%2Fexam%2Foj%3Fpage%3D1%26tab%3DSQL%25E7%25AF%2587%26topicId%3D82&difficulty=undefined&judgeStatus=undefined&tags=&title=

题目如下

在这里插入图片描述

如果我们不用上面的方法直接用下面这种方式查询很明显会出现 ONLY_FULL_GROUP_BY 的报错

select dept_no,d.emp_no,max(s.salary) 
from dept_emp d 
join salaries s on d.emp_no = s.emp_no
group by d.dept_no

我们可以换一种思路首先查询得到2张表一张表为员工薪资表表a一张表为每个部门最高员工薪资表表b。然后通过表连接 on d.emp_no = s.emp_no 使这两张表结合通过on a.salary = b.salary使薪资统一即这两张表连接后的表的薪资为每个部门的最高员工薪资。最后再通过一次查询得到每个部门中当前员工薪水最高的相关信息。

select a.dept_no, a.emp_no, b.salary
from
    (select d.dept_no, s.emp_no, s.salary
     from dept_emp as d join salaries as s 
     on d.emp_no = s.emp_no 
    ) as a
    join
    (select d.dept_no, max(s.salary) as salary
     from dept_emp as d join salaries as s 
     on d.emp_no = s.emp_no
     group by d.dept_no
    ) as b 
    on a.salary = b.salary and a.dept_no = b.dept_no
order by a.dept_no