sql无法兼容mysql8.0

[wuou-learn]

背景

由于我们环境的mysql版本是8.0，所以在一些包含group by关键字的查询sql中，会报如下错误

2022-12-05 10:51:25.593 ERROR 30622 --- [nio-7001-exec-6] o.a.c.c.C.[.[.[/].[dispatcherServlet]    : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is org.springframework.jdbc.BadSqlGrammarException: 
### Error querying database.  Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'chaosblade.t_chaos_application_device.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
### The error may exist in com/alibaba/chaosblade/box/dao/mapper/ApplicationDeviceMapper.java (best guess)
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: SELECT  id,app_name,namespace,group_name,device_name,private_ip,public_ip,pid,device_type,connect_time,last_health_ping_time,host_configuration_id,configuration_id,user_id,cluster_id,app_id,status,is_deleted,dimension,kub_namespace,os_type,gmt_create,gmt_modified  FROM t_chaos_application_device     WHERE status = ? AND is_deleted = ? AND user_id = ? GROUP BY app_id
### Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'chaosblade.t_chaos_application_device.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
; bad SQL grammar []; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'chaosblade.t_chaos_application_device.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by] with root cause

这是由于5.7.5以后默认sql_mode包含ONLY_FULL_GROUP_BY导致的，由于严格模式开启对我们有利，所以我只能选择对代码中的sql进行兼容。

修改思路

对使用group by关键字查询的sql，先查出相应数据的id，根据id查询相应数据

tips

由于贡献文档的缺失，我不知该如何贡献自己修改的代码

[wuou-learn]

主要改动了三个地方的sql，因这几个表的id都是自增的，取数据id最大值即=最新创建的数据，再根据id去查询相应数据

public List<SceneAuthorizedDO> getAuthorizedRecordsGroupBy(SceneAuthorizedQueryRequest query) {
        List<SceneAuthorizedDO> sceneAuthorizedDoList = sceneAuthorizedMapper.selectList(buildQueryWrapper(query)
                .select("max(id) id")
                .groupBy(
                        "function_id"
                ));
        if (CollectionUtil.isNullOrEmpty(sceneAuthorizedDoList)) {
            return Lists.newArrayList();
        }
        List<Long> collect = sceneAuthorizedDoList.stream().map(SceneAuthorizedDO::getId).filter(Objects::nonNull).collect(Collectors.toList());
        return sceneAuthorizedMapper.selectList(new QueryWrapper<SceneAuthorizedDO>().lambda().in(SceneAuthorizedDO::getId, collect));
    }

public List<ApplicationDeviceDO> findUserDeviceGroupByAppId(String userId,List<Long> appIds) {
		QueryWrapper<ApplicationDeviceDO> queryWrapper = new QueryWrapper<>();
		queryWrapper.select("max(id) id");
		queryWrapper.eq("status", DeviceStatus.ONLINE.getStatus());
		queryWrapper.eq("is_deleted", 0);
		if (CollectionUtil.isNullOrEmpty(appIds)) {
			queryWrapper.eq("user_id", userId);
		} else {
			queryWrapper.and(wrapper -> wrapper.eq("user_id", userId).or().in("app_id", appIds));
		}
		queryWrapper.groupBy("app_id");
		List<ApplicationDeviceDO> applicationDeviceIdList = applicationDeviceMapper.selectList(queryWrapper);
		if (CollectionUtil.isNullOrEmpty(applicationDeviceIdList)) {
			return Lists.newArrayList();
		}
		return applicationDeviceMapper.selectList(
				new QueryWrapper<ApplicationDeviceDO>()
						.lambda()
						.in(ApplicationDeviceDO::getId,
								applicationDeviceIdList
										.stream()
										.map(ApplicationDeviceDO::getId)
										.filter(Objects::nonNull)
										.collect(Collectors.toList())));
	}

@Select("<script>" +
            "select tmp.* from (" +
            "SELECT t.* FROM t_chaos_application_device t where t.id in ( " +
            "select " +
            "max(ad.id) " +
            "from t_chaos_application_device ad WHERE 1=1 " +
            "<if test='null != query.appId and query.appId != \"\" '>" +
            "AND ad.app_id = #{query.appId} " +
            "</if>" +
            "<if test='null != query.status and query.status != \"\" '>" +
            "AND ad.status = #{query.status} " +
            "</if>" +
            "<if test='null != query.partName and query.partName != \"\" '>" +
            "AND ad.private_ip like #{query.partName} " +
            "</if>" +
            "<if test='null != query.osType' >" +
            "AND ad.os_type = #{query.osType} " +
            "</if>" +
            "<if test='null != query.groups and query.groups.size != 0'>" +
            "AND ad.group_name in " +
            "<foreach collection='query.groups' item='groupName' index='index' open='(' close=')' separator=',' >" +
            "#{groupName}" +
            "</foreach>" +
            "</if>" +
            "<if test='null != query.dimensions and query.dimensions.size != 0'>" +
            "AND ad.dimension in " +
            "<foreach collection='query.dimensions' item='dimension' index='index' open='(' close=')' separator=',' >" +
            "#{dimension}" +
            "</foreach>" +
            "</if>" +
            "<if test='null != query.tags and query.tags.size != 0'>" +
            " AND ad.configuration_id IN " +
            "(select configuration_id from t_chaos_application_device_tag adt where adt.tag_name in " +
            "<foreach collection='query.tags' item='tag' index='index' open='(' close=')' separator=',' >" +
            "#{tag}" +
            "</foreach>" +
            ")" +
            "</if>" +
            "group by ad.private_ip " +
            ")) tmp " +
            "ORDER BY tmp.gmt_create DESC" +
            "</script>")
    IPage<ApplicationDeviceDO> selectPageByTagsForHost(IPage<ApplicationDeviceDO> page, @Param("query") ApplicationDeviceQuery query);

[MandssS]

非常感谢你的贡献，如果没有问题，会在下个版本中进行发布