Recursive CTE trong MySQL
Xem thửTình huống mở đầu
Trang web của cửa hàng shop hiển thị đường dẫn (breadcrumb) cho mỗi danh mục: Electronics > Computers > Laptops. Bảng categories có cột parent_id trỏ tới cha. Lập trình viên viết bằng hai lần nối bảng với chính nó, như đã học ở bài SELF JOIN, và trang chạy tốt. Một tháng sau, danh mục Gaming laptops được thêm vào dưới Laptops. Đường dẫn của nó chỉ hiện Gaming laptops > Laptops > Computers: thiếu Electronics ở gốc, vì tầng thứ tư không có lần nối nào để tìm. Danh mục có 8 dòng, 4 tầng, nhưng câu lệnh chỉ đi được 3 tầng.
Hãy đoán trước: nếu mai thêm tầng thứ năm, thứ sáu thì phải thêm bao nhiêu lần nối nữa, và có cách viết một lần cho mọi độ sâu không? Cuối bài bạn sẽ viết câu lệnh đó, rồi chạy nó vào đúng ba cái bẫy: kiểu cột, giới hạn số vòng và vòng lặp trong dữ liệu.
Ôn nhanh bài trước: vì sao DELETE o, oi FROM orders o JOIN order_items oi ... báo lỗi 1451 trong bài DELETE với JOIN?
Đáp án: khóa ngoại không cho xóa dòng cha khi còn dòng con, mà lệnh nhiều bảng không bảo đảm thứ tự xóa. Bài này không xóa gì, nhưng cũng làm việc với quan hệ cha con, ở độ sâu tùy ý.
Mục tiêu bài học
Sau bài này bạn có thể:
- Viết
WITH RECURSIVEcó phần gốc và phần đệ quy để đi xuống mọi cấp con, và đi ngược lên mọi cấp cha. - Thêm cột
depthvàpathvào kết quả, và tránh lỗi 1406 do kiểu cột lấy từ phần gốc. - Giải thích lỗi 3636, đổi
cte_max_recursion_depth, và chặn đệ quy vô hạn bằng điều kiện dừng,LIMITđặt trong phần đệ quy hoặc theo dõi đường đi. - Sinh dãy ngày bằng recursive CTE thay cho bảng chữ số.
Bức tranh lớn
Xuyên suốt bài, bảng categories là sơ đồ gia phả của danh mục: ai cũng ghi tên cha, và gốc không có cha. Recursive CTE giống việc lần theo gia phả theo từng thế hệ: bắt đầu với các tổ tiên, rồi mỗi lượt gọi tất cả con của những người vừa tìm được, cho tới khi không còn ai. Dự án xuyên suốt của chương là cửa hàng shop, lần này là cây danh mục nhiều cấp.
Hình dưới trả lời một câu hỏi: vòng lặp của recursive CTE lặp những gì và dừng khi nào?
Đang tải sơ đồ…
Cách đọc hình: ô xanh dương là phần gốc, chạy đúng một lần. Kết quả của nó chảy vào ô tím "Rows found so far". Ô tím thứ hai là phần đệ quy, lấy con của các dòng mới nhất. Ô cam hỏi có dòng mới không: có thì quay lại ô tìm thấy, không thì ô xanh lá dừng và trả mọi dòng đã tìm.
Chỗ ẩn dụ không còn đúng: gia phả thật có thể có người tìm được hai lần qua hai nhánh. Với dữ liệu có vòng (A là cha của B, B là cha của A), vòng lặp không bao giờ dừng, và MySQL phải tự cắt bằng giới hạn số vòng.
Khái niệm cốt lõi
Bài này có bốn khái niệm.
1. Phần gốc, phần đệ quy và điều kiện dừng
WITH RECURSIVE tên AS (phần_gốc UNION ALL phần_đệ_quy). Phần gốc chạy một lần. Phần đệ quy tham chiếu chính tên và chạy lặp; mỗi vòng nó thấy các dòng mới của vòng trước. Vòng lặp dừng khi phần đệ quy không sinh thêm dòng.
2. Cột phụ depth và path, và kiểu cột
depth đếm tầng bằng t.depth + 1. path ghép tên bằng CONCAT. Kiểu của mọi cột lấy từ phần gốc: nếu path bắt đầu bằng cột name kiểu VARCHAR(20) thì các đường dẫn dài hơn 20 ký tự gây lỗi 1406. Bọc bằng CAST(... AS CHAR(200)) ở phần gốc để cột đủ rộng.
3. Dừng đúng lúc
Số vòng tối đa mặc định là 1000 (cte_max_recursion_depth). Vượt qua, MySQL báo lỗi 3636. LIMIT đặt trong phần đệ quy dừng được vòng lặp, còn LIMIT ở SELECT ngoài thì không. Dữ liệu có vòng cần theo dõi các id đã đi qua.
4. Đi xuống và đi lên
Đi xuống: phần đệ quy nối c.parent_id = t.id để lấy con. Đi lên: nối c.id = u.parent_id để lấy cha. Cùng kỹ thuật cho cả hai chiều, và cho cả việc sinh dãy số hoặc ngày.
Ví dụ đầu tiên
Dựng bảng danh mục 8 dòng, 4 tầng [Đã chạy: MySQL 8.0.44]:
CREATE TABLE categories (
id INT PRIMARY KEY,
name VARCHAR(20) NOT NULL,
parent_id INT NULL, -- NULL nghĩa là danh mục gốc
FOREIGN KEY (parent_id) REFERENCES categories (id)
);
INSERT INTO categories VALUES
(1, 'Electronics', NULL), (2, 'Computers', 1), (3, 'Laptops', 2), (4, 'Gaming laptops', 3),
(5, 'Accessories', 1), (6, 'Keyboards', 5), (7, 'Mice', 5), (8, 'Books', NULL);
| Dòng code | Máy làm gì | Vì sao viết như vậy |
|---|---|---|
parent_id INT NULL | Số id của danh mục cha | Danh mục gốc có NULL |
FOREIGN KEY (parent_id) REFERENCES categories (id) | Cha phải là một danh mục có thật | Dữ liệu sạch, không có cha ma |
(1, 'Electronics', NULL), (8, 'Books', NULL) | Hai danh mục gốc | Cây có nhiều gốc |
(4, 'Gaming laptops', 3) | Danh mục ở tầng 4 | Làm cách nối hai lần bị cụt |
Cách nối bảng với chính nó hai lần chỉ lên được hai cấp [Đã chạy: MySQL 8.0.44]:
SELECT c.name AS category, p.name AS parent, g.name AS grand_parent
FROM categories AS c
LEFT JOIN categories AS p ON p.id = c.parent_id
LEFT JOIN categories AS g ON g.id = p.parent_id
WHERE c.id = 4;
+----------------+---------+--------------+
| category | parent | grand_parent |
+----------------+---------+--------------+
| Gaming laptops | Laptops | Computers |
+----------------+---------+--------------+
Không thấy Electronics, vì cần lần nối thứ ba. Bây giờ viết một lần cho mọi độ sâu:
WITH RECURSIVE tree AS (
SELECT id, name, 1 AS depth, CAST(name AS CHAR(200)) AS path
FROM categories
WHERE parent_id IS NULL -- phần gốc: danh mục không có cha
UNION ALL
SELECT c.id, c.name, t.depth + 1, CONCAT(t.path, ' > ', c.name)
FROM categories AS c
JOIN tree AS t ON c.parent_id = t.id -- phần đệ quy: con của dòng đã có
)
SELECT id, CONCAT(REPEAT('- ', depth - 1), name) AS category, depth, path
FROM tree
ORDER BY path;
Dự đoán trước khi xem kết quả: có bao nhiêu dòng, và Gaming laptops có depth bằng mấy?
+------+----------------------+-------+----------------------------------------------------+
| id | category | depth | path |
+------+----------------------+-------+----------------------------------------------------+
| 8 | Books | 1 | Books |
| 1 | Electronics | 1 | Electronics |
| 5 | - Accessories | 2 | Electronics > Accessories |
| 6 | - - Keyboards | 3 | Electronics > Accessories > Keyboards |
| 7 | - - Mice | 3 | Electronics > Accessories > Mice |
| 2 | - Computers | 2 | Electronics > Computers |
| 3 | - - Laptops | 3 | Electronics > Computers > Laptops |
| 4 | - - - Gaming laptops | 4 | Electronics > Computers > Laptops > Gaming laptops |
+------+----------------------+-------+----------------------------------------------------+
| Dòng code | Máy làm gì | Vì sao viết như vậy |
|---|---|---|
WITH RECURSIVE tree AS ( | Khai báo bảng tạm tree có thể tự tham chiếu | Từ khóa RECURSIVE là bắt buộc |
SELECT id, name, 1 AS depth, CAST(name AS CHAR(200)) AS path ... WHERE parent_id IS NULL | Phần gốc: các danh mục không có cha, depth bằng 1 | Điểm xuất phát; CAST để cột path đủ rộng |
UNION ALL | Nối phần gốc với phần đệ quy | Giữ mọi dòng, không loại trùng |
SELECT c.id, c.name, t.depth + 1, CONCAT(t.path, ' > ', c.name) | Phần đệ quy: tính depth và path của con | Mỗi con hơn cha một tầng |
FROM categories AS c JOIN tree AS t ON c.parent_id = t.id | Lấy các danh mục có cha nằm trong tree | JOIN là điều kiện lấy con của dòng đã có |
ORDER BY path | Sắp theo đường dẫn | Cha đứng ngay trước các con của nó |
Tám dòng, Gaming laptops ở tầng 4. Thêm tầng thứ năm thì không phải sửa câu lệnh.
Mẹo: UNION ALL thay cho UNION trong phần lớn recursive CTE. UNION thêm bước loại trùng và, với cột depth thay đổi theo vòng, không giúp dừng vòng lặp.
Xem từng bước
Animation dưới đây chạy từng vòng của câu lệnh trên. Hãy trả lời câu dự đoán trước khi bấm bước đầu tiên.
Đang tải Animation…
Bảng trace bằng chữ [Đã chạy: MySQL 8.0.44]:
| Vòng | Phần chạy | Dòng mới | Tổng dòng |
|---|---|---|---|
| 0 | Phần gốc | Electronics, Books | 2 |
| 1 | Phần đệ quy | Computers, Accessories | 4 |
| 2 | Phần đệ quy | Laptops, Keyboards, Mice | 7 |
| 3 | Phần đệ quy | Gaming laptops | 8 |
| 4 | Phần đệ quy | (không có) | 8, dừng |
Đi xuống một nhánh và đi lên
Chỉ lấy cây con của Accessories (id 5), cả chính nó [Đã chạy: MySQL 8.0.44]:
WITH RECURSIVE walk AS (
SELECT id, name, 0 AS depth
FROM categories
WHERE id = 5
UNION ALL
SELECT c.id, c.name, w.depth + 1
FROM categories AS c
JOIN walk AS w ON c.parent_id = w.id
)
SELECT id, name, depth
FROM walk
ORDER BY depth, id;
+------+-------------+-------+
| id | name | depth |
+------+-------------+-------+
| 5 | Accessories | 0 |
| 6 | Keyboards | 1 |
| 7 | Mice | 1 |
+------+-------------+-------+
Chỉ đổi phần gốc thành WHERE id = 5; phần đệ quy giữ nguyên. Đi ngược lên từ Gaming laptops để dựng breadcrumb, đổi điều kiện nối thành c.id = u.parent_id [Đã chạy: MySQL 8.0.44]:
WITH RECURSIVE up AS (
SELECT id, name, parent_id, 1 AS level
FROM categories
WHERE id = 4
UNION ALL
SELECT c.id, c.name, c.parent_id, u.level + 1
FROM categories AS c
JOIN up AS u ON c.id = u.parent_id
)
SELECT id, name, level
FROM up
ORDER BY level DESC;
+------+----------------+-------+
| id | name | level |
+------+----------------+-------+
| 1 | Electronics | 4 |
| 2 | Computers | 3 |
| 3 | Laptops | 2 |
| 4 | Gaming laptops | 1 |
+------+----------------+-------+
| Dòng code | Máy làm gì | Vì sao viết như vậy |
|---|---|---|
WHERE id = 4 | Phần gốc: bắt đầu từ chính danh mục cần dựng đường dẫn | Điểm xuất phát là lá, không phải gốc cây |
JOIN up AS u ON c.id = u.parent_id | Lấy cha của dòng mới nhất | Ngược với đi xuống: id của dòng mới là parent_id của dòng trước |
u.level + 1 | Đếm số bước đã đi lên | Dòng càng lên cao, level càng lớn |
ORDER BY level DESC | Hiện gốc trước, lá sau | Ra đường dẫn đúng thứ tự đọc |
Bạn vừa làm được: viết một câu lệnh cho cây bất kỳ độ sâu, đi xuống và đi lên, và thấy vì sao nó dừng.
Concept Check
Dùng bảng categories ở trên.
Câu 1. Phần gốc WHERE parent_id IS NULL lấy bao nhiêu dòng, và phần đệ quy được chạy lần đầu trên dòng nào?
- A. 8 dòng, vì phần gốc lấy cả bảng.
- B. 2 dòng, Electronics và Books, và phần đệ quy chạy trên hai dòng đó.
- C. 1 dòng, danh mục có
idnhỏ nhất. - D. 0 dòng, vì phần gốc không có điều kiện dừng.
Câu 2. Vì sao phần đệ quy dùng JOIN tree AS t ON c.parent_id = t.id?
- A. Để nối với bảng
treeở vòng đầu tiên mà thôi. - B. Để lấy các danh mục có cha là dòng vừa được tìm thấy.
- C. Để loại các danh mục không có cha.
- D. Để sắp xếp các danh mục theo tên.
Câu 3. Cột path bắt đầu bằng name AS path (cột name là VARCHAR(20)). Điều gì xảy ra khi ghép đường dẫn dài hơn 20 ký tự?
- A. Đường dẫn bị cắt im lặng còn 20 ký tự.
- B. Lỗi 1406
Data too long for column 'path', vì kiểu cột lấy từ phần gốc. - C. Cột tự mở rộng cho vừa đường dẫn.
- D. Lỗi 3636, vì đường dẫn dài làm vòng lặp quá nhiều.
Đáp án và giải thích:
- Câu 1: B. Phần gốc chạy một lần trên điều kiện
parent_id IS NULL, ra Electronics và Books; phần đệ quy chạy trên hai dòng đó. A sai vì có điều kiện. C và D bịa ra hành vi. - Câu 2: B. Điều kiện nối chọn con của các dòng đã có trong
tree. A mô tả sai vì phần đệ quy chạy nhiều vòng. C và D không phải việc củaJOINnày. - Câu 3: B. Bài đã chạy ra lỗi 1406. A là hành vi của chế độ SQL không nghiêm ngặt, không phải mặc định. C sai vì kiểu cột cố định theo phần gốc. D nhầm hai lỗi: 3636 là quá số vòng.
Làm cùng mình
Trang web cần danh sách danh mục thụt lề để hiển thị menu: cha đứng trước con, mỗi tầng thụt thêm một dấu - .
Bước 1. Vẽ ra giấy
Vẽ cây 8 danh mục thành các cột theo tầng. Viết cạnh mỗi nút path mà nó sẽ có, ví dụ Electronics > Accessories.
Bước 2. Điền chỗ trống
Điền hai chỗ còn thiếu:
WITH RECURSIVE tree AS (
SELECT id, name, 1 AS depth, CAST(name AS CHAR(200)) AS path
FROM categories
WHERE parent_id IS NULL
UNION ALL
SELECT c.id, c.name, t.depth + 1, CONCAT(t.path, ' > ', c.name)
FROM categories AS c
-- TODO 1: nối với tree để lấy các danh mục con của từng dòng đã có
)
SELECT CONCAT(REPEAT('- ', depth - 1), name) AS category
FROM tree
-- TODO 2: sắp xếp theo path để danh mục cha đứng ngay trước các con
;
Gợi ý cho chỗ trống thứ nhất: nối với tree bằng điều kiện c.parent_id = t.id. Gợi ý cho chỗ trống thứ hai: ORDER BY path.
Bước 3. Chạy và so sánh
Chạy rồi so với path bạn ghi ở Bước 1. Đối chiếu với kết quả ở mục Lời giải và đáp án.
Bước 4. Vẽ lại ra giấy
Viết một câu: vì sao ORDER BY path đặt con ngay sau cha, còn ORDER BY depth thì không.
Thử phá nó đi
Năm lỗi hay gặp với recursive CTE [Đã chạy: MySQL 8.0.44]:
| Code lỗi | Hiện tượng | Nguyên nhân | Cách sửa |
|---|---|---|---|
Phần gốc name AS path | ERROR 1406: Data too long for column 'path' | Kiểu cột lấy từ phần gốc, VARCHAR(20) | CAST(name AS CHAR(200)) AS path |
| Đệ quy không điều kiện dừng, hoặc 1500 vòng | ERROR 3636: Recursive query aborted after 1001 iterations | Vượt cte_max_recursion_depth mặc định 1000 | Thêm điều kiện dừng, hoặc tăng giá trị cho phiên |
LIMIT 5 ở SELECT ngoài | Vẫn lỗi 3636 | LIMIT ngoài không dừng vòng lặp | Đặt LIMIT trong phần đệ quy |
| Dữ liệu có vòng (cha của gốc là lá) | Lỗi 3636 dù dữ liệu nhỏ | Vòng lặp không bao giờ hết dòng mới | Theo dõi id đã đi qua |
Quên từ khóa RECURSIVE | CTE không được tự tham chiếu | Phải khai báo WITH RECURSIVE | Thêm RECURSIVE |
Lỗi đầu tiên: bạn thấy cả bảng đúng nhưng thất bại vì một đường dẫn dài [Đã chạy: MySQL 8.0.44]:
WITH RECURSIVE tree AS (
SELECT id, name, 1 AS depth, name AS path
FROM categories
WHERE parent_id IS NULL
UNION ALL
SELECT c.id, c.name, t.depth + 1, CONCAT(t.path, ' > ', c.name)
FROM categories AS c
JOIN tree AS t ON c.parent_id = t.id
)
SELECT id, path FROM tree;
ERROR 1406 (22001): Data too long for column 'path' at row 1
Cột path mang kiểu VARCHAR(20) từ name, nên Electronics > Accessories (25 ký tự) không vừa. Giới hạn số vòng mặc định [Đã chạy: MySQL 8.0.44]:
SELECT @@cte_max_recursion_depth AS max_depth;
+-----------+
| max_depth |
+-----------+
| 1000 |
+-----------+
WITH RECURSIVE n AS (
SELECT 1 AS i
UNION ALL
SELECT i + 1 FROM n WHERE i < 1500
)
SELECT COUNT(*) AS total FROM n;
ERROR 3636 (HY000): Recursive query aborted after 1001 iterations. Try increasing @@cte_max_recursion_depth to a larger value.
Tăng giới hạn cho phiên, và câu lệnh chạy được [Đã chạy: MySQL 8.0.44]:
SET SESSION cte_max_recursion_depth = 2000;
WITH RECURSIVE n AS (
SELECT 1 AS i
UNION ALL
SELECT i + 1 FROM n WHERE i < 1500
)
SELECT COUNT(*) AS total FROM n;
+-------+
| total |
+-------+
| 1500 |
+-------+
LIMIT ở SELECT ngoài không cứu được một đệ quy vô hạn [Đã chạy: MySQL 8.0.44]:
WITH RECURSIVE n AS (
SELECT 1 AS i
UNION ALL
SELECT i + 1 FROM n
)
SELECT i FROM n LIMIT 5;
ERROR 3636 (HY000): Recursive query aborted after 1001 iterations. Try increasing @@cte_max_recursion_depth to a larger value.
Đặt LIMIT trong phần đệ quy thì vòng lặp dừng sau 5 dòng [Đã chạy: MySQL 8.0.44, MySQL từ 8.0.19]:
WITH RECURSIVE n AS (
SELECT 1 AS i
UNION ALL
SELECT i + 1 FROM n LIMIT 5
)
SELECT i FROM n;
+------+
| i |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
+------+
Dữ liệu có vòng: đặt Electronics làm con của Gaming laptops. Mọi đường đều quay về chính nó [Đã chạy: MySQL 8.0.44]:
WITH RECURSIVE walk AS (
SELECT id, name, 1 AS depth
FROM categories
WHERE id = 1
UNION ALL
SELECT c.id, c.name, w.depth + 1
FROM categories AS c
JOIN walk AS w ON c.parent_id = w.id
)
SELECT COUNT(*) AS total FROM walk;
ERROR 3636 (HY000): Recursive query aborted after 1001 iterations. Try increasing @@cte_max_recursion_depth to a larger value.
Cách sửa là ghi lại các id đã đi qua trong cột ids và bỏ qua danh mục đã gặp:
WITH RECURSIVE walk AS (
SELECT id, name, 1 AS depth, CAST(id AS CHAR(200)) AS ids
FROM categories
WHERE id = 1
UNION ALL
SELECT c.id, c.name, w.depth + 1, CONCAT(w.ids, ',', c.id)
FROM categories AS c
JOIN walk AS w ON c.parent_id = w.id
WHERE NOT FIND_IN_SET(c.id, w.ids) -- bỏ qua danh mục đã đi qua
)
SELECT id, name, depth, ids
FROM walk
ORDER BY depth;
+------+----------------+-------+---------+
| id | name | depth | ids |
+------+----------------+-------+---------+
| 1 | Electronics | 1 | 1 |
| 2 | Computers | 2 | 1,2 |
| 5 | Accessories | 2 | 1,5 |
| 3 | Laptops | 3 | 1,2,3 |
| 6 | Keyboards | 3 | 1,5,6 |
| 7 | Mice | 3 | 1,5,7 |
| 4 | Gaming laptops | 4 | 1,2,3,4 |
+------+----------------+-------+---------+
| Dòng code | Máy làm gì | Vì sao viết như vậy |
|---|---|---|
CAST(id AS CHAR(200)) AS ids | Phần gốc: danh sách id đã đi qua, dạng chuỗi | Bắt đầu bằng chính id gốc, cột đủ rộng |
CONCAT(w.ids, ',', c.id) | Thêm id mới vào danh sách | Mỗi nhánh nhớ đường đi của chính nó |
WHERE NOT FIND_IN_SET(c.id, w.ids) | Bỏ qua danh mục đã có trong đường đi | Chặn vòng: không đi lại chỗ cũ |
Hình dưới đối chiếu mô hình sai với mô hình đúng về kiểu cột trong recursive CTE.
Đang tải sơ đồ…
Cách đọc hình: hai chuỗi độc lập. Chuỗi đỏ là niềm tin sai rằng cột path tự dài ra theo nhu cầu, dẫn đến việc chờ mọi đường dẫn vừa. Chuỗi xanh là thực tế: kiểu cột lấy từ phần gốc, name là VARCHAR(20), nên đường dẫn dài hơn gây lỗi 1406.
Tự tay làm
Dùng chung bảng categories của bài. Mỗi bài nêu đầu vào, kết quả mong đợi và tiêu chí đạt.
Cơ bản
Bài 1. Cây con. Đầu vào: bảng categories. Liệt kê danh mục Accessories (id 5) và mọi danh mục con ở mọi cấp, kèm độ sâu tính từ nó (chính nó là 0). Tiêu chí đạt: 3 dòng: Accessories (0), Keyboards (1), Mice (1). Gợi ý: phần gốc WHERE id = 5, phần đệ quy nối c.parent_id = w.id.
Vận dụng
Bài 2. Breadcrumb. Đầu vào: cùng bảng. Dựng đường dẫn từ gốc đến Gaming laptops (id 4): bốn danh mục theo thứ tự từ gốc xuống. Tiêu chí đạt: Electronics, Computers, Laptops, Gaming laptops. Gợi ý: đi ngược lên bằng c.id = u.parent_id, sắp theo level giảm dần.
Thử thách (tùy chọn)
Bài 3. Lịch 7 ngày. Đầu vào: bảng daily_orders(day, orders) có 2026-03-01 là 4 đơn, 2026-03-03 là 2 đơn, 2026-03-04 là 6 đơn. Dùng recursive CTE sinh 7 ngày từ 2026-03-01 rồi LEFT JOIN để ngày không có đơn ra 0. Tiêu chí đạt: 7 dòng, kết quả giống bài lịch 7 ngày trong bài CROSS JOIN. Gợi ý: SELECT day + INTERVAL 1 DAY FROM d WHERE day < '2026-03-07'.
Tự kiểm tra
Trả lời bằng lời của bạn, không nhìn lại bài. Mỗi câu ghi mục tiêu được kiểm tra.
- (Mục tiêu 1, nhớ lại) Recursive CTE gồm những phần nào, và phần nào chạy lặp?
- (Mục tiêu 1, dự đoán) Cây có một gốc và mỗi tầng một danh mục, 6 tầng. Phần đệ quy được chạy mấy vòng có dòng mới và tổng có bao nhiêu dòng?
- (Mục tiêu 2, giải thích) Vì sao
pathphải bắt đầu bằngCAST(name AS CHAR(200))chứ không phảiname? - (Mục tiêu 3, tìm lỗi) Lỗi 3636 xuất hiện. Nêu hai nguyên nhân có thể và cách xử lý mỗi nguyên nhân.
- (Mục tiêu 4, vận dụng) Cần 40 ngày liên tiếp bắt đầu từ ngày bạn chọn. Viết khung câu lệnh.
Điều cần nhớ
- Recursive CTE: phần gốc chạy một lần, phần đệ quy (tham chiếu chính CTE) chạy lặp cho tới khi không có dòng mới.
- Đi xuống:
c.parent_id = t.id. Đi lên:c.id = u.parent_id. - Kiểu cột lấy từ phần gốc; cột ghép chuỗi cần
CAST(... AS CHAR(n))đủ rộng ở phần gốc, nếu không lỗi 1406. - Số vòng tối đa mặc định 1000 (lỗi 3636);
LIMITtrong phần đệ quy dừng được vòng lặp,LIMITngoài thì không. - Dữ liệu có vòng cần theo dõi id đã đi qua (
FIND_IN_SET) hoặc giới hạn độ sâu.
Cheat sheet:
| Muốn | Viết | Lưu ý |
|---|---|---|
| Mọi cấp con của một nút | WITH RECURSIVE w AS (SELECT ... WHERE id = x UNION ALL SELECT ... JOIN w ON c.parent_id = w.id) | Phần gốc là nút x |
| Mọi cấp cha của một nút | Phần đệ quy nối c.id = u.parent_id | Sắp theo level DESC |
| Đường dẫn | CONCAT(t.path, ' > ', c.name) với path là CAST(... AS CHAR(200)) | Tránh lỗi 1406 |
| Chặn vòng | WHERE NOT FIND_IN_SET(c.id, w.ids) | Mỗi nhánh nhớ id đã đi |
| Tăng giới hạn vòng | SET SESSION cte_max_recursion_depth = n | Mặc định 1000 |
| Sinh dãy ngày | SELECT day + INTERVAL 1 DAY FROM d WHERE day < ngày_cuối | Không cần bảng chữ số |
Hình tóm tắt của bài là sơ đồ ở mục Bức tranh lớn.
Câu hỏi để dành cho chương sau: cột nối của hai bảng có dấu cách thừa ở đầu hoặc cuối ('cafe ' và 'cafe') thì JOIN có ghép được không, và hàm nào dọn sạch giá trị trước khi so sánh?
Đào sâu
Phần này dành cho bạn tò mò.
- Cơ chế bên trong. [Suy luận] MySQL giữ kết quả của recursive CTE trong một bảng tạm và, mỗi vòng, chạy phần đệ quy trên các dòng vừa được thêm vào. Vì vậy mỗi vòng chỉ nhìn thấy dòng mới nhất, chứ không phải cả bảng tạm. Điều đó khớp với hành vi bạn thấy trong animation.
- So sánh với lựa chọn khác. Bảng
categoriescó thể lưu đường dẫn sẵn (như cộtpath) hoặc mô hình tập lồng nhau (nested sets), nhanh khi đọc nhưng khó khi ghi. Recursive CTE đọc trực tiếp từ cộtparent_idnên dữ liệu đơn giản, đổi lại mỗi lần đọc phải tính lại cây. PostgreSQL cũng dùng cú phápWITH RECURSIVEgần như giống hệt. - Hiểu lầm nâng cao. Có người nghĩ đặt
UNIONthayUNION ALLlà chặn được vòng lặp. Điều này chỉ đúng khi mọi cột của dòng lặp lại giống hệt; nếu có cộtdepthtăng dần thì dòng nào cũng mới, vàUNIONkhông giúp. Theo dõi id đã đi qua là cách chắc chắn hơn. - Giới hạn của bài. Bài này chưa nói đến nhiều phần gốc cho nhiều cây, đệ quy lẫn (nhiều phần đệ quy trong một CTE), hay lưu kết quả vào bảng thật.
- Đọc thêm. MySQL 8.0 Reference Manual, trang "WITH (Common Table Expressions)", mục Recursive Common Table Expressions: https://dev.mysql.com/doc/refman/8.0/en/with.html
Bài tiếp theo
Chương tiếp theo là Hàm MySQL, mở đầu bằng hàm DATE(): cắt phần ngày từ một giá trị ngày giờ, thứ rất cần khi ghép đơn hàng theo ngày như bảng daily_orders của bài này. Mini-dự án nối tiếp: chuẩn hóa ngày đơn hàng của cửa hàng shop.
Lời giải và đáp án
Làm cùng mình, Bước 2. Điền:
JOIN tree AS t ON c.parent_id = t.id
và
ORDER BY path
Kết quả giống tree_ok ở trên:
+------+----------------------+-------+----------------------------------------------------+
| id | category | depth | path |
+------+----------------------+-------+----------------------------------------------------+
| 8 | Books | 1 | Books |
| 1 | Electronics | 1 | Electronics |
| 5 | - Accessories | 2 | Electronics > Accessories |
| 6 | - - Keyboards | 3 | Electronics > Accessories > Keyboards |
| 7 | - - Mice | 3 | Electronics > Accessories > Mice |
| 2 | - Computers | 2 | Electronics > Computers |
| 3 | - - Laptops | 3 | Electronics > Computers > Laptops |
| 4 | - - - Gaming laptops | 4 | Electronics > Computers > Laptops > Gaming laptops |
+------+----------------------+-------+----------------------------------------------------+
| Dòng code | Máy làm gì | Vì sao viết như vậy |
|---|---|---|
JOIN tree AS t ON c.parent_id = t.id | Lấy con của các dòng đã có trong tree | Đây là bước lặp: mỗi vòng nối với dòng mới nhất |
ORDER BY path | Sắp xếp theo đường dẫn | path của con bắt đầu bằng path của cha, nên đứng ngay sau |
REPEAT('- ', depth - 1) | Lặp - theo số tầng trừ một | Gốc không thụt, mỗi tầng thêm một dấu |
Bước 4: ORDER BY depth nhóm mọi danh mục cùng tầng lại với nhau, nên con không đứng cạnh cha. path bắt đầu bằng đường dẫn của cha nên con đứng ngay sau cha khi sắp theo chuỗi.
Bài 1. Dùng đúng câu down:
WITH RECURSIVE walk AS (
SELECT id, name, 0 AS depth
FROM categories
WHERE id = 5
UNION ALL
SELECT c.id, c.name, w.depth + 1
FROM categories AS c
JOIN walk AS w ON c.parent_id = w.id
)
SELECT id, name, depth
FROM walk
ORDER BY depth, id;
+------+-------------+-------+
| id | name | depth |
+------+-------------+-------+
| 5 | Accessories | 0 |
| 6 | Keyboards | 1 |
| 7 | Mice | 1 |
+------+-------------+-------+
Bài 2. Dùng đúng câu up:
WITH RECURSIVE up AS (
SELECT id, name, parent_id, 1 AS level
FROM categories
WHERE id = 4
UNION ALL
SELECT c.id, c.name, c.parent_id, u.level + 1
FROM categories AS c
JOIN up AS u ON c.id = u.parent_id
)
SELECT id, name, level
FROM up
ORDER BY level DESC;
+------+----------------+-------+
| id | name | level |
+------+----------------+-------+
| 1 | Electronics | 4 |
| 2 | Computers | 3 |
| 3 | Laptops | 2 |
| 4 | Gaming laptops | 1 |
+------+----------------+-------+
Bài 3.
WITH RECURSIVE d AS (
SELECT DATE('2026-03-01') AS day
UNION ALL
SELECT day + INTERVAL 1 DAY FROM d WHERE day < '2026-03-07'
)
SELECT d.day, COALESCE(o.orders, 0) AS orders
FROM d
LEFT JOIN daily_orders AS o ON o.day = d.day
ORDER BY d.day;
+------------+--------+
| day | orders |
+------------+--------+
| 2026-03-01 | 4 |
| 2026-03-02 | 0 |
| 2026-03-03 | 2 |
| 2026-03-04 | 6 |
| 2026-03-05 | 0 |
| 2026-03-06 | 0 |
| 2026-03-07 | 0 |
+------------+--------+
| Dòng code | Máy làm gì | Vì sao viết như vậy |
|---|---|---|
SELECT DATE('2026-03-01') AS day | Phần gốc: ngày đầu | Kiểu DATE, đủ cho mọi ngày sinh ra |
SELECT day + INTERVAL 1 DAY FROM d WHERE day < '2026-03-07' | Mỗi vòng thêm một ngày | WHERE là điều kiện dừng |
LEFT JOIN daily_orders AS o ON o.day = d.day | Gắn số đơn vào ngày | Ngày không có đơn nhận NULL |
COALESCE(o.orders, 0) | Ngày không có đơn ra 0 | Giống kết quả của bài CROSS JOIN |
Tự kiểm tra.
- Phần gốc (chạy một lần) và phần đệ quy (tham chiếu chính CTE, chạy lặp), nối bằng
UNION ALLhoặcUNION. - 5 vòng có dòng mới (tầng 2 đến tầng 6) và tổng 6 dòng; vòng thứ sáu không có dòng mới nên dừng.
- Vì kiểu cột lấy từ phần gốc:
namelàVARCHAR(20)nên đường dẫn dài hơn bị lỗi 1406.CAST(... AS CHAR(200))làm cột đủ rộng. - Dữ liệu có vòng: theo dõi id đã đi qua bằng
FIND_IN_SET. Phần đệ quy thiếu điều kiện dừng hoặc cây thật sự sâu hơn 1000: thêm điều kiện dừng, hoặc tăngcte_max_recursion_depth. WITH RECURSIVE d AS (SELECT DATE('...') AS day UNION ALL SELECT day + INTERVAL 1 DAY FROM d WHERE day < ngày_cuối), với ngày cuối là ngày đầu cộng 39 ngày.