1 of ORDER BY clause is not in SELECT list, references column 'xxx' which is not in SELECT list

参考:https://blog.csdn.net/chengxi666/article/details/96431248

 

最近在php中操作数据库时一直出错，使用

$result = mysqli_query($link, $sql) or die(mysqli_error($link));

 

• 1

该段代码获取到异常信息如下：
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

php中调试输出的sql语句为

select distinct orderid,guestname,holder,state from tb_orderinfo where isdeleted=0 order by CreateTime desc

 

• 1

奇怪的是我将该sql语句放到Navicat中执行并不会报错，执行成功了。

通过查阅资料发现在mysql5.7.5及以上版本实现了对功能依赖的检测。默认启用了ONLY_FULL_GROUP_BY SQL模式。在该模式下，

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

解决方法

1. 通过命令关闭ONLY_FULL_GROUP_BY SQL模式

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';
set session sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

 

• 1
• 2

但该方法在重启Mysql服务后会失效，重启服务后会发现ONLY_FULL_GROUP_BY还是存在的。

2. 通过修改mysql的配置文件关闭ONLY_FULL_GROUP_BY SQL模式

[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'

 

• 1
• 2