Analyse des Verwendungsbeispiels für den Common Table Expression CTE in mysql8

Dieser Artikel beschreibt anhand eines Beispiels die Verwendung des MySQL 8 Common Table Expression (CTE). Teilen Sie uns die Einzelheiten zu Ihrer Information mit:

Der allgemeine Tabellenausdruck CTE ist ein benannter temporärer Ergebnissatz, dessen Gültigkeitsbereich die aktuelle Anweisung ist.

Vereinfacht ausgedrückt können Sie es sich als wiederverwendbare Unterabfrage vorstellen. Natürlich unterscheidet es sich ein wenig von einer Unterabfrage. CTE kann auf andere CTEs verweisen, aber Unterabfragen können nicht auf andere Unterabfragen verweisen.

1. Das Syntaxformat von cte:

mit_Klausel:
 MIT [REKURSIV]
  cte_name [(col_name [, col_name] ...)] AS (Unterabfrage)
  [, cte_name [(col_name [, col_name] ...)] AS (Unterabfrage)] …

2. Wo können Sie die with-Anweisung verwenden, um CTE zu erstellen

1. Der Beginn von Select-, Update- und Delete-Anweisungen

MIT … AUSWÄHLEN …
MIT … UPDATE …
MIT … LÖSCHEN …

2. Zu Beginn einer Unterabfrage oder einer abgeleiteten Tabellenunterabfrage

AUSWÄHLEN ... WO id IN (MIT ... AUSWÄHLEN ...) ...
WÄHLEN SIE * VON (MIT ... WÄHLEN SIE ...) ALS dt ...

3. Unmittelbar nach SELECT, vor der Anweisung, die die SELECT-Anweisung enthält

EINFÜGEN ... MIT ... AUSWÄHLEN ...
ERSETZEN ... DURCH ... AUSWÄHLEN ...
TABELLE ERSTELLEN … MIT … AUSWÄHLEN …
ANSICHT ERSTELLEN … MIT … AUSWÄHLEN …
CURSOR DEKLARIEREN … MIT … AUSWÄHLEN …
ERKLÄREN … MIT … AUSWÄHLEN …

3. Lassen Sie uns eine Tabelle erstellen und einige Daten vorbereiten

CREATE TABLE `Menü` (
 `id` int(11) unsigned NOT NULL AUTO_INCREMENT KOMMENTAR 'ID',
 `name` varchar(32) STANDARD '' KOMMENTAR 'name',
 `url` varchar(255) DEFAULT '' KOMMENTAR 'URL-Adresse',
 `pid` int(11) DEFAULT '0' COMMENT 'Übergeordnete ID',
 PRIMÄRSCHLÜSSEL (`id`)
)ENGINE=InnoDB STANDARD-CHARSET=utf8mb4;

Punktdaten einfügen:

INSERT INTO `menu` (`id`, `name`, `url`, `pid`) VALUES ('1', 'Hintergrundverwaltung', '/verwalten', '0');
INSERT INTO `menu` (`id`, `name`, `url`, `pid`) VALUES ('2', 'Benutzerverwaltung', '/manage/user', '1');
INSERT INTO `menu` (`id`, `name`, `url`, `pid`) VALUES ('3', 'Artikelverwaltung', '/manage/article', '1');
INSERT INTO `menu` (`id`, `name`, `url`, `pid`) VALUES ('4', 'Benutzer hinzufügen', '/manage/user/add', '2');
INSERT INTO `menu` (`id`, `name`, `url`, `pid`) VALUES ('5', 'Benutzerliste', '/manage/user/list', '2');
INSERT INTO `menu` (`id`, `name`, `url`, `pid`) VALUES ('6', 'Artikel hinzufügen', '/manage/article/add', '3');
INSERT INTO `menu` (`id`, `name`, `url`, `pid`) VALUES ('7', 'Artikelliste', '/verwalten/artikel/liste', '3');

4. Nicht rekursiver CTE

Hierbei wird zu jedem Menü per Unterabfrage der direkt übergeordnete Name abgefragt.

wähle m.*, (wähle Namen aus Menü, wobei ID = m.pid) als pname aus Menü als m;

Hier verwenden wir cte, um die obige Funktion zu vervollständigen

mit cte als (
 Wählen Sie * aus dem Menü
) 
wähle m.*, (wähle cte.name aus cte, wobei cte.id = m.pid) als pname aus dem Menü als m;

Das obige Beispiel ist nicht sehr gut, es dient nur zur Demonstration der Verwendung von CTE. Sie müssen nur wissen, dass CTE ein wiederverwendbarer Ergebnisset ist.

Im Vergleich zu einigen Unterabfragen sind CTEs effizienter, da nicht-rekursive CTEs nur einmal abgefragt und wiederverwendet werden.

CTE kann auf die Ergebnisse anderer CTEs verweisen. In der folgenden Anweisung verweist beispielsweise CTE2 auf die Ergebnisse in CTE1.

