MySQL/MariaDB에서 동적 SQL(Dynamic SQL) 사용하는 방법 정리

찾아본 계기

쿼리 조회 시, 속성(열) 명을 내가 지정할 수 있을까 고민을 하다가 찾아보았습니다. 

heidi sql로 실행 시 결과적으로 속성에 날짜가 나오도록 하는 쿼리를 짜보았습니다.

 

 

동적 SQL은 SQL문 자체를 문자열로 만들어 실행하는 기법입니다.
복잡한 조건 처리나 피벗 쿼리, 조건문이 많아지는 통계/리포트 쿼리에서 매우 유용하게 쓰입니다.

MySQL/MariaDB에서는 이를 위해 다음과 같은 문법을 사용합니다:

 

PREPARE / EXECUTE / DEALLOCATE

---

📌 사용 시나리오

• 열(column)의 이름이나 개수가 동적으로 바뀌는 경우
• 반복되는 조건을 가진 쿼리를 자동 생성하고 싶을 때
• 동적으로 조합한 SQL을 문자열 변수로 구성해서 실행하고 싶을 때

예: 날짜를 기준으로 피벗 테이블을 만들고 싶을 때

---

✅ 주요 문법 구조

1. SET @sql = '쿼리 문자열';

• SQL 쿼리를 문자열 변수로 저장합니다.
• 변수 이름은 보통 @sql 또는 @query 등으로 사용합니다.

 

SET @sql = 'SELECT * FROM tb_user WHERE company_id = 42';

---

2. PREPARE stmt FROM @sql;

• @sql에 담긴 쿼리를 실행 가능한 상태로 "준비(prepared)" 시킵니다.
• stmt는 prepared statement 이름이며 자유롭게 정할 수 있습니다.

 

PREPARE stmt FROM @sql;

---

3. EXECUTE stmt;

• 준비한 statement를 실행합니다.

 

EXECUTE stmt;

---

4. DEALLOCATE PREPARE stmt;

• 사용이 끝난 statement는 반드시 해제(삭제) 해야 합니다.
• 안 하면 메모리에 쌓여서 리소스를 낭비합니다.

 

DEALLOCATE PREPARE stmt;

---

🔁 전체 예시 코드

-- 1. GROUP_CONCAT 길이 늘리기 (동적 SQL이 길어질 수 있으므로)
SET SESSION group_concat_max_len = 1000000;

-- 2. 쿼리 문자열 생성
SET @sql = 'SELECT user_id, user_name FROM tb_user WHERE company_id = 42';

-- 3. 실행 준비
PREPARE stmt FROM @sql;

-- 4. 실행
EXECUTE stmt;

-- 5. 자원 해제
DEALLOCATE PREPARE stmt;

---

🧪 실전 예시: 날짜별 근무자 수 피벗 만들기

-- 날짜별 열을 자동 생성하여 하나의 SELECT 문으로 만들어냄
SELECT
  GROUP_CONCAT(
    DISTINCT CONCAT(
      'SUM(CASE WHEN work_date = ''',
      work_date,
      ''' THEN 1 ELSE 0 END) AS `',
      work_date, '`'
    )
  ) INTO @sql
FROM tb_work_log
WHERE work_date BETWEEN '2025-05-01' AND '2025-05-09';

-- 쿼리 문자열 조립
SET @sql = CONCAT('
  SELECT COUNT(*) AS 총_인원, ', @sql, ' 
  FROM tb_work_log
  WHERE company_id = 42
');

-- 실행
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

이렇게 하면 날짜별 근무자 수를 각각의 열(column)로 출력하는 쿼리를 자동으로 생성하고 실행할 수 있습니다.

 

참고) SQL 내부 문자열에서는 작은따옴표 ' → '' 두 개로 적어야 합니다.

백틱(`)은 컬럼명으로 안전하게 인식됩니다.

---

🧠 팁: 문제 해결 체크리스트

체크포인트설명

@sql 내용 확인	SELECT @sql;
세션이 유지되고 있는가?	PREPARE, EXECUTE는 같은 세션에서 실행해야 함
group_concat_max_len	동적 쿼리가 길어지면 기본 길이(1024) 초과 주의
구문 오류 여부	SQL 조립 시 CONCAT, 따옴표 처리 신중히

 

---

✅ 마무리 요약

명령어역할

SET @sql = '...'	실행할 SQL을 문자열로 저장
PREPARE stmt FROM @sql	SQL을 실행 가능한 형태로 준비
EXECUTE stmt	준비된 SQL 실행
DEALLOCATE PREPARE stmt	자원 해제 (필수!)

 

동적 SQL을 활용하면 SQL을 코드처럼 조립할 수 있어, 복잡한 쿼리 자동화, 통계 리포트, 피벗 처리 등 다양한 고급 쿼리 작성이 가능해집니다.