SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column
SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #38 of SELECT list is not in GROUP BY clause and contains nonaggregated column
which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
一般我们搜索到这个问题的答案都是让我们关闭MySQL配置中的sql-mode,但是有没有人想过，这个模式能作为默认值的价值是什么?
我觉得是给我们的查询加了一层规范，当我们的查询存在异议时（存在随机性的结果集），给我们一个反馈。
举例说明：
CREATE TABLE `mytable` (
`id` int(11) NOT NULL,
`a` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`b` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`c` int(11) NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
INSERT INTO `mytable` VALUES (1, 'abc', 'qrs', 1000);
INSERT INTO `mytable` VALUES (2, 'abc', 'tuv', 2000);
INSERT INTO `mytable` VALUES (3, 'def', 'qrs', 4000);
INSERT INTO `mytable` VALUES (4, 'def', 'tuv', 8000);
INSERT INTO `mytable` VALUES (5, 'abc', 'qrs', 16000);
INSERT INTO `mytable` VALUES (6, 'def', 'tuv', 32000);
当我们执行：
SELECT a, SUM(c) FROM mytable; #错误的sql
这时就会报错:
1140 - In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'user_order.mytable.a'; this is incompatible with sql_mode=only_full_group_by
因为sum（b)是一个集合，sum了所有可能出现的记录。这时，当a存在多个时，a的值就存在不确定性。
正确的SQL应该是：
select a,b,sum(c) from mytable where a='abc' and b='qrs';#这样通过where条件，可以限制a和b的取值范围
select a,b,sum(c) from mytable group by a,b;#这样通过分组，来确定a和b的取值范围
延续上面得出的结论，当我们执行：
select a,b,sum(c) as c from mytable order by c;
也会得到报错，因为前面的select结果集具有不确定性，无法对一个不确定的数据集进行排序。
我们在哪些场景下会遇到这个问题呢？
1.join的表需要聚合函数进行统计（比如join b 表，并统计b表中aID出现的次数放到查询结果中）
2.join的表需要排序功能（需要根据join的b表的某个字段进行排序，且当a表对b表是一对多的关系时）
如何解决?
问题的来源是：MySQL会对含有随机性的查找拒绝查询并报错。
问题的解决就是：使查询的字段有意义并且值确定。一个大体的思路是，当遇到这种报错时，检查select的字段是否具有异议性，如果有，是否出现在where中并且以and连接或者进行了group by分组。
常见的解决方法：
1.关闭sql_mode中对only_full_group_by的限制
2.规范自己的查询，把选择权握在自己手中，加上条件去选择合适的值（如果不在意值，可以用ANY_VALUE()聚合函数来标识，也能绕过检测）
3.分开查询，程序中去做聚合排序，或者拆分需要聚合函数的部分，程序中进行拼接。
4.砍需求（判断是否有应用场景）
5.曲线实现，添加筛选按钮，做二次定向查询
更多详情，请参考官方文档MySQL对GROUP BY的处理
网友评论