mysql执行sql遇到'SELECT list is not in GROUP BY clause and contains nonaggregated column...'的错误
线上有个数据库迁移之后，执行如下sql时报错：
SELECT id,dt, count(*) from data_summary_ele_ka_day where (dt IN ('2023-05-25','2023-05-26','2023-05-27','2023-05-28','2023-05-29','2023-05-30','2023-05-31')) GROUP BY dt
完整报错信息如下：error querying database. Cause: java.sql.SQLSyntaxErrorException: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'market_sem.data_summary_ele_ka_day.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
上述报错的含义是：select查询的列不在GROUP BY子句中，并且包含非聚合列market_sem.data_summary_ele_ka_day。id'不依赖于GROUP BY子句中的列;这与sql_mode=only_full_group_by不兼容。
那么此处的sql_mode=only_full_group_by的是什么含义？
什么是sql_mode?
mysql服务器可以在不同的sql_mode下运行，sql_mode影响mysql支持的sql语法和它所执行的数据验证检查。
The MySQL server can operate in different SQL modes, and can apply these modes differently for different clients, depending on the value of the sql_mode system variable. Modes affect the SQL syntax MySQL supports and the data validation checks it performs.
常见的sql_mode有：STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION等
only_full_group_by是其中的一种sql_mode。
only_full_group_by：Reject queries for which the select list, HAVING condition, or ORDER BY list refer to nonaggregated columns that are neither named in the GROUP BY clause nor are functionally dependent on (uniquely determined by) GROUP BY columns.
也即select,having,order by后面出现的列，必须在group by后面出现。
从MySQL 5.7.5开始，默认的SQL模式包括ONLY_FULL_GROUP_BY。
注：5.7版本默认的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, and NO_ENGINE_SUBSTITUTION.
那么为了让上面报错的sql正常运行，只需要将sql_mode中的ONLY_FULL_GROUP_BY模式去掉即可。
要在运行时更改SQL模式，使用set语句设置全局或会话sql_mode系统变量:
SET GLOBAL sql_mode = 'modes';
SET SESSION sql_mode = 'modes';
设置GLOBAL变量需要SUPER特权，并影响从那时起连接到服务器的所有客户端的操作。设置SESSION变量只影响当前客户端。每个客户端都可以随时更改其会话sql_mode值。
如何查看sql_mode的值？
SELECT @@GLOBAL.sql_mode;
SELECT @@SESSION.sql_mode;
参考文档：
https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sql-mode-setting
点个“赞 or 在看” 你最好看！
👇👇👇 谢谢各位老板啦！！！
