526互联

mysql报错 1140 - In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'a.user_name'; this is incompatible with sql_mode=only_full_group_by

发布时间 2023-03-27 15:45:45作者: r1-12king

表结构如下：

CREATE TABLE `user` (
    `id` bigint NOT NULL,
    `user_name` varchar(255) DEFAULT NULL,
    `password` varchar(255) DEFAULT NULL,
    `create_time` datetime DEFAULT NULL,
    `status` int DEFAULT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

 

 

查询如下 sql：

SELECT
a.user_name,
a.PASSWORD,
COUNT(*) sumCount
FROM
( SELECT user_name, `password` FROM USER WHERE user_name = 'lisi' ORDER BY create_time DESC LIMIT 1 ) a
INNER JOIN `user` b ON a.user_name = b.user_name
WHERE
b.STATUS = 0

报错：

1140 - In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'a.user_name'; this is incompatible with sql_mode=only_full_group_by, Time: 0.000000s

 

原因：

在MySQL5.7.5后，默认开启了ONLY_FULL_GROUP_BY，所以导致了之前的一些SQL无法正常执行，其实，是我们的SQL不规范造成的，因为group by 之后，返回的一些数据是不确定的，所以才会出现这个错误。

在不使用group by 子句的聚合查询中，Select列表中的第一个表达式包含了非聚合的列**（改为聚会函数形成就可以正常执行了）；当sql_mode为only_full_group_by时，是不能出现这种情况的。
也就是说，mysql的sql_mode是only_full_group_by的时候，在不使用group by 并且select后面出现聚集函数的话，那么所有被select的都应该是聚集函数，否则就会报错

 

root用户下，直接修改mysql.conf

查询mysql安装文件：whereis mysql
编辑/etc/my.cnf文件，加入如下参数，

set sql_mode="NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES";

或直接中终端使用命令：

set sql_mode="NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES";

 

默认情况下，sql_mode的值为

 ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION