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

当将MySQL数据库升级到5.7版本后，可能会出现一些与group by查询相关的问题，这是因为MySQL 5.7的group by语句在某些情况下会产生与之前版本不同的结果。

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

1.启用SQL_MODE

MySQL 5.7具有更严格的SQL_MODE，以提高数据的一致性和准确性。可以通过修改/etc/mysql/mysql.conf.d/mysql.cnf文件（具体路径可能会有所不同）以启用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查询

MySQL 5.7的group by语句默认启用ONLY_FULL_GROUP_BY选项，这意味着在进行group by查询时，查询的列必须要出现在SELECT语句中，否则会产生错误。可以使用以下语句关闭ONLY_FULL_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;

在MySQL 5.6及更早版本中，以上查询是有效的，可以返回正确的结果。但是，在MySQL 5.7中，执行以上查询会产生以下错误：

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

这是因为MySQL 5.7默认启用了ONLY_FULL_GROUP_BY选项，查询语句中的customers.name列未在GROUP BY语句中出现。修复该错误的方法是将customers.name列添加到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;

在MySQL 5.6及更早版本中，以上查询是有效的，可以返回正确的结果（对于没有订单的客户，AVG(orders.total)返回NULL）。但是，在MySQL 5.7中，执行以上查询会产生以下错误：

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函数，以便正确计算客户的订单数。

通过以上步骤和示例，我们可以正确地使用MySQL 5.7进行group by查询。