Làm chủ MySQL Recursive CTEs: Duyệt hệ phân cấp & Tạo chuỗi số

Recursive CTE (Common Table Expression - Biểu thức bảng chung đệ quy) là một truy vấn con tham chiếu đến chính nó bằng tên của nó. Nó được định nghĩa sử dụng WITH RECURSIVE và phải có điều kiện kết thúc. Recursive CTEs hữu ích cho việc tạo chuỗi số, 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): hàng ban đầu
  SELECT col1, col2 FROM table_name
  UNION ALL
  -- Recursive case: tham chiếu cte_name
  SELECT col1, col2 FROM cte_name WHERE condition
) SELECT * FROM cte_name;

• Câu SELECT đầu tiên là base case cung cấp hàng ban đầu.
• UNION ALL thêm hàng từ mỗi lần lặp (DISTINCT loại bỏ trùng lặp).
• Câu SELECT thứ hai là recursive case chạy cho đến khi điều kiện WHERE thất bại.

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 |
+-------+---+

Base case trả về (1, 1). Mỗi lần lặp tăng sr_no lên 1 và n lên 2. Đệ quy dừng khi sr_no đạt 5.

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

Một trường hợp sử dụng thực tế hơn là duyệt hệ phân cấp quản lý-nhân viên:

-- Giả sử: employees(id, name, manager_id)
WITH RECURSIVE org_chart (id, name, level) AS (
  -- Base: quản lý cấp cao nhất (không có manager)
  SELECT id, name, 0 FROM employees WHERE manager_id IS NULL
  UNION ALL
  -- Recursive: tìm cấp trực tiếp
  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;

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

Điểm chính

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

Kết luận

Recursive CTEs sử dụng WITH RECURSIVE để định nghĩa truy vấn tự tham chiếu với base case và recursive case. Chúng thiết yếu cho duyệt dữ liệu phân cấp (sơ đồ tổ chức, cây danh mục), tạo chuỗi số và duyệt đồ thị trong MySQL.