MySQL：报错 ERROR 1055 (42000)sql_mode=only_full_group_by

环境

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.16    |
+-----------+

执行查询语句

mysql> select * from my_student;
+----+--------+----------+------+--------+
| id | name   | class_id | age  | gender |
+----+--------+----------+------+--------+
|  1 | 刘备   |        1 |   18 |      2 |
|  2 | 李四   |        1 |   19 |      1 |
|  3 | 王五   |        2 |   20 |      2 |
|  4 | 张飞   |        2 |   21 |      1 |
|  5 | 关羽   |        1 |   22 |      2 |
|  6 | 曹操   |        1 |   20 |   NULL |
+----+--------+----------+------+--------+
6 rows in set (0.00 sec)

-- 需求：按照年龄排序后，取出每个班级年龄最大的学生
select * from (
    select * from my_student order by age desc
) as t group by t.class_id;

报错

ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause 
and contains nonaggregated column 't.id' 
which is not functionally dependent on columns in GROUP BY clause; 
this is incompatible with sql_mode=only_full_group_by

原因：

MySQL 5.7.5及以上功能依赖检测功能

解决办法

-- 查看当前配置项
select @@global.sql_mode

-- 将 ONLY_FULL_GROUP_BY 去掉
set @@global.sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

设置完后，如果不生效，可以退出重新登录

参考 mySql中Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggre的问题