MySQL报错:sql_mode=only_full_group_by 4种解决方法含举例,轻松解决ONLY_FULL_GROUP_BY的报错问题
作为初学者,我们在使用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_nogroup 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.salaryfrom (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_noorder by a.dept_no
当然,具体问题,具体对待。遇到相差比较大的问题,就需要换一种查询方式了。
如果需要练习此题,可以点击文中代码直接进入牛客,或者在评论区找到创建该题目的代码,在电脑中运行,试一试,有没有其他更巧妙的办法解决这道题。
来源地址:https://blog.csdn.net/qq118640594X/article/details/128024350
免责声明:
① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的,并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据,供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。
② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341