MySQL5.7 group by新特性报错1055的解决办法

  • Post category:http

MySQL 5.7 的 GROUP BY 新特性在使用过程中,可能会遇到错误码1055的报错,这是由于新特性对 GROUP BY 的使用做出了一定限制,因此出现了这一问题。下面将详细讲解该问题的解决办法。

问题背景

在 MySQL 5.7 中,对 GROUP BY 进行了一定改进和限制,在使用 GROUP BY 时,必须按照 SQL 标准对 SELECT 中出现的字段进行操作。如果 SELECT 中出现了未在 GROUP BY 中的字段,就会出现报错码1055。

问题原因

MySQL 5.7 引入了 SQL 模式的概念,开启了 ONLY_FULL_GROUP_BY 模式。此模式下,如果 SELECT 中出现了未在 GROUP BY 中的字段,就会出现报错码1055。

解决方案

针对这个问题,有以下两种解决方案:

方案一:改变 SQL 模式

在 MySQL 5.7 中,可以通过修改 SQL 模式来关闭 ONLY_FULL_GROUP_BY 模式。具体操作如下:

1.登陆MySQL客户端,使用以下命令查看当前SQL模式:

SELECT @@sql_mode;

2.找到包含ONLY_FULL_GROUP_BY的模式,将其删除。例如:

SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

3.通过以下命令验证是否已成功关闭ONLY_FULL_GROUP_BY模式:

SELECT @@sql_mode;

方案二:更新GROUP BY语法

在 SQL 标准下,对 GROUP BY 进行操作时,除了 SELECT 列出的字段外,都必须进行 GROUP BY。因此,当出现报错码1055时,可以通过加入缺失的字段名来解决问题。例如,以下 SQL 语句会报错:

SELECT name, COUNT(*) FROM table GROUP BY name;

报错原因是 SELECT 中的 COUNT(*) 未在 GROUP BY 中列出。要解决这个问题,只需将语句修改为:

SELECT name, COUNT(*) FROM table GROUP BY name, column;

注意:column 是被查询的表中出现在 SELECT 中的字段名。

示例说明

下面给出具体的两个示例说明:

示例一

假设有一张商品表格,包含商品名(name)、生产厂商(producer)、价格(price)三个字段,需要按照生产厂商进行聚合统计并输出结果。

SELECT producer, COUNT(*), MIN(price), MAX(price)
FROM goods
GROUP BY producer;

执行以上语句时,如果出现错误:

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

这时,我们可以采用方案一中的方法关闭 ONLY_FULL_GROUP_BY 模式,或者采用方案二中的方法,将未在 GROUP BY 中的 price 字段加入:

SELECT producer, COUNT(*), MIN(price), MAX(price)
FROM goods
GROUP BY producer, price;

示例二

假设员工有两个属性,工号(no)和部门(department),需要对每个部门的员工按照工号进行排序,并分别统计出每个部门的平均工资(salary)和人数,并以此为基础建立统计报表。

SELECT department, AVG(salary), COUNT(*)
FROM employee
GROUP BY department
ORDER BY department, no;

执行以上语句时,如果出现错误:

ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column

这时,我们可以采用方案一中的方法关闭 ONLY_FULL_GROUP_BY 模式,或者采用方案二中的方法,将未在 GROUP BY 中的 no 字段加入:

SELECT department, AVG(salary), COUNT(*)
FROM employee
GROUP BY department, no
ORDER BY department, no;

通过以上方法,我们可以成功将数据展示为带有所需统计信息的报表。