[SQL] 스토어드 프로그램

Chloe·2023년 3월 1일

목록 보기

4/4

스토어드 프로그램은 절차적인 처리를 위해 사용되는 문법입니다.
스토어드 프로그램을 작성하기 위해 먼저 문법을 알아야 하겠지만, 이번 장에서는 문법 보다는 자주 사용되는 제어문과 권한, 보안, 예외 핸들링 등 주의해야 할 사항을 위주로 학습합니다.

1. 스토어드 프로그램의 장단점

스토어드 프로그램이 절차적인 처리를 제공하지만, 애플리케이션을 대체할 수 있을지 충분히 고려해 봐야 한다.
스토어드 프로그램을 사용하기로 했다면, 어떤 기능에 사용할 것인지도 고려해야 한다.

1.1) 스토어드 프로그램의 장점

데이터베이스 보안 향상
- 스토어드 프로그램 단위로 실행 권한을 부여할 수 있다.
- 주요 기능을 스토어드 프로그램으로 작성하면 SQL Injection 같은 보안 사고를 피할 수 있다.
기능의 추상화
- 개발 언어나 도구에 관계없이 공통된 기능을 사용할 수 있다.
네트워크 소요 시간 절감
- 네트워크를 경유하는 시간은 횟수에 비례해 증가한다.
- 여러 네트워크 경유해서 호출하는 기능을 스토어드 프로그램으로 구현하면 네트워크 경유 비용을 감소시킬 수 있다.
절차적 기능 구현
- SQL 쿼리는 절차적인 기능을 제공하지 않는다.
- 스토어드 프로그램으로 절차적인 기능을 잘 구현하면, 네트워크 경유 시간과 불필요한 애플리케이션 코드를 줄일 수 있다.
개발 업무 구분
- 애플리케이션 개발 조직과 DBMS 조직이 구분되어 있는 경우, 트랜잭션 단위로 스토어드 프로그램을 만들어 API로 제공할 수 있다.

1.2) 스토어드 프로그램의 단점

낮은 처리 성능
- MySQL 서버는 절차적 코드 처리가 주목적이 아니기 때문에 스토어드 프로그램의 처리 성능이 떨어진다.
- MySQL 서버의 스토어드 프로그램은 실행할 때 마다 스토어드 프로그램의 코드가 파싱되어야 한다.
애플리케이션 코드의 조각화
- 각 기능을 담당하는 프로그램 코드가 애플리케이션과 DB에 분산되면 설치와 배포, 유지보수가 어려워진다.

2. 스토어드 프로그램의 문법

2.1) 예제 테스트 시 주의사항

스택 공간이 부족한 경우 thread_stack 설정을 조정한다. (512KB 정도면 충분하다.)
프로시저나 함수의 이름과 파라미터를 입력하는 괄호 사이에 공백이 있는 경우 프로시저나 함수를 인식하지 못할 수 있다. 이 경우 ignore_space 설정을 추가할 수도 있다.

2.2) 스토어드 프로시저

스토어드 프로시저는 데이터를 주고받아야 하는 여러 쿼리를 하나의 그룹으로 묶어서 독립적으로 실행하기 위해 사용한다.

서로 연관되어 데이터를 주고 받으면서 반복적으로 실행되어야 하는 기능에서 사용된다. (ex. 배치 작업)
스토어드 프로시저는 반드시 독립적으로 호출되어야 한다. SQL 문장에서 스토어드 프로시저를 참조할 수 없다.

스토어드 프로시저 생성 및 삭제

스토어드 프로시저 생성 : CREATE PROCEDURE

CREATE PROCEDURE sp_sum(IN param1 INTEGER, IN param2 INTEGER, OUT param3 INTEGER)
BEGIN
  SET param3 = param1 + param2;
END;;

스토어드 프로시저 생성 시 주의사항
- 스토어드 프로시저는 기본 반환값이 없다. 즉, RETURN 명령을 사용할 수 없다.
- 파라미터의 특성
  - IN 타입: 입력 전용 파라미터
  - OUT 타입: 출력 전용 파라미터
  - IOUT 타입: 입력 및 출력 용도로 모두 사용
