짬타이거 화이팅!

--프로시저 실행일 다음달 1일부터 말일까지 테이블 생성

--템플릿 테이블 필요(테이블명_exam)

--생성테이블 규칙 : TB_NAME_yyyyMMdd

DROP PROCEDURE IF EXISTS `create_log_table`;

DELIMITER $$

CREATE PROCEDURE create_log_table(IN TB_NAME VARCHAR(50))

BEGIN

DECLARE i INT DEFAULT 0;

        SET @startDay = DATE_ADD(LAST_DAY(NOW()), INTERVAL 1 DAY);

SET @lastDay = DAY(LAST_DAY(DATE_ADD(NOW(),interval 1 MONTH)));

WHILE i < @lastDay DO

SET @log_date = DATE_FORMAT(DATE_ADD(@startDay, INTERVAL i DAY), '%Y%m%d');

SET @stmt_query = CONCAT('CREATE TABLE ',TB_NAME,'_',@log_date,' LIKE ',TB_NAME,'_exam;');

            PREPARE stmt FROM @stmt_query;

            START TRANSACTION;

EXECUTE stmt;

DEALLOCATE PREPARE stmt;

COMMIT;

SET i = i + 1;

END WHILE;

END$$

DELIMITER $$

-- 1달에 1번 실행, 시작일 2017-12-01

DROP EVENT IF EXISTS event_create_log_table;

DELIMITER $$

CREATE EVENT event_create_log_table

     -- ON SCHEDULE EVERY 1 MONTH STARTS TIMESTAMP('2017-12-01') ENABLE

        ON SCHEDULE EVERY 1 MONTH STARTS '2017-12-01 00:00:00' ENABLE

    DO 

    job1:BEGIN

CALL log.create_log_table('viewlog');

CALL log.create_log_table('clicklog');

                CALL log.create_log_table('ad_log');

END job1

$$

DELIMITER ;