"only_full_group_by"sql模式下select 非聚合列和group by的冲突处理

jcuser
459 阅读4分钟

「这是我参与2022首次更文挑战的第8天，活动详情查看：2022首次更文挑战」

前言

上一篇文章中"only_full_group_by"sql模式对GROUP BY的影响及处理，我对这个问题的处理想法是写一个拦截器，对于包含group by的sql进行拦截并加上any_value()，但是后来一想每次都要拦截修改还不如直接修改一次效率更高。同时我也在尝试了其他的方式，最终选择了给select列表添加any_value()，含有group by的sql单独使用一个公用的select列表，下面是我在解决这个问题的记录。

使用mybits拦截器

上一篇文章中我自作聪明想到了使用mybits拦截器,于是我就开始去学习了mybits拦截器，由于第一次接触拦截器，踩了不少坑，这里顺便记录一下springboot+mybits如何使用mybits拦截器。

首先我的思路是在sql预编译的时候拦截，判断sql是否包含group by语句，如果包含就对select 列表判断和加any_value()。下面是我的代码（针对简单的查询，复杂查询子查询还没写到就弃用这个方式）：

  //前提是含有group by的sql才处理
    //处理SELECT 语句的select列表，前提是
    public StringBuilder modifySqlSelectList(String sql) {
        StringBuilder sqlBuilder = new StringBuilder();
        //去掉多余的空格
        List<String> sqlToList = Arrays.stream(
                 sql.replace("\n", " ")
                    .replace("\t", " ")
                    .split(" "))
                .filter(s -> !"".equals(s.trim()))
                .collect(Collectors.toList());
        //使用空格分割
        sqlToList.forEach(s -> {
            if("select".equalsIgnoreCase(s) || "from".equalsIgnoreCase(s)){
                sqlBuilder.append(s.toLowerCase(Locale.ROOT)).append(" ");
            }else {
                sqlBuilder.append(s).append(" ");
            }
        });
          //取出select列表，group by列表
          List<String> selectList;
          List<String> selectListNew = new ArrayList<>();
          String sqlSelect = sqlBuilder.toString();
          int s1 = sqlSelect.indexOf("select") + 6;
          int s2 = sqlSelect.indexOf("from");

          String selectLi = sqlSelect.substring(s1, s2);
          //使用逗号分割select列表
          String[] selectLiSplit = selectLi.split(",");
          selectList = Arrays.asList(selectLiSplit);

          //处理select列表，添加聚合函数 ANY_VALUE()
          for (String s : selectList) {
              if (s.trim().contains(" ")) { //如果含有空格，说明有别名,不改变原别名
                  int ss = s.trim().indexOf(" ");
                  String col = s.trim().substring(0, ss);
                  if (!col.contains("(") && !col.contains(")")) { //不是一个聚合函数列
                      s = "ANY_VALUE(" + col + ") " + s.trim().substring(col.length()).trim();
                  }else { //是聚合列
                      // 这里有可能是 (id) as number 这种情况，应该判断括号前是否有字母
                      int s3 = col.indexOf("(");
                      // (id)
                      if("".equals(col.substring(0,s3).trim())){
                          s = "ANY_VALUE(" + col + ") " + s.trim().substring(col.length()).trim();
                          //ANY_VALUE((id)) as number 这样也没问题
                      }
                  }
              } else {
                  //加上ANY_VALUE()
                  s = "ANY_VALUE(" + s.trim() + ") as " + s;
              }
              selectListNew.add(s.trim());
          }
          //拼接回原来的sql
          StringBuilder sqlSelectNew = new StringBuilder();
          selectListNew.forEach(s -> sqlSelectNew.append(s).append(", "));
          //替换select列表最后一个列的逗号和逗号为空格
          sqlSelectNew.replace(sqlSelectNew.length() - 2, sqlSelectNew.length(), " ");
          //替换select列表
          sqlBuilder.replace(s1 + 1, s2, sqlSelectNew.toString());
          return sqlBuilder;
    }

大概就是取出select到from之间的列表，分别加上any_value()再拼接回去，如果是聚合列就不处理，如果含有别名不能改变原来的别名。

