SELECT list is not in GROUP BY clause and contains nonaggregated column
来源：互联网 发布：nginx设置别名 编辑：程序博客网 时间：2024/06/07 02:53
今天使用mysql5.7的时候遇到一个问题，在查询语句后面使用GROUP BY的时候提示SELECT list is not in GROUP BY clause and contains nonaggregated column ‘xxx’的错误，这是因为mysql5.7有一个叫sql_mode的环境变量，其中定义了mysql应该支持的sql语法、数据校验等，这里面有一个属性是only_full_group_by ，在mysql以前的版本里是允许一条select语句中target list中输出的表达式是除聚集函数，group by column以外的表达式，这个表达式的值可能在经过group by操作后变成undefined，无法确定(实际上mysql的表现是分组内第一行对应列的值)，而对于语义限制都比较严谨的多家数据库，如SQLServer、Oracle、PostgreSql都不支持select target list中出现语义不明确的列，这样的语句在这些数据库中是会被报错的，所以mysql在后续的版本中出了一个修正语义，就是我们所说的only_full_group_by 语义，所以在出现上面那种错误是，有两种做法：
1.修改sql语句，使其符合only_full_group_by语义
2.在mysql的配置文件(my.cnf)中添加一个配置项sql_mode，将only_full_group_by去掉，例如：
sql_mode=’STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION’
- 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 'userinfo.
- #1055 - Expression of SELECT list is not in GROUP BY clause and contains nonaggregated column this i
- [mysql] Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'lo
- MySQL: Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'sss
- Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test.tdb_
- ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'information_schema
- Error Code: 1055. Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated
- Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'inform
- [Err] 1055 - Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated c
- [Err] 1055 - Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated c
- [Err] 1055 - Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated..
- [Err] 1055 - Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated c
- jdbc4.MySQLSyntaxErrorException:Expression #2 of SELECT list is not in GROUP BY clause and contains
- MySQL报错“Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggre”
- ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregate
- mysql遇见Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggre的问题
- #ASM 翻译系列第十九弹：ASM Internal ASM Continuing Operations Directory
- 浮动小结
- ionic 判断网络连接
- MyBatis 源码分析——生成Statement接口实例
- 520. Detect Capital
- SELECT list is not in GROUP BY clause and contains nonaggregated column
- #ASM 翻译系列第二十弹：ASM Internal ASM file number 7
- servlet概述
- iOS 开发单元格没有数据时显示提示文字
- Android Studio解决unspecified on project app resolves to an APK archive which is not supported
- C++ 引用详解
- bootstrap datetimepicker使用方法
- Javascript中JSON与String相与转换
- 167. Two Sum II
