MySQL에서 날짜 범위 사이의 일자 목록 생성하는 방법

MySQL에서 날짜 범위 사이의 일자 생성하기

MySQL은 날짜 시퀀스를 자동으로 생성해 주는 전용 함수를 제공하지 않지만, ADDDATE() 함수와 숫자 조합 기법을 활용하면 지정한 날짜 범위 사이의 모든 일자를 쿼리 하나로 손쉽게 만들어낼 수 있습니다.

날짜 생성 쿼리 예제

아래 쿼리는 ADDDATE() 함수를 사용하여 '2016-12-15'부터 '2016-12-31'까지의 날짜를 생성합니다.

mysql> select * from
    -> (select adddate('1970-01-01',t4*10000 + t3*1000 + t2*100 + t1*10 + t0) gen_date from
    -> (select 0 t0 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
    -> (select 0 t1 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
    -> (select 0 t2 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
    -> (select 0 t3 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
    -> (select 0 t4 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4) v
    -> Where gen_date between '2016-12-15' and '2016-12-31'
    ->;

실행 결과

+------------+
| gen_date   |
+------------+
| 2016-12-15 |
| 2016-12-16 |
| 2016-12-17 |
| 2016-12-18 |
| 2016-12-19 |
| 2016-12-20 |
| 2016-12-21 |
| 2016-12-22 |
| 2016-12-23 |
| 2016-12-24 |
| 2016-12-25 |
| 2016-12-26 |
| 2016-12-27 |
| 2016-12-28 |
| 2016-12-29 |
| 2016-12-30 |
| 2016-12-31 |
+------------+
17 rows in set (0.30 sec)

쿼리 동작 원리

이 쿼리는 다음 세 단계로 작동합니다.

숫자 테이블 생성: 0부터 9까지의 값을 가진 서브쿼리(t0~t4)를 5개 교차 조인(CROSS JOIN)하여 최대 99,999개의 연속된 정수를 만들어냅니다.
날짜 변환: 각 자릿수에 가중치(10000, 1000, 100, 10, 1)를 곱해 합산한 뒤, ADDDATE('1970-01-01', n)으로 기준일에 n일을 더한 날짜를 계산합니다.
범위 필터링: WHERE 절의 BETWEEN 조건으로 '2016-12-15' ~ '2016-12-31' 범위에 해당하는 날짜만 추출합니다.

1970-01-01을 기준으로 약 273년에 해당하는 99,999일까지 생성할 수 있으므로 대부분의 실무 요구 사항을 충분히 커버합니다. 더 넓은 범위가 필요하다면 자릿수(t5 등)를 하나 더 추가하면 됩니다.

MySQL 8.0 이상: 재귀 CTE 활용하기

MySQL 8.0 이상 버전에서는 재귀 CTE(Common Table Expression)를 사용하면 훨씬 간결하게 동일한 결과를 얻을 수 있습니다.

WITH RECURSIVE dates AS (
  SELECT '2016-12-15' AS gen_date
  UNION ALL
  SELECT gen_date + INTERVAL 1 DAY
  FROM dates
  WHERE gen_date < '2016-12-31'
)
SELECT * FROM dates;

재귀 CTE는 기본 반복 한도(cte_max_recursion_depth)가 1,000회로 설정되어 있으므로, 그보다 긴 기간을 생성하려면 먼저 SET SESSION cte_max_recursion_depth = 1000000;처럼 한도를 늘려 주어야 합니다.