Expression #1 of ORDER BY clause is not in SELECT list, references column 'ekbX1.t0.name' which is not in SELECT list; this is incompatible with DISTINCT
List in not of is with 39
2023-09-11 14:14:08 时间
报错信息:
Expression #1 of ORDER BY clause is not in SELECT list, references column 'ekbX1.t0.name' which is not in SELECT list; this is incompatible with DISTINCT
问题原因:
mysql5.7.5及以上版本将sql_mode的ONLY_FULL_GROUP_BY模式默认设置为打开状态,会导致一些错误:
1、我们使用GROUP BY查询时,出现在SELECT字段后面的只能是GROUP BY后面的分组字段,或使用聚合函数包裹着的字段,否则会报错如下信息:
Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'database.table.column' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by 2、当使用ORDER BY查询时,不能使用SELECT DISTINCT去重查询。否则会报错如下信息:
Expression #1 of ORDER BY clause is not in SELECT list, references column 'database.table.column' which is not in SELECT list; this is incompatible with DISTINCT
查询验证:
select version(); 查询版本
![](https://img2018.cnblogs.com/blog/747821/201911/747821-20191118150522311-2133944098.png)
select @@global.sql_mode 查询sql_mode
![](https://img2018.cnblogs.com/blog/747821/201911/747821-20191118150513099-2118303644.png)
解决方法:
去除ONLY_FULL_GROUP_BY
1、通过命令关闭: 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服务后会失效,重启服务后会失效
2、通过修改mysql的配置文件关闭ONLY_FULL_GROUP_BY SQL模式
sudo vim /etc/mysql/conf.d/mysql.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
sudo service mysql restart
相关文章
- Performance comparison for loops of List in java
- java8 lambda 求list最大值、最小值、平均值、求和、中位数、属性排序(空指针异常,空值排前、排后)、去重
- [Algorithm] Linked List Data Structure in JavaScript
- 【RF库Collections测试】Remove From List
- python两个 list 交集,并集,差集的方法+两个tuple比较操作+两个set的交集,并集,差集操作+两个dict的比较操作
- {sharepoint} Setting List Item Permissions Programatically in sharepoint
- mysql问题解决SELECT list is not in GROUP BY clause and contains nonaggregated column
- java mongodb 基础系列---查询,排序,limit,$in,$or,输出为list,创建索引,$ne 非操作
- [Algorithm] 1290. Convert Binary Number in a Linked List to Integer
- [Python] List & Object spread in Python
- Column '表名.某列名' is invalid in the select list because it is not contained in either an aggregate f
- sap.m.list render initialization process
- How where used list in SAP GUI is implemented
- SAP Spartacus B2B List里的listData$设计原理
- Atitit r2017 r4 doc list on home ntpc.docx 驱动器 D 中的卷是 p2soft 卷的序列号是 9AD0-D3C8 D:ati extr2017
- 微信小程序报错errMsg: “request:fail url not in domain list“
- List & Label Enterprise Reporting 28.1 Crack
- 成功解决pml.Simple derived_col_names[int(obj[‘split_feature‘])], IndexError: list index out of range
- mybatis-plus中的in的使用,是传Array?还是传List?别再纠结了
- Sqlachemy的警告SAWarning: The IN-predicate on "sns_object.BIZ_ID" was invoked with an empty sequence. This results in a contradiction, which nonetheless can be expensive to evaluate.
- List 泛型 集合中 Find 的用法
- [LeetCode] Delete Node in a Linked List
- Mybatis多表查询,报错:Column 'id' in field list is ambiguous
- Java Stream 处理分组后取每组最大&Stream流之list转map、分组取每组第一条&Java 8 Collectors:reducing 示例(List分组取最值)