Khi làm việc với dữ liệu có cấu trúc phân cấp như danh mục khóa học, cây menu, hay cơ cấu tổ chức, việc truy vấn hiệu quả là rất quan trọng. MySQL cung cấp hai phương pháp chính để truy vấn dữ liệu dạng cây lưu trong bảng.

Giả sử chúng ta có bảng course_category với cấu trúc gồm các cột: id, name, parentid (khóa ngoại tự tham chiếu), và orderby. Dưới đây là hai cách để truy vấn toàn bộ cây từ một nút gốc cho trước.

1. Sử dụng JOIN nội (INNER JOIN) cho cấu trúc đơn giản

Phương pháp này phù hợp khi cây có độ sâu cố định và không quá phức tạp.

SELECT 
    parent.id AS parent_id,
    parent.name AS parent_name,
    child.id AS child_id,
    child.name AS child_name
FROM 
    course_category AS parent
    INNER JOIN course_category AS child ON child.parentid = parent.id
WHERE 
    parent.parentid = '1'
ORDER BY 
    parent.orderby, child.orderby;

Truy vấn trên chỉ lấy ra hai cấp (cha và con) từ nút có parentid = '1'. Nếu cây có nhiều cấp hơn, bạn cần thực hiện nhiều JOIN hơn, dẫn đến câu lệnh phức tạp và khó bảo trì.

2. Sử dụng CTE đệ quy (Recursive CTE) cho cấu trúc linh hoạt

Với MySQL 8.0 trở lên, CTE đệ quy là giải pháp tối ưu để truy vấn cây có độ sâu không xác định.

Trước tiên, hãy xem một ví dụ đơn giản về CTE đệ quy để tạo dãy số từ 1 đến 5:

WITH RECURSIVE number_series AS (
    -- Phần neo (anchor): điểm bắt đầu
    SELECT 1 AS value
    UNION ALL
    -- Phần đệ quy: tăng giá trị lên 1
    SELECT value + 1 
    FROM number_series 
    WHERE value < 5
)
SELECT * FROM number_series;

Giới hạn mặc định cho số lần lặp đệ quy trong MySQL là 1000. Bạn có thể điều chỉnh bằng biến cte_max_recursion_depth và giới hạn thời gian bằng cte_max_recursion_time.

MySQL thực thi đệ quy bên trong stored procedure, nhưng Java chỉ tạo một kết nối duy nhất để thực hiện toàn bộ quá trình đệ quy, vì vậy hiệu suất mạng không bị ảnh hưởng nếu số lần gọi đệ quy được kiểm soát.

Truy vấn từ trên xuống (Top-down)

WITH RECURSIVE category_tree AS (
    -- Lấy nút gốc
    SELECT * FROM course_category WHERE id = '1'
    UNION ALL
    -- Đệ quy lấy các nút con
    SELECT child.* 
    FROM course_category AS child
    INNER JOIN category_tree AS parent ON parent.id = child.parentid
)
SELECT * FROM category_tree ORDER BY id;

Truy vấn từ dưới lên (Bottom-up)

WITH RECURSIVE category_tree AS (
    -- Bắt đầu từ nút lá
    SELECT * FROM course_category WHERE id = '1-1-1'
    UNION ALL
    -- Đệ quy lấy các nút cha
    SELECT parent.* 
    FROM course_category AS parent
    INNER JOIN category_tree AS child ON child.parentid = parent.id
)
SELECT * FROM category_tree ORDER BY id;

Triển khai trong Java (Spring Boot + MyBatis)

Để sử dụng kết quả truy vấn CTE trong Java và xây dựng cấu trúc cây, bạn cần:

• Một lớp POJO (ví dụ: CourseCategoryDto) kế thừa từ entity, có thêm trường List<CourseCategoryDto> children.
• Mapper MyBatis thực thi truy vấn CTE và trả về danh sách các CourseCategoryDto.
• Dịch vụ (Service) xử lý logic xây dựng cây.

public List<CourseCategoryDto> buildCategoryTree(String rootId) {
    // Lấy tất cả node từ gốc trở xuống
    List<CourseCategoryDto> allNodes = categoryMapper.selectTreeNodes(rootId);

    // Chuyển đổi thành Map để tra cứu nhanh
    Map<String, CourseCategoryDto> nodeMap = allNodes.stream()
            .filter(node -> !node.getId().equals(rootId))
            .collect(Collectors.toMap(CourseCategoryDto::getId, Function.identity(), (oldVal, newVal) -> newVal));

    List<CourseCategoryDto> rootChildren = new ArrayList<>();

    allNodes.stream()
            .filter(node -> !node.getId().equals(rootId))
            .forEach(currentNode -> {
                String parentId = currentNode.getParentid();
                if (parentId.equals(rootId)) {
                    // Node là con trực tiếp của gốc
                    rootChildren.add(currentNode);
                } else {
                    // Tìm node cha
                    CourseCategoryDto parent = nodeMap.get(parentId);
                    if (parent != null) {
                        if (parent.getChildren() == null) {
                            parent.setChildren(new ArrayList<>());
                        }
                        parent.getChildren().add(currentNode);
                    }
                }
            });

    return rootChildren;
}

Giải thích: Phương pháp này lấy tất cả các node từ CTE, xây dựng Map giúp tra cứu nhanh, sau đó duyệt qua từng node để gán node con vào danh sách tương ứng của node cha. Kết quả cuối cùng là một cấu trúc cây hoàn chỉnh với rootId là gốc.