Mysql——Mysql版本导致GROUP BY分组异常

最新推荐文章于 2024-08-02 04:06:54 发布

原创最新推荐文章于 2024-08-02 04:06:54 发布 · 639 阅读

0 ·

本内容遵循CC 4.0 BY-SA版权协议

标签

#mysql

Java 专栏收录该内容

11 篇文章

订阅专栏

本文介绍了在MySQL 5.7.x版本中遇到的GROUP BY分组异常问题，错误信息涉及到sql_mode=only_full_group_by。文章详细列举了两种解决方案：一是修改MySQL配置文件，取消ONLY_FULL_GROUP_BY设置；二是直接修改SQL语句，使用any_value()函数。作者建议尽量避免修改配置，而是优化SQL以适应新的SQL模式。

Mysql版本导致GROUP BY分组异常

一、异常信息

在这里插入图片描述

Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘ecpnew.m.id’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

二、数据库版本

版本 1
在这里插入图片描述

版本 2（出事版本）

在这里插入图片描述

三、SQL

        select count(m.material_name)  realSorageNumber  ,m.*
        from tb_material_inventory   m
        left join
        tb_catalog c  on  m.catalog_buss_key =  c.buss_key
        where  m.catalog_buss_key  in (
                SELECT
                    buss_key
                FROM
                    (
                SELECT
                    @ids AS _ids,
                    ( SELECT @ids := GROUP_CONCAT( id ) FROM tb_catalog WHERE FIND_IN_SET( parent_id, @ids ) ) AS cids,
                    @l := @l + 1 AS LEVEL
                FROM
                    tb_catalog,
                    ( SELECT @ids := #{catalogId}, @l := 0 ) b
                WHERE
                    @ids IS NOT NULL
                    ) id,
                    tb_catalog DATA
                WHERE
                    FIND_IN_SET( DATA.id, _ids )
                ORDER BY
                    LEVEL,id
        )
        and m.warehouse_buss_key = #{bussKey}
        GROUP BY m.catalog_buss_key

该处使用的url网络请求的数据。

四、解决方案

1、修改Mysql配置文件（不建议采用）

原配置文件

select @@global.sql_mode;
结果：ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

修改配置（将ONLY_FULL_GROUP_BY去掉）

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';

2、修改SQL

MySQL也提供了any_value()函数来抑制ONLY_FULL_GROUP_BY值被拒绝（5.5版本不支持这个函数）

    <select id="selectMaterialByRoot" resultType="com.luke.admin.entity.vo.MaterialInventoryVO">
        select count(m.material_name)  realSorageNumber  , <include refid="anyValue"/>
        from tb_material_inventory   m
        left join
        tb_catalog c  on  m.catalog_buss_key =  c.buss_key
        where  m.catalog_buss_key  in (
                SELECT
                    buss_key
                FROM
                    (
                SELECT
                    @ids AS _ids,
                    ( SELECT @ids := GROUP_CONCAT( id ) FROM tb_catalog WHERE FIND_IN_SET( parent_id, @ids ) ) AS cids,
                    @l := @l + 1 AS LEVEL
                FROM
                    tb_catalog,
                    ( SELECT @ids := #{catalogId}, @l := 0 ) b
                WHERE
                    @ids IS NOT NULL
                    ) id,
                    tb_catalog DATA
                WHERE
                    FIND_IN_SET( DATA.id, _ids )
                ORDER BY
                    LEVEL,id
        )
        and m.warehouse_buss_key = #{bussKey}
        GROUP BY m.catalog_buss_key
    </select>
    <sql id="anyValue">
        any_value(m.id) as id ,
        any_value(m.material_name) as material_name ,
        any_value(m.catalog_buss_key) as catalog_buss_key,
        any_value(m.catalog_name) as catalog_name,
        any_value(m.rfid) as rfid,
        any_value(m.warehouse_buss_key) as warehouse_buss_key,
        any_value(m.warehouse_name) as warehouse_name,
        any_value(m.manufacture_date) as manufacture_date,
        any_value(m.expiration_date) as expiration_date,
        any_value(m.model) as model,
        any_value(m.storage_number) as storage_number,
        any_value(m.reason_name) as reason_name,
        any_value(m.storage_location) as storage_location,
        any_value(m.specifications) as specifications,
        any_value(m.material_img) as material_img,
        any_value(m.material_type) as material_type,
        any_value(m.factory_id) as factory_id,
        any_value(m.factory_name) as factory_name,
        any_value(m.material_state) as material_state,
        any_value(m.batch) as batch,
        any_value(m.company) as company,
        any_value(m.create_time) as create_time,
        any_value(m.update_time) as update_time,
        any_value(m.description) as description
    </sql>

五、总结

mysql5.7.x版本，默认是开启了 only_full_group_by 模式的，但开启这个模式后，原先的 group by 语句就报错，关闭这个模式是不可取的。MySQL 5.7.5和up实现了对功能依赖的检测。如果启用了only_full_group_by SQL模式(在默认情况下是这样)，那么MySQL就会拒绝选择列表、条件或顺序列表引用的查询，这些查询将引用组中未命名的非聚合列，而不是在功能上依赖于它们。