解決mySQL 5.7 SELECT list is not in GROUP BY clause and contains nonaggregated column which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by 問題

1. vi /etc/my.cnf
2. 增加
   
   [mysqld] sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
   
3. 儲存然後restart mysql
4. 或者:
   
   

   mysql -u root -p
   mysql > SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

查了其他的資料, 原因如下:

You need to specify all of the columns that you're not using for an aggregation function in your GROUP BY clause like this:

select libelle,credit_initial,disponible_v,sum(montant) as montant 
FROM fiche,annee,type where type.id_type=annee.id_type and annee.id_annee=fiche.id_annee 
and annee = year(current_timestamp) GROUP BY libelle,credit_initial,disponible_v order by libelle asc

The full_group_by mode basically makes you write more idiomatic SQL. You can turn off this setting if you'd like. There are different ways to do this that are outlined in the MySQL Documentation. Here's MySQL's definition of what I said above:

MySQL 5.7.5 and up implements detection of functional dependence. If the ONLY_FULL_GROUP_BY SQL mode is enabled (which it is by default), MySQL rejects queries for which the select list, HAVING condition, or ORDER BY list refer to nonaggregated columns that are neither named in the GROUP BY clause nor are functionally dependent on them. (Before 5.7.5, MySQL does not detect functional dependency and ONLY_FULL_GROUP_BY is not enabled by default. For a description of pre-5.7.5 behavior, see the MySQL 5.6 Reference Manual.)

You're getting the error because you're on a version < 5.7.5

資料來源:  http://stackoverflow.com/a/37951819

以及以下的:

MySQL Handling of GROUP BY

only_full_group_by (Default in mysql 5.7.9)
SQL92 and earlier does not permit queries for which the select list, HAVING condition, or ORDER BY list refer to nonaggregated columns that are neither named in the GROUP BY clause nor are functionally dependent on (uniquely determined by) GROUP BY columns.
以下面的例子來說，如果 name 是 primary key 或是 unique NOT NULL column，GROUP 時 address 可以對映到唯一 name。反之，將造成一個 address 會找到超過一個 name ，這種情況（nonaggregated columns）是被禁止的。

1 2 3 4 5

mysql> SELECT name, address, MAX(age) FROM t GROUP BY name; > ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'mydb.t.address' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

• 解法一：ANY_VALUE()

1	mysql> SELECT name, ANY_VALUE(address), MAX(age) FROM t GROUP BY name;

• 解法二：關閉 ONLY_FULL_GROUP_BY mode

1 2	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';

Note.

一對多的資料，展開後會出現多筆，但只要回傳一筆，原本的版本可以對 id 進行 GROUP。但是新版的 MYSQL 禁止這種 GROUP 0.0」

資料來源:  http://v123582.github.io/blog/2016/01/26/%E5%9C%A8mac-%E6%9B%B4%E6%96%B0-mysql-%E7%AD%86%E8%A8%98/