CTE and Recursion · Advanced

RECURSIVE CTE

handling hierarchical or recursive data.

This topic uses window functions or CTEs; MySQL requires version 8.0 or later.

Overview

A recursive CTE starts with anchor rows and repeatedly evaluates a recursive member against the previous result until no rows remain. Its termination condition must be demonstrable, while hierarchical data also needs cycle and duplicate-path protection. MySQL 8.0 recursion-depth limits belong in the failure plan.

Basic syntax

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

Examples

RECURSIVE CTE example

The example starts at 1 and uses n < 5 as an explicit stopping condition, producing only 1 through 5.

Run result

result
n
1
2
3
4
5

Common issues

Why must a recursive CTE have a termination condition?

Each recursive step must move toward a stopping state, or rows may keep expanding until the database hits depth or resource limits.

How should cycles in hierarchy data be handled?

Track visited paths or nodes and exclude repeats in the recursive predicate. Keep the database recursion-depth limit as a final safeguard.

What should I inspect when a recursive CTE is slow?

Check anchor size, indexes on recursive join keys, rows added per level, and maximum depth; avoid carrying unnecessary large columns.

Was this explanation clear?

← WITH