在MySQL 5.7后，MySQL默認開啟了SQL_MODE嚴格模式，對數據進行嚴格校驗。如果代碼中含有group by聚合操作，那么select中的列，除了使用聚合函數之外的，如max()、min()等，都必須出現在group by中。 例： ~~~ mysql> select * from goods group by category_id; 1055 - Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'booleanbx.goods.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by ~~~ 查看數據庫 sql_mode： ~~~ 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.03 sec) ~~~ ## 方法一：修改mysql配置文件 去掉查詢結果中的 `ONLY_FULL_GROUP_BY`,修改mysql配置： 1. 臨時性修改 ~~~ mysql> set global sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'; Query OK, 0 rows affected (0.00 sec) ~~~ 2. 永久性修改 修改mysql配置文件 mysql.ini 或者 my.ini（Windows 配置文件是 .ini，Mac/linux 是 .cnf）。添加如下： ~~~ sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION ~~~ ## 方法二：使用 any_value() 或 group_concat() > [any_value()](https://dev.mysql.com/doc/refman/8.0/en/miscellaneous-functions.html#function_any-value)：將分到同一組的數據里第一條數據的指定列值作為返回數據。 （any_value()函數就是MySQL提供的用來抑制ONLY_FULL_GROUP_BY值被拒絕的） ~~~ mysql> SELECT category_id, any_value(id) FROM goods GROUP BY category_id; +-------------+---------------+ | category_id | any_value(id) | +-------------+---------------+ | 1 | 1 | | 6 | 7 | | 7 | 8 | | 11 | 10 | | 16 | 12 | +-------------+---------------+ 5 rows in set (0.05 sec) ~~~ > [group_concat()](https://dev.mysql.com/doc/refman/8.0/en/aggregate-functions.html#function_group-concat)：將分到同一組的數據默認用逗號隔開作為返回數據 ~~~ mysql> SELECT category_id, group_concat(id) FROM goods GROUP BY category_id; +-------------+------------------+ | category_id | group_concat(id) | +-------------+------------------+ | 1 | 1,2,3 | | 6 | 7,17,18 | | 7 | 8,9 | | 11 | 10,11,13,14 | | 16 | 12,15,16,19 | +-------------+------------------+ 5 rows in set (0.06 sec) ~~~