Nắm vững CTE đệ quy của MySQL:Truyền tải phân cấp và tạo chuỗi

CTE đệ quy (Biểu thức bảng chung) là một truy vấn con tham chiếu chính nó bằng tên riêng của nó. Nó được xác định bằng WITH RECURSIVE và phải có điều kiện kết thúc. CTE đệ quy rất hữu ích cho việc tạo chuỗi, duyệt dữ liệu phân cấp và duyệt đồ thị.

Cú pháp

WITH RECURSIVE cte_name (col1, col2, ...) AS (
 -- Non-recursive (base case): initial rows
 SELECT col1, col2 FROM table_name
 UNION ALL
 -- Recursive case: references cte_name
 SELECT col1, col2 FROM cte_name WHERE condition
)
SELECT * FROM cte_name;

• SELECT đầu tiên là trường hợp cơ sở cung cấp các hàng đầu tiên.
• UNION ALL thêm các hàng từ mỗi lần lặp (DISTINCT loại bỏ các bản sao).
• SELECT thứ hai là trường hợp đệ quy chạy cho đến khi điều kiện WHERE không thành công.

Ví dụ 1:Tạo 5 số lẻ đầu tiên

WITH RECURSIVE odd_no (sr_no, n) AS (
 SELECT 1, 1
 UNION ALL
 SELECT sr_no + 1, n + 2
 FROM odd_no
 WHERE sr_no < 5
)
SELECT * FROM odd_no;

+-------+---+
| sr_no | n |
+-------+---+
| 1 | 1 |
| 2 | 3 |
| 3 | 5 |
| 4 | 7 |
| 5 | 9 |
+-------+---+

Trả về trường hợp cơ sở (1, 1). Mỗi lần lặp lại tăng sr_no lên 1 và n lên 2. Quá trình đệ quy dừng khi sr_no đạt 5.

Ví dụ 2:Hệ thống phân cấp nhân viên

Một cách sử dụng thực tế hơn thông qua hệ thống phân cấp người quản lý-nhân viên ?

-- Assume: employees(id, name, manager_id)
WITH RECURSIVE org_chart (id, name, level) AS (
 -- Base: top-level manager (no manager)
 SELECT id, name, 0
 FROM employees
 WHERE manager_id IS NULL
 UNION ALL
 -- Recursive: find direct reports
 SELECT e.id, e.name, oc.level + 1
 FROM employees e
 JOIN org_chart oc ON e.manager_id = oc.id
)
SELECT * FROM org_chart ORDER BY level;

Điều này xây dựng một sơ đồ tổ chức bắt đầu từ người quản lý cấp cao nhất (cấp 0) và tìm đệ quy tất cả các báo cáo ở mỗi cấp.

Những điểm chính

• Luôn bao gồm điều kiện kết thúc trong mệnh đề WHERE của SELECT đệ quy để tránh vòng lặp vô hạn.
• MySQL có giới hạn đệ quy mặc định là 1000 lần lặp (có thể định cấu hình qua cte_max_recursion_depth ).
• Sử dụng UNION ALL để thực hiện; UNION DISTINCT chỉ khi phải loại bỏ các bản sao.

Kết luận

CTE đệ quy sử dụng WITH RECURSIVE để xác định các truy vấn tự tham chiếu với trường hợp cơ sở và trường hợp đệ quy. Chúng rất cần thiết cho việc truyền tải dữ liệu phân cấp (biểu đồ tổ chức, cây danh mục), tạo chuỗi và truyền tải biểu đồ trong MySQL.