@의 의미와 ROWNUM으로 활용하기

MySql에서 사용하는 @의 의미는 무엇일까? 사용자 정의 변수이다.
게시판 글번호 매기는 기능은 화면에서 할 수 있지만 MySQL에서도 할 수 있다.

사용자 정의 변수

접두사 @는 사용자정의함수를 의미한다.
아래처럼 변수를 초기화할 수 있고 초기화시 NULL값이 할당된다.

//변수 abc 선언
SELECT @abc;

MySQL은 NULL을 리턴한다.
사용자 정의 변수는 하나의 커넥션상에서 공유되기때문에 변수를 사용하고 초기화하지않으면 다른 쿼리 결과에 영향을 미칠 수 있다.
따라서 사용자 정의 변수 사용시 변수값을 항상 초기화해줘야한다.

사용자 정의 변수 초기화

사용자 정의 변수에 할당할 수 있는 데이터 타입은 5가지이다.
별도의 타입을 지정하는 것은 아니고 스크립트언어처럼 저장하는 값에 의해 타입이 정해진다.

1. 문자열
2. 정수
3. 십진수
4. 부동 소수점
5. NULL

사용자 정의 변수 초기화 방법에는 3가지가 있다.

1. SET 이용
2. SELECT 이용 + FROM절에서 초기화
3. SELECT 이용 + WHERE절에서 초기화

이때 명령문 2가지를 사용할 수 있는데 두 명령문은 동일하다.

1. SET이용시 @변수명=값 또는 @변수명:=값
2. SELECT이용시 @변수명:=값

// 1. SET이용하여 변수 abc 초기화
SET @abc=77; 
SET @abc:=77;

// set한 변수 사용 예시
SELECT @abc as '순번'
FROM example;

// 2. SELECT이용 + FROM절에서 변수 abc를 77로 초기화 후 10씩 증가
SELECT @abc:=@abc+10
FROM   example t1, (SELECT @abc:=77) t2;

// 3. SELECT 이용 + WHERE절에서 변수 abc를 77로 초기화 후 10씩 증가
SELECT @abc:=@abc+10
FROM   example t1
WHERE  (@abc:=77) = 77;

// 결과
87
97
107
.
.
.

ROWNUM 사용

// FROM절에서 초기화
SELECT @rowNum:=@rowNum+1  as '순번'
FROM   example t1, (SELECT @rowNum:=0) t2;

실제로 내가 업무에서 사용하고 있는 방식은 WHERE절에서 초기화하는 방식이다.

// WHERE절에서 초기화
SELECT @rowNum:=@rowNum+1 as '순번'
FROM   example
WHERE (@rowNum:=0) = 0;

추가로 역순으로 나타내고 싶을 때 사용할 수 있는 방법이 있다.

// 역순 정렬
SELECT A.no as '순번'
FROM (
	   SELECT @rowNum := @rowNum + 1 AS 순번
	   FROM   example t1, (SELECT @rowNum:=0) t2;
     ) A
ORDER BY 순번 DESC

여기서 하나 의문점은 select절 별칭부분에 따옴표를 넣어 as '순번'을 사용하고 order by '순번' desc를 한다면 정렬이 되지 않는 다.
여러 테스트를 해본 결과 DBtool이 아닌 MariaDB문제인 것 같다.
왜 순번은 되고 '순번'은 안될까?
구글링해도 뚜렷한 답이 나오지 않아 issue를 보내려고 했으나 레포에 issue창은 없고 PR창만 있어서 기여는 다음에 언젠가 하는 걸로….

참고

• https://three-pleasure.tistory.com/256
• https://wakestand.tistory.com/556
• https://dev.mysql.com/doc/refman/8.0/en/user-variables.html