DELIMITER: 명령의 끝을 알려주는 종료 문자
- 종료 문자는 어떤 것이든 쓸 수 있지만 스토어드 프로그램에서는 사용되지 않은 문자열을 선택해야 한다.
- 일반적으로 CREATE로 스토어드 프로그램을 생성할 때는 ";;" 또는 "//" 같이 연속된 2개의 문자열을 종료 문자로 설정한다.
스토어드 프로시저 변경 : ALTER PROCEDURE
- 프로시저의 보안이나 작동 방식과 관련된 특성을 변경할 때 사용한다.
- 프로시저의 파라미터나 처리 내용을 변경할 때는 ALTER PROCEDUER로 처리할 수 없다. 이때는 프로시저를 삭제하고 다시 생성해야 한다.
```
--// 프로시저의 보안 옵션을 DEFINER로 변경
ALTER PROCEDURE sp_sum SQL SECURITY DEFINER;
```
스토어드 프로시저 삭제 : DROP PROCEDURE
```
DROP PROCEDURE sp_sum;;
```

스토어드 프로시저 실행

프로그램을 실행하는 방법은 스토어드 프로시저와 스토어드 함수의 큰 차이점이다.
프로시저는 SELECT 쿼리에 사용될 수 없고, 반드시 CALL 명령어로 실행해야 한다.

프로시저의 IN 타입 파라미터는 리터럴 형태로 전달해도 되지만, OUT 또는 INOUT 타입 파라미터는 세션 변수를 사용해야 한다.

--// 리터럴 변수와 세션 변수를 사용해 프로시저 실행
SET @result:=0;
CALL sp_sum(1, 2, @result);
SELECT @result;
+---------+
| @result |
+---------+
|       3 |
+---------+

--// 세션 변수를 사용해 프로시저 실행
SET @param1:=1;
SET @param2:=2;
CALL sp_sum(@param1, @param2, @result);

스토어드 프로시저의 커서 반환

스토어드 프로그램은 명시적으로 커서를 파라미터로 전달받거나 반환할 수 없다.
MySQL에서 커서(Cursor)는 데이터베이스 쿼리 결과를 한 번에 하나씩 처리할 수 있도록 하는 데이터베이스 객체이다.
하지만 스토어드 프로시저 내에서 커서를 오픈하지 않거나 SELECT 쿼리의 결과 셋을 페치하지 않으면 해당 쿼리의 결과 셋은 클라이언트로 바로 전송된다.
```
--// SELECT 쿼리의 결과 셋을 클라이언트로 바로 전송
CREATE PROCEDURE sp_selectEmployees (IN in_empno INTEGER)
BEGIN
  SELECT * FROM employees WHERE emp_no = in_empno;
END;;

CALL sp_selectEmployees(10001);;
```
- SELECT 쿼리의 결과 셋을 반환하는 OUT 변수가 없고, 별도로 화면에 출력하는 처리를 하지 않았지만 쿼리의 결과가 클라이언트로 전송되었다.

스토어드 프로시저에서 쿼리의 결과 셋을 클라이언트로 전송하는 기능은 스토어드 프로시저의 디버깅 용도로 자주 사용한다. (SIGNAL, RESIGNAL, DIAGNOSTICS 명령을 사용하는 방법도 있다.)

-- // 디버깅 용도의 프로시저
CREATE PROCEDURE sp_sum2 (IN param1 INTEGER, IN param2 INTEGER, OUT param3 INTEGER)
BEGIN
  SELECT '> Stored procedure started.' AS debug_message;
  SELECT CONCAT('> param1 : ', param1) AS debug_message;
  SELECT CONCAT('> param2 : ', param2) AS debug_message;

  SET param3 = param1 + param2;
  SELECT '> Stored procedure completed.' AS debug_message;
END;;

