[JPA] 네이티브 쿼리에서 MySQL 변수(@변수 :=) 사용 시 오류

JPA 네이티브 쿼리에서 MySQL 사용자 변수로 행 번호를 매기다가 만난 오류와 해결 방법을 정리한다.

작성 당시 환경은 다음과 같다. 최신 버전에서 달라진 부분은 글 마지막에 따로 정리했다.

항목	버전
Spring Boot	2.7.x
Hibernate	5.6.x
MySQL	5.7

상황

목록에 순번을 붙이기 위해 MySQL 사용자 변수를 사용했다. MySQL 5.7에는 ROW_NUMBER() 함수가 없어서, 변수 값을 1씩 늘리는 방식으로 행 번호를 만드는 경우가 많다.

SELECT @rownum := @rownum + 1 AS rowNum, m.name
  FROM member m, (SELECT @rownum := 0) r
 ORDER BY m.created_at DESC;

(SELECT @rownum := 0) r — 쿼리 시작 시 변수를 0으로 초기화한다.
@rownum := @rownum + 1 — 행을 하나 읽을 때마다 1을 더해 그 값을 반환한다.
:= — MySQL의 변수 할당 연산자다. SELECT 절에서는 =가 비교 연산자로 해석되므로 할당에는 :=를 써야 한다.

DB 클라이언트에서는 잘 실행되는 쿼리를 그대로 @Query에 넣었다.

@Query(value = "SELECT @rownum := @rownum + 1 AS rowNum, m.name AS name"
             + "  FROM member m, (SELECT @rownum := 0) r"
             + " ORDER BY m.created_at DESC",
       nativeQuery = true)
List<MemberRow> findAllWithRowNum();

실행하면 다음 오류가 발생한다.

org.hibernate.QueryException: Space is not allowed after parameter prefix ':'

원인: Hibernate는 `:`를 파라미터 시작으로 본다

JPA 쿼리에서 :memberId처럼 **콜론(:)으로 시작하는 단어는 이름 있는 파라미터(named parameter)**다. 네이티브 쿼리도 예외가 아니어서, Hibernate는 SQL을 DB에 보내기 전에 문자열을 훑으며 :를 찾아 파라미터로 바꾼다.

WHERE m.id = :memberId      →  WHERE m.id = ?     (파라미터로 치환)
@rownum := @rownum + 1      →  ':' 뒤가 공백 → 파라미터 이름이 없음 → 오류

@rownum := ...의 :도 파라미터의 시작으로 읽는데, 바로 뒤에 파라미터 이름 대신 공백이 오니 “파라미터 접두사(:) 뒤에 공백은 올 수 없다”는 오류가 난 것이다. Hibernate는 이 :가 MySQL 할당 연산자의 일부라는 것을 알지 못한다.

해결: 역슬래시로 이스케이프

Hibernate는 \:를 파라미터가 아닌 일반 콜론으로 처리한다. 다만 Java 문자열 안에서 역슬래시는 그 자체가 이스케이프 문자라서, 역슬래시 하나를 넣으려면 \\로 두 번 써야 한다.

@Query(value = "SELECT @rownum \\:= @rownum + 1 AS rowNum, m.name AS name"
             + "  FROM member m, (SELECT @rownum \\:= 0) r"
             + " ORDER BY m.created_at DESC",
       nativeQuery = true)
List<MemberRow> findAllWithRowNum();

문자열이 거치는 단계별로 보면 다음과 같다.

단계	값
Java 소스 코드	`"@rownum \\:= @rownum + 1"`
Java 문자열 (실제 값)	`@rownum \:= @rownum + 1`
Hibernate가 DB로 보내는 SQL	`@rownum := @rownum + 1`

처음 정리할 때는 “Hibernate 버전에 따라 역슬래시를 하나만 써도 된다”고 들었다고 적었는데, 이는 단계를 섞어서 말한 것으로 보인다. Hibernate가 필요로 하는 역슬래시는 실제 문자열 기준으로 하나다. Java 소스에서는 그 하나를 표현하기 위해 \\로 써야 하고, "\:"처럼 하나만 쓰면 잘못된 이스케이프 문자로 컴파일 에러가 난다. 역슬래시를 그대로 쓸 수 있는 곳(예: XML 매핑 파일의 쿼리)에서는 하나만 쓴다.

Java 15 이상의 텍스트 블록(""")에서도 역슬래시는 이스케이프 문자로 처리되므로 똑같이 \\:로 써야 한다.

결과는 인터페이스 프로젝션으로 받는다.

public interface MemberRow {
    Long getRowNum();
    String getName();
}

대안: MySQL 8의 ROW_NUMBER()

MySQL 8.0부터는 윈도 함수 ROW_NUMBER()를 쓸 수 있다. 변수를 쓰지 않으니 : 문제도 생기지 않는다.

@Query(value = "SELECT ROW_NUMBER() OVER (ORDER BY m.created_at DESC) AS rowNum,"
             + "       m.name AS name"
             + "  FROM member m",
       nativeQuery = true)
List<MemberRow> findAllWithRowNum();

MySQL 8.0에서는 SELECT 절처럼 식 안에서 사용자 변수에 값을 할당하는 방식이 deprecated되었다. 이후 버전에서 제거될 예정이므로, MySQL 8 이상이라면 ROW_NUMBER()를 쓰는 것이 좋다.

순번이 화면 표시용이라면 DB에서 계산하지 않고, 조회한 목록의 인덱스로 애플리케이션에서 붙이는 것도 방법이다. 페이징을 쓴다면 (페이지 번호 × 페이지 크기) + 인덱스 + 1로 계산할 수 있다.

같은 원인으로 생기는 다른 경우

네이티브 쿼리에 :가 들어가는 다른 경우도 같은 문제가 생길 수 있다.

경우	예	해결
시간 문자열	`WHERE time > '10:30'`	따옴표 안의 문자열은 Hibernate가 파라미터로 보지 않아 보통 문제없다.
PostgreSQL 타입 캐스트	`created_at::date`	`CAST(created_at AS date)`로 바꾸면 콜론이 사라져 안전하다.
JSON 문자열 등을 쿼리에 직접 작성	`'{"a":1}'`	따옴표 안이면 문제없다. 값은 되도록 파라미터로 넘긴다.

Spring Boot 3 / Hibernate 6에서 달라진 점

네이티브 쿼리에서 :를 파라미터로 해석하는 규칙은 같고, \\: 이스케이프도 계속 지원된다. 다만 Hibernate 6에서 쿼리 파서가 크게 바뀌었으므로, 버전을 올린 뒤에는 해당 쿼리가 정상 동작하는지 한 번 확인하는 것이 좋다.
Spring Boot 3를 쓰는 환경이라면 MySQL 8 이상인 경우가 대부분이므로, 변수 대신 ROW_NUMBER()를 쓰는 것을 권장한다.