31.11. this is incompatible with sql_mode=only_full_group_by

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

		
mysql> select @@version;
+-----------+
| @@version |
+-----------+
| 5.7.10    |
+-----------+
1 row in set (0.00 sec)

mysql> select @@GLOBAL.sql_mode;
+-------------------------------------------------------------------------------------------------------------------------------------------+
| @@GLOBAL.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 |
+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SET sql_mode = '';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> select id,name from contact group by name limit 10;
+-------+-------------+
| id    | name        |
+-------+-------------+
| 84046 |   张伟      |
| 80259 |   张磊      |
|   784 |   王岩      |
| 87685 |  杨钞       |
+-------+-------------+
10 rows in set (0.07 sec)
		
		
		

不建议设置 SET sql_mode = ''，正确方式如下：

		
mysql> set global sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
mysql> set session sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';		
		
		

或者采用

				
Adding only one mode to sql_mode without removing existing ones:

SET sql_mode=(SELECT CONCAT(@@sql_mode,',<mode_to_add>'));
Removing only a specific mode from sql_mode without removing others:

SET sql_mode=(SELECT REPLACE(@@sql_mode,'<mode_to_remove>',''));
In your case, if you want to remove only ONLY_FULL_GROUP_BY mode, then use below command:

SET sql_mode=(SELECT REPLACE(@@sql_mode, 'ONLY_FULL_GROUP_BY', ''));		
		
		

原文出处：Netkiller 系列 手札
本文作者：陈景峯
转载请与作者联系，同时请务必标明文章原始出处和作者信息及本声明。