亲测有效!网上很多帖子有BUG,一直失败,最后终于弄好了。所以必须分享下,以免大家按照错误的帖子浪费时间!
报错信息:
Expression #1 of ORDER BY clause is not in SELECT list, references column ‘xxxx’ 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();
-- 查询sql_mode
select @@global.sql_mode
解决方法:
去除ONLY_FULL_GROUP_BY
方法一:
通过命令关闭
set global sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
方法二:
在Mysql配置文件底部添加如下内容。(配置文件一般在/etc/mysql中寻找)
[mysqld]
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
保存并重启mysql。
如果还有疑问可以下面回复!