Mysql的sqlMode=only_full_group_by限制是什么？为什么会导致group by语法差别

本文分享一次高低版本中mysql配置sqlMode= only_full_group_by开启关闭状态不一致造成group by语法错误。

问题产生原因

先来讲讲这个由于sqlMode导致语法报错的问题吧，由于新项目和老项目mysql版本不一致，当我copy一个老项目线上的sql到新项目执行，表结构都一致竟然报错了！！！

举一个案例吧：

select a,b from table1 group by c

报错信息如下
Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘X’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

解决方案

解决这个问题很简单，下面针对不同场景给出三个解决方案。

暴力解决

如果你拥有修改数据库配置的权限，那么很简单将sqlMode = ONLY_FULL_GROUP_BY关闭，即可。

当然如果说你是新项目，你可以去掉该配置，以后都无需注意该事项，但是如果你是老项目，之前sql都遵循这个sqlMode规范，你去除之后sql语句写法就不太统一了，当然不会影响之前的sql。
我也是这样做的，因为我毕竟是两个数据库配置不一致，导致新老两个项目会存在规范不一致问题。一个功能，两个项目要写两份sql多少有点麻烦对吧。

优雅的解决

方法1

关于group by用法，如果你仅仅是为了去重而使用该用法，其实本身使用group by就有问题。

-- 案例1 查询购买过合同 且金额大于100元 用户集合（去重）
SELECT o.user_id, u.name
  FROM orders  o
  LEFT JOIN user_info u on o.user_id = u.id
  WHERE o.pay > 100
  GROUP BY o.user_id;

我们可以使用distinct来替代group by达到同样的效果。

SELECT distinct(o.user_id), u.name
  FROM orders  o
  LEFT JOIN user_info u on o.user_id = u.id
  WHERE o.pay > 100;

方法2

其实除了利用distinc还可以使用mysql提供的内置函数any_value()

SELECT o.user_id, any_value(u.name)
  FROM orders  o
  LEFT JOIN user_info u on o.user_id = u.id
  WHERE o.pay > 100
  GROUP BY o.user_id;

方法3

不妨你顺从他，需要的列都进行group by进行聚合。当然前提这样修改不会影响你业务需求。

案例1例子按照下方改就不会影响业务需求，当然也会存在影响的，更改完sql查询语句并不是想要的，在下面在给你详细介绍吧。

SELECT o.user_id, u.name
  FROM orders  o
  LEFT JOIN user_info u on o.user_id = u.id
  WHERE o.pay > 100
  GROUP BY o.user_id, u.name;

其实还有很多方法，例如 放在子查询聚合，然后将in一下聚合的范围进行查询想要的列等很多方法。就不一一阐述了，接下来我们深入了解一下为什么Mysql要这么要求我们，非要规定这个规范呢？

深入了解

mysql官方文档链接

only_full_group_by介绍

MySQL 5.7.5 and later implements detection of functional dependence. If the ONLY_FULL_GROUP_BY SQL mode is enabled (which it is by default), MySQL rejects queries for which the select list, HAVING condition, or ORDER BY list refer to nonaggregated columns that are neither named in the GROUP BY clause nor are functionally dependent on them. (Before 5.7.5, MySQL does not detect functional dependency and ONLY_FULL_GROUP_BY is not enabled by default.)

翻一下就是Mysql在5.75之后默认开启ONLY_FULL_GROUP_BY的sqlMode。
核心就是 若使用了group by，那么查询列，使用having后的条件列 或 order by的排序列都必须是group by后的聚合列。
举个例子就是 若group a,b 那么我们只能查询a,b列，having只能利用a或b进行过滤，以及排序必须是a或b。

MySQL 5.7.5 and later also permits a nonaggregate column not named in a GROUP BY clause when ONLY_FULL_GROUP_BY SQL mode is enabled, provided that this column is limited to a single value, as shown in the following example.

mysql> CREATE TABLE mytable (
    ->    id INT UNSIGNED NOT NULL PRIMARY KEY,
    ->    a VARCHAR(10),
    ->    b INT
    -> );

mysql> INSERT INTO mytable
    -> VALUES (1, 'abc', 1000),
    ->        (2, 'abc', 2000),
    ->        (3, 'def', 4000);

mysql> SET SESSION sql_mode = sys.list_add(@@session.sql_mode, 'ONLY_FULL_GROUP_BY');

mysql> SELECT a, SUM(b) FROM mytable WHERE a = 'abc';
+------+--------+
| a    | SUM(b) |
+------+--------+
| abc  |   3000 |
+------+--------+

mysql还支持 你即使没有group by a，只要where条件中 a 等于某一个值，mysql可以等同于你group by a了，你就可以sum(b)

这样限制的意义

那mysql为什么要这样限制呢？我们还是接着上个table来去解释吧：

mysql> CREATE TABLE mytable (
    ->    id INT UNSIGNED NOT NULL PRIMARY KEY,
    ->    a VARCHAR(10),
    ->    b INT
    -> );

mysql> INSERT INTO mytable
    -> VALUES (1, 'abc', 1000),
    ->        (2, 'abc', 2000),
    ->        (3, 'def', 4000);

如果我们执行下方sql：
SELECT a, b FROM mytable WHERE a = ‘abc’ group by a;

你会发现一件，我们似乎不能确认最终的结果，因为存在两种情况：
情况1

+------+--------+
| a    |    b   |
+------+--------+
| abc  |   1000 |
+------+--------+

情况2

+------+--------+
| a    |    b   |
+------+--------+
| abc  |   2000 |
+------+--------+

所以Mysql不清楚你想要哪种结果，所以想要你显示指定如何选择非聚合列（也是例子中b列）。

总结

其实ONLY_FULL_GROUP作用就是想用户在聚合情况下，需要指定非聚合列选择方式（ANY_VALUE(b),SUM(b),Max(b)等，让Mysql从多行中筛选出用户想要的那一行。