CALL sp_sum2(1, 2, @result);;
+-----------------------------+
| debug_message               |
+-----------------------------+
| > Stored procedure started. |
+-----------------------------+
+---------------+
| debug_message |
+---------------+
| > param1 : 1  |
+---------------+
+---------------+
| debug_message |
+---------------+
| > param2 : 2  |
+---------------+
+-------------------------------+
| debug_message                 |
+-------------------------------+
| > Stored procedure completed. |
+-------------------------------+

스토어드 프로시저 딕셔너리

MySQL 8.0 이전 버전까지 스토어드 프로시저는 mysql 데이터베이스의 proc 테이블에 저장된다.

MySQL 8.0 버전부터 스토어드 프로시저는 사용자에게 보이지 않는 시스템의 테이블로 저장된다.

information_schema 데이터베이스의 ROUTINES 뷰를 통해 스토어드 프로시저의 정보를 조회할 수 있다.

--// 스토어드 프로시저 정보 조회 
SELECT routine_schema, routine_name, routine_type
FROM information_schema.ROUTINES
WHERE routine_schema = 'realmysql';
+----------------+----------------------------+--------------+
| ROUTINE_SCHEMA | ROUTINE_NAME               | ROUTINE_TYPE |
+----------------+----------------------------+--------------+
| realmysql      | distanceInSphere           | FUNCTION     |
| realmysql      | getDistanceMBR             | FUNCTION     |
| realmysql      | sf_divide                  | FUNCTION     |
| realmysql      | sp_selectEmployees         | PROCEDURE    |
| realmysql      | sp_sum                     | PROCEDURE    |
| realmysql      | sp_sum2                    | PROCEDURE    |
+----------------+----------------------------+--------------+

--// 스토어드 프로그램의 상세 정보 확인
SELECT routine_schema, routine_name, routine_definition, routine_body
FROM information_schema.ROUTINES
WHERE routine_name = 'sp_sum' \G
*************************** 1. row ***************************
  ROUTINE_SCHEMA: realmysql
    ROUTINE_NAME: sp_sum
ROUTINE_DEFINITION: BEGIN
  SET param3 = param1 + param2;
END
    ROUTINE_BODY: SQL

2.3) 스토어드 함수

스토어드 함수는 하나의 SQL 문장으로 작성이 불가능한 기능을 하나의 SQL 문장으로 구현해야 할 때 사용한다.

다만, 스토어드 함수는 프로시저보다 제약 사항이 많기 때문에 별도로 실행되는 기능이라면 프로시저를 사용하는 것이 좋다.
스토어드 함수는 SQL 문장의 일부로 사용된다는 점이 프로시저 비해 갖는 장점이다.
MySQL 5.7 버전까지는 특정 그룹별로 몇 건씩만 레코드를 조회하는 기능을 단일 SQL로 작성할 수 없었지만, MySQL 8.0 버전부터는 래터럴 조인이나 윈도우 함수를 이용해 구현할 수 있게 되었다.

--// 부서별로 최근 배속된 사원 2명씩 가져오는 기능
-- 에러 발생: [Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_unicode_ci,IMPLICIT) for operation '=']
-- collation을 추가하여 해결: mysql은 collation을 명시하지 않으면 default collation으로 설정한다. (시스템 변수의 character_set_database 값 참고)
-- 스토어드 함수에서 collation을 명시적으로 지정하지 않으면, IMPLICIT으로 생성된다.
DELIMITER //
CREATE FUNCTION sf_getRecentEmp(param CHAR(4) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci)
  RETURNS VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci
DETERMINISTIC
BEGIN
  DECLARE result VARCHAR(100);
  SELECT GROUP_CONCAT(CONCAT_WS(': ', emp_no, CONCAT_WS(' ', last_name, first_name)) SEPARATOR ', ') INTO result
  FROM (
		SELECT d.dept_name, e.emp_no, en.last_name, en.first_name
		FROM departments d
		  INNER JOIN dept_emp e ON e.dept_no = d.dept_no
		  INNER JOIN employee_name en on e.emp_no = en.emp_no
		WHERE d.dept_no = param
		ORDER BY e.from_date DESC
		LIMIT 2
  ) recent_employees;
  RETURN result;
