Bagaimana untuk Mengemudi Data Hierarki dengan Cekap dengan Pertanyaan Rekursif dalam MySQL?

Perjalanan data hierarki MySQL dan pertanyaan rekursif

Apabila berurusan dengan data berstruktur hierarki dalam pangkalan data MySQL, melintasi dan mendapatkan semula tahap data yang berbeza dengan cekap boleh menjadi satu cabaran. Artikel ini akan memperkenalkan pendekatan komprehensif untuk mencipta pertanyaan rekursif dalam MySQL, menyediakan penyelesaian untuk kedua-dua versi moden dan lama.

Pertanyaan rekursif dalam MySQL 8.0

MySQL 8.0 memperkenalkan sintaks WITH RECURSIVE yang berkuasa, membolehkan anda menulis pertanyaan rekursif yang ringkas dan cekap. Contohnya:

<code class="language-sql">WITH RECURSIVE cte (id, name, parent_id) AS (
  SELECT id, name, parent_id
  FROM products
  WHERE parent_id = 19
  UNION ALL
  SELECT p.id, p.name, p.parent_id
  FROM products p
  INNER JOIN cte ON cte.id = p.parent_id
)
SELECT * FROM cte;</code>

Dalam pertanyaan ini, ungkapan jadual biasa (CTE) bernama cte merentasi jadual products secara rekursif, bermula pada parent_id yang ditentukan (di sini 19) dan mendapatkan semula semua nod anaknya.

Pertanyaan rekursif untuk MySQL 5.x

Sebelum MySQL 8.0, pertanyaan rekursif langsung tidak disokong. Walau bagaimanapun, terdapat dua alternatif yang boleh anda ambil:

Tugasan boleh ubah:

<code class="language-sql">SELECT id, name, parent_id 
FROM (SELECT * FROM products
      ORDER BY parent_id, id) products_sorted,
      (SELECT @pv := '19') initialisation
WHERE FIND_IN_SET(parent_id, @pv)
AND LENGTH(@pv := CONCAT(@pv, ',', id));</code>

Pertanyaan kompleks ini menggunakan penugasan berubah-ubah dalam pertanyaan itu sendiri. Ia secara berulang mengenal pasti nod anak berdasarkan parent_id yang disediakan (19 dalam kes ini). Walau bagaimanapun, pendekatan ini mungkin mengalami masalah prestasi pada set data yang besar.

Pengecam gaya laluan:

Menggunakan struktur ID seperti pepohon dengan pengecam gaya laluan memudahkan pertanyaan rekursif. Contohnya:

<code class="language-sql">CREATE TABLE products (id VARCHAR(255) PRIMARY KEY, name VARCHAR(255));
INSERT INTO products (id, name) VALUES ('19', 'category1');
INSERT INTO products (id, name) VALUES ('19/1', 'category2');
INSERT INTO products (id, name) VALUES ('19/1/1', 'category3');
INSERT INTO products (id, name) VALUES ('19/1/1/1', 'category4');</code>

Pendekatan ini membolehkan traversal hierarki yang cekap menggunakan kad bebas:

<code class="language-sql">SELECT * FROM products WHERE id LIKE '19%';</code>

Ringkasan

Kaedah yang anda pilih untuk pertanyaan rekursif dalam MySQL bergantung pada versi khusus yang digunakan dan saiz set data anda. Untuk versi MySQL moden, sintaks WITH RECURSIVE menyediakan penyelesaian yang elegan dan cekap. Untuk versi lama, alternatif yang digariskan di atas tersedia, dengan prestasi yang berbeza-beza.

Atas ialah kandungan terperinci Bagaimana untuk Mengemudi Data Hierarki dengan Cekap dengan Pertanyaan Rekursif dalam MySQL?. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!