报错信息：
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(); 查询版本
解决方法：
去除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模式
文件底部追加:
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
参考链接:https://www.cnblogs.com/start-fxw/p/11882230.html
本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)
