[mysql 설정] ONLY_FULL_GROUP_BY

이번 포스트에서는 mysql의 sql_mode에서 ONLY_FULL_GROUP_BY 설정에 대해 알아보자.

 

ONLY_FULL_GROUP_BY

 

이상한 group by 동작

기본으로 제공되는 world 데이터베이스에서 다음의 쿼리를 실행해보자.

select sum(population) from city where countrycode='AFG';

countrycode가 'AFG'인 나라의 population 총 합을 물어보고 있는데 2332100가 나오면 정답이다.  

다시 다음의 쿼리를 살펴보자.

select name, countrycode, sum(population)
from city
where countrycode='AFG';

 

Oracle등에서 쿼리를 공부해보았다면 위의 쿼리는 일반 컬럼인 name과 집계함수 sum(population)이 함께 수행되고 있어서 말이 안된다고 생각할 것이다. 하지만 기본적으로 mysql에서는 말이 된다. ㅎ

물론 결과는 애매하다. 저 인구는 Kabul의 인구 합이 아니라 AFG에 해당하는 나라의 인구수 총합이기 때문이다.

mysql은 기본적으로 단일 컬럼과 group by 함수를 그냥 사용할 수 있는 신기한 기능이 있다. 신기할 뿐 일상 쿼리에서는 말이 안되는 내용으로 오류가 발생하는 것이 적합하다.  실제 Oracle에서는 오류가 발생한다.

 

sql_mode 활용

이 경우 현재 수행되고 있는 sql의 mode를 살펴보자.

select @@sql_mode;

# 출력 내용: 
STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

가장 중요한 내용은 ONLY_FULL_GROUP_BY라는 내용이 없어서 이상한 group by가 동작하는 것이다.

이제 아래의 명령을 이용해서 sql_mode를 변경해주자.

# 전역 레벨 설정
set sql_mode='ONLY_FULL_GROUP_BY,기존내용';
# 현재의 session에만 설정
set session sql_mode='ONLY_FULL_GROUP_BY,기존내용';

기존의 조회 내용 앞에 ONLY_FULL_GROUP_BY,를 추가해주면 된다. 위와 같이 설정 후 앞서 성공했던 이상한 쿼리를 다시한번 실행시켜보면...

Error Code: 1140. In aggregated query without GROUP BY, 
expression #1 of SELECT list contains nonaggregated column 'world.city.Name'; 
this is incompatible with sql_mode=only_full_group_by

이제 only_full_group_by에 의해 오류가 발생되었음을 알려준다.