[MySQL] Dynamic SQL을 사용하여 프로시저 생성

매달 생기는 테이블에 자동으로 인덱스 생성을 해주고자 한다.
매달 1일에 동적으로 테이블 이름을 넣어서 날짜 컬럼에 인덱스를 생성하는 프로시저를 작성한다.

 

Dynamic SQL 이란?
"?"로 지정된 자리에 들어온 변수를 대입하여 실행되는 쿼리

 

 

아래 링크를 참고하여 프로시저를 작성했지만 프로시저 실행시 에러가 발생한다.

Mysql stored procedure don't take table name as parameter

-- 잘못된 예시
DELIMITER $$
CREATE PROCEDURE `test`.`sp_create_index_DATE`(IN tableName VARCHAR(20))
	BEGIN                  
		SET @paramA = tableName;
		SET @sql = CREATE INDEX USER_HIST_IX1 ON ? (RGST_DATE) ;
		PREPARE s1 from @sql;
		EXECUTE s1 USING @paramA;
	END$$
DELIMITER ;

 

에러 내용

쿼리: call `test`.`sp_create_index_DATE`()

오류 코드: 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '? (CMP_SND_DTTM)' at line 1

 

---

보통은 위와 같이 작성하는 것이 맞지만, 들어올 변수가 테이블명일때는 바로 사용을 못하는 듯 하다.

sql 변수를 concat으로 만드는 방식으로 변경했다.

-- 프로시저
DELIMITER $$
CREATE PROCEDURE `test`.`sp_create_index_DATE`(IN tableName VARCHAR(20))
	BEGIN                  
		SET @sql = CONCAT('CREATE INDEX USER_HIST_IX1 ON ', tableName, ' (RGST_DATE) ');
		PREPARE s1 FROM @sql;
		EXECUTE s1;
		DEALLOCATE PREPARE s1;
	END$$
DELIMITER ;

 

프로시저를 상세히 설명해보면

DELIMITER $$
-- VARCHAR(20)의 tableName을 파라미터로 받는 프로시저 생성
CREATE PROCEDURE `test`.`sp_create_index_DATE`(IN tableName VARCHAR(20)) 
BEGIN                  
	-- 입력받은 파라미터인 tableName을 이용하여 변수를 생성한다
        SET @sql = CONCAT('CREATE INDEX USER_HIST_IX1 ON ', tableName, ' (RGST_DATE) '); 
	-- 변수로 받은 sql을 s1로 준비한다 (실행X)
        PREPARE s1 FROM @sql; 
	-- 준비된 s1 실행
        EXECUTE s1; 
        -- 저장된 s1의 정보를 해제
	DEALLOCATE PREPARE s1; 
END$$
DELIMITER ;

 

이 프로시저를 매달 1일에 실행하는 이벤트도 만들어 줄 수 있다.

-- 이벤트
DELIMITER $$
CREATE EVENT `test`.`ev_create_index` ON SCHEDULE EVERY 1 MONTH STARTS '2022-09-01 01:00:00' ON COMPLETION PRESERVE ENABLE
DO 
   BEGIN
      SET @tableName=CONCAT('USER_HIST_',DATE_FORMAT(NOW(),"%Y%m"));
      CALL `test`.`sp_create_index_DATE`(@tableName);
   END$$
DELIMITER ;

 

매달 생성되는 USER_HIST_YYYYMM 테이블 이름을 변수로 설정하고,

해당 변수를 파라미터로 설정하여 프로시저를 생성하는 이벤트이다.

 

 

 

---

참고

[MySQL_MariaDB] 스토어드프로시저_변수_if_case_while_에러처리_동적쿼리