END //
DELIMITER ;

--// 결과 조회
SELECT dept_no, dept_name, sf_getRecentEmp(dept_no) as recent_empolyee
FROM  departments
GROUP BY dept_no;
+---------+--------------------+---------------------------------------------------+
| dept_no | dept_name          | recent_empolyee                                   |
+---------+--------------------+---------------------------------------------------+
| d009    | Customer Service   | 459412: Zschoche Insup, 408784: Sidou Constantijn |
| d005    | Development        | 270784: Docker Piyush, 83118: Chepyzhov Krisda    |
| d002    | Finance            | 445457: Matzat Jeong, 238435: Lalonde Alois       |
| d003    | Human Resources    | 109702: Maraist Jaihie, 71408: Rajala Kristinn    |
| d001    | Marketing          | 483985: Piazza Aleksandar, 248699: Sidou Shimshon |
| d004    | Production         | 413250: Gihr Shigeichiro, 25734: Rosis Kish       |
| d006    | Quality Management | 227384: Kilgour Luise, 209485: Janocha Deborah    |
| d008    | Research           | 282455: Bierbaum Yucai, 499098: Milicic Sachem    |
| d007    | Sales              | 71987: Zirintsis Shahid, 86425: Morris Shigeu     |
+---------+--------------------+---------------------------------------------------+

스토어드 함수 생성 및 삭제

스토어드 함수 생성 : CREATE FUNCTION

--// 스토어드 함수 생성 
-- 에러 발생: [This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)]
-- DETERMINISTIC 으로 명시하여 해결 
CREATE FUNCTION sf_sum(param1 INTEGER, param2 INTEGER)
  RETURNS INTEGER
DETERMINISTIC
BEGIN
  DECLARE param3 INTEGER DEFAULT 0;
  SET param3 = param1 + param2;
  RETURN param3;
END;;

DETERMINISTIC 키워드는 저장 프로시저 또는 함수가 동일한 입력에 대해 항상 동일한 결과를 반환한다는 것을 나타낸다.

스토어드 함수와 프로시저의 차이점
- 함수 정의부에 RETURNS로 반환되는 값의 타입을 명시해야 함
- 함수 본문 마지막에 정의부에 지정된 타입과 동일한 타입의 값을 RETURN 명령으로 반환해야 함
스토어드 함수의 본문에 허용하지 않는 사항
- PREPARE와 EXECUTE 명령을 이용한 프리페어 스테이트먼트
- ROLLBACK/COMMIT을 유발하는 SQL 문장
- 재귀 호출
- 프로시저 호출
- 결과 셋을 반환하는 SQL 문장

스토어드 함수에서 결과 셋을 반환하려고 하면 에러가 발생한다.

--// 스토어드 함수를 디버깅 용도로 사용할 수 없음
-- [0A000][1415] Not allowed to return a result set from a function
CREATE FUNCTION sf_resultset_test()
  RETURNS INTEGER
DETERMINISTIC
BEGIN
  DECLARE param INTEGER DEFAULT 0;
  SELECT 'Start stored function AS debug_message';
  RETURN param;
END;;

스토어드 함수 변경 : ALTER FUNCTION
- 스토어드 함수의 파라미터나 처리 내용을 변경할 때는 ALTER FUNCTION으로 처리할 수 없다. 이때는 프로시저를 삭제하고 다시 생성해야 한다.
```
--// 스토어드 함수의 보안 옵션을 DEFINER로 변경
ALTER FUNCTION sf_sum SQL SECURITY DEFINER;
```
스토어드 함수 삭제 : DROP FUNCTION
```
DROP FUNCTION sf_sum;;
```

스토어드 함수 실행

스토어드 함수는 프로시저와 달리 CALL 명령으로 실행할 수 없고, SELECT 문장을 이용해 실행한다.

--// call 명령으로 실행시 오류 발생
-- ERROR 1305 (42000): PROCEDURE realmysql.sf_sum does not exist
CALL sf_sum(1,2);

SELECT sf_sum(1,2) AS sum;
+------+
| sum  |
+------+
|    3 |
+------+

2.4) 트리거

트리거는 테이블의 레코드가 저장되거나 변경될 때 미리 정의해둔 작업을 자동으로 실행해 주는 스토어드 프로그램이다.
- 즉, 데이터의 변화가 생길 때, 다른 작업을 함께 처리하도록 하는 것이다.
- 칼럼의 유효성 체크, 데이터의 복사, 백업, 계산된 결과를 다른 테이블에 함께 업데이트 하는 등의 작업을 위해 트리거를 사용한다.

트리거는 스토어드 함수나 프로시저보다는 필요성이 떨어지는 편이다.
트리거가 생성되어 있는 테이블에 칼럼을 추가하거나 삭제할 때 실행 시간이 훨씬 더 오래 걸린다.
- 테이블에 칼럼을 추가하는 작업은 임시 테이블에 데이터를 복사하는 작업이 필요한데, 이때 레코드마다 트리거를 실행해야 하기 때문이다.
MySQL 트리거는 테이블에 대해서만 생성할 수 있다.
- MySQL 5.7 이전 버전에서 하나의 이벤트에 대해 트리거를 2개 이상 생성할 수 없다.
- MySQL 5.7 버전부터 하나의 테이블에서 동일 이벤트에 트리거를 2개 이상 생성할 수 있다.
바이너리 로그 기반 복제를 하는 경우, 트리거 된 결과는 로그 포맷(ROW/STATEMENT)에 관계없이 동일한 결과를 만들지만, 트리거의 실행 위치가 다르다는 차이가 있다.

트리거 생성

스토어드 함수 생성 : CREATE TRIGGER

--// employees 테이블의 데이터 삭제 시 salaries 테이블에서도 삭제하는 트리거
CREATE TRIGGER on_delete BEFORE DELETE ON employees
    FOR EACH ROW
BEGIN
    DELETE FROM salaries WHERE emp_no = OLD.emp_no;
end;;

테이블의 변경되기 전 레코드는 OLD 키워드, 변경될 레코드는 NEW 키워드로 지칭한다.

트리거가 실행될 이벤트(INSERT,UPDATE, DELETE)와 시점(BEFORE, AFTER)을 명시할 수 있다.
모든 트리거는 항상 레코드(Row) 단위로 실행된다. (FOR EACH ROW)
테이블에 대해 DROP이나 TRUNCATE가 실행되는 경우에는 트리거 이벤트가 발생하지 않는다.
BEGIN ... END 코드 블록에서 허용하지 않는 사항
- 외래키 관계에 의해 자동으로 변경되는 경우 트리거 호출 X
- 레코드 기반 복제(ROW)복제를 사용하는 경우 레플리카 서버에서 트리거 호출 X
- ROLLBACK/COMMIT을 유발하는 SQL 문장
- RETURN 문장 (트리거를 종료할 때는 LEAVE 명령을 사용)
- information_schema, performance_schema에 존재하는 테이블에 대한 트리거

트리거 실행

트리거는 명시적으로 실행 할 수 없고, 트리거의 이벤트를 실행해서 작동을 확인해야 한다.

트리거 딕셔너리

트리거는 MySQL 8.0 이전 버전까지 데이터베이스 디렉터리의 *.TRG 파일로 기록되었다.

MySQL 8.0 버전부터는 시스템 테이블로 저장되고, information_schema 데이터베이스의 TRIGGERS 뷰를 통해 조회만 할 수 있다.

--// 트리거 뷰 조회
SELECT trigger_schema, trigger_name, event_manipulation, action_timing
FROM information_schema.TRIGGERS
WHERE trigger_schema = 'realmysql';
+----------------+--------------+--------------------+---------------+
| TRIGGER_SCHEMA | TRIGGER_NAME | EVENT_MANIPULATION | ACTION_TIMING |
+----------------+--------------+--------------------+---------------+
| realmysql      | on_delete    | DELETE             | BEFORE        |
+----------------+--------------+--------------------+---------------+

