MYSQL5.7版本sql_mode=only_full_group

本文介绍了解决MySQL中因sql_mode设置为ONLY_FULL_GROUP_BY导致的错误的方法。通过更改全局sql_mode设置并针对现有数据库进行调整，可以有效地解决这一问题。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

方法一：重启后失效

转载于

https://www.cnblogs.com/zhi-leaf/p/5998820.html

具体出错提示：

[Err] 1055 - Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'information_schema.PROFILING.SEQ' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

1、查看sql_mode

select @@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

2、去掉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';

3、上面是改变了全局sql_mode，对于新建的数据库有效。对于已存在的数据库，则需要在对应的数据下执行：

set sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'

MYSQL5.7版本sql_mode=only_full_group_by问题