京公网安备11010502063487号“this is incompatible with sql_mode=only_full_group_by” 是从 MySQL 5.6 升到 5.7 / 8.0 后最常见的报错。网上最常见的“解决办法”是把 ONLY_FULL_GROUP_BY 从 sql_mode 里删掉,这篇实测删掉以后查到的到底是什么,什么情形本来就不会报,以及“每组取最高值那一行”的几种正确写法差多少。
SELECT 里的 name: select dept, name, max(salary) from emp group by dept
-> OperationalError: (1055, "Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'd.emp.name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by")
ORDER BY 里的 name: select dept, max(salary) from emp group by dept order by name
-> OperationalError: (1055, "Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'd.emp.name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by")
HAVING 里引用没出现在 SELECT 的 name: select dept, max(salary) from emp group by dept having name like 'n1%'
-> OperationalError: (1054, "Unknown column 'name' in 'having clause'")
DISTINCT + ORDER BY: select distinct dept from emp order by salary
-> OperationalError: (3065, "Expression #1 of ORDER BY clause is not in SELECT list, references column 'd.emp.salary' which is not in SELECT list; this is incompatible with DISTINCT")SELECT list 和 ORDER BY clause 里的非聚合列都是 1055;HAVING 引用 SELECT 之外的列是 1054;DISTINCT 加 ORDER BY 里 SELECT 之外的列是 3065。
没有 dept 索引: 前三个部门返回 [(0, 'n0', 982624), (1, 'n1', 999414), (2, 'n2', 994696)];返回的 name 恰好就是最高薪那一行的部门数 = 8 / 1000 加了 idx_dept(dept): 前三个部门返回 [(0, 'n0', 982624), (1, 'n1', 999414), (2, 'n2', 994696)];返回的 name 恰好就是最高薪那一行的部门数 = 8 / 1000 换成 idx_ds(dept, salary): 前三个部门返回 [(0, 'n42000', 982624), (1, 'n189001', 999414), (2, 'n179002', 994696)];返回的 name 恰好就是最高薪那一行的部门数 = 0 / 1000 对照:这三个部门真正最高薪的人: ((0, 'n163000', 982624), (1, 'n100001', 999414), (2, 'n75002', 994696))
拿到的是组里任意一行的值,max(salary) 对,name 却不是那一行的。换一个索引,返回的行就变了,这类 bug 不会报错。
GROUP BY 主键 ok select id, b from t1 group by id GROUP BY 非空唯一索引的列 ok select a, b from t2 group by a GROUP BY 可空的唯一索引的列 报 1055 select a, b from t3 group by a 可空唯一索引 + WHERE a IS NOT NULL ok select a, b from t3 where a is not null group by a GROUP BY 普通列 报 1055 select a, b from t1 group by a GROUP BY 表达式 报 1055 select a + 1, b from t1 group by a + 1 JOIN 后 GROUP BY 主键,取另一表的列 ok select dd.id, dd.name, count(*) from dd join ee on ee.dept_id = dd.id group by dd.id GROUP BY ee.dept_id,取等值连接的 dd.name ok select ee.dept_id, dd.name from ee join dd on dd.id = ee.dept_id group by ee.dept_id 同上但是 LEFT JOIN ok select ee.dept_id, dd.name from ee left join dd on dd.id = ee.dept_id group by ee.dept_id 同上但是 dd LEFT JOIN ee,GROUP BY ee.dept_id 报 1055 select ee.dept_id, dd.name from dd left join ee on dd.id = ee.dept_id group by ee.dept_id
GROUP BY 的列能唯一决定其它列就不会报:主键、非空唯一索引的列、等值连接的主键。可空唯一索引、普通列、表达式不行。
ANY_VALUE(name) 返回 1000 行,name 不是最高薪那个人的行数 1000,耗时 147.8 ms 窗口函数 ROW_NUMBER() 返回 1000 行,name 不是最高薪那个人的行数 0,耗时 358.9 ms JOIN 分组子查询 返回 1000 行,name 不是最高薪那个人的行数 0,耗时 5.5 ms 相关子查询 返回 1000 行,name 不是最高薪那个人的行数 0,耗时 5298.3 ms (JOIN 分组子查询若同一部门有并列最高薪,会多返回行;这份数据里并列的情况有: 0 行)
ANY_VALUE 只是让报错消失,窗口函数和先分组再 JOIN 都正确,相关子查询正确但慢得多。20 万行、1000 组的实测差了接近 1000 倍。