MySQL Recursive CTEs

How it works

A recursive query starts from an anchor and repeatedly adds related rows. This lab walks the mentor hierarchy from volunteers without a mentor. The depth bound is a safety limit for the example, not complete cycle detection for arbitrary graphs. Real hierarchical data needs an explicit policy for cycles, missing roots and repeated paths. The seed is a tiny tree so the expected result is easy to check.

Run in a disposable MySQL lab

Use Oracle MySQL 8.4 with InnoDB, not Bhylu’s SQLite playground. The examples were reviewed on MySQL 8.4.8. XAMPP may provide MariaDB, which has different feature and collation behaviour. Check your actual server before running the script. These lessons do not require a change to Bhylu’s PHP, Apache or normal database service.

1. On a separate lab instance, create the empty bhylu_mysql_lab schema. Stop if it already exists; do not overwrite an existing database.
2. Select that schema and import the complete repair-parts seed once. It sets the documented session mode, utf8mb4 collation, UTC and English date-name locale.
3. Run this lesson in a fresh copy of the seed, not after another lesson’s writes. Use the command-line client or a compatible editor. DELIMITER lines are client instructions for stored-program examples.

Download this complete lesson script. Its setup is the linked seed; review the dataset before changing it. Administrative and destructive examples are restricted to the specifically named disposable objects. Every script repeats the session settings because each new connection has its own session. No MySQL browser runner is currently available.

Example

SET NAMES utf8mb4 COLLATE utf8mb4_0900_ai_ci;
SET SESSION sql_mode = 'STRICT_TRANS_TABLES,ONLY_FULL_GROUP_BY,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
SET SESSION time_zone = '+00:00';
SET SESSION lc_time_names = 'en_US';
WITH RECURSIVE chain AS (
  SELECT id, name, 0 AS depth FROM volunteers WHERE mentor_id IS NULL
  UNION ALL
  SELECT v.id, v.name, c.depth + 1
  FROM volunteers AS v JOIN chain AS c ON v.mentor_id = c.id
  WHERE c.depth < 10
)
SELECT name, depth FROM chain ORDER BY id;

Expected result

Ira | 0
Nalin | 1
Tara | 1

Ira is the root and the other two volunteers are one step away. UNION ALL preserves each reached path; it is not an automatic cycle safeguard. The server also has a recursion limit, but a meaningful data-level stopping condition should be part of the design. When accumulating text paths, explicitly choose a wide enough anchor type so recursive values do not exceed it.