[SQL] 사용자 정의 변수 @variable

사용자 정의 변수(User-Defined Variables)란?

Mysql에서는 사용자 정의 변수(User-Defined Variables)를 만들어서 사용할 수 있습니다.

사용자 정의 변수의 역할은 값을 저장해서(storing a value), 이 저장한 값을 다른 statement에서 불러와서 다시 사용할 수 있도록 해주는 것 입니다.

일반적으로 사용자 정의 변수는 아래와 같은 형태로 선언합니다.

다만 변수를 만들때 몇 가지 참고해야할 사항이 있습니다.

1) 변수명은 대문자-소문자를 구분하지 않기 때문에, @mark나 @MARK나 동일한 변수입니다.

2) 변수명의 최대 길이는 64 characters 입니다.

3) 변수명은 영어, 숫자, .(점), _ , $ 표시를 포함할 수 있습니다.

4) 다만 변수명에 따옴표 ' ', " ", ` ` 형태를 사용하게 되면 다른 문자들도 포함이 가능합니다. 예를 들어서, @ 'var-1', @ "var^2", @ `var#3` 형태들도 가능하게 됩니다.

 

사용자 정의 변수의 특징들

사용자 정의 변수는 또 다른 특징들도 지니는데, 일단 다른 클라이언트가 정의한 변수를 보거나 사용할 수 없습니다. 물론 이러한 부분에 대해서 Performance Schema user_variables_by_thread table을 볼 수 있는 유저의 경우에는 모든 유저가 정의한 변수들을 다 볼 수 있습니다.

또한 사용자 정의 변수는 session specific하기 때문에 해당 클라이언트의 세션에서 생성한 변수는 그 클라이언트가 종료하는 경우 자동을 변수도 사라집니다.

 

간단한 사용예시

일단 아래와 같은 형태로 작성할 수 있습니다. SET을 사용할 때 값에 대한 대입연산자는  =  혹은  :=  두 가지 형태 모두 사용이 가능합니다.

SET @var_name = expression
SET @var_name := expression

또한 변수에 값에 해당하는 것으로 사용할 수 있는 데이터 타입은 정수, 소수, 10진수, 2진수 혹은 2진수가 아닌 문자열, 또는 NULL 값까지 가능합니다. 만약에 값을 선언하지 않으면 NULL 값이 적용되게 됩니다.

 

1. SET을 사용하여 변수에 값을 저장하기

set @var1 = 3 * 10;
set @var2 = b'1000001';
set @var3 = @var1 - 10;

2. SELECT를 사용하여 저장한 값을 확인하기

select @var1, @var2, @var3;

변수 생성 결과

변수 선언을 이용한 RANK 설정

이제 world 데이터베이스에서 city table을 이용하여 도시별 인구수에 따른 ranking을 구해보도록 하겠습니다. 아래는 city 테이블의 일부입니다.

city table의 일부

1) 도시별 랭킹을 구하기 위해서 먼저 @rank 변수를 만들어 줍니다.

set @rank = 0;

2) select 절에서 rank를 점점 증가하도록 한 후, order by ... desc 를 통해서 내림차순 정렬을 해줍니다. 이번 예시에서는 상위 5위의 도시들만 출력하였습니다.

select @rank := @rank + 1 as ranking, countrycode, name, population
from city
order by population desc
limit 5
;

결과 화면

이렇게 하면 손쉽게 사용자 변수를 활용해서 랭킹을 구할 수 있습니다.

참고로 위에서의 select 절에서  @rank := @rank + 1  형태로 작성하였는데, 이를  @rank = @rank + 1  로 바꿔서 작성하면 ranking 열이 초기값인 0으로 출력되게 됩니다. 이러한 일이 발생하는 이유는 SQL에서 대입연산자는 = 과 := 가 있는데, = 는 SET 구문에서는 := 과 동일하게 사용될 수 있지만, select 같은 곳에서 assign을 할 때는 :=를 사용하여 value assignment를 하게됩니다. 따라서 ranking을 출력하는 쿼리문에서도 select 절 안에서 값을 할당하기 위해 := 대입연산자를 사용했던 것입니다.

* 참고로 대입연산자와 관련해서는 다음의 mysql documentaion을 참고하면 좋을 것입니다.

https://dev.mysql.com/doc/refman/8.0/en/assignment-operators.html#operator_assign-value

 

* 참고할만한 사이트

1) MySQL 8.0 Reference Manual: https://dev.mysql.com/doc/refman/8.0/en/user-variables.html

2) geeksforgeeks mysql-user-defined-variables: https://www.geeksforsgeeks.org/mysql-user-defined-variables/#:~:text=Mysql%20also%20supports%20the%20concept,.%2C%20_%2C%20and%20%24.