Mysql 5.7 group by 1055錯誤

SELECT name, address FROM t GROUP BY name;

ERROR  (): 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
           

錯誤原因

mysql資料庫在only_full_group_by模式下，select中含有的非函數字段名未完全包含在group by 子句中，就會報1055錯誤，如本例中address字段不在group by後，就會報1055錯誤。

解決方法

1.在 group by 後将select中含有的非函數字段名補充完。

2.使用ANY_VALUE()函數引用address

3.修改address為表t的主鍵或者将address改為t表中唯一的非空列

4.禁用only_full_group_by