SQL - MySQL, Event & Trigger

MySQL

 

 

✅ Event & Trigger

---

Event

MySQL에서 기본적으로 제공하는 기능

Database가 해야 할 작업들을 스케줄링 할 수 있다.

ex) 특정 조건에 해당되는 row들을 매주 수요일 오후 3시에 삭제한다.

 

PostgreSQL에서도 지원하지만 확장프로그램을 이용해야 한다.

SQLite에서는 사용이 불가능하다.

 

 

Trigger

Database에서 일어나는 일에 반응할 수 있다.

ex) 누군가가 row를 삭제하기 전에 그 삭제에 대한 log를 log 테이블에 만든다.

 

MySQL, PostgreSQL, SQLite 모두 사용 가능

 

데이터 없이 구조만 그대로 가져와 테이블을 생성하는 법

CREATE TABLE archived_movies LIKE movies;

 

 

 

✅ Event

---

• 관리
▫ 이벤트를 보려면: ‎`SHOW EVENTS;`
▫ 이벤트를 삭제하려면: ‎`DROP EVENT 이벤트이름;`
▫ 이벤트 스케줄러가 켜져 있어야 동작합니다: SET GLOBAL event_scheduler = ON;

 

Event 생성

CREATE EVENT archived_old_movies ON schedule EVERY 2 DAY;

DAY 부분에는 MINUTE, WEEK, YEAR, MONTH 등 다양하게 들어갈 수 있다.

CREATE EVENT archived_old_movies ON schedule EVERY 2 MINUTE DO
INSERT INTO
	archived_movies
SELECT
	*
FROM
	movies
WHERE
	release_date < YEAR(CURDATE()) - 20;

20년 전 영화들만 골라서 2분마다 archived_movies TABLE에 넣기

 

Table을 DROP 시키지 않고 내용만 전부 지우는 방법(TRUNCATE)

TRUNCATE TABLE archived_movies;

 

Event 내에서 statement가 2개 이상이면 BEGIN과 END를 사용해야 한다

CREATE EVENT archived_old_movies ON SCHEDULE EVERY 2 MINUTE starts CURRENT_TIMESTAMP + INTERVAL 2 MINUTE DO BEGIN
INSERT INTO
	archived_movies
SELECT
	*
FROM
	movies
WHERE
	release_date < YEAR(CURDATE()) - 20;

DELETE FROM movies
WHERE
	release_date < YEAR(CURDATE()) - 20;

END;

 DO만 사용하는 것은 오직 statement가 1개일 때.

그런데 위 코드는 정상 동작이 되지 않는다.

EVENT 내에서 첫 번째 WHERE 절까지가 하나의 명령어로 보기 때문.

 

 

Delimiter

Database에게 SQL statement가 끝났다고 알려주는 문자(;)

위 코드를 보면 BEGIN, END 블록 내에 delimiter가 2개라서 SQL이 헷갈려한다.

DELIMITER $$
CREATE EVENT archived_old_movies ON SCHEDULE EVERY 2 MINUTE starts CURRENT_TIMESTAMP + INTERVAL 2 MINUTE DO BEGIN
INSERT INTO
	archived_movies
SELECT
	*
FROM
	movies
WHERE
	release_date < YEAR(CURDATE()) - 20;

DELETE FROM movies
WHERE
	release_date < YEAR(CURDATE()) - 20;

END$$
DELIMITER ;

DELIMITER 키워드로 $$를 DELIMITER로 사용한다고 지정하고, END부분에서 사용한 뒤 다시 ;로 DELIMITER를 바꾼다.

DELIMITER로 지정한 delimiter는 EVENT 내부가 아닌 외부에 적용된다.

 

 

Event Scheduling Options

https://chatgpt.com/share/d5844020-c1d8-46d9-a363-879f3421750f

 

 

✅ Trigger

---

Trigger에는 2종류가 있다.

1. Before Trigger

2. After Trigger

 

Trigger 생성하는 법

CREATE TRIGGER before_movie_insert BEFORE
INSERT
	ON movies FOR EACH ROW
INSERT INTO
	records (changes)
VALUES
	(CONCAT('Will insert', NEW.title));

movies table의 모든 row에 대하여 insert가 일어나기 전에 새로 insert되는 movie data의 title을 rocords table의 changes column에 insert한다.

 

NEW, OLD

insert할 때 NEW.title처럼 NEW로 새로 추가 될 데이터를 참조할 수 있고,

update할 때는 OLD.title처럼 OLD를 사용하여 업데이트 되기 전 기존 데이터를 참조할 수 있다.

CREATE TABLE records (record_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, changes TINYTEXT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL);

CREATE TRIGGER before_movie_delete BEFORE
DELETE ON movies FOR EACH ROW
INSERT INTO
	records (changes)
VALUES
	(CONCAT('Will delete: ', OLD.title));

CREATE TRIGGER after_movie_delete AFTER DELETE ON movies FOR EACH ROW
INSERT INTO
	records (changes)
VALUES
	(CONCAT('Bye Bye: ', OLD.title));

INSERT INTO
	movies
SELECT
	*
FROM
	archived_movies
WHERE
	movie_id = 2;

 

 

Trigger를 프로그래밍 언어처럼 사용할 수 있다.

DELIMITER $$
CREATE TRIGGER after_movie_update
AFTER UPDATE
ON movies
FOR EACH ROW
BEGIN
	DECLARE changes TINYTEXT DEFAULT '';
	IF NEW.title <> OLD.title THEN SET changes = CONCAT('Title changed ', OLD.title, '->', NEW.title, '\n');
	END IF;

	IF NEW.budget <> OLD.budget THEN SET changes = CONCAT(changes, 'Budget changed ', OLD.budget, '->', NEW.budget);
	END IF;

	INSERT INTO records (changes) VALUES (changes);
END$$
DELIMITER ;

TRUNCATE TABLE records;
DROP TRIGGER after_movie_update;

DECLARE는 변수 선언 키워드

<>는 != 의 표준 SQL 표현이다.

!= 는 일부 DBMS에서 지원