เชี่ยวชาญ MySQL Recursive CTEs: การเดินทางลำดับชั้นและสร้างชุดข้อมูล

Recursive CTE (Common Table Expression) คือ Subquery ที่อ้างอิงตัวเองด้วยชื่อของมัน ถูกกำหนดด้วย WITH RECURSIVE และต้องมีเงื่อนไขสิ้นสุด (Terminating Condition) Recursive CTEs มีประโยชน์สำหรับสร้างชุดข้อมูล (Generating Series), เดินทางข้อมูลลำดับชั้น (Hierarchical Data) และ Graph Traversals

Syntax

WITH RECURSIVE cte_name (col1, col2, ...) AS (
  -- Non-recursive (base case): initial rows
  SELECT col1, col2 FROM table_name
  UNION ALL
  -- Recursive case: references cte_name
  SELECT col1, col2 FROM cte_name WHERE condition
) SELECT * FROM cte_name;

• SELECT แรกคือ Base Case ให้แถวเริ่มต้น
• UNION ALL เพิ่มแถวจากแต่ละรอบ (DISTINCT ลบซ้ำ)
• SELECT ที่สองคือ Recursive Case รันจนเงื่อนไข WHERE ล้มเหลว

ตัวอย่าง 1: สร้างเลขคี่ 5 ตัวแรก

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 คืนค่า (1, 1) แต่ละรอบเพิ่ม sr_no 1 และ n 2 Recursion หยุดเมื่อ sr_no ถึง 5

ตัวอย่าง 2: ลำดับชั้นพนักงาน (Employee Hierarchy)

การใช้งานจริงมากกว่า: เดินทางลำดับชั้น Manager-Employee

-- Assume: employees(id, name, manager_id)
WITH RECURSIVE org_chart (id, name, level) AS (
  -- Base: Top-level manager (no manager)
  SELECT id, name, 0 FROM employees WHERE manager_id IS NULL
  UNION ALL
  -- Recursive: หาลูกนักโดยตรง
  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;

สิ่งนี้สร้างต้นไม้องค์กรเริ่มจาก Manager ระดับบนสุด (Level 0) และค้นหาลูกนักทุกระดับแบบ Recursive

จุดสำคัญ

• ใส่เงื่อนไขสิ้นสุดใน WHERE Clause ของ Recursive SELECT เสมอ เพื่อกัน Infinite Loops
• MySQL มีค่าจำกัด Recursion เริ่มต้น 1000 รอบ (ตั้งค่าได้ผ่าน cte_max_recursion_depth)
• ใช้ UNION ALL สำหรับประสิทธิภาพ; UNION DISTINCT เฉพาะเมื่อต้องลบซ้ำ

สรุป

Recursive CTEs ใช้ WITH RECURSIVE กำหนดคิวรีอ้างอิงตัวเอง พร้อม Base Case และ Recursive Case จำเป็นสำหรับการเดินทางข้อมูลลำดับชั้น (Org Charts, Category Trees), สร้างชุดข้อมูล และ Graph Traversals ใน MySQL