sql_mode-ONLY_FULL_GROUP_BY

sql_mode 用于控制 MySQL 的行为

比如说 MySQL-8.0.x sql_mode 的默认值如下。

select @@sql_mode;
+-----------------------------------------------------------------------------------------------------------------------+
| @@sql_mode                                                                                                            |
+-----------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
+-----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

---

ONLY_FULL_GROUP_BY

要说清楚这个，还是要说一个以前 MySQL 校验不严格的锅，假设有如下数据。

select * from t_person;
+----+-------+-----+
| id | name  | age |
+----+-------+-----+
|  1 | tom   |  17 |
|  2 | jerry |  17 |
|  3 | bob   |  18 |
+----+-------+-----+
3 rows in set (0.00 sec)

MySQL 以前检验不严格，我们是能写出一些逻辑上有问题，但是可以正常执行的 SQL 的。

-- 在 sql_mode 中去掉 ONLY_FULL_GROUP_BY ，用以回到以前那个不严格的年代
set @@session.sql_mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION";

-- 没有按 name 分组但是 name 能出现在分组过滤的条件语句中。 dog 呀！
select name, age, count(*) as cnts from t_person group by age having name='tom' ;
+------+-----+------+
| name | age | cnts |
+------+-----+------+
| tom  |  17 |    2 |
+------+-----+------+
1 row in set (0.00 sec)

如果我们加上这个 SQL_MODE 类似的 SQL 在执行过程中会报错。

select name, age, count(*) as cnts from t_person group by age having name='tom' ;

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

还有一个差不多的场景就是 group by 之后的 order，先来一段正常的

select age, count(*) as cnts from t_person group by age order by cnts desc;
+-----+------+
| age | cnts |
+-----+------+
|  17 |    2 |
|  18 |    1 |
+-----+------+
2 rows in set (0.00 sec)

再来一段逻辑上明显有问题的 SQL 。

select age, count(*) as cnts from t_person group by age order by name desc;

ERROR 1055 (42000): Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'tempdb.t_person.name' 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 还是非常有必要的，至少一些逻辑上明显有错误的 SQL 语句现在可以报错了。

---