【MySQL】Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 异常解决

待我称王封你为后i 2022-05-23 06:49 483阅读 0赞

## 遇到问题 ##

最近给公司某系统公众号端开发新功能，其中涉及到的一个功能点就是要分组查询每个强电井最新的一条记录，考虑到代码量和代码的复杂度，我直接使用了 MySQL 中的组内查询，在本地测试运行程序可行，但当部署到测试服务器时出现了异常，拉取测试服务器日志出现以下异常：  
　　![70][]  
产生异常的原因就是所写组内查询引起的：

SELECT a.* FROM tbl_inspection_equipment_record a
    RIGHT JOIN (
        SELECT * FROM tbl_inspection_equipment_record 
            WHERE state = 1
            AND build_id = ?
        ORDER BY end_time DESC
    ) AS temp
    ON a.id = temp.id
    GROUP BY room_id
    ORDER BY end_time DESC

## 问题原因 ##

MySQL 5.7.5及以上功能依赖检测功能。如果启用了 ONLY\_FULL\_GROUP\_BY SQL 模式（默认情况下），MySQL 将拒绝选择列表，HAVING 条件或 ORDER BY 列表的查询引用在 GROUP BY 子句中既未命名的非集合列，也不在功能上依赖于它们。（5.7.5之前，MySQL 没有检测到功能依赖关系，默认情况下不启用 ONLY\_FULL\_GROUP\_BY。有关5.7.5之前的行为的说明，请参见“MySQL 5.6参考手册”。）

## 解决方案 ##

#### 方法一(该方式每次mysql服务重新启动恢复原来的模式) ####

**1** 执行sql语句

select @@global.sql_mode;

![70 1][]  
　　**2** 执行以下sql语句，去除步骤1中 `ONLY_FULL_GROUP_BY`

set @@global.sql_mode =
    'STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION';

#### 方法二 ####

**1** 远程服务器找到 `my.cnf` 文件  
　　![70 2][]  
　　**2** 在该文件的底部加入以下内容：

sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

**3** 重启mysql服务

[70]: /images/20220523/18b6628a5dc14a4a9e17e693de1e2b0c.png
[70 1]: /images/20220523/36a9d17eadf74c52997c264b9656cfa4.png
[70 2]: /images/20220523/88accd55a6564f8e9fd95bc29c5f7a51.png

【MySQL】Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 异常解决

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关 1055 - Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column的解决办法

相关 MySQL Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column异常处理

相关 Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column

相关 MySQL5.7:of SELECT list is not in GROUP BY clause and contains nonaggregated column 'userinfo.

相关 Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column

相关 Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'userinfo.

相关【MySQL】Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 异常解决

相关 MySQL错误SELECT list is not in GROUP BY clause and contains nonaggregated column解决”

相关 Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘sss.mon

相关 Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregate