Mysql升级到5.7后遇到的group_by查询问题解决

为了解决这些问题,可以采用以下步骤：

1.启用SQL_MODE

在文件的[mysqld]部分添加如下行（如果已有其他SQL_MODE,请在末尾添加）：

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服务以使更改生效.

2.使用ONLY_FULL_GROUP_BY选项进行group by查询

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

可以将以上命令添加到连接数据库之前的脚本中,以确保每次连接时都会关闭ONLY_FULL_GROUP_BY选项.

示例1：group by查询中的错误

假设我们有以下两个表：

CREATE TABLE orders (
  id INT PRIMARY KEY,
  customer_id INT,
  total INT
);

CREATE TABLE customers (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  age INT
);

orders表中存储了每个客户的订单信息,customers表中存储了客户的基本信息.现在我们想获取每个客户的名字和订单的平均总价.可以使用以下查询语句：

SELECT 
  customers.name, 
  AVG(orders.total) 
FROM 
  orders 
  INNER JOIN customers 
ON orders.customer_id = customers.id 
GROUP BY 
  customers.id;

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

SELECT 
  customers.name, 
  AVG(orders.total) 
FROM 
  orders 
  INNER JOIN customers 
ON orders.customer_id = customers.id 
GROUP BY 
  customers.id, 
  customers.name;

示例2：使用ONLY_FULL_GROUP_BY选项进行group by查询

现在我们想获取每个客户的名字和订单的平均总价,但是只想返回有订单的客户.可以使用以下查询语句：

SELECT 
  customers.name, 
  AVG(orders.total) 
FROM 
  orders 
  RIGHT JOIN customers 
ON orders.customer_id = customers.id 
GROUP BY 
  customers.id 
HAVING 
  COUNT(orders.total) > 0;

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

这是因为查询语句中使用了RIGHT JOIN,有些客户可能没有订单,AVG(orders.total)会返回NULL.为了修复该错误,可以使用以下查询语句：

SELECT 
  customers.name, 
  AVG(IFNULL(orders.total, 0)) 
FROM 
  orders 
  RIGHT JOIN customers 
ON orders.customer_id = customers.id 
GROUP BY 
  customers.id 
HAVING 
  COUNT(IFNULL(orders.total, 0)) > 0;

以上查询使用了IFNULL函数将NULL值替换为0,从而使AVG函数返回正确的值.同时,在COUNT函数中也使用了IFNULL函数,以便正确计算客户的订单数.