时间戳

首页 > 解决方案 > 重写 SQL 查询以修复 MySQL 5.7 严格模式导致的功能依赖问题

mysql - 重写 SQL 查询以修复 MySQL 5.7 严格模式导致的功能依赖问题

问题描述

我最近将我的 MySQL 服务器升级到 5.7 版，但以下示例查询不起作用：

SELECT * 
FROM (SELECT * 
        FROM exam_results 
        WHERE exam_body_id = 6674 
        AND exam_date >= DATE_SUB(CURDATE(), INTERVAL 1 WEEK) 
        AND subject_ids LIKE '%4674%' 
        ORDER BY score DESC 
    ) AS top_scores 
GROUP BY user_id 
ORDER BY percent_score DESC, time_advantage DESC 
LIMIT 10

该查询应该从指定的表中选择与在某个时间间隔内完成特定考试的最高得分者匹配的考试结果。我在第一次编写查询时必须包含 GROUP BY 子句的原因是为了消除重复用户，即在同一时间段内有多个最高分的用户参加考试。在不消除重复用户 ID 的情况下，前 10 名高分者的查询可能会返回同一个人的考试结果。

我的问题是：如何重写此查询以消除与 MySQL 5.7 严格模式相关的错误，该模式在 GROUP BY 子句上强制执行，同时仍保留我想要的功能？

标签： mysqlsqlgroup-bymysql-5.7mysql-error-1055

解决方案

当您GROUP BY按列的子集 ( ) 聚合 ( ) 结果集时，user_id需要聚合所有其他列。

注意：根据 SQL 标准，如果您按主键分组，则没有必要这样做，因为所有其他列都依赖于 PK。但是，您的问题并非如此。

现在，您可以使用任何聚合函数，如MAX()、MIN()、SUM()等。我选择使用MAX()，但您可以为其中任何一个更改它。

查询可以运行为：

SELECT 
  user_id,
  max(exam_body_id),
  max(exam_date),
  max(subject_ids),
  max(percent_score),
  max(time_advantage)
FROM exam_results 
WHERE exam_body_id = 6674 
  AND exam_date >= DATE_SUB(CURDATE(), INTERVAL 1 WEEK) 
  AND subject_ids LIKE '%4674%' 
GROUP BY user_id 
ORDER BY max(percent_score) DESC, max(time_advantage) DESC 
LIMIT 10

请参阅DB Fiddle上的运行示例。

现在，您问为什么需要聚合其他列？由于您正在对行进行分组，因此引擎需要为每组生成一行。因此，当有许多值可供选择时，您需要告诉引擎选择哪个值：最大的值、最小的值、它们的平均值等。

在 MySQL 5.7.4 或更早版本中，引擎不要求您聚合其他列。引擎默默随机为你决定。您今天可能已经得到了您想要的结果，但明天引擎可能会在您不知情的情况下选择 theMIN()而不是 the MAX()，因此每次运行查询时都会导致不可预测的结果。

推荐阅读