mit cte1 als (
 Wählen Sie * aus dem Menü
), cte2 als (
 wähle m.*, cte1.name als pname aus dem Menü als m links, schließe dich cte1 an auf m.pid = cte1.id 
)
wähle * aus cte2;

5. Rekursiver CTE

Ein rekursiver CTE ist ein spezieller CTE, dessen Unterabfrage auf sich selbst verweist, und die with-Klausel muss mit „with recursive“ beginnen.

Die rekursive Unterabfrage des CTE besteht aus zwei Teilen: einer Seed-Abfrage und einer rekursiven Abfrage, getrennt durch union [all] oder union distinct.

Die Seed-Abfrage wird einmal ausgeführt, um die anfängliche Datenteilmenge zu erstellen.

Eine rekursive Abfrage wird wiederholt ausgeführt, um Teilmengen von Daten zurückzugeben, bis der vollständige Ergebnisset erhalten wird. Die Rekursion wird beendet, wenn die Iteration keine neuen Zeilen generiert.

mit rekursivem cte(n) als (
 Wählen Sie 1
 Vereinigung alle
 wähle n + 1 aus cte, wobei n < 10
)
wähle * aus cte;

Die obige Anweisung zeigt rekursiv 10 Zeilen an, wobei jede Zeile die Zahlen 1-10 enthält.

Der rekursive Prozess läuft wie folgt ab:

1. Führen Sie zuerst „Select 1“ aus, um das Ergebnis 1 zu erhalten. Dann ist der aktuelle Wert von n 1.

2. Führen Sie dann „select n + 1 from cte where n < 10“ aus. Da der aktuelle Wert von n 1 ist, ist die Where-Bedingung erfüllt, eine neue Zeile wird generiert und „select n + 1“ liefert das Ergebnis 2. Der aktuelle Wert von n ist 2.

3. Fahren Sie mit der Ausführung von „select n + 1 from cte“ fort, wobei n < 10 ist. Da der aktuelle Wert von n 2 ist, ist die Where-Bedingung erfüllt, eine neue Zeile wird generiert und „select n + 1“ liefert das Ergebnis 3. Der aktuelle Wert von n ist 3.

4. Rekursiv weitermachen

5. Wenn n 10 ist, ist die Where-Bedingung nicht erfüllt und es kann keine neue Zeile generiert werden, und die Rekursion wird beendet.

Bei einigen Daten mit hierarchischen Beziehungen kann der rekursive CTE zur guten Handhabung verwendet werden.

Wir möchten beispielsweise den Pfad von jedem Menü zum Menü der obersten Ebene abfragen

mit rekursivem cte als (
 select id, name, cast('0' as char(255)) als Pfad aus Menü, wo pid = 0
 Vereinigung alle
 Wählen Sie menu.id, menu.name, concat(cte.path, ',', cte.id) als Pfad vom inneren Join-CTE des Menüs auf menu.pid = cte.id
)
wähle * aus cte;

Der rekursive Prozess läuft wie folgt ab:

1. Fragen Sie zunächst alle Menüdaten mit PID = 0 ab und setzen Sie den Pfad auf „0“. Zu diesem Zeitpunkt besteht der Ergebnissatz von cte aus allen Menüdaten mit PID = 0.

2. Führen Sie den Menü-Inner-Join cte für menu.pid = cte.id aus. Zu diesem Zeitpunkt wird die Menütabelle mit cte (dem in Schritt 1 erhaltenen Ergebnissatz) innerlich verknüpft, um die Daten zu erhalten, deren übergeordnetes Element das Menü der obersten Ebene ist.

3. Führen Sie weiterhin den Menü-Inner-Join-CTE für menu.pid = cte.id aus. Zu diesem Zeitpunkt wird das Tabellenmenü mit cte (dem in Schritt 2 erhaltenen Ergebnissatz) innerlich verbunden, um die Daten des Menüs der obersten Ebene zu erhalten, dessen übergeordnetes Element das übergeordnete Element des Menüs ist.

4. Rekursiv weitermachen

5. Die Rekursion wird beendet, wenn keine Zeilen zurückgegeben werden.

Alle übergeordneten Menüs eines angegebenen Menüs abfragen

mit rekursivem cte als (
 Wählen Sie ID, Name und PID aus dem Menü, wobei ID = 7 ist.
 Vereinigung alle
 Wählen Sie menu.id, menu.name, menu.pid aus dem inneren Menü-Join-CTE auf cte.pid = menu.id
)
wähle * aus cte;

Leser, die an weiteren MySQL-bezogenen Inhalten interessiert sind, können sich die folgenden Themen auf dieser Site ansehen: „MySQL-Abfragekenntnisse“, „Zusammenfassung der allgemeinen MySQL-Funktionen“, „MySQL-Protokolloperationskenntnisse“, „Zusammenfassung der MySQL-Transaktionsoperationskenntnisse“, „MySQL-gespeicherte Prozedurkenntnisse“ und „Zusammenfassung der MySQL-Datenbanksperrenkenntnisse“.

Ich hoffe, dass dieser Artikel für jedermann beim Entwurf einer MySQL-Datenbank hilfreich ist.