Mysql 5.7以上版本Group By异常
在运行group by
语句时,出现以下错误:
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'xxx.xxx.room_id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
这是由于sql_mode
中设置了ONLY_FULL_GROUP_BY
。
ONLY_FULL_GROUP_BY:此模式下,GROUP BY 语句只能查询GROUP BY 后出现的字段以及聚合函数。
例如以下sql在这种模式下可以正常执行:
SELECT
room_id AS roomId,
SUM( use_data ) AS useData,
SUM( price ) AS price
FROM
cost_record
GROUP BY
room_id
以下sql在这种模式下无法正常执行,并会报错:
SELECT
room_id AS roomId,
room_name AS roomName,
SUM( use_data ) AS useData,
SUM( price ) AS price
FROM
cost_record
GROUP BY
room_id
room_name 字段没有在 GROUP BY 后,也不是一个聚合函数,所以会报错。
一般情况下,我们都会按照第二种来查询数据,如果要能正常执行,修改一下sql_mode
,去掉ONLY_FULL_GROUP_BY
就可以了:
- 查询sql_mode
select @@sql_mode;
ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
- 更新sql_mode
SET GLOBAL sql_mode='STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'