MySQL only_full_group_by 错误

Question

这个问题已经有答案了:

Error related to only_full_group_by when executing a query in MySql (18 个回答)

已关闭 5 年前。

我不是 DBA，我继承了这个查询，但我不太确定为什么我会得到 sql_mode=only_full_group_by错误。特别是因为错误正在谈论 result.fullURL我在查询中没有看到这一点。有人可以向我解释一下吗？

查询

SELECT *
FROM (
    SELECT content.*, content.navigationOrder AS sortOrder
    FROM LB_Content AS content
    INNER JOIN LB_Content AS other ON content.contentSectionId = other.contentSectionId
    WHERE other.fullURL = '/index'
    AND content.contentSlug <> 'index'
    GROUP BY content.contentId
    UNION
    SELECT content.*, query.sectionOrder AS sortOrder
    FROM LB_Content AS content, (
        SELECT section.*
        FROM LB_ContentSections AS section
        INNER JOIN LB_Content AS other ON section.parentContentSectionId = other.contentSectionId
        WHERE other.fullURL = '/index'
    ) AS query
    WHERE content.contentSectionId = query.contentSectionId
    AND content.contentSlug = 'index'
) as result,
LB_ContentTypes AS types
WHERE result.showInNavigation = 1
AND result.status = 1
AND result.published = 1
AND result.contentTypeId = types.contentTypeId
AND types.useOption = 1
GROUP BY result.contentId
ORDER BY result.sortOrder ASC

错误输出

SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'result.fullURL' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

感谢您的帮助!

Answer 1

“仅完整分组依据”表示聚合查询必须对结果中的所有非聚合字段进行分组。它们是否“隐藏”在 * 中并不重要，这些字段也必须按分组依据进行分组。

该查询一开始就不应该分组；没有使用聚合函数。如果“only full group by”不是当前设置，则此查询将是“为每个 contentId 提供一个有效的随机结果，传递 where 条件”。