SELECT trigger_schema, trigger_name, event_manipulation, action_timing, action_statement
FROM information_schema.TRIGGERS
WHERE trigger_schema = 'realmysql' \G
*************************** 1. row ***************************
  TRIGGER_SCHEMA: realmysql
    TRIGGER_NAME: on_delete
EVENT_MANIPULATION: DELETE
   ACTION_TIMING: BEFORE
ACTION_STATEMENT: BEGIN
  DELETE FROM salaries WHERE emp_no = OLD.emp_no;
end

2.5) 이벤트

주어진 특정한 시간에 스토어드 프로그램을 실행할 수 있는 스케줄러 기능을 이벤트라고 한다.
이벤트는 스케줄링을 전담하는 스레드가 활성화된 경우에만 실행된다.
이벤트 스레드를 활성화하려면 event_sheduler 시스템 변수의 값을 1 또는 ON으로 설정한다.

MySQL은 전체 이벤트 실행 내역을 보관하지 않으며, 최근에 실행된 정보만 information_schema 데이터베이스의 EVENTS 뷰에서 확인할 수 있다.

--// 이벤트 스레드 설정 확인
SHOW GLOBAL VARIABLES LIKE 'event_scheduler';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| event_scheduler | ON    |
+-----------------+-------+

--// 프로세스 목록 확인
SHOW PROCESSLIST;
+----+-----------------+------------------+-----------+---------+--------+------------------------+------------------+
| Id | User            | Host             | db        | Command | Time   | State                  | Info             |
+----+-----------------+------------------+-----------+---------+--------+------------------------+------------------+
|  5 | event_scheduler | localhost        | NULL      | Daemon  | 727964 | Waiting on empty queue | NULL             |
| 68 | user            | localhost        | realmysql | Sleep   | 430022 |                        | NULL             |
| 69 | user            | localhost        | realmysql | Query   |      0 | starting               | SHOW PROCESSLIST |
| 70 | user            | 172.26.0.1:58546 | realmysql | Sleep   |    406 |                        | NULL             |
| 72 | user            | 172.26.0.1:58550 | realmysql | Sleep   |   1591 |                        | NULL             |
+----+-----------------+------------------+-----------+---------+--------+------------------------+------------------+

이벤트 생성

이벤트는 반복 실행 여부에 따라 일회성 이벤트와 반복성 이벤트로 나눌 수 있다.
- 일회성 이벤트 키워드 : ON SCHEDULE AT
- 반복성 이벤트 키워드 : ON SCHEDULE EVERY n [MINUTE/HOUR/DAY/YEAR/..]

이벤트 처리 내용을 작성하는 DO 절은 하나의 쿼리나 프로시저를 호출하는 명령으로 작성할 수도 있고, BEGIN ... END로 구성되는 복합 절을 사용할 수도 있다.

--// 예제를 위한 테이블 생성
CREATE TABLE daily_rank_log(
    exec_dttm DATETIME,
    exec_msg  VARCHAR(50)
);

--// 일회성 이벤트 생성
CREATE EVENT onetime_job
  ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR
DO
  INSERT INTO daily_rank_log VALUES (NOW(), 'Done');

--// 반복성 이벤트 생성
CREATE EVENT daily_ranking
  ON SCHEDULE EVERY 1 DAY STARTS ' 2023-03-10 : 01:00:00' ENDS '2023-03-12 02:00:00'
DO
  INSERT INTO daily_rank_log VALUES (NOW(), 'Done');

--// DO 절에서 프로시저 호출 
CREATE EVENT daily_ranking_p
  ON SCHEDULE EVERY 1 DAY STARTS ' 2023-03-10 : 02:00:00' ENDS '2023-03-12 03:00:00'
