MySQL报错SELECT list is not in GROUP BY clause and contains nonaggre的问题

林末 等级 821 0 0

报错现象

执行SQL报错如下:

SELECT student.s_no,student.s_name,SUM(result.mark) FROM student,result WHERE student.s_no=result.s_no GROUP BY student.s_no
> 1055 - Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'db_ketest.student.s_name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
> 时间: 0.081s

原因

MySQL5.7.5及以上版本有依赖检测功能。如果启用了ONLY_FULL_GROUP_BY SQL模式(默认开启),MySQL将拒绝选择列表,HAVING条件或ORDER BY列表的查询引用在GROUP BY子句中既未命名的非集合列,也不在功能上依赖于它们。(5.7.5之前,MySQL没有检测到功能依赖关系,默认情况下不启用ONLY_FULL_GROUP_BY。)

解决方法

方法一 使用命令行或者数据库客户端执行SQL 1.SQL语句,select @@global.sql_mode查询

mysql> select @@global.sql_mode;
+-------------------------------------------------------------------------------------------------------------------------------------------+
| @@global.sql_mode                                                                                                                         |
+-------------------------------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.06 sec)

2.去掉ONLY_FULL_GROUP_BY,重新设置值

mysql> 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';

方法二 vi修改MySQL配置文件my.cnf

[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

保存配置文件,重启MySQL服务:service mysql restart End 林末 https://www.helloworld.net/linmo

收藏
评论区

相关推荐

MySQL报错SELECT list is not in GROUP BY clause and contains nonaggre的问题
报错现象 执行SQL报错如下: bash SELECT student.s_no,student.s_name,SUM(result.mark) FROM student,result WHERE student.s_noresult.s_no GROUP BY student.s_no 1055 Expression 2 of SELECT lis
MySQL 8.0 创建 user 及允许远程连接
进入 mysql 命令行模式mysql h127.0.0.1 uroot p 查看当前 mysql 版本select version(); 查看当前 mysql 用户列表信息select host, user, authentication_string, plugin from user; 执行完上面的命令后会显示一个表格 查看表格
COUNT 和 IFNULL函数
用COUNT函数: mysql> SELECT count(one) FROM tb\_test; +------------+ | count(http://www.amjmh.com/v/BIBRGZ\_558768/) | +------------+ | 3 | +------------+ 1 row in set my
MYSQL函数group_concat的使用
今天对一批数据要迁移转换,查了下,有group\_concat这个函数简单实现字段的列转行设置,过程记录如下 **一.测试数据准备** mysql> use test; Database changed mysql> select * from t_kenyon; +------+ | id |
MySQL数据库出现Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated colum...
**原因:** **MySQL 5.7.5及以上功能依赖检测功能。如果启用了ONLY\_FULL\_GROUP\_BY SQL模式(默认情况下),MySQL将拒绝选择列表,HAVING条件或ORDER BY列表的查询引用在GROUP BY子句中既未命名的非集合列,也不在功能上依赖于它们。(5.7.5之前,MySQL没有检测到功能依赖关系,默认情况下不启用O
mysql [Err] 1055
\[Err\] 1055 - Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'information\_schema.PROFILING.SEQ' which is not functionally dependent
mysql 分页查询语句数据库查询
mysql分页查询是我们经常见到的问题,那么应该如何实现呢?下面就教您一个实现[mysql](https://www.oschina.net/action/GoToLink?url=http%3A%2F%2Fdatabase.51cto.com%2Fart%2F201011%2F232949.htm)分页查询的好方法,供您参考学习。 mysql中利用sel
mysql 命令集
sql\_mode定义了mysql应该支持的sql语法,数据校验等 select @@sql_mode; 属性 说明 ONLY\_FULL\_GROUP\_BY 对于GROUP BY聚合操作,如果在SELECT中的列,没有在GROUP BY中出现,那么将认为这个SQL是不合法的,因为列不在GROUP BY从句中 STRICT\_
mysql 数据库group by 按排序取值
mysql 数据库group by 按排序取值  mysql 5.7 以后,group by 想要按照排序的顺序来取值,在子结果集中要有union,group by 等操作,不然按照id默认排序来取值 示例: 数据库: score表 id  user\_id  num 1  100         95 2  100         96 3
mysql5.7之only_full_group_by
mysql进入5.7版本之后,默认开启sql\_mode的only\_full\_group\_by模式。 影响在于:   sql语句中如果有group by部分,那么select部分不能出现group by框定的字段以外的字段(聚合函数除外) 举个例子:   select count(\*),ORG\_NAME,ORG\_CODE from PUB
mysql查询出现In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregate...
出现问题: Error querying database.  Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: In aggregated query without GROUP BY,expression #2 of SELECT list contains nonagg
mysql用户以及用户授权
1.1创建mysql用户 mysql> create user test identified by 'test'; 1.2为用户授权后面不加identified。 grant select on test.\* to test@localhost; 1.3查看MYSQL数据库中所有用户 select distinct concat('User:
mysql错误:[Err] 1055
今天迁移django数据库的时候,跑程序的时候出现这样的错误: [Err] 1055 - Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'information_schema. PROFILING.SE
List的Select 和Select().tolist()
List<Person> delp = new List<Person> { new Person{ Id=1,Name="小明1",Age=11,Sign=0 }, new Person{ Id=2,Name="小明2",Age=12 ,
TiDB数据库 使用syncer工具同步实时数据
mysql> select campaign_id ,count(id) from creative_output group by campaign_id; 602843 rows in set (4 min 44.23 sec) mysql> select is_cr_own ,count(id) from creative_o