远程与本地MySQL执行SQL查询差异：报错排查与修复

问题场景 #

远程MySQL服务器可正常执行以下SQL查询，但本地通过Django调用执行时触发报错。

执行的SQL查询 #

select
    y.school_grade
    , y.gender
    , count(1)
from
    (
    select
        x.*
    from
        (
        select
            sc.student_id
            , s.gender
            , s.school_grade
            , o.category_id
        from
            student_class sc
        left join student s on
            sc.student_id = s.student_id
        left join student_history h on
            h.student_id = s.student_id
        left join class c on
            c.class_id = sc.class_id_id
        left join category o on
            o.category_id = c.category_id_id
        where 
            s.is_active = 1
            and h.payment_complete = 1
            and month(s.created_date) >= 7
            and year(s.created_date) >= 2023
        order by
            s.school_grade
            , s.gender
) 
as x
    group by
        x.student_id
    having
        count(1) = 1
)
as y
where
    y.category_id = 1
group by
    y.school_grade
    , y.gender
order by
    y.school_grade
    , y.gender desc;

Django调用代码 #

>>> query1 = r"<query above>"
>>> with connection.cursor() as cursor:
...  cursor.execute(query1)
...  rows_all = cursor.fetchall()

报错信息 #

django.db.utils.ProgrammingError: (1055, "1055 (42000): Expression #4 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'x.category_id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by", '42000')

观察结果 #

• 该查询可在远程服务器正常执行
• 本地服务器执行该查询报错

待解决问题 #

1. 如何定位此问题的根本原因？
2. 如何修复该问题？

问题解答 #

1. 根本原因定位 #

核心问题是远程与本地MySQL的sql_mode配置不一致：

• 远程服务器未启用only_full_group_by模式，MySQL允许不符合SQL标准的GROUP BY查询（即SELECT列表中出现非聚合且未被GROUP BY包含的列）。
• 本地服务器启用了only_full_group_by模式（MySQL 5.7及以上版本默认开启），该模式要求：SELECT列表中的所有非聚合列必须出现在GROUP BY子句中，或与GROUP BY子句中的列存在函数依赖（比如主键关联的唯一值）。

回到你的SQL：中间子查询x的GROUP BY仅指定了x.student_id，但SELECT列表包含category_id这个非聚合列，且category_id与student_id无函数依赖（一个学生可能对应多个分类），因此在开启only_full_group_by的本地服务器上触发报错。

2. 修复方法 #

有两种可行方向，优先选择符合SQL标准的方案：

方案一：修改SQL语句适配only_full_group_by模式

由于中间子查询的having count(1) = 1已确保每个student_id仅对应一条记录，可通过以下两种方式调整：

方式1：将非聚合列加入GROUP BY子句

把category_id、gender、school_grade加入中间子查询的GROUP BY中（每个student_id对应的这些字段唯一，不会影响结果）：

select
    y.school_grade
    , y.gender
    , count(1)
from
    (
    select
        x.student_id
        , x.gender
        , x.school_grade
        , x.category_id
    from
        (
        select
            sc.student_id
            , s.gender
            , s.school_grade
            , o.category_id
        from
            student_class sc
        left join student s on
            sc.student_id = s.student_id
        left join student_history h on
            h.student_id = s.student_id
        left join class c on
            c.class_id = sc.class_id_id
        left join category o on
            o.category_id = c.category_id_id
        where 
            s.is_active = 1
            and h.payment_complete = 1
            and month(s.created_date) >= 7
            and year(s.created_date) >= 2023
        order by
            s.school_grade
            , s.gender
) 
as x
    group by
        x.student_id
        , x.gender
        , x.school_grade
        , x.category_id
    having
        count(1) = 1
)
as y
where
    y.category_id = 1
group by
    y.school_grade
    , y.gender
order by
    y.school_grade
    , y.gender desc;

方式2：对非聚合列使用聚合函数

因为having count(1)=1，每个student_id对应的category_id仅有一个值，用MAX()或MIN()包裹category_id，满足only_full_group_by要求：

select
    y.school_grade
    , y.gender
    , count(1)
from
    (
    select
        x.student_id
        , x.gender
        , x.school_grade
        , MAX(x.category_id) as category_id
    from
        (
        select
            sc.student_id
            , s.gender
            , s.school_grade
            , o.category_id
        from
            student_class sc
        left join student s on
            sc.student_id = s.student_id
        left join student_history h on
            h.student_id = s.student_id
        left join class c on
            c.class_id = sc.class_id_id
        left join category o on
            o.category_id = c.category_id_id
        where 
            s.is_active = 1
            and h.payment_complete = 1
            and month(s.created_date) >= 7
            and year(s.created_date) >= 2023
        order by
            s.school_grade
            , s.gender
) 
as x
    group by
        x.student_id
        , x.gender
        , x.school_grade
    having
        count(1) = 1
)
as y
where
    y.category_id = 1
group by
    y.school_grade
    , y.gender
order by
    y.school_grade
    , y.gender desc;

方案二：修改本地MySQL的sql_mode（不推荐）

若必须保留原有SQL，可修改本地MySQL配置移除only_full_group_by：

1. 执行SELECT @@sql_mode;查看当前配置
2. 修改MySQL配置文件（如my.cnf/my.ini），删除sql_mode中的ONLY_FULL_GROUP_BY
3. 重启MySQL服务

注意：该方案不符合SQL标准，可能导致查询结果异常，仅作为临时应急方案使用。

内容的提问来源于stack exchange，提问作者ablaze