分享每一天-【mysql5.7】MySQL报错：SOLSTATE[42000]: Syntax error or access violation: 155 Expression #1 ofSELECT list is not in GROUP BY clause

【mysql5.7】MySQL报错：SOLSTATE[42000]: Syntax error or access violation: 155 Expression #1 ofSELECT list is not in GROUP BY clause

Mysql 776 0 创建时间：2023-04-24 19:56:56

mysql5.7

#### 【mysql5.7】SOLSTATE[42000]: Syntax error or access violation: 155 Expression #1 ofSELECT list is not in GROUP BY clause

#### 完整的报错信息
```markdown
SOLSTATE[42000]: Syntax error or access violation: 155 Expression #1 ofSELECT list is not in GROUP BY clause and contains nonaggregated column'box.b.id' which is not functionally dependent on columns in GROUP BYclause; this is incompatible with sql mode=only full group by
```
#### 二、原因分析

###### 1、表描述

1> 表结构

2> sql语句

select qty,businessDate,flag, sum(IF(flag=1, 0 - reverseQty, qty)) as sum from test_baobiao GROUP BY tradeOrderLineBO, businessDate;

3> 返回的报错信息

Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘test.test_baobiao.qty’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

###### 2、分析

从报错信息来看，它告诉我们：select字段里包含了没有被group by 条件唯一确定的字段qty

因为执行sql_make_group语句时会把两行纪录id=1和id=2两行记录合并成一行，但是搜索引擎不知道该返回哪一条，所以报错。

###### 3、原因总结

MySQL 5.7.5后only_full_group_by成为sql_mode的默认选项之一，这可能导致一些sql语句失效。我们只需要把only_full_group_by从sql_mode中移除即可。

#### 三、解决方案

1、直接在mysql-cli层面做设置

1> 先查询出所有的sql_mode

select @@global.sql_mode;

2> 将ONLY_FULL_GROUP_BY从sql_mode中移除：

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'

3> 重启MySQL客户端

如果此种方法无效（博主在navicat上操作有效），采用方法二、直接再MySQL-server侧修改sql_mode的配置

#### 二、在MySQL-server层面修改sql_mode
1>
在配置文件my.cnf中关闭sql_mode=ONLY_FULL_GROUP_BY。 将sql_mode配置放在 [mysqld] 最后，如下：

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

wq保存文件并退出，最后重启MySQL即可。

【mysql5.7】MySQL报错：SOLSTATE[42000]: Syntax error or access violation: 155 Expression #1 ofSELECT list is not in GROUP BY clause

商品推荐

广告推荐

推荐文章