mysql group by 报错

由于mysql版本升级到5.7.27之后,之前的一个sql语句报错:

; bad SQL grammar []; nested exception is java.sql.SQLSyntaxErrorException: Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'vse_console_upms.o.name' which is n
ot functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

其实就是sql中查询的字段不在分组的字段中

SELECT u.`id`, u.`name`, o.`name` AS orgName, u.`phone`, u.offset_vector
    , GROUP_CONCAT(ro.name) AS roleNames, GROUP_CONCAT(ro.id) AS roleIds
    , GROUP_CONCAT(ro.`type`) AS roleTypes
FROM `upms_user` u
    LEFT JOIN `upms_user_org_rel` rel ON rel.`user_id` = u.`id`
    LEFT JOIN `upms_org` o ON o.`id` = rel.`org_id`
    LEFT JOIN `upms_user_role_rel` re ON re.`us
er_id` = u.`id`
    LEFT JOIN `upms_role` r ON r.id = re.`role_id`
    LEFT JOIN `upms_user_role_rel` rr ON rr.`user_id` = u.`id`
    LEFT JOIN `upms_role` ro ON ro.id = rr.`role_id`
WHERE o.`tenant_id` = ?
    AND rel.`tenant_id` = ?
    AND re.`tenant_id` = ?
    AND rr.`tenant_id` = ?
    AND ro.`tenant_id` = ?
    AND ro.`id` = r.`id`
GROUP BY u.`id`

这个问题的解决办法大致两种
1 改变sql_model
2 加any_value

第一种的方式无非两种: 要么改配置文件,要么用执行命令改现有模式
改配置文件:
修改mysql配置文件/etc/my.cnf
1.修改配置文件，执行命令：vim /etc/my.cnf
2.配置文件底部添加内容：
[mysqld]
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
3.重启mysql ：service mysqld restart
执行命令:
mysql > SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

第二种加特殊函数处理

SELECT u.`id`,any_value(u.`name`) name,any_value(o.`name`) orgName,any_value(u.`phone`) phone
        ,any_value(u.offset_vector) offset_vector,GROUP_CONCAT(ro.name) roleNames
        ,GROUP_CONCAT(ro.id) roleIds,GROUP_CONCAT(ro.`type`) roleTypes
        FROM `upms_user` u
        LEFT JOIN `upms_user_org_rel` rel ON rel.`user_id` = u.`id`
        LEFT JOIN `upms_org` o ON o.`id` = rel.`org_id`
        LEFT JOIN `upms_user_role_rel` re ON re.`user_id` = u.`id`
        LEFT JOIN `upms_role` r ON r.id = re.`role_id`
        LEFT JOIN `upms_user_role_rel` rr ON rr.`user_id` = u.`id`
        LEFT JOIN `upms_role` ro ON ro.id = rr.`role_id`
        GROUP BY u.`id`

本文转自 jimolvxing.blog.csdn.net/article/det…，如有侵权，请联系删除。