{"id":1484,"date":"2020-01-09T23:05:01","date_gmt":"2020-01-09T14:05:01","guid":{"rendered":"https:\/\/oboki.net\/workspace\/?p=1484"},"modified":"2020-01-30T23:26:29","modified_gmt":"2020-01-30T14:26:29","slug":"with-recursive-row-generator","status":"publish","type":"post","link":"https:\/\/oboki.net\/workspace\/data-engineering\/database\/mysql\/with-recursive-row-generator\/","title":{"rendered":"[MySQL] WITH RECURSIVE \uad6c\ubb38\uc744 \uc774\uc6a9\ud55c Row Generator"},"content":{"rendered":"<p>MySQL \uc5d0\uc11c\ub294 \uc544\ub798\uc640 \uac19\uc774, Oracle \uc5d0\uc11c\ucc98\ub7fc, \uac04\ub2e8\ud558\uac8c \ub9ac\uc2a4\ud2b8 \ub370\uc774\ud130\ub97c \uc0dd\uc131\ud574\ub0b4\ub294 \ubb38\ubc95\uc774 \uc5c6\ub2e4.<\/p>\n<pre><code class=\"language-sql\">SELECT LEVEL FROM DUAL CONNECT BY LEVEL &lt; 9;<\/code><\/pre>\n<p>\uc624\ub77c\ud074\uc5d0\uc11c\ub294 <code>connect by<\/code>\ub97c \uc774\uc6a9\ud574\uc11c, \ub370\uc774\ud130\ub97c \ucd94\ucd9c\ud560\ub54c \uc870\uc778\uac78\uc5b4 \uc4f0\uac70\ub098 \uac04\ub2e8\ud558\uac8c \ud14c\uc2a4\ud2b8 \ub370\uc774\ud130\ub97c \ub9cc\ub4e4 \ub54c \uc720\uc6a9\ud558\uac8c \uc4f8 \uc218 \uc788\uc5c8\ub294\ub370 \ub2e4\ub978 RDBMS\uc5d0\uc11c\ub294 \uae30\ubcf8 \uc9c0\uc6d0\ub418\uc9c0 \uc54a\uc544 \uc544\uc27d\ub2e4. <del>PostgreSQL \uc5d0\uc11c\ub3c4 <code>generate_series(1,n)<\/code> \uc744 \uc774\uc6a9\ud574\uc11c range list \ub97c \ub9cc\ub4e4\uc5b4 \uc904 \uc218 \uc788\ub2e4\uace0 \ud55c\ub2e4.<\/del><\/p>\n<p>\uc5b4\uca0b\ub4e0 <code>connect by<\/code>\uac00 \uc5c6\ub294 \uc774\ub7f0 \uc0c1\ud669\uc5d0\uc11c\ub294 <code>WITH RECURSIVE<\/code> \ubb38\ubc95\uc744 \uc774\uc6a9\ud558\ub294\uac8c \uac00\uc7a5 \uac04\ud3b8\ud55c \ubc29\ubc95\uc778 \uac83 \uac19\ub2e4.<\/p>\n<pre><code class=\"language-sql\">WITH RECURSIVE rgen (n)\n  AS (SELECT 1\n       UNION ALL\n      SELECT n+1\n        FROM rgen\n       WHERE n &lt; 1000\n     )\nSELECT *\n  FROM rgen;<\/code><\/pre>\n<p>\ub9e4\ubc88 WITH \uad6c\ubb38\uc744 \uc0ac\uc6a9\ud558\ub294\uac8c \ubc88\uac70\ub86d\ub2e4\uba74 VIEW \ub85c \ub9cc\ub4e4\uc5b4 \ub450\uace0 \uc0ac\uc6a9\ud574\ub3c4 \uad1c\ucc2e\uc740\ub370<\/p>\n<pre><code class=\"language-sql\">CREATE OR REPLACE VIEW rgen\n    AS\n  WITH RECURSIVE rgen (n)\n    AS (SELECT 1\n         UNION ALL\n        SELECT n+1\n          FROM rgen\n         WHERE n &lt; 1000\n       )\nSELECT *\n  FROM rgen;<\/code><\/pre>\n<p><code>@@cte_max_recursion_depth<\/code> \uac12\uc5d0 \ub530\ub77c \ub2e4\uc74c\uacfc \uac19\uc740 \uc5d0\ub7ec\uac00 \ubc1c\uc0dd\ud560 \uc218\ub3c4 \uc788\ub2e4.<\/p>\n<blockquote>\n<p>ERROR 3636 (HY000): Recursive query aborted after 1001 iterations. Try increasing @@cte_max_recursion_depth to a larger value.<\/p>\n<\/blockquote>\n<p>\uc544\ub798\uc640 \uac19\uc774 \uc7ac\uadc0 \ud638\ucd9c \uc81c\ud55c\uc744 \ud480\uc5b4\uc8fc\ub294 \ubc29\ubc95\ub3c4 \uc788\uaca0\uc9c0\ub9cc \uac1c\uc778 \uc18c\uc720\uc758 \uc2dc\uc2a4\ud15c\uc774 \uc544\ub2d0 \uc218\ub3c4 \uc788\uace0, \ub610 \uc815\ucc45\uc5d0 \ub530\ub77c \uae30\uc900\uc774 \ubc14\ub014 \uc218\ub3c4 \uc788\uae30 \ub54c\ubb38\uc5d0 \uc774 \ubc29\ubc95\uc740 <code>n &lt; 1000<\/code> \uc73c\ub85c \uc0ac\uc6a9\ud560 \ub54c \uc4f0\uba74 \uc88b\uc744 \uac83 \uac19\ub2e4.<\/p>\n<pre><code class=\"language-sql\">SET SESSION cte_max_recursion_depth = 10000;\n--SUPER \uad8c\ud55c\uc774 \uc788\ub294 \uacbd\uc6b0, SET @@cte_max_recursion_depth = 10000;<\/code><\/pre>\n<p>1000 \uc774\uc0c1\uc758 range \uac00 \ud544\uc694\ud55c \uacbd\uc6b0\uc5d0\ub294 \uc544\ub798 \ub9c1\ud06c\uc5d0\uc11c \uc18c\uac1c\ud558\ub294 \ubc29\ubc95\uc744 \ucc38\uace0\ud558\uba74 20^20 \uae4c\uc9c0\uc758 \uc22b\uc790\ub97c \ube44\ud2b8 \uc5f0\uc0b0\uc744 \ud1b5\ud574 \ube60\ub978 \uc131\ub2a5\uc73c\ub85c \ub9cc\ub4e4\uc5b4\ub0bc \uc218 \uc788\ub2e4\uace0 \ud55c\ub2e4.<\/p>\n<pre><code class=\"language-sql\">CREATE OR REPLACE VIEW generator_16\nAS SELECT 0 n UNION ALL SELECT 1  UNION ALL SELECT 2  UNION ALL \n   SELECT 3   UNION ALL SELECT 4  UNION ALL SELECT 5  UNION ALL\n   SELECT 6   UNION ALL SELECT 7  UNION ALL SELECT 8  UNION ALL\n   SELECT 9   UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL\n   SELECT 12  UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL \n   SELECT 15;\n\nCREATE OR REPLACE VIEW generator_256\nAS SELECT ( ( hi.n &lt;&lt; 4 ) | lo.n ) AS n\n     FROM generator_16 lo, generator_16 hi;\n\nCREATE OR REPLACE VIEW generator_4k\nAS SELECT ( ( hi.n &lt;&lt; 8 ) | lo.n ) AS n\n     FROM generator_256 lo, generator_16 hi;\n\nCREATE OR REPLACE VIEW generator_64k\nAS SELECT ( ( hi.n &lt;&lt; 8 ) | lo.n ) AS n\n     FROM generator_256 lo, generator_256 hi;\n\nCREATE OR REPLACE VIEW generator_1m\nAS SELECT ( ( hi.n &lt;&lt; 16 ) | lo.n ) AS n\n     FROM generator_64k lo, generator_16 hi;<\/code><\/pre>\n<h2>\ucc38\uace0 \ub9c1\ud06c<\/h2>\n<p><a href=\"https:\/\/use-the-index-luke.com\/blog\/2011-07-30\/mysql-row-generator#mysql_generator_code\">https:\/\/use-the-index-luke.com\/blog\/2011-07-30\/mysql-row-generator#mysql_generator_code<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>MySQL \uc5d0\uc11c\ub294 \uc544\ub798\uc640 \uac19\uc774, Oracle \uc5d0\uc11c\ucc98\ub7fc, \uac04\ub2e8\ud558\uac8c \ub9ac\uc2a4\ud2b8 \ub370\uc774\ud130\ub97c \uc0dd\uc131\ud574\ub0b4\ub294 \ubb38\ubc95\uc774 \uc5c6\ub2e4. SELECT LEVEL FROM DUAL CONNECT BY LEVEL &lt; 9; \uc624\ub77c\ud074\uc5d0\uc11c\ub294 connect by\ub97c \uc774\uc6a9\ud574\uc11c, \ub370\uc774\ud130\ub97c \ucd94\ucd9c\ud560\ub54c \uc870\uc778\uac78\uc5b4 \uc4f0\uac70\ub098 \uac04\ub2e8\ud558\uac8c \ud14c\uc2a4\ud2b8 \ub370\uc774\ud130\ub97c \ub9cc\ub4e4 \ub54c \uc720\uc6a9\ud558\uac8c \uc4f8 \uc218 \uc788\uc5c8\ub294\ub370 \ub2e4\ub978 RDBMS\uc5d0\uc11c\ub294 \uae30\ubcf8 \uc9c0\uc6d0\ub418\uc9c0 \uc54a\uc544 \uc544\uc27d\ub2e4. PostgreSQL \uc5d0\uc11c\ub3c4 generate_series(1,n) \uc744 \uc774\uc6a9\ud574\uc11c range list \ub97c \ub9cc\ub4e4\uc5b4 \uc904 \uc218 [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[62],"tags":[],"class_list":["post-1484","post","type-post","status-publish","format-standard","hentry","category-mysql"],"_links":{"self":[{"href":"https:\/\/oboki.net\/workspace\/wp-json\/wp\/v2\/posts\/1484","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/oboki.net\/workspace\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/oboki.net\/workspace\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/oboki.net\/workspace\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/oboki.net\/workspace\/wp-json\/wp\/v2\/comments?post=1484"}],"version-history":[{"count":0,"href":"https:\/\/oboki.net\/workspace\/wp-json\/wp\/v2\/posts\/1484\/revisions"}],"wp:attachment":[{"href":"https:\/\/oboki.net\/workspace\/wp-json\/wp\/v2\/media?parent=1484"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/oboki.net\/workspace\/wp-json\/wp\/v2\/categories?post=1484"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/oboki.net\/workspace\/wp-json\/wp\/v2\/tags?post=1484"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}