Kursus MariaDB / Menelusuri Hierarki dan Mencegah Siklus dengan CTE Rekursif
Common Table Expression (CTE) rekursif adalah ekspresi tabel bernama yang dimulai dari kumpulan baris awal, lalu berulang kali mengikuti hubungan untuk menemukan baris berikutnya. Kueri hierarki yang dapat diandalkan memerlukan titik awal, aturan berhenti, kapasitas kolom jalur yang cukup, pencegahan kunjungan ulang, dan pengurutan hasil secara eksplisit.
Kenali kapan kueri rekursif berguna
CTE biasa memberi nama pada satu langkah kueri. CTE rekursif juga merujuk nama itu di dalam definisinya sendiri. Bagian awal, atau anchor, menghasilkan baris pertama; bagian rekursif menemukan baris berikutnya dari hasil langkah sebelumnya. Contoh ini menggabungkan kedua bagian dengan UNION ALL.
Pohon kategori dan bagan organisasi dapat mempunyai kedalaman yang berubah-ubah. Untuk hubungan tetap dua tingkat, JOIN mungkin sudah cukup. Hierarki besar juga memerlukan akses anak yang sesuai dan batas kedalaman yang disengaja. Gunakan latihan indeks untuk menilai akses tersebut. Ikhtisar resmi CTE rekursif MariaDB menjelaskan proses pengulangan dan tipe kolom yang ditentukan oleh anchor.
Siapkan pohon kategori berisi enam baris
Gunakan appops_lab melalui akun administrator atau akun belajar yang boleh membuat basis data dan tabel. Periksa dahulu SELECT VERSION(), DATABASE(); serta basis data dan tabel yang ada. Jika appops_lab sudah dipakai pada latihan Docker, pertahankan isinya. CREATE DATABASE IF NOT EXISTS tidak menggantikan pemeriksaan tersebut. Jika category_tree sudah ada, tinjau latihan sebelumnya sebelum mengulang pembuatan tabel.
Kunci asing memastikan bahwa induk yang dirujuk memang ada. Kunci asing tidak membuktikan bahwa seluruh hierarki bebas dari siklus, yaitu jalur yang kembali ke simpul yang sudah dilewati.
CREATE DATABASE IF NOT EXISTS appops_lab
CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE appops_lab;
CREATE TABLE category_tree (
category_id INT NOT NULL PRIMARY KEY,
parent_id INT NULL,
category_name VARCHAR(40) NOT NULL,
INDEX ix_parent (parent_id),
CONSTRAINT fk_category_parent FOREIGN KEY (parent_id)
REFERENCES category_tree(category_id)
) ENGINE=InnoDB;
INSERT INTO category_tree VALUES
(1, NULL, 'Catalog'), (2, 1, 'Books'), (3, 1, 'Tools'),
(4, 2, 'SQL'), (5, 2, 'Python'), (6, 3, 'Editors');
Nama kategori Inggris dipertahankan sebagai nilai data contoh. Catalog adalah akar katalog yang mempunyai Books, yaitu buku, dan Tools, yaitu alat. Books mempunyai SQL dan Python; Tools mempunyai Editors, yaitu editor. Indeks pada parent_id mendukung pencarian simpul anak. Enam baris ini menunjukkan hubungan dan ketepatan hasil, bukan membuktikan peningkatan kecepatan secara umum.
Jalankan kueri lengkap dengan kedalaman dan jalur
WITH RECURSIVE tree AS (
SELECT category_id, parent_id, category_name, 0 AS depth,
CAST(CONCAT('/', category_id, '/') AS CHAR(1000)) AS visited,
CAST(category_name AS CHAR(1000)) AS name_path
FROM category_tree WHERE category_id = 1
UNION ALL
SELECT c.category_id, c.parent_id, c.category_name, t.depth + 1,
CONCAT(t.visited, c.category_id, '/'),
CONCAT(t.name_path, ' > ', c.category_name)
FROM category_tree c JOIN tree t ON c.parent_id = t.category_id
WHERE t.depth < 8
AND LOCATE(CONCAT('/', c.category_id, '/'), t.visited) = 0
)
SELECT category_id, parent_id, depth, name_path
FROM tree ORDER BY depth, category_id;
SELECT pertama mengembalikan kategori 1 pada kedalaman nol. SELECT rekursif menemukan kategori yang parent_id-nya sama dengan kategori saat ini, lalu menambah kedalaman. Jalur visited mengapit ID numerik dengan garis miring, sehingga pemeriksaan ID 1 tidak keliru cocok dengan ID 11.
Kedua kolom jalur pada anchor secara eksplisit dikonversi menjadi CHAR(1000). Jika teks awal yang pendek menentukan tipe terlalu sempit, penggabungan jalur berikutnya dapat melampaui kapasitasnya. Contoh ini mengasumsikan ID bilangan bulat, nama pendek, dan kedalaman maksimum delapan. Hitung kembali kapasitas dan batas untuk kebutuhan lain; angka 1000 bukan ukuran yang selalu cukup.
| ID | Induk | Kedalaman | Jalur |
|---|---|---|---|
| 1 | NULL | 0 | Catalog |
| 2 | 1 | 1 | Catalog > Books |
| 3 | 1 | 1 | Catalog > Tools |
| 4 | 2 | 2 | Catalog > Books > SQL |
| 5 | 2 | 2 | Catalog > Books > Python |
| 6 | 3 | 2 | Catalog > Tools > Editors |
ORDER BY terakhir berdasarkan kedalaman dan ID memastikan urutan tampilan ini. Jangan menyimpulkan urutan keluaran dari proses rekursinya. Mengurutkan jalur teks menghasilkan urutan leksikografis yang dapat berbeda dari urutan ID numerik.
Ubah satu kondisi dan amati cakupan hasil
Ubah hanya t.depth < 8 menjadi t.depth < 1, lalu jalankan kueri kembali. Hasilnya anchor berkedalaman nol dan anak langsungnya, yaitu ID 1, 2, dan 3. Kondisi membandingkan kedalaman induk, sehingga simpul pada kedalaman nol masih dapat menghasilkan anak pada kedalaman satu. Kembalikan batas ke delapan setelah perbandingan.
Untuk pengamatan terpisah, ubah hanya anchor menjadi category_id = 2. Hasilnya subpohon Books dengan ID 2, 4, dan 5; Books sekarang berada pada kedalaman nol. Kembalikan anchor ke 1 sebelum percobaan siklus berikutnya.
Hasil yang dipotong batas kedalaman belum tentu mencakup seluruh hierarki. Periksa apakah ada tingkat lebih dalam sebelum menaikkan batas. Variabel server max_recursive_iterations membatasi jumlah pengulangan, tetapi tidak menggantikan aturan berhenti sesuai kebutuhan atau pemeriksaan hubungan induk.
Buat siklus sementara dan batalkan perubahannya
Pada koneksi yang sama, jalankan dua pernyataan ini, lalu ulangi kueri rekursif lengkap di atas. Koneksi lain mungkin tidak melihat perubahan induk yang belum dikomit.
START TRANSACTION;
UPDATE category_tree SET parent_id = 4 WHERE category_id = 1;
Memberikan induk 4 kepada kategori 1 membentuk siklus 1 → 2 → 4 → 1 saat hubungan anak diikuti. Kueri dimulai secara eksplisit pada kategori 1. Pemeriksaan ID yang sudah dikunjungi menolak kunjungan kembali, sehingga kueri tetap selesai dengan enam baris; nilai induk pada baris akar untuk sementara menjadi 4. Segera batalkan perubahan setelah mengamati hasil:
ROLLBACK;
SELECT category_id, parent_id FROM category_tree WHERE category_id = 1;
Induk kategori 1 harus kembali NULL. Anchor parent_id IS NULL dapat melewatkan seluruh komponen yang bersiklus setelah akar tanpa induknya hilang. Karena itu, mencegah kunjungan ulang pada satu penelusuran tidak membuktikan bahwa seluruh basis data bebas siklus atau semua komponennya terjangkau. MariaDB juga menyediakan sintaks CYCLE ... RESTRICT; jalur eksplisit pada contoh ini memperlihatkan aturan penelusurannya.
Periksa hierarki sebelum digunakan dalam aplikasi
- Pastikan anchor memilih akar atau kumpulan akar yang dimaksud.
- Bedakan induk yang hilang, referensi ke diri sendiri, siklus, dan komponen yang tidak terjangkau.
- Tentukan kedalaman maksimum dan kapasitas jalur yang sesuai.
- Periksa indeks pencarian anak dan EXPLAIN, lalu cocokkan jumlah baris dengan hubungan yang diharapkan.
Untuk membersihkan latihan, akhiri transaksi yang masih terbuka dan pastikan tabel hanya berisi data latihan yang boleh dibuang. Jika sesuai, jalankan DROP TABLE appops_lab.category_tree;. Perintah ini menghapus struktur dan baris serta tidak dapat dibatalkan dengan ROLLBACK biasa. Jangan mengganti namanya dengan tabel bisnis atau menghapus basis data yang dipakai bersama latihan lain.





