[MySQL] WITH RECURSIVE 구문을 이용한 Row Generator

MySQL 에서는 아래와 같이, Oracle 에서처럼, 간단하게 리스트 데이터를 생성해내는 문법이 없다.

SELECT LEVEL FROM DUAL CONNECT BY LEVEL < 9;

오라클에서는 connect by를 이용해서, 데이터를 추출할때 조인걸어 쓰거나 간단하게 테스트 데이터를 만들 때 유용하게 쓸 수 있었는데 다른 RDBMS에서는 기본 지원되지 않아 아쉽다. PostgreSQL 에서도 generate_series(1,n) 을 이용해서 range list 를 만들어 줄 수 있다고 한다.

어쨋든 connect by가 없는 이런 상황에서는 WITH RECURSIVE 문법을 이용하는게 가장 간편한 방법인 것 같다.

WITH RECURSIVE rgen (n)
  AS (SELECT 1
       UNION ALL
      SELECT n+1
        FROM rgen
       WHERE n < 1000
     )
SELECT *
  FROM rgen;

매번 WITH 구문을 사용하는게 번거롭다면 VIEW 로 만들어 두고 사용해도 괜찮은데

CREATE OR REPLACE VIEW rgen
    AS
  WITH RECURSIVE rgen (n)
    AS (SELECT 1
         UNION ALL
        SELECT n+1
          FROM rgen
         WHERE n < 1000
       )
SELECT *
  FROM rgen;

@@cte_max_recursion_depth 값에 따라 다음과 같은 에러가 발생할 수도 있다.

ERROR 3636 (HY000): Recursive query aborted after 1001 iterations. Try increasing @@cte_max_recursion_depth to a larger value.

아래와 같이 재귀 호출 제한을 풀어주는 방법도 있겠지만 개인 소유의 시스템이 아닐 수도 있고, 또 정책에 따라 기준이 바뀔 수도 있기 때문에 이 방법은 n < 1000 으로 사용할 때 쓰면 좋을 것 같다.

SET SESSION cte_max_recursion_depth = 10000;
--SUPER 권한이 있는 경우, SET @@cte_max_recursion_depth = 10000;

1000 이상의 range 가 필요한 경우에는 아래 링크에서 소개하는 방법을 참고하면 20^20 까지의 숫자를 비트 연산을 통해 빠른 성능으로 만들어낼 수 있다고 한다.

CREATE OR REPLACE VIEW generator_16
AS SELECT 0 n UNION ALL SELECT 1  UNION ALL SELECT 2  UNION ALL 
   SELECT 3   UNION ALL SELECT 4  UNION ALL SELECT 5  UNION ALL
   SELECT 6   UNION ALL SELECT 7  UNION ALL SELECT 8  UNION ALL
   SELECT 9   UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL
   SELECT 12  UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL 
   SELECT 15;

CREATE OR REPLACE VIEW generator_256
AS SELECT ( ( hi.n << 4 ) | lo.n ) AS n
     FROM generator_16 lo, generator_16 hi;

CREATE OR REPLACE VIEW generator_4k
AS SELECT ( ( hi.n << 8 ) | lo.n ) AS n
     FROM generator_256 lo, generator_16 hi;

CREATE OR REPLACE VIEW generator_64k
AS SELECT ( ( hi.n << 8 ) | lo.n ) AS n
     FROM generator_256 lo, generator_256 hi;

CREATE OR REPLACE VIEW generator_1m
AS SELECT ( ( hi.n << 16 ) | lo.n ) AS n
     FROM generator_64k lo, generator_16 hi;

참고 링크

https://use-the-index-luke.com/blog/2011-07-30/mysql-row-generator#mysql_generator_code