mySql中Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggre的问题
2023-09-27 14:21:58 时间
报错信息
Expression #2 of SELECT list is not in GROUP BY clause and contains
nonaggregated column ‘a.id’ which is not functionally
dependent on columns in GROUP BY clause; this is incompatible with
sql_mode=only_full_group_by
产生原因
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参考手册”。)
解决方法
select @@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
执行
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’;
成功解决
相关文章
- mysql二级索引
- MySQL基础篇(03):系统和自定义函数总结,触发器使用详解
- 【MySQL高级】MySql中常用工具及Mysql 日志
- 《MySQL DBA修炼之道》——1.4 MySQL权限
- Mysql mysql lost connection to server during query 问题解决方法
- Mysql 报错“Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggre”
- Mysql外键约束
- Mysql运行状态查询命令及调优详解
- MySQL主从延迟如何解决?
- 基于 mysql 异步驱动的非阻塞 Mybatis【待验证】
- 将SHP导入MySQL中
- MySQL Replication主从复制
- MySQL是如何实现事务的——锁机制、MVVC详解
- mysql备忘
- Mysql中以逗号分割的id转换成文字