MySQL only_full_group_by error

Question

我不是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]：语法错误或访问冲突：1055 表达式 #2 的 SELECT 列表不在 GROUP BY 子句中，并且包含非聚合 列"结果.完整URL"，在功能上不依赖于列 在分组依据子句中;这与 sql_mode=only_full_group_by

感谢您的任何帮助！

Answer 1

">

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

此查询甚至不应该一开始就分组;没有使用聚合函数。如果"仅全分组依据"不是当前设置，则此查询将是"为每个 contentId 给我一个有效的随机结果，传递 where 条件"。