Trong cơ sở dữ liệu, việc xử lý các mối quan hệ đệ quy thường gặp phải khi làm việc với các cấu trúc dữ liệu phân cấp như cây thư mục, tổ chức nhân sự hoặc các mối quan hệ cha con. Trong MySQL, từ phiên bản 8.0 trở đi, tính năng CTE (Common Table Expressions) cùng với từ khóa RECURSIVE đã được giới thiệu, giúp việc truy vấn đệ quy trở nên dễ dàng và trực quan hơn.
Tổng Quan về CTE và RECURSIVE
CTE là một kỹ thuật giúp định nghĩa một tập hợp kết quả tạm thời tồn tại chỉ trong phạm vi câu lệnh SQL thực hiện. Khi kết hợp từ khóa RECURSIVE, CTE có khả năng gọi lại chính nó trong quá trình xử lý, từ đó giải quyết được các bài toán đệ quy.
Cách hoạt động của CTE Đệ Quy
CTE đệ quy bao gồm hai phần chính:
- Phần Mỏ neo (Anchor Member): Đây là phần cơ sở của CTE, thường là câu lệnh SQL không đệ quy.
- Phần Đệ quy (Recursive Member): Phần này chứa câu lệnh SQL tham chiếu quay lại chính CTE để tiếp tục quá trình truy vấn.
Cấu trúc cơ bản của một CTE đệ quy gồm:
WITH RECURSIVE cte_name AS (
-- Anchor Member
SELECT ...
UNION ALL
-- Recursive Member
SELECT ... FROM cte_name WHERE ...
)
SELECT * FROM cte_name;
Ví dụ về đệ quy trong MySQL
Duyệt Cây Thư Mục
Giả sử bạn có một bảng categories chứa thông tin cây thư mục với các mối quan hệ cha con như sau:
CREATE TABLE categories (
id INT PRIMARY KEY,
name VARCHAR(100),
parent_id INT
);
Dữ liệu mẫu:
INSERT INTO categories (id, name, parent_id) VALUES
(1, 'Electronics', NULL),
(2, 'Laptops', 1),
(3, 'Desktops', 1),
(4, 'Smartphones', 1),
(5, 'Gaming Laptops', 2),
(6, 'Ultrabooks', 2);
Để duyệt cây thư mục này và lấy tất cả các danh mục con bắt đầu từ một danh mục gốc (Ví dụ: danh mục Electronics), bạn có thể viết truy vấn đệ quy như sau:
WITH RECURSIVE category_tree AS (
-- Anchor Member: Lấy danh mục gốc
SELECT id, name, parent_id
FROM categories
WHERE id = 1
UNION ALL
-- Recursive Member: Lấy các danh mục con
SELECT c.id, c.name, c.parent_id
FROM categories c
JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT * FROM category_tree;
Kết quả trả về sẽ bao gồm tất cả các danh mục thuộc nhánh của Electronics:
| id | name | parent_id |
|----|-----------------|-----------|
| 1 | Electronics | NULL |
| 2 | Laptops | 1 |
| 3 | Desktops | 1 |
| 4 | Smartphones | 1 |
| 5 | Gaming Laptops | 2 |
| 6 | Ultrabooks | 2 |
Tính toán cấp bậc nhân viên
Một ví dụ khác thường gặp là tính toán cấp bậc trong tổ chức nhân sự. Giả sử bạn có bảng employees với cấu trúc sau:
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
name VARCHAR(100),
manager_id INT
);
Dữ liệu mẫu:
INSERT INTO employees (employee_id, name, manager_id) VALUES
(1, 'CEO', NULL),
(2, 'CTO', 1),
(3, 'CFO', 1),
(4, 'Dev Manager', 2),
(5, 'Developer', 4);
Bạn có thể viết truy vấn đệ quy để tính toán cấp bậc của nhân viên trong tổ chức như sau:
WITH RECURSIVE employee_hierarchy AS (
-- Anchor Member: Lấy cấp cao nhất (CEO)
SELECT employee_id, name, manager_id, 1 AS level
FROM employees
WHERE manager_id IS NULL
UNION ALL
-- Recursive Member: Lấy cấp dưới
SELECT e.employee_id, e.name, e.manager_id, eh.level + 1
FROM employees e
JOIN employee_hierarchy eh ON e.manager_id = eh.employee_id
)
SELECT * FROM employee_hierarchy
ORDER BY level;
Kết quả trả về sẽ hiển thị cây tổ chức nhân sự theo cấp bậc:
| employee_id | name | manager_id | level |
|-------------|-------------|------------|-------|
| 1 | CEO | NULL | 1 |
| 2 | CTO | 1 | 2 |
| 3 | CFO | 1 | 2 |
| 4 | Dev Manager | 2 | 3 |
| 5 | Developer | 4 | 4 |
Kết Luận
Từ khóa RECURSIVE trong MySQL cho phép chúng ta dễ dàng thực hiện các truy vấn đệ quy mà trước đây cần nhiều thao tác phức tạp hơn. Với tính năng này, việc xử lý các cấu trúc phân cấp và các bài toán đệ quy khác trở nên đơn giản và hiệu quả hơn nhiều. Các ví dụ trên đây chỉ là một phần nhỏ trong vô số ứng dụng mà CTE đệ quy có thể mang lại trong thực tế.
Comments