使用GROUP BY替代DISTINCT时MariaDB JOIN查询结果不符合预期的问题咨询

这绝对是个让人摸不着头脑的问题！先给你吃颗定心丸：这不是你的数据库异常，而是MariaDB在特定配置下的预期行为，核心和SQL模式以及优化器的处理逻辑有关。让我一步步给你拆解：

问题根源：ONLY_FULL_GROUP_BY 模式的缺失 #

AWS免费版的MariaDB实例默认可能没有启用ONLY_FULL_GROUP_BY这个严格SQL模式。虽然你的子查询写法select id from b group by id看起来完全合理（SELECT的列就是GROUP BY的分组列），但在非严格模式下，MariaDB的优化器偶尔会出现逻辑偏差——它可能错误地跳过了GROUP BY的去重逻辑，或者把JOIN的匹配条件做了不合理的优化，导致最终结果等价于外连接。

验证步骤 #

你可以先单独执行两个子查询确认结果：

• 执行 select id from b group by id;，看看返回的是不是2到100的偶数（共50行）。如果这里出现了id=1，那说明你可能误操作给表B插入了id=1的数据；如果子查询结果正确，但JOIN后还是返回全量，那肯定是优化器的锅。
• 执行 SELECT @@sql_mode;，查看当前的SQL模式列表。如果里面没有ONLY_FULL_GROUP_BY，那就是我说的配置问题。

解决方案（适配AWS免费版的限制） #

因为你几乎无法修改实例的全局配置，试试这些变通方法：

1. 临时启用严格模式：在执行你的JOIN查询前，先运行这条语句临时修改会话级别的SQL模式：

   SET SESSION sql_mode = CONCAT(@@sql_mode, ',ONLY_FULL_GROUP_BY');
   

   之后再执行你的GROUP BY版本查询，结果应该就正常了。这个设置只会影响当前会话，不会改变全局配置。
2. 继续使用DISTINCT写法：select distinct(id) id from b的行为在所有SQL模式下都更稳定，是获取唯一值的标准写法，完全可以作为替代方案。
3. 给GROUP BY加聚合函数：如果一定要用GROUP BY，可以在子查询里加一个无关的聚合函数，强制数据库正确分组：

   select a.id from a join (select id, COUNT(*) from b group by id) x on a.id=x.id;
   

   这样优化器就不会跳过分组逻辑了，结果也会符合预期。

总结 #

这种情况是MariaDB在非严格GROUP BY模式下的潜在优化偏差，不是数据库损坏或者异常。用上面的方法就能轻松规避这个问题啦。

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