连表查询(group by)导致SQL报错的原因

报错代码

 Error querying database.  Cause: com.baomidou.mybatisplus.core.exceptions.MybatisPlusException: Error: Method queryTotal execution error of sql : 
 SELECT COUNT(1) FROM ( SELECT ap.id AS activeId, ap.number, ap.`name` activity_plan_name, ap.organization activity_plan_organization, ap.organization_id AS activityPlanOrganizationId, ap.address, ap.start_time AS startTime, ap.end_time AS endTime, ap.need_volunteer AS needVolunteer, ap.recruit_person AS recruitPerson, ap.integral, av.appreise_status AS appreiseStatus FROM t_elder_activity_plan ap LEFT JOIN t_elder_activity_volunteer av ON av.active_id = ap.id WHERE av.is_delete = 0 GROUP BY ap.number ) TOTAL 

### The error may exist in URL [jar:file:/opt/cloud/retirement/elder/elder-cloud.jar!/BOOT-INF/lib/ehe-elder-service-2.2.2-V3-SNAPSHOT.jar!/mapper/ActivityPlanMapper.xml]
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### Cause: com.baomidou.mybatisplus.core.exceptions.MybatisPlusException: Error: Method queryTotal execution error of sql : 
 SELECT COUNT(1) FROM ( SELECT ap.id AS activeId, ap.number, ap.`name` activity_plan_name, ap.organization activity_plan_organization, ap.organization_id AS activityPlanOrganizationId, ap.address, ap.start_time AS startTime, ap.end_time AS endTime, ap.need_volunteer AS needVolunteer, ap.recruit_person AS recruitPerson, ap.integral, av.appreise_status AS appreiseStatus FROM t_elder_activity_plan ap LEFT JOIN t_elder_activity_volunteer av ON av.active_id = ap.id WHERE av.is_delete = 0 GROUP BY ap.number ) TOTAL 
] with root cause
java.sql.SQLSyntaxErrorException: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'retirement-v2.ap.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
	

我的SQL

问题就处在ORDER BY ap.name这里

    <select id="getVolunteerByActvityId" resultType="com.ehe.elder.vo.ActivityVolunteerVO">
        SELECT
        av.*,
        ap.`name` activity_plan_name,
        ap.organization activity_plan_organization,
        vi.`name` volunteer_info_name,
        vi.id_no volunteer_info_no,
        vi.phone volunteer_info_phone
        FROM
        t_elder_activity_volunteer av
        LEFT JOIN t_elder_activity_plan ap ON av.active_id = ap.id
        LEFT JOIN t_elder_volunteer_info vi ON av.principal_id = vi.principal_id
        WHERE
        av.is_delete = 0
        <if test="activityId != null and activityId != ''">
            AND ap.id = #{activityId}
        </if>
        ORDER BY
        ap.name
    </select>

重点看这一行：

Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'retirement-v2.ap.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

问题出现的原因：

问题出现的原因：
MySQL 5.7.5及以上功能依赖检测功能。如果启用了ONLY_FULL_GROUP_BY SQL模式（默认情况下），MySQL将拒绝选择列表，HAVING条件或ORDER BY列表的查询引用在GROUP BY子句中既未命名的非集合列，也不在功能上依赖于它们。（5.7.5之前，MySQL没有检测到功能依赖关系，默认情况下不启用ONLY_FULL_GROUP_BY。有关5.7.5之前的行为的说明，请参见“MySQL 5.6参考手册”。）

解决思路

1、查询数据库版本和SQLmodel
我本地环境的版本的8.0.25

SELECT VERSION()

2、查询正式环境的版本：
正式环境是8.0.21

SELECT VERSION()

3、sqlmodel

select @@global.sql_mode

4、修改SQLmodel
依次执行下面两条命令

set @@global.sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

set @@session.sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

这一种可能数据库重启后会失效。
另一种解决办法， 直接修改数据配置文件

sudo vim /etc/mysql/conf.d/mysql.cnf

文件底部复制并粘贴

[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

保存退出并重启

sudo service mysql restart