解决MySQL 5.7.9版本sql_mode=only_full_group_by问题

当MySQL的版本为5.7.9及以上时，启动sql_mode为only_full_group_by时，可能会导致部分SQL语句执行异常。本攻略将会介绍如何解决这个问题。

问题描述

在MySQL 5.7.9及以上版本中，启动sql_mode为only_full_group_by时，如果有GROUP BY的SQL语句中包含非GROUP BY中的字段，MySQL会抛出如下异常。

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

解决方法

1. 修改MySQL配置文件

可以直接修改MySQL的配置文件，去掉only_full_group_by模式。

找到MySQL的配置文件my.cnf或者my.ini，这个文件默认在MySQL的安装目录的etc目录下。
找到[mysqld]标签所在的位置，增加如下配置。

[mysqld]
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

重启MySQL服务以生效。

2. 修改SQL语句

可以在SQL语句中进行调整，使其符合only_full_group_by模式中规定的限制条件。

正确的方式是确保GROUP BY子句包含了所有查询选择列表中的非聚合列，并不是所有的非聚合列都需要包含在GROUP BY子句里面。如果我们只想要非聚合列的唯一值，而不在意它们的值，那么可以使用聚合函数。

例如，原本的SQL语句是这样的：

SELECT col1, col2, col3
FROM tbl
WHERE some_col='some_val'
GROUP BY col1

在only_full_group_by模式下，这条SQL语句会失败。我们可以调整SQL语句，使用MAX()函数或者MIN()函数，将GROUP BY子句中缺少的非聚合列从SELECT子句中删除并用聚合函数代替。

SELECT col1, MAX(col2), MAX(col3)
FROM tbl
WHERE some_col='some_val'
GROUP BY col1

如果需要全部保留非聚合列，则可以使用内联子查询或者临时表。

举例：

SELECT t1.*, t2.*
FROM (
    SELECT col1
    FROM tbl
    WHERE some_col='some_val'
    GROUP BY col1
) AS t1
JOIN tbl AS t2 ON t1.col1=t2.col1

示例说明

示例1

假设有这样一张表格t_student，其中包含学生ID、所属省份和所属城市三列数据。

CREATE TABLE t_student (
    id INT PRIMARY KEY,
    province VARCHAR(255) NOT NULL,
    city VARCHAR(255) NOT NULL
);

INSERT INTO t_student VALUES(1, 'Shandong', 'Jinan');
INSERT INTO t_student VALUES(2, 'Shandong', 'Qingdao');
INSERT INTO t_student VALUES(3, 'Jiangsu', 'Nanjing');
INSERT INTO t_student VALUES(4, 'Jiangsu', 'Suzhou');

如果我们想要获取每个省份中的任意一个城市，则可以使用如下的SQL语句：

SELECT id, province, city
FROM t_student
GROUP BY province;

在MySQL 5.7.9及以上的版本中，启用only_full_group_by时，会抛出如下异常：

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

为了解决这个问题，我们可在my.cnf或者my.ini中去掉only_full_group_by模式，或者修改SQL语句。具体来说，我们可以使用MAX()函数或者MIN()函数，将GROUP BY子句中缺少的非聚合列从SELECT子句中删除并用聚合函数代替。

-- 修改后的SQL语句
SELECT province, MAX(city)
FROM t_student
GROUP BY province;

示例2

假设有这样一张表格t_result，其中包含学生ID、考试类型和成绩三列数据。

CREATE TABLE t_result (
    id INT NOT NULL,
    type VARCHAR(255) NOT NULL,
    score FLOAT NOT NULL
);

INSERT INTO t_result VALUES (1, 'Math', 90);
INSERT INTO t_result VALUES (2, 'Math', 80);
INSERT INTO t_result VALUES (3, 'Chinese', 95);
INSERT INTO t_result VALUES (4, 'Chinese', 85);

如果我们想要获取每种考试类型中的最高分，则可以使用如下的SQL语句：

SELECT type, id, MAX(score)
FROM t_result
GROUP BY type;