MySQL Unions, Events, Triggers

기록

MySQL Unions, Events, Triggers

als982001 2025. 4. 6. 17:57

1. 작성한 코드

-- movies normalization

-- Normalization Status
CREATE TABLE statuses (
  status_id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  status_name ENUM (
    'Canceled',
    'In Production',
    'Planned',
    'Post Production',
    'Released',
    'Rumored'
	) NOT NULL,
  explanation TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL
);

INSERT 
	INTO statuses (status_name) 
SELECT status FROM movies GROUP BY status;
 
ALTER TABLE movies ADD COLUMN status_id BIGINT UNSIGNED; 
  
ALTER TABLE movies ADD CONSTRAINT fk_status FOREIGN KEY (status_id) REFERENCES statuses (status_id) ON DELETE SET NULL;   
  
UPDATE movies SET status_id = (SELECT status_id FROM statuses WHERE status_name = movies.status);  
  
ALTER TABLE movies DROP COLUMN status; 
  
  
-- Normalizing Directors
CREATE TABLE directors (
  director_id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(120),
	created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL
);

INSERT INTO 
	directors (name)
SELECT 
	director 
FROM movies 
GROUP BY 
	director 
HAVING director <> '';
  
ALTER TABLE movies ADD COLUMN director_id BIGINT UNSIGNED;

ALTER TABLE movies ADD CONSTRAINT fk_director FOREIGN KEY (director_id) REFERENCES directors (director_id) ON DELETE SET NULL;
 
CREATE INDEX idx_director_name ON directors (name);
 
UPDATE 
	movies 
SET 
	director_id = (
    SELECT director_id 
    FROM directors 
    WHERE name = movies.director
);
  
ALTER TABLE movies DROP COLUMN director;
  
-- Normalizing Original Language
CREATE TABLE langs (
	lang_id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(120),
  code CHAR(2),
	created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL
);

INSERT INTO langs (code)
SELECT original_language
FROM movies
GROUP BY original_language;

ALTER TABLE movies ADD COLUMN original_lang_id BIGINT UNSIGNED;

ALTER TABLE movies
ADD CONSTRAINT fk_org_lang FOREIGN KEY (original_lang_id) 
REFERENCES langs (lang_id) ON DELETE SET NULL;

UPDATE movies SET original_lang_id = (SELECT lang_id FROM langs WHERE code = movies.original_language);

ALTER TABLe movies DROP COLUMN original_language;

-- Normalizing Countries
CREATE TABLE countries (
  country_id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  country_code CHAR(2) UNIQUE NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL
);

INSERT INTO countries (country_code)
SELECT country 
FROM movies 
WHERE country NOT LIKE '%,%'
GROUP BY country;

-- JOIN은 기본적으로 테이블을 수평적으로 합침(수평적으로 테이블을 확장)
-- UNION은 기본적으로 테이블을 합쳐주는데, 중복된 값들은 삭제함
-- UNION ALL은 중복된 값을 삭제 안함
SELECT 'a', 1
UNION
SELECT 'b', 2;

INSERT IGNORE INTO countries (country_code)
SELECT 
  SUBSTRING_INDEX(country, ',', 1)
FROM movies 
WHERE country LIKE '__,__'
GROUP BY country
UNION
SELECT 
  SUBSTRING_INDEX(country, ',', -1)
FROM movies 
WHERE country LIKE '__,__'
GROUP BY country;

INSERT IGNORE INTO countries (country_code)
SELECT 
  SUBSTRING_INDEX(country, ',', 1)
FROM movies 
WHERE country LIKE '__,__,__'
GROUP BY country
UNION
SELECT 
  SUBSTRING_INDEX(country, ',', -1)
FROM movies 
WHERE country LIKE '__,__,__'
GROUP BY country
UNION
SELECT 
  SUBSTRING_INDEX(SUBSTRING_INDEX(country, ',', 2), ',', -1)
FROM movies 
WHERE country LIKE '__,__,__'
GROUP BY country;

SELECT SUBSTRING_INDEX('gb,us,it', ',', 1);

CREATE TABLE movies_countries (
  movie_id BIGINT UNSIGNED,
  country_id BIGINT UNSIGNED,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL,
  PRIMARY KEY (movie_id, country_id),
  FOREIGN KEY (movie_id) REFERENCES movies (movie_id) ON DELETE CASCADE,
  FOREIGN KEY (country_id) REFERENCES countries (country_id) ON DELETE CASCADE
);

