人才社区

 找回密码
 免费入驻

QQ登录

只需一步,快速开始

搜索
查看: 167|回复: 0

[已解决] mysql5.7查询出提示which is not functionally dependent on columns in GROUP BY clause解决方法

[复制链接]

46

主题

46

帖子

353

积分

管理员

Rank: 9Rank: 9Rank: 9

积分
353
发表于 2019-7-28 11:18:05 | 显示全部楼层 |阅读模式

安装了最新版本的phpstudy v8之后,mysql竟然只有mysql5.7和mysql8.0两个版本,,而且原来的一个站点用到的GROUP BY查询出错,错误提示:

  1. 1055:Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column '74cms50.qs_jobs_search.refreshtime' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by [ SQL语句 ] : SELECT distinct uid,count(id) as jobs_num FROM `qs_jobs_search` WHERE MATCH (`key`) AGAINST ("+(set2 set3 set4)" IN BOOLEAN MODE) GROUP BY uid ORDER BY refreshtime desc,id desc LIMIT 0,6
复制代码


我们来进查询编辑器去看下sql_mode的值,执行:SELECT @@GLOBAL.sql_mode
发现sql_mode值中含有only_full_group_by模式。
此错误的解决方法 :查询编辑器执行:
  1. SET GLOBAL sql_mode = '';
复制代码
或查询编辑器执行:
  1. 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';
复制代码

sql_mode 中的only_full_group_by模式去掉即可。验证是否生效执行:SELECT @@GLOBAL.sql_modeSELECT @@sql_mode

当然不同的环境有不同的解决方法,下面看看大神解决方法:
一、旧方法,修改mysql配置文件,但是会导致数据丢失等不可预知的错误在用mysql执行如下查询的时候:
select * from `sys_user_group` group by `GROUP_ID`
报错信息如下:
[Err] 1055 - Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'information_schema.PROFILING.SEQ' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

错误的原因是我mysql版本是5.7的,使用如下语句查询可知:
select @@sql_mode;



里面默认设置了:sql_mode=only_full_group_by

only_full_group_by:使用这个就是使用和oracle一样的group 规则, select的列都要在group中,或者本身是聚合列(SUM,AVG,MAX,MIN) 才行,其实这个配置目前个人感觉和distinct差不多的,所以去掉就好
直接修改mysql配置文件(我的系统是Ubuntu16.04的,在/etc/mysql/mysql.conf.d/mysqld.cnf中并没有sql_mode这个配置,所以直接加上就好,如果是其他系统有得修改就不用添加了)

[mysqld]下
  1. 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配置文件的完整配置如下:

[mysqld_safe]
socket          = /var/run/mysqld/mysqld.sock
nice            = 0

[mysqld]
#
# * Basic Settings
#
user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
lower_case_table_names=1
character-set-server=utf8
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
#bind-address           = 127.0.0.1
#
# * Fine Tuning
#
key_buffer_size         = 16M
max_allowed_packet      = 16M
thread_stack            = 192K


二、不修改配置文件,修改sql写法
在分组时,5.7之前我们会这样写:
SELECT `id` AS `id`,`name` FROM `game` GROUP BY `name`

但是在5.7中,需要使用聚合函数:
SELECT ANY_VALUE(`id`) AS `id`,MAX(`id`),`name` FROM `game` GROUP BY `name`





本帖子中包含更多资源

您需要 登录 才可以下载或查看,没有帐号?免费入驻

x
回复

使用道具 举报

您需要登录后才可以回帖 登录 | 免费入驻

本版积分规则

快速回复 返回顶部 返回列表