在写完了简单的查询后，发现每次查询都要拦截处理，还不如直接修改mapper文件效率高，但是后来事实证明也没有白写，可以把需要改的列表复制过来直接生成，相当于写了个工具方法。


springboot+mybits+mybits拦截器

为了方便测试，我首先在我的demo上实现了拦截器，具体步骤如下:

新建GroupBySqlInterceptor 实现了org.apache.ibatis.plugin.Interceptor接口，

重写接口定义的方法：

@Log4j2
@Intercepts({@Signature(type = StatementHandler.class, method = "prepare", args = {Connection.class, Integer.class})})
@Component
public class GroupBySqlInterceptor implements Interceptor {

    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        return invocation.proceed();
    }

    @Override
    public Object plugin(Object target) {
        return Interceptor.super.plugin(target);
    }

    @Override
    public void setProperties(Properties properties) {
        Interceptor.super.setProperties(properties);
    }
}

**@Intercepts **注解用来指定拦截的对象和方法。

@Signature： type：拦截哪一类对象；

method：拦截什么方法；

args：方法参数类型；（**mybits3.4以上需要加上Integer.class**）

@Component: 使用这个注解能够实现bean的注入，所以不需要再编写配置文件。

主要要实现的方法是：intercept方法。

  @Override
    public Object intercept(Invocation invocation) throws Throwable {
        StatementHandler statementHandler = (StatementHandler) invocation.getTarget();
        MetaObject metaStatementHandler = MetaObject.forObject(statementHandler, new DefaultObjectFactory(), new DefaultObjectWrapperFactory(), new DefaultReflectorFactory());
        MappedStatement mappedStatement = (MappedStatement) metaStatementHandler.getValue("delegate.mappedStatement");
        //sql
        BoundSql sql = (BoundSql) metaStatementHandler.getValue("delegate.boundSql");
        //sql类型
        SqlCommandType sqlCommandType = mappedStatement.getSqlCommandType();
        
        //在这对sql修改
        
        return invocation.proceed();
    }

有趣的查询

在观察了sql之后，我发现大多数sql包含group by的都是 group by 一个字段。目的是根据这个字段去重，比如满足条件的有多行，但是broup by后的字段有重复的，就只需要其中一条就行。

根据这个我又想能不能通过子查询实现去重。

首先我们的数据如下

fe44c0dd191acfd6711c97f8dbb23b34.png 我想要实现按照年龄分组并且要获取所有字段。

在only_full_group_by开启时，以下查询会报错

select id, name,age,salary from emp group by age;

2e3f6e5a7f11299af88f4be64498f833.png

于是我给select加上any_value()

SELECT
	any_value ( id ), any_value ( name ), age, any_value ( salary ) 
FROM emp 
GROUP BY age;

857fcdb228d7d0df7594ede4abeab3d8.png

age在group by列，所以不用加

既然没有select 聚合函数列，那么使用子查询去重也可以。

SELECT
  id ,
	name,
	age,
	salary 
FROM
	emp 
WHERE
id in (SELECT min(id) FROM emp GROUP BY age)

3f5a2cb420a81eef0d603dcad69129f8.png

但是这样做做了两次查询，并且如果有查询条件的话父子查询都要携带相同的查询条件，否则min(id)不一定是我们想要的id。 (这里纠正一下，查询条件只需要子查询携带即可，因为子查询已经根据条件把数据查询出来了，使用min(id)拿到了数据的id,所以父查询不需要条件了)


发现一个有趣的查询，把min改成any_value的时候：

SELECT
  id ,
	name,
	age,
	salary 
FROM
	emp 
WHERE
id in (SELECT any_value(id) FROM emp GROUP BY age)

72be4bd4fbae0586b3fcef2a85fefe6e.png

我们把子查询拿出来单独查询：

SELECT any_value(id) FROM emp GROUP BY age;

861c5a712e4e2f9ecde7de2460b20f78.png

只查出来4个id，那么为什么上面那个查询会查询出其他id的数据呢？如果你知道的话请在评论区告诉我吧！

avatar
文章
阅读
粉丝