拓展阅读
MySQL 02 truncate table 与 delete 清空表的区别和坑
MySQL 03 Expression 1 of ORDER BY clause is not in SELECT list,references column
MySQL 04 EMOJI 表情与 UTF8MB4 的故事
MySQL 05 MySQL入门教程（MySQL tutorial book）
MySQL 06 mysql 如何实现类似 oracle 的 merge into
MySQL 08 datetime timestamp 以及如何自动更新，如何实现范围查询
MySQL 09 MySQL-09-SP mysql 存储过程
现象
给数据库升了个级，后台纷纷报错误：
报错信息：
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
版本：
+-----------+
| version() |
+-----------+
| 5.7.24 |
+-----------+
1 row in set (0.00 sec)
模式：
+-------------------------------------------------------------------------------------------------------------------------------------------+
| @@global.sql_mode |
+-------------------------------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
解决方案
去除 ONLY_FULL_GROUP_BY
更改
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服务后会失效，重启服务后会失效。
问题，个人在测试的时候发现修改依然报错。
后来发现 SELECT @@SESSION.sql_mode; 有类似的问题，应该也需要修改：
+-------------------------------------------------------------------------------------------------------------------------------------------+
| @@SESSION.sql_mode |
+-------------------------------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
修改：
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';
修改配置文件
通过修改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
为什么要这么设计
可能是
1、在sql查询语句中不需要group by的字段上使用any_value()函数
这种对于已经开发了不少功能的项目不太合适，毕竟要把原来的sql都给修改一遍
2、DISTINCT和order by都会对数据进行排序操作，所以会产生冲突
在sql语句中使用DISTINCT时不使用order by进行排序，获取结果集后通过php进行数据的排序，同时也提高了mysql的性能。
同时group by，limit和其中的一起搭配使用也会导致错误。
mysql5.7版本中，如果DISTINCT和order by一起使用将会报3065错误，sql语句无法执行。这是由于5.7版本语法比之前版本语法要求更加严格导致的。
3、MySQL Server 默认开启了 sql_mode=only_full_group_by 模式，此模式要求 group by 字段必须出现在查询项中（select），否则就会报出该错误。因为GROUP BY处理变得更加复杂，包括检测功能依赖性。
参考资料
Expression #1 of ORDER BY clause is not in SELECT list,references column ‘xxxxxxx’
https://www.cnblogs.com/ritchy/archive/2019/10/29/11757948.html
