MySQL #4_ 사용자 정의 변수 사용하기

안녕하세요. 문범우입니다.

오늘은 MySQL에서 변수를 이용하는 방법에 대해 알아보도록 하겠습니다.

이해를 돕기 위해 예제를 가지고 진행하는 방식으로 해볼게요.

예제에서는 delimiter에 대한 사용이 나옵니다.

delimiter에 대해서 잘 모르신다면 지난 포스팅을 통해 확인하시길 바랍니다.

---

1. 예제 환경 구성

오늘은 아래와 같은 테이블에서 시작합니다.

테이블을 만들기 위한 코드는 아래와 같습니다.

CREATE TABLE person_1(
_id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(32) NOT NULL,
job VARCHAR(64),
height INT NOT NULL,
english INT NOT NULL
);
 
INSERT INTO person_1 (name,job,height,english)
VALUES ('김철수',NULL,173,84),
('최고야','student',189,99),
('박사장','ceo',162,76),
('이세상',NULL,159,12),
('김영희',NULL,182,35),
('오감자','snack',139,3),
('수박바','student',155,65);

위의 테이블을 가지고 진행을 해보도록 하겠습니다.

오늘의 목표는, 각 사람이 가진 영어점수를 바탕으로 순위를 매기는 것 입니다.

단순히 출력할때 순위가 보이도록 하는 것이 아니라, 새로운 속성을 추가하여 각각에 대한 순위를 저장하도록 하겠습니다.

2. MySQL 사용자 정의 변수

MySQL 에서는 사용자 정의 변수를 이용할 수 있습니다.

또한 당연히, 선언한 변수를 쿼리문에서 사용할 수 있습니다.

만약 var라는 이름의 변수를 선언하고 싶다면 아래와 같이 쿼리를 입력하면 됩니다.

SET @var = 1;

위의 쿼리와 같이 SET @(변수명) 을 통해 변수를 선언합니다.

그리고 변수를 사용할때는 @(변수명) 을 통해서 변수를 사용하면 됩니다.

3. 순위 속성을 추가하고 순위 매기기

그럼 위에서 말한 것과 같이 새로운 '순위' 속성을 추가하고 각각에 대한 영어점수의 순위를 매겨보도록 하겠습니다.

먼저 rank 속성을 추가합니다.

쿼리문은 아래와 같습니다.

ALTER TABLE person_1 add column rank INT;

그리고 이제 지난 포스팅에서 학습한 delimiter와 사용자 정의 변수를 이용해보도록 하겠습니다.

먼저 전체 쿼리문은 아래와 같습니다.

SET @rank = 1;
 
DELIMITER //
CREATE TRIGGER person_update
AFTER UPDATE ON person_1
FOR EACH ROW BEGIN
SET @rank=@rank+1;
END //
DELIMITER ;
 
UPDATE person_1 SET rank=@rank ORDER BY english DESC;

1번 라인을 보시면 rank 라는 변수를 1로 선언과 동시에 초기화 하였습니다.

그리고 이어서 DELIMITER를 사용하여 트리거를 만듭니다.

트리거는 person_1 테이블이 update될 때마다 작동되며 6번 라인을 통해 각 행마다 적용되도록 설정하였습니다.

트리거 내용으로는, rank 변수의 값을 1을 증가 시키는 내용입니다.

이렇게 트리거를 만들고 다시 DELIMITER를 수정합니다.

그리고 11번 라인에서와 같이 person_1테이블의 새로만든 속성, rank 속성에 rank변수 값을 입력합니다.

이때 테이블은 english 점수의 내림차순으로 출력된 상태입니다.

이렇게 쿼리를 입력하고 테이블을 확인하면 아래와 같습니다.

어떻게 해서 이런 결과가 나올까요?

다시 쿼리문을 확인해보면, 트리거는 각 행마다 진행이 됩니다.

즉 테이블이 영어점수의 내림차순으로 출력된 상태라면 제일 첫번째 행은 가장 높은 점수를 가진사람일 것입니다.

그럼 UPDATE 문에 의해서 그 사람의 rank는 1로 입력이 되고 이후 rank의 값은 트리거에 의해 1이 증가합니다.

그리고 이어서 두번째 사람의 rank에는 변수의 값이 1이 증가되었기 때문에 2가 저장되고,

그 다음사람은 3이 저장되는 방식입니다.

---

사용자 정의 변수에 대해서 간략하게 알아보고 예제를 통해 사용해보았습니다.

이후 사용자 정의 변수에 대해서는 사용이 될 때마다 추가적인 설명을 붙이도록 하겠습니다.