KAL
Menu

CTE Rekursif

Menelusuri hierarki data dengan CTE rekursif pada MySQL 8.

Daftar isi

CTE rekursif adalah common table expression yang bagian terakhirnya memanggil dirinya sendiri, sehingga sebuah query dapat menelusuri hubungan berulang seperti hierarki kategori dan silsilah organisasi tanpa menulis perulangan di luar SQL. Topik ini membahas struktur anggota jangkar dan anggota rekursif, penelusuran hierarki kategori perpustakaan dari akar ke daun, agregasi yang mencakup seluruh subtree, serta batas kedalaman yang diatur server. Setelah mengikuti topik ini, data bertingkat dapat dikonversi menjadi daftar lurus yang siap ditampilkan atau dirangkum.

Struktur CTE rekursif#

CTE rekursif dideklarasikan dengan WITH RECURSIVE dan terdiri atas dua bagian yang dihubungkan UNION ALL. Bagian pertama disebut jangkar (anchor), yaitu query yang menghasilkan baris-baris awal. Bagian kedua disebut anggota rekursif (recursive member), yaitu query yang merujuk nama CTE-nya sendiri dan menghasilkan baris berikutnya dari baris sebelumnya.

sql Bentuk umum CTE rekursif 
-- tidak-dijalankan
WITH RECURSIVE nama_cte AS (
  SELECT ...    -- jangkar: baris awal, biasanya baris akar
  UNION ALL
  SELECT ...    -- anggota rekursif: turunan dari baris sebelumnya
)
SELECT ... FROM nama_cte;

Server mengeksekusinya dalam putaran. Putaran pertama menjalankan jangkar dan menyimpan hasilnya. Putaran berikutnya menjalankan anggota rekursif dengan memandang seluruh baris putaran sebelumnya sebagai tabel bernama CTE itu. Eksekusi berhenti ketika anggota rekursif tidak lagi menghasilkan baris baru. Setiap baris yang pernah dihasilkan tetap bertahan, sehingga hasil akhirnya adalah gabungan seluruh putaran.

Hierarki kategori perpustakaan#

Skema contoh memuat tabel kategori yang merujuk dirinya sendiri melalui kolom parent_id, sebuah pola yang lazim untuk data hierarki. Kategori tanpa parent_id berperan sebagai akar, sedangkan kategori lain menggantung pada kategori di atasnya. Diagram berikut memperlihatkan hierarki yang dipakai sepanjang topik ini beserta tingkatnya.

tingkat 1tingkat 2tingkat 3FiksiNonfiksiFiksi SejarahFiksi SainsFiksi DetektifSains AlamTeknologiAstronomiBiologi
Hierarki kategori perpustakaan

Jalankan persiapan berikut untuk membangun basis data contoh dari nol.

sql persiapan-cte-rekursif.sql 
-- tidak-dijalankan
-- Skrip persiapan topik CTE rekursif: hierarki kategori perpustakaan

DROP DATABASE IF EXISTS perpustakaan;
CREATE DATABASE perpustakaan;
USE perpustakaan;

CREATE TABLE kategori (
  id        INT AUTO_INCREMENT PRIMARY KEY,
  nama      VARCHAR(100) NOT NULL,
  parent_id INT NULL,
  FOREIGN KEY (parent_id) REFERENCES kategori(id)
);

CREATE TABLE buku (
  id          INT AUTO_INCREMENT PRIMARY KEY,
  judul       VARCHAR(200) NOT NULL,
  kategori_id INT NOT NULL,
  FOREIGN KEY (kategori_id) REFERENCES kategori(id)
);

INSERT INTO kategori (id, nama, parent_id) VALUES
  (1, 'Fiksi',          NULL),
  (2, 'Nonfiksi',       NULL),
  (3, 'Fiksi Sejarah',  1),
  (4, 'Fiksi Sains',    1),
  (5, 'Fiksi Detektif', 1),
  (6, 'Sains Alam',     2),
  (7, 'Astronomi',      6),
  (8, 'Biologi',        6),
  (9, 'Teknologi',      2);

INSERT INTO buku (judul, kategori_id) VALUES
  ('Kronik Kerajaan Bahari', 3),
  ('Kapal dan Jalur Rempah', 3),
  ('Planet Tetangga Kita', 7),
  ('Peta Langit Malam', 7),
  ('Serangga Kota Besar', 8),
  ('Kode Rahasia Perpustakaan', 5),
  ('Teka-Teki Jam Dinding', 5),
  ('Misteri Wisma Tua', 5),
  ('Robot Rumah Tangga', 9),
  ('Gravitasi untuk Pemula', 4),
  ('Kebun Obat Keluarga', 6);
