Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column报错

1.场景

2020-05-09 17:54:23.297  WARN 1 --- [ XNIO-3 task-10] .m.m.a.ExceptionHandlerExceptionResolver : Resolved [org.springframework.jdbc.BadSqlGrammarException: 
### Error querying database.  Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'hzero_platform.hu2.unit_name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
### The error may exist in URL [jar:file:/app.jar!/BOOT-INF/classes!/mapper/HsCodeMapper.xml]
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: select hu.unit_id unitId,                hu.unit_name companyName,                hu2.unit_name departName,                hp.position_name positionName         from hzero_platform.hpfm_unit hu                  left join hzero_platform.hpfm_employee_assign hea on hea.unit_company_id = hu.unit_id                  left join hzero_platform.hpfm_unit hu2 on hu2.unit_id = hea.unit_id                  left join hzero_platform.hpfm_position hp on hp.position_id = hea.position_id                  left join hzero_platform.hpfm_employee_user heu on heu.employee_id = hea.employee_id         where hu.unit_type_code = 'C'           and heu.user_id = ?           group by hu.unit_id
### Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'hzero_platform.hu2.unit_name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
; bad SQL grammar []; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'hzero_platform.hu2.unit_name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by]

2.原因分析

MySQL 5.7.5及以上功能依赖检测功能。如果启用了ONLY_FULL_GROUP_BY
SQL模式（默认情况下），MySQL将拒绝选择列表，HAVING条件或ORDER BY列表的查询引用在GROUP BY子句中既未命名的非集合列，也不在功能上依赖于它们。（5.7.5之前，MySQL没有检测到功能依赖关系，默认情况下不启用ONLY_FULL_GROUP_BY。有关5.7.5之前的行为的说明，请参见“MySQL5.6参考手册”。）

3.解决方案

1.修改docker.cnf

添加下面这句

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

2.重启mysql服务，完美解决