[MySQL] GROUP BY 와 ONLY_FULL_GROUP_BY

 

SELECT 구문에서 GROUP BY 절을 사용할 때 오류가 나는 경우가 있습니다.

 

GROUP BY와 전체 컬럼 조회 사용 시 오류

 

전체 오류 메시지는 다음과 같습니다.

Error Code: 1055. Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'world.city.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 절에 없으며, GROUP BY 절의 열에 기능적으로 종속되지 않은 열(world.city.ID)을 포함합니다. 이는 sql_mode = only_full_group_by와 호환되지 않습니다.

 

이런 내용입니다.

*을 사용해서 조회하면 GROUP BY와 상관없는 열까지 포함되기 때문에 호환이 되지 않는다고 합니다.

 

GROUP BY와 상관없는 열이 ID열이라고 나오는 이유는 *로 조회했을 때 제일 먼저 나오는 열이라서 그런것 같습니다.

 

*을 사용하지 않고 GROUP BY에 없는 컬럼 아무거나 넣으면 그 컬럼이 오류 메시지에 나옵니다.

GROUP BY에 없는 컬럼 사용 시 오류

 

06:28:36 select Name from city group by CountryCode Error Code: 1055. Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'world.city.Name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by 0.000 sec

 

오류메시지에 표시되는 컬럼이 변경되었습니다.

 

 

 

 

ONLY_FULL_GROUP_BY란?

ONLY_FULL_GROUP_BY는 GROUP BY 절을 잘못 사용하는 것을 방지하는 옵션입니다.

 

 

sql_mode의 ONLY_FULL_GROUP_BY 옵션이 꺼져있으면 다음과 같은 문제가 생길 수 있습니다.

• 쿼리가 잘못되었다는 것을 알 수 없음
• 의미 없는 열이 표시될 수 있음
• 실행된 결과가 원하는 결과가 아닐 수 있음

 

sql_mode 변경 방법

ONLY_FULL_GROUP_BY 옵션을 켜두는 것을 권장하고 있지만, 끄고싶다면 다음과 같이 진행하면 됩니다.

 

sql_mode를 조회하는 쿼리입니다.

select @@sql_mode;

 

sql_mode 조회

여러가지 옵션이 있는데, 그 중에 ONLY_FULL_GROUP_BY가 있는 것을 확인할 수 있습니다.

 

# 현재 연결에만 적용(DB 재접속하면 해제)
set @@sql_mode = replace(@@sql_mode, 'ONLY_FULL_GROUP_BY', '');

# 전역 설정 적용(재접속시 적용)
set global sql_mode = replace(@@sql_mode, 'ONLY_FULL_GROUP_BY', '');

set global sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

 

 

mysql 설정 파일(my.ini)에서 하는 방법도 있긴 한데, 제 컴퓨터에 있는 설정 파일에는 sql_mode가 없어서 방법을 찾게되면 작성하겠습니다.

 

만약 해당 쿼리를 실행할 권한이 없거나, MySQL 설정 파일에 접근할 수 없는 DB(ex, AWS RDS)의 경우에는 쿼리를 실행할 수 있는 권한을 부여하거나, 혹은 설정에 접근하는 다른 방법을 사용해야 합니다.

 

Reference

https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html

https://stackoverflow.com/questions/64824498/why-should-not-disable-only-full-group-by