bash 
# membuat ulang basis data perpustakaan dari nol
mysql -u root < persiapan-cte-rekursif.sql

Penelusuran dari akar ke daun#

Jangkar mengambil kategori akar, yaitu baris dengan parent_id bernilai NULL, lalu melengkapinya dengan kolom tingkat bernilai satu. Anggota rekursif mencari anak dari setiap baris pada putaran sebelumnya dan menaikkan nilai tingkat. Kolom jalur dirakit dengan CONCAT sehingga tiap baris membawa rangkaian nama dari akar sampai kategori itu.

bash 
# menelusuri seluruh kategori dari akar ke daun
mysql -t -u root perpustakaan <<'SQL'
WITH RECURSIVE turun AS (
  SELECT id, nama, parent_id, 1 AS tingkat,
         CAST(nama AS CHAR(200)) AS jalur
  FROM kategori
  WHERE parent_id IS NULL
  UNION ALL
  SELECT k.id, k.nama, k.parent_id, t.tingkat + 1,
         CONCAT(t.jalur, ' / ', k.nama)
  FROM kategori AS k
  JOIN turun AS t ON k.parent_id = t.id
)
SELECT id, nama, tingkat, jalur
FROM turun
ORDER BY id;
SQL
text Keluaran 
+------+----------------+---------+-----------------------------------+
| id   | nama           | tingkat | jalur                             |
+------+----------------+---------+-----------------------------------+
|    1 | Fiksi          |       1 | Fiksi                             |
|    2 | Nonfiksi       |       1 | Nonfiksi                          |
|    3 | Fiksi Sejarah  |       2 | Fiksi / Fiksi Sejarah             |
|    4 | Fiksi Sains    |       2 | Fiksi / Fiksi Sains               |
|    5 | Fiksi Detektif |       2 | Fiksi / Fiksi Detektif            |
|    6 | Sains Alam     |       2 | Nonfiksi / Sains Alam             |
|    7 | Astronomi      |       3 | Nonfiksi / Sains Alam / Astronomi |
|    8 | Biologi        |       3 | Nonfiksi / Sains Alam / Biologi   |
|    9 | Teknologi      |       2 | Nonfiksi / Teknologi              |
+------+----------------+---------+-----------------------------------+

Kolom tingkat menjelaskan kedalaman setiap kategori, sedangkan jalur menyediakan jejak dari akar yang siap ditampilkan pada menu atau breadcrumb. CAST(nama AS CHAR(200)) pada jangkar menetapkan tipe kolom jalur agar cukup lebar untuk gabungan nama sepanjang hierarki.

Agregasi lintas subtree#

Pertanyaan yang lebih sulit adalah menghitung buku per kategori termasuk seluruh subkategori. CTE rekursif menjawabnya dengan membangun tabel pasangan keturunan. Jangkar memasangkan setiap kategori dengan dirinya sendiri, lalu anggota rekursif menggandakan pasangan itu ke setiap anak di bawahnya. Hasil akhirnya memuat pasangan dari setiap kategori ke seluruh keturunannya, dan pernyataan utama tinggal menghitung buku per pasangan.

bash 
# jumlah buku per kategori termasuk seluruh subkategori
mysql -t -u root perpustakaan <<'SQL'
WITH RECURSIVE keturunan AS (
  SELECT id AS akar_id, id AS keturunan_id
  FROM kategori
  UNION ALL
  SELECT t.akar_id, k.id
  FROM keturunan AS t
  JOIN kategori AS k ON k.parent_id = t.keturunan_id
)
SELECT ka.nama AS kategori, COUNT(b.id) AS jumlah_buku
FROM keturunan AS t
JOIN kategori AS ka ON ka.id = t.akar_id
LEFT JOIN buku AS b ON b.kategori_id = t.keturunan_id
GROUP BY t.akar_id, ka.nama
ORDER BY jumlah_buku DESC, ka.nama;
SQL
text Keluaran 
+----------------+-------------+
| kategori       | jumlah_buku |
+----------------+-------------+
| Fiksi          |           6 |
| Nonfiksi       |           5 |
| Sains Alam     |           4 |
| Fiksi Detektif |           3 |
| Astronomi      |           2 |
| Fiksi Sejarah  |           2 |
| Biologi        |           1 |
| Fiksi Sains    |           1 |
| Teknologi      |           1 |
+----------------+-------------+

