mysql 数据库报错 Expression #1 of ORDER BY clause is not in SELECT list,references column 'xxxxxxx'

2021年08月10日 439点热度 0人点赞 0条评论

前言

客户环境系统迁移新服务器,客户要求数据库从 mariadb 修改为 mysql ,新装了一个 mysql5.7 迁移旧数据后,启动项目,后台纷纷报错
报错信息:

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(); # 查询版本

select @@global.sql_mode 查询sql_mode

解决方法:

去除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

浮生一程

读书-行路-悦人-识物