달바라기
MySQL 변수로 rownum 구현 시 서브쿼리의 order by 문제 본문
오늘 좀 황당한 일을 당해 오랜만에 글을 올려 봅니다.
MySQL의 경우 rownum 같은 것을 따로 제공하지 않습니다.
해서 변수를 사용한 편법으로 rownum을 구하죠.
일단 일반적인 경우
SELECT col1, col2
FROM TABLE1
ORDER BY col2
의 rownum을 구현할 경우
SELECT @rownum:=@rownum+1 as num, x.*
FROM (SELECT col1, col2
FROM TABLE1
ORDER BY col2) X,
(SELECT @rownum:=0) r
과 같은 방법을 사용할 것입니다.
헌데..
원하는 결과가
num |
col1 |
col2 |
1 |
2 |
1 |
2 |
1 |
2 |
3 |
4 |
3 |
4 |
3 |
|
인데, 실제 결과는
num | col1 | col2 |
1 | 1 | 2 |
2 | 2 | 1 |
3 | 3 |
|
4 | 4 | 3 |
위와 같이 ORDER BY를 무시하는 결과를 출력하는 경우가 있습니다.
이 경우는 서브쿼리의 order by가 무시 된 경우입니다.
혹시나 해서
SELECT @rownum:=@rownum+1 as num, x.*
FROM (SELECT col1, col2
FROM TABLE1
ORDER BY col2) X,
(SELECT @rownum:=0) r
ORDER BY col2
위에처럼 밖에 다시 order by를 써주면
num | col1 | col2 |
2 | 2 | 1 |
1 | 1 | 2 |
4 | 4 | 3 |
3 | 3 | 4 |
처럼 num이 순서대로 출력이 안 되는 경우도 있을 것입니다.
이 경우는 select 후 order by를 적용하기 때문입니다.
뭐가 문제인지 별별 테스트를 다 해 보고 검색을 해 보았지만,
결론은 MySQL의 서브 쿼리에서의 order by는 경우에 따라 무의미하다는 것입니다.
MySQL은 서브쿼리의 결과 순서를 따로 메모리에 적재해 놓지 않기에 서브쿼리의 order가 메인쿼리에서는 무시됩니다.
혹시나 오라클의 힌트와 같은 기능을 하는 것이 있는지 찾아보았지만..
없었습니다...ㅜㅜ
그러나 구글 신의 도움으로 찾은 한 줄기 빛!!!
https://mariadb.com/kb/en/mariadb/why-is-order-by-in-a-from-subquery-ignored/
서브쿼리에 LIMIT를 정해 주는 것으로 원하는 결과를 얻을 수 있었습니다.
SELECT @rownum:=@rownum+1 as num, x.*
FROM (SELECT col1, col2
FROM TABLE1
ORDER BY col2
LIMIT 18446744073709551615
) X,
(SELECT @rownum:=0) r
뭐... 조회하는 테이블의 row가 18446744073709551615 넘어간다면 숫자를 더 크게 써줘야 하겠지만... 그런 경우가 있을까요? ^^;;
이상.. 오늘의 삽질기를 마칩니다.