MySQL 5.7預設ONLY_FULL_GROUP_BY語義介紹以及故障解決

問題描述：Thinkphp程式由mysql5.6版本升級為mysql5.7版本，出現FULL_GROUP_BY。

SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'dbtest.ims_sudu8_page_collect.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

mysql column which is not functionally dependent GROUP BY clause

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

【解決方案】

檢視sql_mode

mysql> show variables like "sql_mode";

配置檔案my.ini

sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

修改為

sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

重啟MySQL服務解決

[技術原理原因]

ONLY_FULL_GROUP_BY是MySQL提供的一個sql_mode，通過這個sql_mode來提供SQL語句“分組求最值”合法性的檢查，在MySQL的sql_mode為非ONLY_FULL_GROUP_BY語義時。一條select語句，MySQL允許target list中輸出的表示式是除聚集函式或group by column以外的表示式，但這個表示式的值可能在經過group by操作後變成了未知的

Tips：所以很多從5.6升級到5.7時，為了語法相容，大部分都會選擇調整sql_mode，使其保持跟5.6一致，為了儘量相容程式。但是這個問題也是需要注意的，不要以為這種投機的寫法不會有人用。在stackoverflow中，該實現的點贊數就有116個，由此可見其受眾之廣。

【故障描述】

在使用group by 查詢一張表的資料的時候：select date,time,max(delaytime) as delaytime,sum(delaynum) as delaynum, max(onlineCount) as onlineCount,sum(perMinuteVerify) as perMinuteVerify,auditor from verifyDelayLog WHERE `date` = '2016-06-29' group by time;

報錯如下：
ERROR 1055 (42000): Expression #7 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'postscan.verifyDelayLog.auditor' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

原因：使用  select @@sql_mode； 命令可以看到，資料庫設定了 ONLY_FULL_GROUP_BY 的mode，意思就是：
      對於GROUP BY聚合操作，如果在SELECT中的列，沒有在GROUP BY中出現，那麼這個SQL是不合法的，因為列不在GROUP BY從句中
        所以對於設定了這個mode的資料庫，在使用group by 的時候，就要用MAX(),SUM(),ANT_VALUE()這種聚合函式，才能完成GROUP BY 的聚合操作。

【附錄其他的一些sql mode含義】
STRICT_TRANS_TABLES：

在該模式下，如果一個值不能插入到一個事務表中，則中斷當前的操作，對非事務表不做限制
NO_ZERO_IN_DATE：

在嚴格模式下，不允許日期和月份為零

NO_ZERO_DATE：

設定該值，mysql資料庫不允許插入零日期，插入零日期會丟擲錯誤而不是警告。

ERROR_FOR_DIVISION_BY_ZERO：

在INSERT或UPDATE過程中，如果資料被零除，則產生錯誤而非警告。如 果未給出該模式，那麼資料被零除時MySQL返回NULL

NO_AUTO_CREATE_USER：

禁止GRANT建立密碼為空的使用者

NO_ENGINE_SUBSTITUTION：

如果需要的儲存引擎被禁用或未編譯，那麼丟擲錯誤。不設定此值時，用預設的儲存引擎替代，並丟擲一個異常

PIPES_AS_CONCAT：

將"||"視為字串的連線操作符而非或運算子，這和Oracle資料庫是一樣的，也和字串的拼接函式Concat相類似

ANSI_QUOTES：

啟用ANSI_QUOTES後，不能用雙引號來引用字串，因為它被解釋為識別符

NO_AUTO_VALUE_ON_ZERO：

該值影響自增長列的插入。預設設定下，插入0或NULL代表生成下一個自增長值。如果使用者 希望插入的值為0，而該列又是自增長的，那麼這個選項就有用了。

>>>需要注意的是

NO_ZERO_DATE：在非嚴格模式下，可以插入形如“0000-00-00 00:00:00”的非法日期，MySQL資料庫僅丟擲一個警告。而啟用該選項後，MySQL資料庫不允許插入零日期，插入零日期會丟擲錯誤而非警告。

NO_ZERO_IN_DATE：在嚴格模式下，不允許日期和月份為零。如“2011-00-01”和“2011-01-00”這樣的格式是不允許的。採用日期或月份為零的格式時MySQL都會直接丟擲錯誤而非警告。

總結一下：MySQL對於ONLY_FULL_GROUP_BY語義的判斷規則是，如果group by list中的表示式是basic column，那麼target list中允許出現表示式是group by list中basic column或者alias column的組合結果，如果group by list中的表示式是複雜表示式（非basic column或者alias column），那麼要求target list中的表示式必須能夠嚴格和group by list中的表示式進行匹配，否者這條查詢會被認為不合法。