1. 報錯場景

在sql語句中使用 group by 報錯

SELECT  FROM `user` GROUP BY `gender`;

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

2. 問題分析

一、原理層面

這個錯誤發(fā)生在mysql 5.7 版本及以上版本，5.7版本默認(rèn)的sql_mode配置中包含 ONLY_FULL_GROUP_BY，這個配置嚴(yán)格執(zhí)行了"SQL92標(biāo)準(zhǔn)"

很多人從5.6升級到5.7時，為了語法兼容，大部分都會選擇調(diào)整sql_mode，使其保持跟5.6一致，為了盡量兼容程序

二、sql層面: sql執(zhí)行時，出現(xiàn)該原因：

簡單來說就是：輸出的結(jié)果是叫 target list，就是select后面跟著的字段，還有一個地方 group by column，就是group by后面跟著的字段。

由于開啟了ONLY_FULL_GROUP_BY的設(shè)置，所以如果一個字段沒有在target list 和 group by 字段中同時出現(xiàn)，或者是聚合函數(shù)的值的話，那么這條sql查詢是被mysql認(rèn)為非法的，會報錯誤。

舉例

// 報錯
SELECT  FROM `user` GROUP BY `gender`; 

// 不報錯,因為gender字段在target list和group by字段中同時出現(xiàn)了
SELECT gender FROM `user` GROUP BY `gender`;

3. 查看sql模式

查看 sql_mode 的語句

show global variables like 'sql_mode';

通過命令行可以看到 sql_mode 的值有一堆，將最前面的 ONLY_FULL_GROUP_BY 去掉即可解決這個問題

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

4. 解決方案（去除 ONLY_FULL_GROUP_BY）

方案一: 臨時性解決 (執(zhí)行SQL語句)

此方式重啟MySQl服務(wù)后失效

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";

方案二: 永久性解決（修改配置文件 my.cnf）

在 [mysqld] 下面添加以下內(nèi)容

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 服務(wù)

mysql.server restart