【mybatis】【mysql】mybatis查询mysql，group by分组查询报错：Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column

时间：2022-07-15 20:50:06
mybatis查询mysql，group by分组查询报错：Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column

mysql版本是5.7

1.导致出错的sql语句是：

<select id="findScNumByTime" parameterType="com.pisen.cloud.luna.ms.security.code.api.beans.ScNumCountBean"

            resultType="com.pisen.cloud.luna.ms.security.code.api.beans.ScNumCountBean">

        SELECT

            create_date queryDate,

            IFNULL(sum(security_code_total), 0) createSCNum,

            IFNULL(sum(print_num), 0) printNum

        FROM

            security_code_config

        WHERE

            tid = #{tid}

        AND

            DATE_FORMAT(create_date,#{queryDateFormat}) &lt;= DATE_FORMAT(#{endDate},#{queryDateFormat})

        AND

            DATE_FORMAT(create_date,#{queryDateFormat}) &gt;= DATE_FORMAT(#{startDate},#{queryDateFormat})

        GROUP BY

            DATE_FORMAT(create_date,#{queryDateFormat})

    </select>

即：

SELECT

    create_date queryDate,

    IFNULL(sum(security_code_total), 0) createSCNum,

    IFNULL(sum(print_num), 0) printNum

FROM

    security_code_config

WHERE

    tid = 'test_tenement_123'

AND

    DATE_FORMAT(create_date,'%Y-%m') <= DATE_FORMAT('2019-03-12','%Y-%m')

AND

    DATE_FORMAT(create_date,'%Y-%m') >= DATE_FORMAT('2018-10-01','%Y-%m')

GROUP BY DATE_FORMAT(create_date,'%Y-%m')

2.导致出错的原因是因为：

请在mysql执行：

SHOW SESSION VARIABLES;

和

SHOW GLOBAL VARIABLES;

都可以看到：

【mybatis】【mysql】mybatis查询mysql，group by分组查询报错：Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column

错误原因就是这里：

only_full_group_by

要求select的列都要在group里面。

在mysql5.5没有这个问题！！！

3.解决方法：

分别执行

set session sql_mode= 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'

和

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'

注意先执行session的修改，再执行global的修改！！！！

修改完成后，再查看【执行第二步的两个sql查看即可】

【mybatis】【mysql】mybatis查询mysql，group by分组查询报错：Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column

4.最后，需要你重启spring boot服务，再进行查询调用，才会有效

【自己测试的情况就是，未重启mysql服务，重启了spring boot服务，再调用就可以正常执行了】

相关文章