1. 异常现象

数据库在迁移后执行SQL出现了问题, 报错信息如下

SELECT list is not in GROUP BY clause and contains nonaggregated column 
'cloud.exam_train_user.user_id' which is not functionally 
dependent on columns in GROUP BY clause; 
this is incompatible with sql_mode=only_full_group_by

可以看出是因为sql_mode中设置了only_full_group_by模式引起的

在这个模式下，我们使用分组查询时，出现在select字段后面的只能是group by后面的分组字段，或使用聚合函数包裹着的字段。

Oracled等数据库都不支持select target list中出现语义不明确的列，这样的语句在这些数据库中是会被报错的，所以从MySQL 5.7版本开始修正了这个语义，就是所说的ONLY_FULL_GROUP_BY语义。

MySQL 官方解释

MySQL 5.7.5 及更高版本默认开启了ONLY_FULL_GROUP_BY的 SQL 模式

解决方法

方法1: 修改sql_mode

可以通过select @@sql_mode查出sql_mode以后去掉ONLY_FULL_GROUP_BY后复制过来

查询sql_mode设置，发现有ONLY_FULL_GROUP_BY

mysql> select version(),@@sql_mode;
+------------+----------------------------------------------------------------+
| version()  | @@sql_mode                                                     |
+------------+----------------------------------------------------------------+
| 5.7.29-log | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+------------+----------------------------------------------------------------+
1 row in set (0.00 sec)

修改sql_mode设置

临时修改

set sql_mode=' ' //改变已经存在的数据库sql_mode

set @@global.sql_mode=' ' //改变全局配置sql_mode

以上配置在重启服务以后失效

更改配置文件（推荐使用）

• linux系统更改/etc/my.cnf文件
• windows下配置文件是安装目录下的my.ini文件

如果有sql_mode=...的注释就把注释打开，如果没有就加上sql_mode=...

sql_mode = 'STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

MySQL5.7: my.cnf参数速查小册子

方法2: select非group by的字段

直接修改修改sql_mode设置, 很多时候对于开发人员时没有权限的, DBA也需要很慎重地修改,因此对于开发人员, 我们可以在SQL层面进行优化

通过上文，我们知道，

如果sql_mode中设置了only_full_group_by模式

在这个模式下，我们使用分组查询时，出现在select字段后面的只能是group by后面的分组字段，或使用聚合函数包裹着的字段。

因此，此时在含有Group by子句的查询语句中，对select关键字后的目标列，存在以下规律

1. 使用group by 时，select 涉及的列要么是参与分组的列，要么列包含在聚合函数中
2. where将对分组前的所有数据进行筛选。having将对分组后的一组数据搞事情。

例如：

select a,b,avg(c),sum(d) from 表 group by a,b

select非group by的字段

• 可以把group by的结果集当作一个表，然后从这里表里取数就可以了
• 例如：查询，订单表中订单号重复的订单信息，分组字段是orderno，select的信息不仅有orderno，还有其他信息

SELECT
  p.id,
  p.orgcode,
  p.idserial,
  p.username,
  p.orderno,
  p.createtime,
  p.payflag,
  p.businessorderno,
  p.paytime
FROM
  pay_order_trade p,
  (
    SELECT
      orderno,
      count(*) 重复次数
    FROM
      pay_order_trade
    GROUP BY
      orderno
    HAVING
      count(1) > 1
  ) s
WHERE
  s.orderno = p.orderno
ORDER BY
  p.orderno DESC;

MySQL 5.7: sql_mode

模式会影响 MySQL 支持的 SQL 语法以及它执行的数据验证检查。

这使得在不同环境中使用 MySQL 以及与其他数据库服务器一起使用 MySQL 变得更容易。

设置 SQL 模式

在MySQL 5.7的默认SQL模式包括以下模式：ONLY_FULL_GROUP_BY， STRICT_TRANS_TABLES， NO_ZERO_IN_DATE， NO_ZERO_DATE， ERROR_FOR_DIVISION_BY_ZERO， NO_AUTO_CREATE_USER，和 NO_ENGINE_SUBSTITUTION。

这些模式已添加到 MySQL 5.7 中的默认 SQL 模式： ONLY_FULL_GROUP_BY和 STRICT_TRANS_TABLES模式已添加到 MySQL 5.7.5 中。该 NO_AUTO_CREATE_USER模式是在 MySQL 5.7.7 中添加的。的 ERROR_FOR_DIVISION_BY_ZERO， NO_ZERO_DATE以及 NO_ZERO_IN_DATE在MySQL 5.7.8添加模式。有关对默认 SQL 模式值的这些更改的其他讨论，请参阅 MySQL 5.7 中的 SQL 模式更改。

sql_mode参数说明

更多信息参考: https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html

参考连接

https://dev.mysql.com/doc/refman/5.7/en/dynindex-sqlmode.html

https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html

https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html