DO
  CALL sp_sum(5, 5)

--// DO 절에서 BEGIN ... END 블록 사용
CREATE EVENT daily_ranking_p
    ON SCHEDULE EVERY 1 DAY STARTS ' 2023-03-10 : 03:00:00' ENDS '2023-03-12 04:00:00'
DO BEGIN
    INSERT INTO daily_rank_log VALUES (NOW(), 'Start');
    -- 랭킹 정보 수집 & 관리
    INSERT INTO daily_rank_log VALUES  (NOW(), 'Done');
END;;

또한, 완전히 종료된 이벤트를 삭제할지, 그대로 유지할지 선택할 수 있다.
- ON COMPLETION : 완전히 종료된 이벤트 자동으로 삭제함
- ON COMPLETION PERSERVE : 이벤트 실행이 완료 후 이벤트를 삭제하지 않음
이벤트를 생성할 때는 ENABLE, DISABLE, DISABLE ON SLAVE 3가지 상태로 생성할 수 있다.
- 기본적으로 복제 소스 서버에서는 ENABLE 상태로 생성된다.
- 레플리카 서버에서는 SLAVESIDE_DISABLED 상태로 생성된다.
- 복제 소스 서버에서 실행된 이벤트의 변경 사항은 레플리카 서버로 복제되기 때문에 레플리카 서버에서 이벤트를 중복해서 실행할 필요는 없다.
- 다만, 레플리카 서버가 승격되는 경우, 이벤트 상태를 ENABLE로 변경해야 한다.
```
--// 이벤트 뷰 확인
SELECT event_schema, event_name, status
FROM information_schema.EVENTS
+--------------+-----------------+---------+
| EVENT_SCHEMA | EVENT_NAME      | STATUS  |
+--------------+-----------------+---------+
| realmysql    | onetime_job     | ENABLED |
| realmysql    | daily_ranking   | ENABLED |
| realmysql    | daily_ranking_p | ENABLED |
+--------------+-----------------+---------+
```

이벤트 실행 및 결과 확인

이벤트는 테스트를 위해 강제로 실행시켜 볼 수는 없다.

--// 스케줄링 시점을 임의로 설정하여 이벤트 실행 테스트
CREATE EVENT daily_ranking_1_min
  ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 MINUTE
  ON COMPLETION PRESERVE
DO BEGIN
  INSERT INTO daily_rank_log VALUES (NOW(), 'Done');
END;;

이벤트 딕셔너리

MuSQL 8.0 이전 버전까지는 생성된 이벤트
MySQL 8.0 버전부터는 시스템 테이블로 저장되고, information_schema 데이터베이스의 EVENTS 뷰를 통해 조회만 할 수 있다.

information_schema 데이터베이스의 EVENTS 뷰는 최근 실행 로그만 가지고 있기 때문에 전체 실행 로그가 필요한 경우에는 별도의 로그 테이블이 필요하다.

--// 이벤트 뷰 조회
SELECT * FROM information_schema.EVENTS \G
*************************** 1. row ***************************
       EVENT_CATALOG: def
        EVENT_SCHEMA: realmysql
          EVENT_NAME: onetime_job
             DEFINER: user@%
           TIME_ZONE: SYSTEM
          EVENT_BODY: SQL
    EVENT_DEFINITION: INSERT INTO daily_rank_log VALUES (NOW(), 'Done')
          EVENT_TYPE: ONE TIME
          EXECUTE_AT: 2023-03-11 00:07:04
      INTERVAL_VALUE: NULL
      INTERVAL_FIELD: NULL
            SQL_MODE: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
              STARTS: NULL
                ENDS: NULL
              STATUS: ENABLED
       ON_COMPLETION: NOT PRESERVE
             CREATED: 2023-03-10 23:07:04
        LAST_ALTERED: 2023-03-10 23:07:04
       LAST_EXECUTED: NULL
       EVENT_COMMENT:
          ORIGINATOR: 1

Chloe

조금씩, 천천히, 꾸준히

이전 포스트