INSERT INTO movies_countries (movie_id, country_id)
SELECT
  movies.movie_id, 
  countries.country_id
FROM movies
	JOIN countries ON movies.country LIKE CONCAT('%', countries.country_code, '%')
	WHERE movies.country <> '';

ALTER TABLE movies DROP COLUMN country;

-- table 구조 복사
CREATE TABLE archived_movies LIKE movies;

DROP TABLE archived_movies;

-- STARTS: 얼마나 시간이 지나고 시작할지 지정. 없으면 바로 시작함
DELIMITER $$
CREATE EVENT archive_old_movies 
ON SCHEDULE EVERY 2 MINUTE
STARTS CURRENT_TIMESTAMP + INTERVAL 2 MINUTE
-- DO는 오직 statement가 1개일 때만 작동
-- INSERT, DELETE 2개의 분리된 statement를 가지고 있으므로 BEGIN + END 입력
-- delimiter 변경도 필요
DO
BEGIN
	INSERT INTO archived_movies
  SELECT * FROM movies 
  WHERE release_date < YEAR(CURDATE()) - 20;
	DELETE FROM movies release_date < YEAR(CURDATE()) - 20; 
END$$
DELIMITER ;

CREATE EVENT archive_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;

DROP EVENT archive_old_movies;

SHOW events;

-- BEFORE: INSERT, UPDATE, DELETE
-- AFTER: INSERT, UPDATE, DELETE

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

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

CREATE TRIGGER after_movie_insert
AFTER INSERT
ON movies
FOR EACH ROW
INSERT INTO records (changes) VALUES (CONCAT('Insert completed: ', NEW.title));

CREATE TRIGGER before_movie_update
BEFORE UPDATE
ON movies
FOR EACH ROW
INSERT INTO records (changes) VALUES (CONCAT('Will update title: ', OLD.title, ' -> ', NEW.title));

CREATE TRIGGER after_movie_update
AFTER UPDATE
ON movies
FOR EACH ROW
INSERT INTO records (changes) VALUES (CONCAT('Will update title: ', OLD.title, ' -> ', NEW.title));

