MySQL Recursive CTE: Generate Series and Walk Trees

A recursive CTE is a CTE that refers to itself. It builds a result step by step: start with some rows, then keep producing new rows from the previous ones until nothing new appears.

It has two parts joined with UNION ALL:

The anchor (non-recursive part): the starting rows.
The recursive part: a query that reads the CTE itself and produces the next rows.

query.sql

SQL

WITH RECURSIVE counter AS (
    SELECT 1 AS n                              -- anchor: start at 1
    UNION ALL
    SELECT n + 1 FROM counter WHERE n < 5      -- recursive: add 1 until 5
)
SELECT n FROM counter;

Output

OUTPUT

+------+
| n    |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
+------+
5 rows in set

How it runs:

The anchor produces the row 1.
The recursive part reads the previous row (1) and produces 2.
Then reads 2 and produces 3, and so on.
When n < 5 is false, the recursive part produces no rows, so the process stops.

Always include a stopping condition (WHERE n < 5). Without it, the query would run forever (see the limit below).

A series of dates

query.sql

SQL

WITH RECURSIVE days AS (
    SELECT DATE('2026-07-01') AS day
    UNION ALL
    SELECT day + INTERVAL 1 DAY FROM days WHERE day < '2026-07-07'
)
SELECT day, DAYNAME(day) AS weekday FROM days;

Output

OUTPUT

+------------+-----------+
| day        | weekday   |
+------------+-----------+
| 2026-07-01 | Wednesday |
| 2026-07-02 | Thursday  |
| 2026-07-03 | Friday    |
| 2026-07-04 | Saturday  |
| 2026-07-05 | Sunday    |
| 2026-07-06 | Monday    |
| 2026-07-07 | Tuesday   |
+------------+-----------+
7 rows in set

Date series are very useful: a calendar with one row per day, to which you LEFT JOIN the data, so that days without data still show (Grouping by Date).

Calculations: factorial and Fibonacci

query.sql

SQL

WITH RECURSIVE factorials (n, factorial) AS (
    SELECT 1, CAST(1 AS UNSIGNED)
    UNION ALL
    SELECT n + 1, factorial * (n + 1) FROM factorials WHERE n < 10
)
SELECT n, factorial FROM factorials;

Output

OUTPUT

+------+-----------+
| n    | factorial |
+------+-----------+
|    1 |         1 |
|    2 |         2 |
|    3 |         6 |
|    4 |        24 |
|    5 |       120 |
|    6 |       720 |
|    7 |      5040 |
|    8 |     40320 |
|    9 |    362880 |
|   10 |   3628800 |
+------+-----------+
10 rows in set

query.sql

SQL

WITH RECURSIVE fib (n, current_value, next_value) AS (
    SELECT 1, 0, 1
    UNION ALL
    SELECT n + 1, next_value, current_value + next_value FROM fib WHERE n < 10
)
SELECT n, current_value AS fibonacci FROM fib;

Output

OUTPUT

+------+-----------+
| n    | fibonacci |
+------+-----------+
|    1 |         0 |
|    2 |         1 |
|    3 |         1 |
|    4 |         2 |
|    5 |         3 |
|    6 |         5 |
|    7 |         8 |
|    8 |        13 |
|    9 |        21 |
|   10 |        34 |
+------+-----------+
10 rows in set

The recursion limit

To protect the server from endless queries, MySQL stops a recursive CTE after cte_max_recursion_depth iterations. The default is 1000:

query.sql

SQL

1
2

WITH RECURSIVE endless AS (SELECT 1 AS n UNION ALL SELECT n + 1 FROM endless)
SELECT COUNT(*) FROM endless;

Output

OUTPUT

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

The error appears when you forgot the stopping condition. If you really need more than 1000 steps (for a long series), raise the limit for your session:

query.sql

SQL

1
2
3

SET SESSION cte_max_recursion_depth = 5000;
WITH RECURSIVE long_series AS (SELECT 1 AS n UNION ALL SELECT n + 1 FROM long_series WHERE n < 3000)
SELECT COUNT(*) AS rows_generated, MAX(n) AS last_number FROM long_series;

Output

OUTPUT

+----------------+-------------+
| rows_generated | last_number |
+----------------+-------------+
|           3000 |        3000 |
+----------------+-------------+
1 row in set

Another safety is max_execution_time or the /+ SET_VAR(cte_max_recursion_depth = ...) / hint.

The column type comes from the anchor

The columns of a recursive CTE take their type and width from the anchor part. If the recursive part produces something wider, it is cut, and in strict mode you get an error:

query.sql

SQL

WITH RECURSIVE words AS (
    SELECT 1 AS n, CAST('x' AS CHAR(2)) AS text
    UNION ALL
    SELECT n + 1, CONCAT(text, 'yyyy') FROM words WHERE n < 3
)
SELECT * FROM words;

Output

OUTPUT

ERROR 1406 (22001): Data too long for column 'text' at row 1

The anchor says CHAR(2), so 'xyyyy' does not fit. Make the anchor column wide enough:

query.sql

SQL

WITH RECURSIVE words AS (
    SELECT 1 AS n, CAST('x' AS CHAR(30)) AS text
    UNION ALL
    SELECT n + 1, CONCAT(text, 'yyyy') FROM words WHERE n < 3
)
SELECT * FROM words;

Output

OUTPUT

+------+-----------+
| n    | text      |
+------+-----------+
|    1 | x         |
|    2 | xyyyy     |
|    3 | xyyyyyyyy |
+------+-----------+
3 rows in set

The same applies to numbers: a recursive column that starts as 1 (a small integer type) and grows can overflow in the same way, so CAST(1 AS UNSIGNED) or CAST(1 AS DECIMAL(30,0)) in the anchor is safer, as in the factorial example.

UNION vs UNION ALL

UNION ALL keeps all rows (the normal choice).
UNION (distinct) removes duplicate rows as it goes, and stops when a step adds no new rows. That can stop a loop in graph data:

query.sql

SQL

WITH RECURSIVE cycle AS (
    SELECT 1 AS n
    UNION
    SELECT n % 3 + 1 FROM cycle
)
SELECT n FROM cycle ORDER BY n;

Output

OUTPUT

+------+
| n    |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set

The values go 1, 2, 3, 1, 2, ... forever. UNION notices that a row it already has comes again, adds nothing new, and the recursion ends. With UNION ALL the same query would hit the 1000 limit.

Restrictions on the recursive part

The recursive SELECT must:

Refer to the CTE once, in its FROM clause (not in a subquery).
Not use aggregate functions, window functions, GROUP BY, ORDER BY, LIMIT or DISTINCT on the recursive CTE itself.
Not appear on the right side of a LEFT JOIN.

Typical uses

Use	Lesson
Number and date series	This lesson, Generating Test Data
Org charts, category trees, comment threads	Hierarchies with Recursive CTEs
Bill of materials	Hierarchies
Graphs: finding routes, connected items	Hierarchies
Filling gaps in a sequence	Series + `LEFT JOIN`

lightbulb

Stop condition first

Write the stopping condition (WHERE n < 10) before anything else. A recursive CTE without one is a loop waiting to be stopped by the limit.