Angka untuk Sains Alam memperlihatkan kekuatan pola ini. Buku yang tercatat langsung pada Sains Alam hanya satu judul, tetapi hasilnya empat karena Astronomi menyumbang dua judul dan Biologi satu judul. Hierarki telah diratakan menjadi pasangan sederhana, sehingga agregasi biasa dapat mengikutinya tanpa trik tambahan.

Batas kedalaman rekursi#

Server melindungi dirinya dari rekursi yang tidak berhenti dengan variabel sesi cte_max_recursion_depth, yang bawaannya bernilai 1000 putaran. Contoh berikut membangun tabel rantai berupa deretan 1200 simpul, dan setiap simpul menggantung pada simpul sebelumnya. Pembangunannya sendiri memakai CTE rekursif dan melewati batas tersebut.

bash 
# membangun rantai 1200 simpul melebihi batas bawaan
mysql -u root perpustakaan <<'SQL'
CREATE TABLE rantai (id INT PRIMARY KEY, induk INT NULL);
INSERT INTO rantai
WITH RECURSIVE urutan AS (
  SELECT 1 AS id
  UNION ALL
  SELECT id + 1 FROM urutan WHERE id < 1200
)
SELECT id, IF(id = 1, NULL, id - 1) FROM urutan;
SQL
text Keluaran 
ERROR 3636 (HY000) at line 2: Recursive query aborted after 1001 iterations. Try increasing @@cte_max_recursion_depth to a larger value.

Error 3636 muncul setelah putaran seribu satu, dan pernyataan yang gagal tidak menyisakan baris apa pun pada tabel rantai. Penaikan batas dilakukan per sesi dengan SET SESSION, sehingga sesi lain tidak terpengaruh. Pada sesi yang sama, persiapan diulang dan penelusuran penuh dijalankan.

bash 
# menaikkan batas kedalaman CTE untuk sesi ini
mysql -t -u root perpustakaan <<'SQL'
SET SESSION cte_max_recursion_depth = 2000;
INSERT INTO rantai
WITH RECURSIVE urutan AS (
  SELECT 1 AS id
  UNION ALL
  SELECT id + 1 FROM urutan WHERE id < 1200
)
SELECT id, IF(id = 1, NULL, id - 1) FROM urutan;
WITH RECURSIVE turun AS (
  SELECT id FROM rantai WHERE induk IS NULL
  UNION ALL
  SELECT r.id FROM rantai AS r JOIN turun AS t ON r.induk = t.id
)
SELECT COUNT(*) AS simpul_dikunjungi FROM turun;
SQL
text Keluaran 
+-------------------+
| simpul_dikunjungi |
+-------------------+
|              1200 |
+-------------------+

Kesalahan umum#

Kata kunci RECURSIVE wajib ditulis ketika CTE memanggil dirinya sendiri. Tanpa kata itu, server menilai nama CTE di dalam anggota rekursif sebagai tabel biasa yang memang tidak ada.

bash 
# tanpa kata kunci RECURSIVE, anggota rekursif dinilai sebagai tabel
mysql -u root perpustakaan -e "WITH turun AS (SELECT id FROM kategori WHERE parent_id IS NULL UNION ALL SELECT k.id FROM turun t JOIN kategori k ON k.parent_id = t.id) SELECT COUNT(*) AS jumlah FROM turun;"
text Keluaran 
ERROR 1146 (42S02) at line 1: Table 'perpustakaan.turun' doesn't exist

Perhatikan pula pilihan antara UNION ALL dan UNION. Anggota rekursif umumnya memakai UNION ALL karena tidak ada baris duplikat pada penelusuran pohon, sedangkan UNION menyaring duplikat dengan biaya tambahan pada setiap putaran. Terakhir, kolom teks hasil rakitan seperti jalur sebaiknya diberi tipe lewat CAST pada jangkar. Tanpa CAST, tipe kolom mengikuti kolom asal yang lebih pendek, dan nilai panjang dapat terpotong atau ditolak.

Ringkasan#

  • CTE rekursif terdiri atas jangkar yang menghasilkan baris awal dan anggota rekursif yang memanggil CTE-nya sendiri melalui UNION ALL.
  • Eksekusi berlangsung dalam putaran hingga anggota rekursif tidak lagi menghasilkan baris, dan seluruh hasil putaran digabungkan.
  • Pola pasangan akar-keturunan meratakan hierarki, sehingga agregasi seperti jumlah buku per subtree menjadi GROUP BY biasa.
  • Batas kedalaman diatur oleh cte_max_recursion_depth, yang dinaikkan per sesi dengan SET SESSION bila hierarki melebihi seribu tingkat.

Referensi#