mysql查询出错误,only_full_group_by导致

1.问题出现的云因

在MySQL5.7.5后,默认开启了ONLY_FULL_GROUP_BY,所以导致了之前的一些SQL无法正常执行,其实,是我们的SQL不规范造成的,因为group by 之后,返回的一些数据是不确定的,所以才会出现这个错误。

2.报错如下

{"code":1,"data":{},"msg":"\n### Error querying database.  Cause: java.sql.SQLSyntaxErrorException: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'a.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by\n### The error may exist in team/gk18software1dev/pcrsys/mapper/StudentMapper.java (best guess)\n### The error may involve team.gk18software1dev.pcrsys.mapper.StudentMapper.getListOnSelectByPcId-Inline\n### The error occurred while setting parameters\n### SQL: SELECT * from ( SELECT * from ( select student_choose_project.id,student_choose_project.stu_id, student_choose_project.pc_id,student_choose_project.p_id,student_choose_project.`status`, student.class_name,student.`name`,student.stu_number,student.telephone, student.station_name,student.wechat_num,student.email,student.remark from student_choose_project LEFT JOIN student on student_choose_project.stu_id=student.stu_id O

3.解决

关闭ONLY_FULL_GROUP_BY
方法一:

  • 查看
select @@global.sql_mode;
  • 设置
set @@global.sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
  • 刷新
flush privileges;

方法二:
Linux的解决步骤:

①、登录进入MySQL,linux登录的:mysql -u username -p ,然后输入密码,输入SQL:show variables like '%sql_mode';

②、编辑my.cnf文件,文件地址一般在:/etc/my.cnf/etc/mysql/my.cnf,找到sql-mode的位置,去掉ONLY_FULL_GROUP_BY,然后重启MySQL;有的my.cnf中没有sql-mode,需要加入:

sql-mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,注意要加入到[mysqld]下面,我就是加入到其他地方,重启后也不生效。
  
③、修改成功后重启MySQL服务,service mysql restart,重启好后,再登录mysql,输入SQL:show variables like '%sql_mode'; 如果没有ONLY_FULL_GROUP_BY,就说明已经成功了。

展开阅读全文
©️2020 CSDN 皮肤主题: 深蓝海洋 设计师: CSDN官方博客 返回首页
实付0元
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值