• 问题
• 解决方法
  • 临时关闭ONLY_FULL_GROUP_BY
  • 永久关闭ONLY_FULL_GROUP_BY
  • 使用聚合函数

问题

• 执行SELECT * FROM person p GROUP BY p.age时报错

错误代码： 1055
Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'xxl_job_test_2.p.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

• 表结构

CREATE TABLE `person` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(32) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO person (`id`, `name`, `age`) VALUES ('1', '张三', '20'); 
INSERT INTO person (`id`, `name`, `age`) VALUES ('2', '李四', '20'); 
INSERT INTO person (`id`, `name`, `age`) VALUES ('3', '王五', '21'); 

• 但是在有些项目中可以执行，原因是MySQL 5.7版本之后默认设置了sql_mode=only_full_group_by，如果SELECT的字段不在GROUP BY中， 并且字段未使用聚合函数SUM、AVG、MAX、MIN等，就会报错

解决方法

临时关闭ONLY_FULL_GROUP_BY

• 执行SELECT @@GLOBAL.sql_mode;，如果结果包含ONLY_FULL_GROUP_BY则说明开启了ONLY_FULL_GROUP_BY
• 执行SET GLOBAL sql_mode='STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';重新设置sql_mode，这种方法只能临时解决，数据库重启失效

永久关闭ONLY_FULL_GROUP_BY

• 修改数据库配置文件my.cnf（windows系统是my.ini），在[mysqld]模块下新增一行sql_mode配置，如下：

[mysqld]
default-time-zone = '+8:00'
default_password_lifetime=0
sql_mode='STRICT_TRANS_TABLESSTRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

• 配置后生效需要重启MySQL数据库
• 重启后执行SELECT @@GLOBAL.sql_mode;验证，结果不包含ONLY_FULL_GROUP_BY说明配置生效

使用聚合函数

• 使用ANY_VALUE函数处理GROUP BY之外的字段，修改后的SQL如下：

SELECT ANY_VALUE(p.id),ANY_VALUE(p.name),p.age FROM person p GROUP BY p.age

• ANY_VALUE是获取分组的第一条数据，也可以使用SUM、AVG、MAX、MIN等聚合函数