mysql sql_mode ONLY_FULL_GROUP_BY

sql_mode

mysql> select @@sql_mode;
+------------------------------------------------------------------------------------------------------------------------+
| @@sql_mode                                                                                                             |
+------------------------------------------------------------------------------------------------------------------------+
| STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> select distinct empno from EMP order by deptno ;
+-------+
| empno |
+-------+
|     1 |
|     0 |
+-------+
2 rows in set (0.02 sec)
mysql> set sql_mode ='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';
Query OK, 0 rows affected (0.01 sec)
mysql> select distinct empno from EMP order by deptno ;
ERROR 3065 (HY000): Expression #1 of ORDER BY clause is not in SELECT list, references column 'test.EMP.DEPTNO' which is not in SELECT list; this is incompatible with DISTINCT
mysql>

5.6之前預設非嚴格的分組函式，5.7開始預設是嚴格的聚合函式，透過ONLY_FULL_GROUP_BY控制