官网: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.As of MySQL 5.7.5, the default SQL mode includes ONLY_FULL_GROUP_BY. (Before 5.7.5, MySQL does not detect functional dependency and ONLY_FULL_GROUP_BY is not enabled by default. For a description of pre-5.7.5 behavior, see the MySQL 5.6 Reference Manual.)
A MySQL extension to standard SQL permits references in the HAVING clause to aliased expressions in the select list. Before MySQL 5.7.5, enabling ONLY_FULL_GROUP_BY disables this extension, thus requiring the HAVING clause to be written using unaliased expressions. As of MySQL 5.7.5, this restriction is lifted so that the HAVING clause can refer to aliases regardless of whether ONLY_FULL_GROUP_BY is enabled.
注:mysql8.0 配置有所变化,具体见:https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html
解决方法
Setting the SQL Mode
The default SQL mode in MySQL 5.7 includes these modes:
ONLY_FULL_GROUP_BY
,STRICT_TRANS_TABLES
,NO_ZERO_IN_DATE
,NO_ZERO_DATE
,ERROR_FOR_DIVISION_BY_ZERO
,NO_AUTO_CREATE_USER
, andNO_ENGINE_SUBSTITUTION
.These modes were added to the default SQL mode in MySQL 5.7: The
ONLY_FULL_GROUP_BY
andSTRICT_TRANS_TABLES
modes were added in MySQL 5.7.5. TheNO_AUTO_CREATE_USER
mode was added in MySQL 5.7.7. TheERROR_FOR_DIVISION_BY_ZERO
,NO_ZERO_DATE
, andNO_ZERO_IN_DATE
modes were added in MySQL 5.7.8. For additional discussion regarding these changes to the default SQL mode value, see SQL Mode Changes in MySQL 5.7.To set the SQL mode at server startup, use the
--sql-mode="
option on the command line, ormodes
"sql-mode="
in an option file such asmodes
"my.cnf
(Unix operating systems) ormy.ini
(Windows).modes
is a list of different modes separated by commas. To clear the SQL mode explicitly, set it to an empty string using--sql-mode=""
on the command line, orsql-mode=""
in an option file.
Setting the SQL Mode
The default SQL mode in MySQL 8.0 includes these modes:
ONLY_FULL_GROUP_BY
,STRICT_TRANS_TABLES
,NO_ZERO_IN_DATE
,NO_ZERO_DATE
,ERROR_FOR_DIVISION_BY_ZERO
, andNO_ENGINE_SUBSTITUTION
.To set the SQL mode at server startup, use the
--sql-mode="
option on the command line, ormodes
"sql-mode="
in an option file such asmodes
"my.cnf
(Unix operating systems) ormy.ini
(Windows).modes
is a list of different modes separated by commas. To clear the SQL mode explicitly, set it to an empty string using--sql-mode=""
on the command line, orsql-mode=""
in an option file.
解决:
执行以下两个命令(注意每个版本sql-model不一样):
以下是mysql5.7的sql-model设置
mysql> 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> 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';
这两个命令,去掉 sql_mode 的 ONLY_FULL_GROUP_BY
直接修改mysql配置文件(我的系统是Ubuntu16.04的,在/etc/mysql/mysql.conf.d/mysqld.cnf 中并没有sql_mode这个配置,所以直接加上就好,如果是其他系统有得修改就不用添加了,注意必须加在mysqld下面)