MariaDB迁移MySQL时GROUP BY引发ERROR 1055问题求助

问题分析 #

你的SQL在MySQL 8.0中报错，核心原因是MySQL的only_full_group_by SQL模式要求SELECT列表中的非聚合列必须出现在GROUP BY子句中，或与GROUP BY列存在函数依赖（比如GROUP BY列是表的主键）。原查询GROUP BY amc_user.user_id，但SELECT amc_user.*包含了大量与user_id无函数依赖的列（如报错中的committee_user_id），违反了该模式的约束。而MariaDB默认允许宽松的GROUP BY行为，因此之前可以正常运行。

你明确表示不希望关闭only_full_group_by，以下是合规的修改方案：

方案1：子查询去重后关联（推荐） #

先通过子查询筛选出符合条件的唯一user_id及排序所需的lastname，再关联amc_user获取完整字段，避免GROUP BY带来的约束问题：

SELECT amc_user.*
FROM amc_user
INNER JOIN (
    SELECT DISTINCT us.id, us.lastname
    FROM users_standard us
    INNER JOIN amc_user au 
        ON us.id = au.user_id
    WHERE au.committee_id = 11 
      AND us.active = 1 
      AND us.system = 0 
      AND us.global_spawn != 1
      AND au.hidden != 1 
      AND (
          au.admin = 1 
          OR au.can_manage LIKE '%collertr%' 
          OR au.can_access LIKE '%collertr%'
      )
      -- 简化原条件：<=> 1 与 IN(1) 重复，直接保留NULL-safe等于判断
      AND us.client_id <=> 1
) AS filtered_users 
    ON filtered_users.id = amc_user.user_id
ORDER BY amc_user.chair DESC, filtered_users.lastname ASC;

方案2：使用ANY_VALUE()聚合函数 #

如果你能确定每个user_id在amc_user中仅对应一条记录，可以用ANY_VALUE()包裹所有非GROUP BY的列，告知MySQL取任意值即可（实际就是唯一的那条记录）：

SELECT 
    ANY_VALUE(amc_user.committee_user_id),
    ANY_VALUE(amc_user.user_id),
    ANY_VALUE(amc_user.committee_id),
    ANY_VALUE(amc_user.hidden),
    ANY_VALUE(amc_user.admin),
    ANY_VALUE(amc_user.can_manage),
    ANY_VALUE(amc_user.can_access),
    ANY_VALUE(amc_user.chair)
    -- 补充amc_user表的其他所有字段
FROM amc_user 
INNER JOIN users_standard 
    ON users_standard.id = amc_user.user_id 
    AND users_standard.client_id <=> 1
WHERE amc_user.committee_id = 11 
  AND users_standard.active = 1 
  AND users_standard.system = 0 
  AND users_standard.global_spawn != 1 
  AND amc_user.hidden != 1 
  AND (
      amc_user.admin = 1 
      OR amc_user.can_manage LIKE '%collertr%' 
      OR amc_user.can_access LIKE '%collertr%'
  )
GROUP BY amc_user.user_id 
ORDER BY ANY_VALUE(amc_user.chair) DESC, users_standard.lastname ASC;

额外优化说明 #

原查询中的(users_standard.client_id <=> 1 OR users_standard.client_id IN (1))可以简化为users_standard.client_id <=> 1，因为IN(1)等价于=1，而<=>已经包含了NULL-safe的等于判断，无需重复。

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