RECURSIVE CTE TRONG MYSQL: NGHỆ THUẬT TRUY VẤN DỮ LIỆU PHÂN CẤP

trong thế giới cơ sở dữ liệu quan hệ (Relational Database), có một bài toán kinh điển từng khiến nhiều lập trình viên phải "đau đầu": Làm thế nào để truy vấn dữ liệu phân cấp (hierarchical data) như danh mục sản phẩm đa cấp (categories), sơ đồ tổ chức công ty (org chart), hay hệ thống cây thư mục chỉ bằng một câu lệnh SQL duy nhất mà không phải dùng vòng lặp while rườm rà ở tầng ứng dụng?

Câu trả lời chính là Recursive CTE (Common Table Expression - Biểu thức bảng chung đệ quy), được hỗ trợ từ MySQL 8.0 trở lên.

Hãy cùng mổ xẻ cơ chế hoạt động cực kỳ mạnh mẽ của công cụ này qua bài viết dưới đây.

1. Bản Chất Của Recursive CTE Là Gì?

CTE (Common Table Expression) thông thường giống như một bảng tạm thời được tạo ra để dùng ngay trong câu lệnh SQL tiếp theo.

Nhưng khi gắn thêm từ khóa RECURSIVE, nó trở thành một vòng lặp tự thân (Self-referencing loop). Câu lệnh sẽ tự động gọi lại chính kết quả mà nó vừa tìm được ở bước trước đó cho đến khi không còn dữ liệu thỏa mãn điều kiện dừng.

Cấu trúc kinh điển của một Recursive CTE luôn bao gồm 2 phần được nối với nhau bằng từ khóa UNION ALL:

SQL

WITH RECURSIVE cte_name AS (
    -- 1. ANCHOR MEMBER (Điểm neo khởi đầu): Lấy phần gốc của cây
    SELECT id, parent_id, name, 1 AS depth
    FROM categories
    WHERE parent_id IS NULL

    UNION ALL

    -- 2. RECURSIVE MEMBER (Phần đệ quy): Lấy các nhánh con dựa vào bảng tạm cte_name phía trên
    SELECT c.id, c.parent_id, c.name, cte.depth + 1
    FROM categories c
    JOIN cte_name cte ON c.parent_id = cte.id
)
SELECT * FROM cte_name;

2. Giải Phẫu 2 Mắt Xích Quan Trọng

Để hiểu tại sao nó chạy được, bạn hãy phân tích 2 thành phần cốt lõi:

• Anchor Member (Điểm neo): Chạy đúng một lần duy nhất ở khởi đầu để xác định điểm xuất phát của cây dữ liệu. Ví dụ: Lấy ra danh mục cấp cao nhất (parent_id IS NULL hoặc id = 1).

• Recursive Member (Phần đệ quy): Phần này sẽ liên tục chạy đi chạy lại. Ở mỗi vòng lặp, nó lấy kết quả từ bảng tạm cte_name của vòng trước đó (JOIN cte_name cte), dò tìm các bản ghi con có parent_id trùng với id của cha, rồi cộng dồn độ sâu (depth + 1).

• Điểm dừng (Termination): Vòng lặp đệ quy sẽ tự động dừng lại khi phần Recursive Member trả về 0 bản ghi (tức là đã quét đến nhánh lá cùng của cây).

3. Ví Dụ Thực Tế: Truy Vấn Cây Danh Mục Sản Phẩm (Categories)

Giả sử bạn có bảng categories lưu danh mục sản phẩm với cột id và parent_id. Bạn muốn lấy ra toàn bộ danh mục cấp 1, cấp 2, cấp 3... kèm theo đường dẫn phân cấp (breadcrumb) để hiển thị giao diện:

SQL

WITH RECURSIVE category_tree AS (
    -- Bước gốc: Lấy danh mục gốc (ví dụ: Danh mục Điện thoại, ID = 1)
    SELECT id, name, parent_id, 0 AS level, CAST(name AS CHAR(255)) AS path
    FROM categories
    WHERE id = 1

    UNION ALL

    -- Bước đệ quy: Nối danh mục con vào danh mục cha
    SELECT c.id, c.name, c.parent_id, ct.level + 1, CONCAT(ct.path, ' > ', c.name)
    FROM categories c
    JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT * FROM category_tree;

Kết quả trả về trên màn hình:

• Level 0 | Path: "Điện thoại"

• Level 1 | Path: "Điện thoại > Smartphone"

• Level 2 | Path: "Điện thoại > Smartphone > iPhone"

Chỉ với một câu lệnh SQL gọn gàng, bạn đã "bóc tách" toàn bộ cấu trúc cây phức tạp mà không cần viết hàng chục câu query lồng nhau ở phía PHP hay Node.js.

4. Cạm Bẫy Chết Người Cần Tránh: Vòng Lặp Vô Tận (Infinite Loop)

Vì là đệ quy, Recursive CTE cực kỳ nguy hiểm nếu dữ liệu trong Database của bạn bị lỗi logic (ví dụ: Danh mục A có cha là B, nhưng danh mục B lại có cha là A — tạo thành một vòng tròn khép kín).

Nếu điều này xảy ra, MySQL sẽ chạy đệ quy mãi mãi không dừng, làm cạn kiệt RAM và làm sập Database Server ngay lập tức.

Cách phòng thủ:

1. Kiểm soát dữ liệu sạch: Đảm bảo hệ thống không cho phép tạo vòng lặp quan hệ cha-con.

2. Giới hạn tầng đệ quy (Safety Guard): Trong môi trường Production, bạn nên chủ động cộng dồn biến đếm tầng (level) và đặt điều kiện chặn an toàn trong phần đệ quy để đề phòng rủi ro:

   SQL

   -- Thêm điều kiện chống trôi dữ liệu vô tận
   WHERE ct.level < 10 
   
   

💡 Lời Kết

Sự ra đời của Recursive CTE trong MySQL 8.0 đã thu hẹp khoảng cách giữa MySQL và các hệ quản trị CSDL lớn như PostgreSQL hay Oracle. Nắm vững Recursive CTE giúp bạn giải quyết gọn gàng các bài toán phân cấp phức tạp, tối ưu hiệu năng và giữ cho tầng Backend của bạn luôn sạch sẽ, không bị vướng vào các vòng lặp xử lý dữ liệu nặng nhọc ở RAM.