MySQL में Recursive CTE
Anchor + Recursive Member + Stop Rule
Recursive CTE में nonrecursive anchor के बाद recursive member होता है, सामान्यतः UNION ALL से। CTE अपने आपको refer करे तो WITH RECURSIVE required है।
WITH RECURSIVE sequence_cte (n) AS (
SELECT 1
UNION ALL
SELECT n + 1
FROM sequence_cte
WHERE n < 5
)
SELECT n FROM sequence_cte;Verified Number Series 1 से 5
Anchor 1 देता है। हर iteration one add करती है। Previous n जब 5 होता है, n < 5 false; next row नहीं बनती और recursion end।
Five dates generate करें
WITH RECURSIVE dates (day_value) AS (
SELECT DATE('2026-08-14')
UNION ALL
SELECT day_value + INTERVAL 1 DAY
FROM dates
WHERE day_value < DATE('2026-08-18')
)
SELECT day_value FROM dates;Verified School Hierarchy Dataset
CREATE TABLE org_employees (
employee_id INT PRIMARY KEY,
employee_name VARCHAR(60) NOT NULL,
manager_id INT NULL,
INDEX (manager_id),
FOREIGN KEY (manager_id)
REFERENCES org_employees(employee_id)
);
INSERT INTO org_employees VALUES
(1, 'Principal', NULL),
(2, 'Coordinator', 1),
(3, 'Teacher A', 2),
(4, 'Teacher B', 2),
(5, 'Lab Assistant', 3);Root का manager_id NULL है। Coordinator, Principal को report करता; two teachers Coordinator को; Lab Assistant, Teacher A को।
Depth और Path के साथ Hierarchy Traversal
WITH RECURSIVE org AS (
SELECT employee_id, employee_name, manager_id,
0 AS depth,
CAST(employee_name AS CHAR(300)) AS path
FROM org_employees
WHERE manager_id IS NULL
UNION ALL
SELECT e.employee_id, e.employee_name, e.manager_id,
o.depth + 1,
CONCAT(o.path, ' > ', e.employee_name)
FROM org AS o
JOIN org_employees AS e
ON e.manager_id = o.employee_id
WHERE o.depth < 10
)
SELECT employee_id, employee_name, depth, path
FROM org
ORDER BY path;Anchor का CAST enough path width define करता है। Depth guard defensive है; leaves के children न होने पर natural recursion भी stop होती है।
Termination, Depth Limits और Cycle Detection
MySQL का cte_max_recursion_depth server protect करता; documented default 1000 है। यह correct stop predicate का replacement नहीं। Development में reasonable depth condition और execution timeout भी लें।
WITH RECURSIVE org AS (
SELECT employee_id, manager_id,
CAST(employee_id AS CHAR(300)) AS id_path,
0 AS depth
FROM org_employees
WHERE manager_id IS NULL
UNION ALL
SELECT e.employee_id, e.manager_id,
CONCAT(o.id_path, ',', e.employee_id),
o.depth + 1
FROM org AS o
JOIN org_employees AS e
ON e.manager_id = o.employee_id
WHERE o.depth < 20
AND FIND_IN_SET(e.employee_id, o.id_path) = 0
)
SELECT * FROM org;id_path guard उसी path पर already visited node reject करता है। Data-validation time पर भी cycles रोकें; defensive query corrupt hierarchy को normal न बनाए।
Type Inference और Recursive-Member Restrictions
- Result column types nonrecursive anchor से infer होती हैं, recursive member से नहीं।
- Growing strings को anchor में CAST से widen करें।
- Recursive member CTE को once और FROM clause में refer करे।
- MySQL recursive SELECT member में aggregate/window functions, GROUP BY, ORDER BY और DISTINCT restrict करता है।
- Anchor और recursive members के बीच UNION DISTINCT duplicates हटा सकता है, पर designed cycle rule का substitute नहीं।
Performance, EXPLAIN और अभ्यास
- manager_id जैसे hierarchy links index करें।
- Projected recursive rows narrow रखें।
- Domain के अनुसार depth cap करें।
- EXPLAIN लें; recursive costs per iteration और total iterations data-dependent हैं।
- Roots, leaves, multiple roots, orphaned rows और cycles test करें।
अभ्यास: 1–10 series; seven-day calendar; Coordinator descendants; disposable copy में cycle add करके guard verify; recursive member के बाहर maximum observed depth निकालें।
Official संदर्भ
Syntax और behavior official MySQL 8.4 manual से जाँचे गए हैं। Production से पहले अपने server पर plans और limits verify करें।