CREATE TRIGGER before_movie_delete
BEFORE DELETE
ON movies
FOR EACH ROW
INSERT INTO records (changes) VALUES (CONCAT('Will deleted: ', 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;

SHOW TRIGGERS;

DROP TRIGGER after_movie_update;

TRUNCATE TABLE records;

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;

DROP TRIGGER after_movie_update;

2. 배운 개념

2-1. 정규화 (Normalization)

중복을 제거하고 데이터의 무결성을 유지하기 위해 테이블을 쪼개는 작업
정규화의 목적
- 중복 제거
- 데이터 일관성 유지
- 변경 시 관리 용이성 확보

2-1-1. 정규화 예제

2-1-1-1. 상태(status) 정규화

-- 1. 상태 테이블 생성
CREATE TABLE statuses (...);

-- 2. 고유 상태 INSERT
INSERT INTO statuses (status_name) 
SELECT status FROM movies GROUP BY status;

-- 3. 외래키 컬럼 추가 + 매핑
ALTER TABLE movies ADD COLUMN status_id ...
UPDATE movies SET status_id = ...
ALTER TABLE movies DROP COLUMN status;

movies.status -> statuses.status_id로 정규화
중복 상태명 제거, 참조 무결성 확보

2-1-1-2. 감독(director) 정규화

CREATE TABLE directors (...);
INSERT INTO directors (name) SELECT director FROM movies GROUP BY director HAVING director <> '';
ALTER TABLE movies ADD COLUMN director_id ...
UPDATE movies SET director_id = ...
ALTER TABLE movies DROP COLUMN director;

movies.director -> directors.director_id로 분리
문자열 직접 저장 대신 ID 참조로 일관성 유지

2-1-1-3. 국가(country) 정규화 + 다대다 관계 구성

CREATE TABLE countries (...);

-- 단일 국가 추출
INSERT INTO countries (country_code)
SELECT country FROM movies WHERE country NOT LIKE '%,%' GROUP BY country;

-- 다중 국가 처리 (UNION + SUBSTRING_INDEX로 쪼갬)
-- "us,fr" → "us" + "fr", "gb,us,it" → 각각 쪼개서 UNION

-- 중복 방지 위해 INSERT IGNORE
-- 최종적으로 다대다 관계 테이블 생성
CREATE TABLE movies_countries (...);
INSERT INTO movies_countries (...)
SELECT movies.movie_id, countries.country_id
FROM movies JOIN countries ON movies.country LIKE CONCAT('%', countries.country_code, '%');

movies.country는 "US,FR"처럼 여러 국가를 한 컬럼에 포함하고 있음
- M:N 관계이기에 별도의 bridge table이 필요
movies.country -> movies_countries (movie_id, country_id)로 정규화
쿼리 최적화 및 다국적 영화 처리 용이

2-2. UNION / UNION ALL

구분	JOIN	UNION
방향	수평 (컬럼 확장)	수직 (행 확장)
목적	테이블 연결	결과 집합 합치기
조건	ON, USING 등 연결 조건 필요	컬럼 개수 & 타입 일치 필요
중복 제거	해당 없음	UNION: 중복 제거 / UNION ALL: 중복 유지

2-2-1. UNION, SUBSTRING_INDEX 예시

SELECT 'a', 1
UNION
SELECT 'b', 2;

결과: a,1 / b,2 (중복 없으면 그대로, 중복 있으면 제거)

-- "us,fr" → us + fr 나누기 위해 SUBSTRING_INDEX 사용
SELECT SUBSTRING_INDEX(country, ',', 1)
UNION
SELECT SUBSTRING_INDEX(country, ',', -1)

UNION을 통해 국가 코드들을 중복 없이 INSERT

2-3.EVENT

특정 시점에 자동으로 실행되는 작업으로, 미리 정해둔 시간이나 주기마다 실행되는 자동 작업 스케쥴러(쿼리)

2-3-1. 문법 구조

CREATE EVENT event_name
ON SCHEDULE
  EVERY [간격]
  STARTS [시작 시간]
DO
BEGIN
  -- 실행할 여러 쿼리
END;

2-3-2. 오래된 영화 아카이브

CREATE EVENT archive_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;

20년 이상된 영화들을 자동 백업 + 삭제하는 이벤트
STARTS를 설정하지 않으면 즉시 시작됨
여러 쿼리를 쓸 때는 반드시 BEGIN ... END 사용

2-3-3. EVENT 관리 명령어

명령어	설명
SHOW EVENTS;	등록된 이벤트 목록 보기
DROP EVENT event_name;	이벤트 삭제
ALTER EVENT ... ENABLE/DISABLE;	이벤트 켜기/끄기
SET GLOBAL event_schedular = ON;	이벤트 스케쥴러 활성화 (기본은 OFF)

2-4. TRIGGER

테이블에 INSERT, UPDATE, DELETE 같은 변화가 생길 때 자동으로 실행되는 로직

2-4-1. 문법 구조

CREATE TRIGGER trigger_name
[BEFORE | AFTER] [INSERT | UPDATE | DELETE]
ON table_name
FOR EACH ROW
[BEGIN]
  -- 트리거 실행 내용
[END]

2-4-2. BEFORE/AFTER INSERT

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

CREATE TRIGGER after_movie_insert
AFTER INSERT ON movies
FOR EACH ROW
INSERT INTO records (changes) VALUES (CONCAT('Insert completed: ', NEW.title));

2-4-3. AFTER UPDATE

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;

NEW, OLD는 각각 변경 후, 변경 전의 값
여러 컬럼도 비교 가능
BEGIN ... END로 여러 구문 처리 가능

2-4-4. BEFORE/AFTER DELETE

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

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

2-4-5. TRIGGER 관리 명령어

명령어	설명
SHOW TRIGGERS;	트리거 목록 확인
DROP TRIGGER trigger_name;	트리거 삭제
TRUNCATE TABLE records;	트리거 로그 비우기
INSERT INTO ...	트리거 자동 실행되는 대상 조작

2-5. NEW/OLD 사용 규칙

트리거 타입	사용 가능한 키워드
BEFORE/AFTER INSERT	NEW만 가능
BEFORE/AFTER UPDATE	OLD, NEW 둘 다 가능
BEFORE/AFTER DELETE	OLD만 가능

2-6. EVENT, TRIGGER 정리 요약

구분	EVENT	TRIGGER
자동 실행 조건	설정된 시간/주기	테이블 변화(삽입/수정/삭제) 발생 시
주요 용도	백업, 정리, 통계	로그, 유효성 검사, 자동 기록
실행 주체	MySQL 스케쥴러	테이블 변경 트리거
BEGIN/END 필요 여부	여러 쿼리 시 필요	여러 쿼리 시 필요
활용 예시	오래된 데이터 주기적 백업	변경 이력 자동 기록