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 系列 手札
本文作者:陈景峯
转载请与作者联系,同时请务必标明文章原始出处和作者信息及本声明。