Huzz's daily blog

MySQL sql_mode ONLY_FULL_GROUP_BY 본문

DB/MySQL

MySQL sql_mode ONLY_FULL_GROUP_BY

허쯔 2018. 9. 23. 20:00

 MySQL에는 sql_mode 라고 해서, 특정 operation 들을 어떻게 처리할 것인지를 설정할 수 있는 값이 있다. 여기서 그 중 ONLY_FULL_GROUP_BY 설정에 대해서 설명한다.


  나는 처음 SQL을 배울 때, SELECT에 포함되는 단일 값을 가진 컬럼(mysql 문서에서는 이를 nonagrregated columns라 하더라. 즉, SUM() 과 같은 aggregated 계산 되지 않은 컬럼들)은 항상 GROUP BY 절에도 포함되어야 하는 것으로 알고 있었는데, 이는 잘못된 지식이었다.


  먼저 다음과 같은 테이블이 있다고 해보자. 테이블명은 test, 컬럼은 a, b, c, d 총 4개이고 a는 primary key, b는 unique & not nullable, c는 unique & nullable, d는 그냥 아무것도 아닌 컬럼이다. ONLY_FULL_GROUP_BY의 설정과 관계없이 다음은 valid한 syntax이다.

SELECT a, b, c, d
FROM test
GROUP BY a;

 b, c, d에 대한 정보가 GROUP BY 절에 등장하지 않지만, a가 이미 primary key므로 a가 결정되면 b, c, d역시 하나의 값으로 결정되기 때문이다. (나는 b, c, d와 관계없이 위와 같은 쿼리는 b, c, d가 GROUP BY 절에 존재하지 않으므로 valid하지 않은 쿼리라 생각했었다. 결과값은 똑같을지라도 실제로 GROUP BY a, b, c, d; 라고 써줘야 한다고 생각했고, 실제로 그렇게 쓰고 있었다.)


  그럼 ONLY_FULL_GROUP_BY 설정에 의해 다르게 operation 되는 경우는 무엇인가? 아래와 같은 쿼리를 보자.

SELECT a, b, c, d
FROM test
GROUP BY d;

 d로 GROUP BY를 하는데, a, b, c가 SELECT 절에 있다. d 컬럼은 unique하지 않으므로 d가 결정된다고 해서 a, b, c 컬럼의 값이 결정되지 않는다. 그러므로 이 쿼리를 보고 ‘뭔가 이상한데?’라는 생각이 들 수 있다. 위 쿼리는 ONLY_FULL_GROUP_BY 설정이 켜져있을 때는 invalid syntax라는 오류가 발생한다. 방금 말한 ‘뭔가 이상한데?’ 라는 생각이 드는 쿼리는 허용하지 않는 것이다. 반대로, 설정이 꺼져있을 때는 해당 쿼리도 동작한다. 그럼 저 결정할 수 없는 a, b, c는 어떻게 동작하느냐? 아무 값이나 선택된다. 사실 위의 쿼리는

SELECT ANY_VALUE(a), ANY_VALUE(b), ANY_VALUE(c), d
FROM test
GROUP BY d;

와 똑같은 쿼리다. 즉, 옵션이 켜져있으면 a가 아니라 b 역시 unique & not nullable이므로 GROUP BY b; 만 써도 valid한 쿼리가 되고, c와 d는 그렇지 않으므로 invalid한 쿼리가 된다. 반대로 옵션이 꺼져있으면 어떤 컬럼이 와도 syntax error가 나타나진 않는다. 의도한 쿼리인지 아닌지는 쿼리를 짜는 사람이 판단해야 할 문제이다.


현재 DB의 sql_mode 값을 확인하는 쿼리는 다음과 같다.

SELECT @@sql_mode;

결과로 나오는 값 중 ONLY_FULL_GROUP_BY가 포함되어 있다면 켜져있는 것이고, 그렇지 않다면 꺼져있는 것이다. 켜져있거나 꺼져있을 때 설정 값을 바꾸고 싶다면 다음과 같이 하면 된다. 위의 쿼리처럼 현재 sql_mode 값을 가져와서 만약 설정을 추가하고 싶다면 , 로 구분해서 ONLY_FULL_GROUP_BY를 추가하고, 제거하고 싶다면 해당 부분을 찾아 지워주면 된다. 그리고 나서

SET SESSION sql_mode = ‘바꾸고 싶은 설정 값’;

으로 쿼리를 쳐주면 현재 세션에서 세팅값을 변경 할 수 있다. 글로벌하게 변경하고 싶다면 SESSION대신 GLOBAL로 쓰면 된다. 물론 직접 세팅값을 쓰지 않고 replace, concat 등의 함수를 이용해서 바꾸는것이 더 안전할 수 있겠다.

'DB > MySQL' 카테고리의 다른 글

MySQL sql_mode ONLY_FULL_GROUP_BY  (0) 2018.09.23
0 Comments
댓글쓰기 폼