MySQL
오류 1055 : ONLY_FULL_GROUP_BY : GROUP BY 절이 아닌 항목 ...

소개

비고

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

GROUP BY 사용 및 오용

 SELECT item.item_id, item.name,     /* not SQL-92 */ 
        COUNT(*) number_of_uses
  FROM item 
  JOIN uses ON item.item_id, uses.item_id
 GROUP BY item.item_id

item 이라는 테이블에 행을 표시하고 uses 라는 테이블에 관련된 행의 수를 표시 uses . 이것은 잘 작동하지만 불행히도 표준 SQL-92는 아닙니다.

왜 안돼? GROUP BY 쿼리의 SELECT 절 (및 ORDER BY 절)에는 다음과 같은 열이 포함되어야합니다.

1. GROUP BY 절에서 언급 한
2. COUNT() , MIN() 등과 같은 집계 함수.

이 예제의 SELECT 절은 item.name 언급 item.name , 그 중 하나를 충족하지 않는 열입니다. MySQL 5.6 및 이전 버전에서는 SQL 모드에 ONLY_FULL_GROUP_BY 가 포함되어 있으면이 쿼리를 거부합니다.

이 예제 쿼리는 이와 같이 GROUP BY 절을 변경하여 SQL-92 표준을 준수하도록 만들 수 있습니다.

SELECT item.item_id, item.name, 
       COUNT(*) number_of_uses
  FROM item 
  JOIN uses ON item.item_id, uses.item_id
 GROUP BY item.item_id, item.name

이후의 SQL-99 표준은 SELECT 문이 DBMS가 그룹 키 열과 그룹 키 열 간의 기능적 종속성을 증명할 수있는 경우 그룹화되지 않은 열을 그룹 키에서 생략 할 수 있도록합니다. item.name 은 item.name 에 기능적으로 종속되어 item.item_id 초기 예제는 유효한 SQL-99입니다. MySQL은 버전 5.7에서 기능 의존성 검사기 를 얻었다. 원래 예제는 ONLY_FULL_GROUP_BY 작동합니다.

예측할 수없는 결과를 반환하기 위해 GROUP BY를 잘못 사용 : Murphy 's Law

SELECT item.item_id, uses.category,   /* nonstandard */ 
       COUNT(*) number_of_uses 
  FROM item 
  JOIN uses ON item.item_id, uses.item_id
 GROUP BY item.item_id

item이라는 테이블에 행을 표시하고 uses라는 테이블에 관련된 행의 수를 표시합니다. 또한 uses.category 라는 열의 값을 보여줍니다.

이 쿼리는 ONLY_FULL_GROUP_BY 플래그가 나타나기 전에 MySQL에서 작동합니다. 그것은 MySQL의 비표준 확장을 GROUP BY 합니다.

그러나 쿼리에는 문제가 있습니다. uses 테이블의 여러 행이 JOIN 절의 ON 조건과 일치하면 MySQL은 해당 행 중 하나에서 category 열을 반환합니다. 어떤 행? 쿼리 작성자와 응용 프로그램 사용자는 미리 알지 못합니다. 공식적으로 말하자면, 예측할 수없는 일입니다 . MySQL은 원하는 모든 가치를 반환 할 수 있습니다.

예측할 수없는 것은 무작위 처럼 하나의 중요한 차이가 있습니다. 때때로 무작위적인 선택이 바뀔 것으로 예상됩니다. 따라서 선택 항목이 임의적 인 경우 디버깅 또는 테스트 중에 선택 항목을 감지 할 수 있습니다. 예측할 수없는 결과는 더욱 심각합니다. MySQL은 쿼리를 사용할 때까지 쿼리를 사용할 때마다 동일한 결과를 반환합니다 . 때로는 다른 결과를 초래하는 MySQL 서버의 새로운 버전입니다. 때때로 그것은 문제를 일으키는 성장하는 테이블입니다. 무엇이 잘못 될 수 있으며 잘못 될 것이며 예상하지 못할 때 일어납니다. 그것은 머피의 법칙 입니다.

MySQL 팀은 개발자가이 실수를 저 지르도록하기 위해 노력해 왔습니다. 5.7 시퀀스의 최신 버전의 MySQL에는 ONLY_FULL_GROUP_BY 라는 sql_mode 플래그가 있습니다. 해당 플래그가 설정되면 MySQL 서버는 1055 오류를 반환하고 이러한 종류의 쿼리 실행을 거부합니다.

GROUP BY를 SELECT *로 오용하고이를 수정하는 방법.

때로 쿼리는 SELECT 절의 * 와 함께 다음과 같이 보입니다.

 SELECT item.*,     /* nonstandard */ 
        COUNT(*) number_of_uses
  FROM item 
  JOIN uses ON item.item_id, uses.item_id
 GROUP BY item.item_id

이러한 쿼리는 ONLY_FULL_GROUP_BY 표준을 준수하도록 리팩토링해야합니다.

이렇게하려면 각 item_id 의 number_of_uses 값을 올바르게 반환하는 GROUP BY 를 사용하는 하위 쿼리가 필요합니다. 이 부속 조회는 쉽고 간단합니다. 왜냐하면 uses 테이블 만 봐야하기 때문입니다.

                              SELECT item_id, COUNT(*) number_of_uses
                                FROM  uses 
                               GROUP BY item_id

그런 다음 해당 하위 쿼리를 item 테이블과 조인 할 수 있습니다.

 SELECT item.*, usecount.number_of_uses
   FROM item
   JOIN (
                              SELECT item_id, COUNT(*) number_of_uses
                                FROM  uses 
                               GROUP BY item_id
        ) usecount ON item.item_id = usecount.item_id

이렇게하면 GROUP BY 절이 간단하고 정확하며 * 지정자를 사용할 수 있습니다.

참고 : 그럼에도 불구하고 현명한 개발자는 어쨌든 * 지정자를 사용하지 않습니다. 일반적으로 쿼리에서 원하는 열을 나열하는 것이 좋습니다.

ANY_VALUE ()

 SELECT item.item_id, ANY_VALUE(uses.tag) tag,   
        COUNT(*) number_of_uses
  FROM item 
  JOIN uses ON item.item_id, uses.item_id
 GROUP BY item.item_id

item 이라는 테이블의 행, 관련 행의 수 및 uses 라는 관련 테이블의 값 중 하나를 표시 uses .

이 ANY_VALUE() 함수 를 이상한 일종의 집계 함수라고 생각할 수 있습니다. count, sum 또는 maximum을 반환하는 대신 MySQL 서버에 문제의 그룹에서 하나의 값을 임의로 선택하도록 지시합니다. 오류 1055를 해결하는 방법입니다.

프로덕션 응용 프로그램의 쿼리에서 ANY_VALUE() 를 사용할 때는주의하십시오.

정말 SURPRISE_ME() 라고해야합니다. GROUP BY 그룹의 일부 행의 값을 반환합니다. 반환하는 행은 불확정합니다. 이는 MySQL 서버에 달려 있다는 것을 의미합니다. 형식적으로 예기치 않은 값을 반환합니다.

서버는 임의의 값을 선택하지 않습니다. 쿼리가 실행될 때까지 쿼리를 실행할 때마다 동일한 값을 반환합니다. 그것은 테이블이 커지거나 줄어들거나, 서버에 RAM이 많거나 적을 때, 서버 버전이 변경 될 때, 또는 화성이 역행하는 경우 (의미가 무엇이든) 또는 전혀 이유없이 변경 될 수 있습니다.

당신은 경고를 받았습니다.