mysql GROUP BY (only full group by)

 MYSQL GROUP BY(incompatible with sql_mode=only full group by)에 대해 알아보자

 

로컬 및 개발 환경에서 멀쩡히 돌아가던 쿼리가

테스트 서버에 올리고 나니 다음과 같은 Exception을 뱉었다.

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column '칼럼명' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

: group by 절에 포함되지 않은 칼럼(집계되지 않은 칼럼(nonaggregated column))을 select 절에서 뽑아올 경우, 어느 칼럼을 표시해야 할 지 몰라 exception 이 발생하는 현상이다. 

only_full_group_by 란?

mysql 5.7버전부터 sql_mode로 추가된 설정으로

집계되지 않은 칼럼을 select 절에서 뽑아오는 경우 exception을 낼지 exception 을 내지 않고 동작할지 결정하는 옵션이다.

 

간단히 예를 들면.

아래와 같이 사람 별로 영어 점수 총 합계를 조회하는 쿼리가 있다.

SELECT id as '아이디'
       ,  phone_num as '전화번호'
       ,  SUM(eng_point) as '영어점수합계'
FROM  sampletable
GROUP BY id

phone_num (전화번호) 은 유니크한 값으로 다른 레코드와 중복될 수 없는 값이란 걸 개발자는 알겠지만,

DB입장에선 phone_num의 경우 어떤 값을 표시해야 할지 알 수 없다. (이를 집계되지 않은 칼럼 nonaggregated column)이라 칭함)

 

* 동일한 쿼리를 ORACLE 에서 실행시 적합하지 않은 group by 사용으로 exception 이 발생한다.

* mysql 에서 실행시 only_full_group_by 옵션의 활성화/비활성화 상태에 따라 exception이 발생/발생하지 않는다.

 

[해결 방법]

1. 쿼리를 수정한다.

1) 쿼리 자체를 수정. 

1-1) group by 절에 nonaggregated column 추가

SELECT  id as '아이디'
       ,  phone_num as '전화번호'
       ,  SUM(eng_point) as '영어점수합계'
 FROM  sampletable
GROUP BY id, phone_num
 

 

1-2) 서브쿼리 사용(경우에 따라 left outer join 사용)

  SELECT  id as '아이디'
       ,  phone_num as '전화번호'
       ,  ( SELECT SUM(eng_point) 
            FROM  sampletable
            WHERE st.id = id 
            GROUP BY id) as '영어점수합계'            
FROM  sampletable st

 

2) select 절의 집계되지 않은 칼럼에 ANY_VALUE(칼럼) 함수 추가

SELECT  id as '아이디'
       ,  ANY_VALUE(phone_num) as '전화번호'
       ,  SUM(eng_point) as '영어점수합계'
FROM  sampletable
GROUP BY id

 

2. mysql 설정파일을 수정한다. 

2-1) 현재 옵션 상태를 확인한다.

 

sql_mode 조회.

> select @@sql_mode;

 

sql_mode 조회 결과.

> | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,                                                                                        DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |

ONLY_FULL_GROUP_BY 이 포함되어 있다면 해당 설정이 활성화 되어 있음을 의미.

 

2-2) my.cnf 설정파일을 열어 해당 부분을 찾아 제거

2-3) mysql 재기동.

 

※ 머릿속에서 나온 예제이므로 적합하지 않을 수 있으며, 실제 동작하지 않는 쿼리일 수도 있다.

※ 애초에 쿼리를 잘 짜면 이 같은 문제가 나올 수 없다...