TigerCow.Door


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

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

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

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

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


1. 예제 환경 구성


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



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


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
CREATE TABLE person_1(
_id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(32NOT 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);
cs


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

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

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


2. MySQL 사용자 정의 변수


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

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

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


1
SET @var = 1;
cs


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

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


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


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

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


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


1
ALTER TABLE person_1 add column rank INT;
cs


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

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


1
2
3
4
5
6
7
8
9
10
11
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;
cs


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이 저장되는 방식입니다.




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

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

블로그 이미지

Tigercow.Door

Back-end / Python / Database / AI / Algorithm / DeepLearning / etc

댓글을 달아 주세요

안녕하세요.

이번 포스팅에서는 MySQL의 명령어가 아닌 개념적인 내용과 해당 개념의 사용법을 알아보려합니다.

오늘 소개해드릴 MySQL의 개념은 Trigger(트리거)와 Delimiter(델리미터) 입니다.

예제가 함께 포함되어 있으니 따라하면서 하시면 이해하시는데 보다 수월하길 것 입니다.


  1. TRIGGER(트리거)

MySQL을 공부하다 보면 트리거(trigger)라는 단어를 볼 수 있습니다.

트리거가 무엇일까요?

위키백과에서의 정의를 보면 이와 같습니다.

 

데이터베이스 트리거(Database Trigger)는 테이블에 대한 이벤트에 반응해 자동으로 실행되는 작업을 의미한다. 트리거는 데이터 조작 언어(DML)의 데이터 상태의 관리를 자동화하는 데 사용된다. 트리거를 사용하여 데이터 작업 제한, 작업 기록, 변경 작업 감사 등을 할 수 있다. (출처: 위키백과)

 

말 그대로 트리거란, 테이블에 대한 이벤트가 발생했을 때 자동으로 실행되는 작업을 말합니다.

이때 이벤트가 발생하는 경우는 크게 DML 실행, DDL 실행, 데이터베이스 동작 실행(logon, logoff, startup, shutdown, servererror ) 로 나눌 수 있습니다.

 

트리거는 문장 트리거, 행 트리거, 2종류로 나누어집니다.

행 트리거는 각 행에 대해서 트리거가 발생하는 것이고 문장 트리거는 전체 트랜젝션 작업에 대해 1번 발생되는 트리거 입니다.

다시 말해, 문장 트리거는 트리거가 설정된 테이블에 트리거 이벤트가 발생했을 때 많은 행에 대해 변경작업이 발생하더라도 단 한 번만 트리거가 발생하는 것입니다.

행 트리거는 조건을 만족하는 여러 개의 행에 대해 트리거를 반복적으로 여러 번 수행하는 방법입니다.

 

또한 트리거는 다음과 같은 속성을 갖습니다.

BEFORE or AFTER : 트리거가 실행되는 시기를 지정합니다.

INSTEAD OF : 트리거를 원래 문장 대신 수행합니다.

WHEN : 트리거를 시작하는 조건식을 지정합니다.

 

MySQL에서는 버전 5.0.2에서 표준 트리거가 지원되었습니다.

INSERT, UPDATE, DELETE 동작에 대한 트리거가 지원되는데 이때 주의하실 점은 MySQL은 각 테이블에 각 형태의 단 하나의 트리거만 허용합니다.

, INSERT BEFORE 하나, INSERT AFTER 하나, … 이런 식으로 허용합니다.

 

그렇다면 트리거는 어떻게 생성할까요?

두 개의 테이블을 이용하여 트리거를 확인해보겠습니다.

먼저 각각의 테이블은 아래와 같습니다.

 


그리고 생성한 트리거의 코드는 아래와 같습니다.


1
2
3
4
5
6
7
8
9
10
DELIMITER $$
CREATE TRIGGER insertTrigger
AFTER INSERT ON test
FOR EACH ROW
BEGIN
UPDATE adultNum SET adult =(SELECT count(_id) FROM test WHERE adult = 'Yes'WHERE table_name='test';
UPDATE adultNum SET not_adult =(SELECT count(_id) FROM test WHERE (adult = 'No') AND (age > 5)) WHERE table_name='test';
UPDATE adultNum SET baby =(SELECT count(_id) FROM test WHERE (adult = 'NO') AND (age <= 5)) WHERE table_name='test';
END $$
DELIMITER ;
cs

 


이와 같은 트리거를 작성하고

 

INSERT INTO test (name,age,adult) VALUES (‘트리거’,1,’No’);

 

쿼리를 날린 후 다시 테이블을 확인하면 아래와 같습니다.

 

 

adultNum 테이블의 변화가 보이시나요?

트리거를 작성하고 test 테이블에 INSERT를 했을 뿐인데 adultNum 테이블의 값이 변화하였습니다.

트리거는 이처럼 특정 table에 이벤트가 발생했을 때 작동하는 코드를 말합니다.

그럼 다시 트리거 코드를 통해 자세히 살펴보겠습니다.


1
2
3
4
5
6
7
8
9
10
DELIMITER $$
CREATE TRIGGER insertTrigger
AFTER INSERT ON test
FOR EACH ROW
BEGIN
UPDATE adultNum SET adult =(SELECT count(_id) FROM test WHERE adult = 'Yes'WHERE table_name='test';
UPDATE adultNum SET not_adult =(SELECT count(_id) FROM test WHERE (adult = 'No') AND (age > 5)) WHERE table_name='test';
UPDATE adultNum SET baby =(SELECT count(_id) FROM test WHERE (adult = 'NO') AND (age <= 5)) WHERE table_name='test';
END $$
DELIMITER ;
cs

 


1: DELIMITER는 뒤에서 다시 이야기 하겠습니다.

2: CREATE TRIGGER (트리거이름)

   (트리거이름)이라는 이름을 가진 트리거를 만듭니다.

3: AFTER INSERT ON (테이블이름)

   어떤 테이블에 언제 영향을 미칠지를 정합니다.

저는 test라는 테이블에 INSERT가 이루어진 AFTER(다음)에 트리거가 실행되도록 하였습니다.

4: FOR EACH ROW

   해당 구문을 통해 행 트리거임을 말합니다.

5: BEGIN

   트리거 구문의 시작을 말합니다.

6~8: 트리거의 내용입니다.

9: Trigger 생성을 끝냅니다.

10: DELIMITER를 다시 수정합니다. (뒤에서 자세히 설명드리겠습니다.)

 

트리거의 코드에 대해서 어느정도 이해가 가시나요?

 

트리거는 많은 테이블을 관리할 때 상당히 유용하게 쓰일 수 있습니다.

그만큼 활용방안도 많지만, 주의할 점도 많습니다.

몇 가지 주의사항으로는

A에 대한(A테이블의 변화에 대한)특정 트리거에서 A라는 테이블의 값에 대한 INSERT / UPDATE / DELETE 쿼리가 존재한다면 A라는 테이블에 INSERT / UPDATE / DELETE 의 쿼리입력은 불가합니다.

이때 만약 A라는 테이블에 대해 INSERT / UPDATE / DELETE 쿼리 수행이 필요한데 트리거를 걸고 싶다면, B라는 테이블을 해당 트리거에서 A라는 테이블에 대한 변화에 따라 A테이블의 모든 값을 복사하여 B라는 테이블에 입력하도록 하는 쿼리를 추가하면 될 것 입니다.

 

또한 트리거 동작은 함수 호출과 같이 동작하기 때문에 MySQL 함수와 같은 제약을 받으며 트리거는 트랜잭션 제어문(COMMIT, ROLLBACK, SAVEPOINT )을 사용할 수 없습니다.

 

추가적으로, 생성된 트리거를 확인하기 위한 쿼리는,

SHOW TRIGGERS;

이며 특정 트리거를 제거할 때는

DROP TRIGGERS (트리거 이름);

이라는 쿼리를 입력하면 됩니다.

 

이처럼 트리거에 대한 내용은 더 많으니 직접 쿼리를 통해 실행해보고 오류가 발생시에 해당 오류에 대한 답을 스스로 찾아보는 것이 중요합니다.

 


 

  2. DELIMITER(델리미터)

위의 트리거 내용에서 DELIMITER 라는 쿼리가 등장하였습니다.

해당 쿼리는 아래와 같이 쓰입니다.

DELIMITER $$ 또는 DELIMITER ; DELIMITER (아무거나!)

DELIMITER가 무엇일까?

DELIMITER에 대해 간단히 말하면, 구문의 끝을 설정해주는 녀석이라고 생각하면 됩니다.

보통 procedure, trigger를 신규로 생성하거나 수행될 때 delimiter를 사용합니다.

이때 trigger(트리거)라는 것은 위에서 말한바와 같이 특정 동작이 이루어질 때 자동으로 실행되는 함수 같은 친구를 말합니다.

DELIMITER를 확인하기 위해 위에서 쓰인 코드를 다시 확인해보겠습니다.

 

1
2
3
4
5
6
7
8
9
10
DELIMITER $$
CREATE TRIGGER insertTrigger
AFTER INSERT ON test
FOR EACH ROW
BEGIN
UPDATE adultNum SET adult =(SELECT count(_id) FROM test WHERE adult = 'Yes'WHERE table_name='test';
UPDATE adultNum SET not_adult =(SELECT count(_id) FROM test WHERE (adult = 'No') AND (age > 5)) WHERE table_name='test';
UPDATE adultNum SET baby =(SELECT count(_id) FROM test WHERE (adult = 'NO') AND (age <= 5)) WHERE table_name='test';
END $$
DELIMITER ;
cs

 

 

1번 라인을 보시면 DELIMITER 가 사용되었습니다.

1번 라인에서 DELIMITER $$ 으로 쿼리를 입력하였으므로 이제 procedure trigger의 종료시점은 $$로 설정되었습니다. 따라서 트리거의 내용인 BEGIN~END 까지 쿼리내에 ; 를 입력하여도 쿼리가 끝나지 않습니다. 그리고 마지막에서 $$ 를 입력함으로써 쿼리가 끝남을 알려줍니다.

그럼 다시 해야 될 일은 무엇일까요?

, 바로 10번 라인의 DELIMITER ; 입니다.

우리는 트리거를 작성하기 편하도록 컴퓨터에게 잠시동안 쿼리의 끝은 $$ !’ 라고 알려준 것입니다.

이제 트리거 작성이 끝났으니, 트리거가 실행될 때 입력한 쿼리가 잘 작동할 수 있도록 다시 컴퓨터에게 쿼리의 끝은 ; !’ 라고 말해줘야겠죠?

DELIMITER ; 를 하지 않는다면 우리가 해당 트리거를 실행시켜도 트리거 내부에 있는 쿼리는 입력만 될 뿐, 끝나지 않아서 제대로 실행되지 않을 것 입니다.

DELIMITER 뒤의 $$ 는 자신이 원하는 문자로 수정이 가능하오니 자신이 편한 문자로 사용하시면 됩니다.


이렇게 해서 이번에는 트리거(trigger)와 델리미터(delimiter)에 대해서 알아보았습니다.

내용에 대한 피드백이나 문의사항은 언제든지 댓글 및 이메일(doorbw@outlook.com)으로 말씀해주세요 :)


블로그 이미지

Tigercow.Door

Back-end / Python / Database / AI / Algorithm / DeepLearning / etc

댓글을 달아 주세요