MySQL Temporäre Tabellen erklärt: Erstellen, Verwenden, Löschen + Bewährte Vorgehensweisen (mit SQL-Beispielen)

1. (Anfängerfreundlich) Was ist eine MySQL-Temporärtabelle? Unterschiede zu regulären Tabellen

Einführung

Beim Verwalten von Daten mit MySQL gibt es Situationen, in denen Sie Daten vorübergehend speichern müssen. Zum Beispiel, wenn Sie einen großen Datensatz verarbeiten, möchten Sie möglicherweise Zwischenergebnisse speichern, während Sie arbeiten. In solchen Fällen ist eine temporäre Tabelle (Temporary Table) sehr nützlich.

In diesem Artikel erklären wir den grundlegenden Mechanismus von MySQL-Temporärtabelle und wie sie sich von regulären Tabellen unterscheiden.

1-1. Was ist eine temporäre Tabelle?

Eine temporäre Tabelle ist eine spezielle Tabelle, die nur während einer Datenbanksitzung (Verbindung) existiert.
Im Gegensatz zu einer regulären Tabelle wird sie automatisch entfernt, wenn die Sitzung endet, was sie ideal für die Speicherung temporärer Daten macht.

Hauptmerkmale von Temporärtabelle

• Pro Sitzung isoliert Eine temporäre Tabelle kann nur innerhalb der Sitzung, die sie erstellt hat, zugegriffen werden. Andere Sitzungen können sie nicht referenzieren.
• Wird automatisch entfernt, wenn die Sitzung endet Temporärtabelle verschwinden automatisch, wenn die Sitzung endet, selbst wenn Sie sie nicht explizit löschen.
• Sie können temporäre Tabellen mit demselben Namen erstellen Im Gegensatz zu regulären Tabellen ist es möglich, temporäre Tabellen mit demselben Namen in verschiedenen Sitzungen zu erstellen .

1-2. Unterschiede zu regulären Tabellen

Temporärtabelle und reguläre Tabellen unterscheiden sich in den folgenden Punkten.

Welche sollten Sie verwenden?

• Wenn Sie die Daten nur vorübergehend benötigen und sie nach der Verarbeitung verwerfen können → Temporary table
• Wenn Sie die Daten dauerhaft behalten und später wiederverwenden möchten → Regular table

Beispielsweise sind temporäre Tabellen sehr nützlich für Aufgaben wie groß angelegte Datenanalyse oder temporäre Aggregation.

1-3. Wann Sie eine temporäre Tabelle benötigen

MySQL-Temporärtabelle sind besonders nützlich in den folgenden Situationen.

1) Abfrageleistung verbessern

Zum Beispiel können Sie bei komplexen JOIN-Operationen die Verarbeitungszeit reduzieren, indem Sie im Voraus eine temporäre Tabelle erstellen, um Zwischendaten zu speichern.

Beispiel: JOIN-Overhead reduzieren

CREATE TEMPORARY TABLE temp_users AS
SELECT id, name FROM users WHERE status = 'active';

Indem Sie die Zieldaten zuerst in einer temporären Tabelle speichern und anschließend den JOIN ausführen, kann die Leistung verbessert werden.

2) Daten vorübergehend speichern

Temporärtabelle sind auch nützlich, wenn eine Anwendung Daten vorübergehend verwalten muss.
Zum Beispiel können Sie Daten, nach denen ein Benutzer gesucht hat, in einer temporären Tabelle speichern und sie nach dem Ende der Sitzung entfernen lassen.

3) Zwischentabellen für Batch-Verarbeitung

Bei der Verarbeitung großer Datenmengen kann die Verwendung von temporären Tabellen als Zwischentabellen die Stabilität des Prozesses verbessern.

1-4. Einschränkungen von Temporärtabelle

Temporärtabelle sind praktisch, aber es gibt einige Einschränkungen.

1) Automatisch entfernt, wenn die Sitzung endet

Da temporäre Tabellen automatisch entfernt werden, wenn die Sitzung endet, eignen sie sich nicht für die dauerhafte Speicherung von Daten.

2) Nicht aus anderen Sitzungen zugänglich

Temporärtabelle können nur innerhalb der Sitzung verwendet werden, die sie erstellt hat, sodass sie nicht mit anderen Benutzern oder Prozessen geteilt werden können.

3) Möglicher Konflikt mit einer regulären Tabelle gleichen Namens

Wenn eine reguläre Tabelle mit demselben Namen existiert, führt das Erstellen einer temporären Tabelle mit diesem Namen dazu, dass die reguläre Tabelle vorübergehend unsichtbar wird, seien Sie also vorsichtig.

CREATE TEMPORARY TABLE users (id INT, name VARCHAR(255));
SELECT * FROM users; -- This query references the temporary table data

Wie oben gezeigt, können Sie nach dem Erstellen einer temporären Tabelle nicht auf die reguläre Tabelle mit demselben Namen zugreifen, bis die temporäre Tabelle entfernt ist. Wählen Sie Tabellennamen sorgfältig aus.

Zusammenfassung

MySQL-Temporärtabelle sind ein praktisches Feature für die vorübergehende Datenspeicherung und Abfrageoptimierung.
Wenn Sie verstehen, wie sie sich von regulären Tabellen unterscheiden und sie angemessen einsetzen, können Sie Daten effizienter verarbeiten.

✔ Kurze Zusammenfassung

• Temporäre Tabellen werden automatisch entfernt, wenn die Sitzung endet
• Im Gegensatz zu regulären Tabellen sind sie pro Sitzung isoliert
• Ideal für temporäre Speicherung und zur Verbesserung der Abfrageleistung
• Nicht geeignet für permanente Speicherung, da Daten verschwinden, wenn die Sitzung endet
• Nicht aus anderen Sitzungen zugänglich und kann mit regulären Tabellen gleichen Namens in Konflikt geraten

2. (Mit Beispielcode) Wie man eine temporäre Tabelle in MySQL erstellt

Einführung

Im vorherigen Abschnitt haben wir das Grundkonzept temporärer Tabellen und deren Unterschiede zu regulären Tabellen erklärt.
In diesem Abschnitt gehen wir darauf ein, wie man eine temporäre Tabelle erstellt und wie man mit den Daten darin arbeitet.

Das Erstellen einer temporären Tabelle ist einfach, aber wenn Sie nicht die korrekte Syntax verwenden, kann das Verhalten unerwartet sein. Dieser Abschnitt erklärt die Grundsyntax, das Erstellen aus einer bestehenden Tabelle und wie man temporäre Tabellen überprüft im Detail.

2-1. Grundsyntax für eine temporäre Tabelle

Um eine temporäre Tabelle zu erstellen, verwenden Sie die Anweisung CREATE TEMPORARY TABLE.

Grundsyntax

CREATE TEMPORARY TABLE table_name (
    column_name data_type [constraints],
    column_name data_type [constraints],
    ...
);

Die Syntax ist fast identisch zu CREATE TABLE, aber das Hinzufügen von TEMPORARY macht sie zu einer temporären Tabelle.

Beispiel: Benutzerinformationen in einer temporären Tabelle speichern

CREATE TEMPORARY TABLE temp_users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    email VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Diese temp_users‑Tabelle ist nur in der aktuellen Sitzung gültig und wird automatisch entfernt, wenn die Sitzung endet.

2-2. Eine temporäre Tabelle basierend auf Daten einer bestehenden Tabelle erstellen

Sie können auch eine temporäre Tabelle basierend auf Daten einer bestehenden Tabelle erstellen.

Syntax

CREATE TEMPORARY TABLE temp_table_name AS
SELECT * FROM existing_table WHERE condition;

Beispiel: Nur aktive Benutzer in einer temporären Tabelle speichern

CREATE TEMPORARY TABLE active_users AS
SELECT id, name, email FROM users WHERE status = 'active';

Diese Methode extrahiert nur Benutzer mit status = 'active' aus der users‑Tabelle und speichert sie in einer neuen temporären Tabelle namens active_users.

Schlüsselpunkte

• Kopiert Daten aus einer bestehenden Tabelle unverändert
• Spaltendatentypen werden automatisch festgelegt
• Indizes werden nicht kopiert, fügen Sie sie bei Bedarf explizit hinzu

2-3. Wie man Daten einer temporären Tabelle überprüft

Tabellen auflisten

SHOW TABLES;

Eine temporäre Tabelle erscheint jedoch nicht in der Liste eines normalen SHOW TABLES.

Struktur einer temporären Tabelle prüfen

DESC temp_users;

oder

SHOW CREATE TABLE temp_users;

Damit können Sie die Spaltenstruktur und Einschränkungen der temporären Tabelle prüfen.

2-4. Daten in eine temporäre Tabelle einfügen

Das Einfügen von Daten in eine temporäre Tabelle ist dasselbe wie bei einer regulären Tabelle.

Daten einfügen

INSERT INTO temp_users (name, email) VALUES 
('Taro Tanaka', 'tanaka@example.com'),
('Hanako Sato', 'sato@example.com');

Daten prüfen

SELECT * FROM temp_users;

Damit wird bestätigt, dass Daten in der temporären Tabelle gespeichert wurden.

2-5. Hinweise beim Erstellen temporärer Tabellen

1) Auf Namenskonflikte achten

Wenn Sie eine temporäre Tabelle mit demselben Namen wie eine reguläre Tabelle erstellen, hat die temporäre Tabelle Vorrang, und die reguläre Tabelle wird vorübergehend nicht zugänglich.

CREATE TEMPORARY TABLE users (id INT, name VARCHAR(50));
SELECT * FROM users; -- This returns data from the temporary table

Aus diesem Grund wird empfohlen, ein Präfix wie „temp_“ für temporäre Tabellennamen zu verwenden.

2) Indizes werden nicht automatisch übernommen

Wenn Sie Daten aus einer bestehenden Tabelle kopieren, werden Indizes nicht automatisch übernommen.
Falls nötig, müssen Sie Indizes explizit hinzufügen.

ALTER TABLE temp_users ADD INDEX (email);

3) Sie benötigen das Privileg, temporäre Tabellen zu erstellen

Um eine temporäre Tabelle zu erstellen, benötigen Sie das Privileg CREATE TEMPORARY TABLES.

GRANT CREATE TEMPORARY TABLES ON database_name.* TO 'user'@'localhost';

Ohne dieses Privileg können Sie keine temporären Tabellen erstellen.

Zusammenfassung

In diesem Abschnitt haben wir erklärt, wie man temporäre Tabellen erstellt.

✔ Kurze Zusammenfassung

• Erstellen Sie eine temporäre Tabelle mit CREATE TEMPORARY TABLE
• Sie können auch eine erstellen, indem Sie Daten aus einer bestehenden Tabelle kopieren
• Wird automatisch entfernt, wenn die Sitzung endet
• Indizes werden nicht automatisch angewendet – seien Sie vorsichtig
• Verwenden Sie ein Präfix wie „temp_“, um Namenskonflikte zu vermeiden
• Sie benötigen das entsprechende Privileg (CREATE TEMPORARY TABLES)

3. Wie man Daten in einer MySQL-Temporärtabelle manipuliert (INSERT, UPDATE, DELETE)

Einführung

Im vorherigen Abschnitt haben wir erklärt, wie man in MySQL eine temporäre Tabelle erstellt.
In diesem Abschnitt erklären wir, wie man Daten in einer temporären Tabelle mit spezifischen SQL-Befehlen einfügt, aktualisiert und löscht.

Temporäre Tabellen unterstützen dieselben Datenoperationen wie reguläre Tabellen,
es gibt jedoch einige wichtige Hinweise, die Sie beachten sollten, und wir werden diese ebenfalls behandeln.

3-1. Daten in eine temporäre Tabelle einfügen (INSERT)

Um Daten zu einer temporären Tabelle hinzuzufügen, verwenden Sie die Anweisung INSERT INTO, genau wie bei einer regulären Tabelle.

Grundsyntax

INSERT INTO temp_table_name (column1, column2, ...) 
VALUES (value1, value2, ...);

Beispiel: Benutzerinformationen hinzufügen

CREATE TEMPORARY TABLE temp_users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    email VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO temp_users (name, email) 
VALUES 
('Taro Tanaka', 'tanaka@example.com'),
('Hanako Sato', 'sato@example.com');

Vorhandene Daten mit INSERT…SELECT einfügen

Sie können auch Daten aus einer bestehenden Tabelle abrufen und in eine temporäre Tabelle einfügen.

INSERT INTO temp_users (id, name, email)
SELECT id, name, email FROM users WHERE status = 'active';

Diese Methode ermöglicht es Ihnen, nur aktive Benutzer in einer temporären Tabelle zu speichern.

3-2. Daten in einer temporären Tabelle aktualisieren (UPDATE)

Um Daten in einer temporären Tabelle zu ändern, verwenden Sie die Anweisung UPDATE.

Grundsyntax

UPDATE temp_table_name 
SET column_name = value
WHERE condition;

Beispiel: Namen eines Benutzers aktualisieren

UPDATE temp_users 
SET name = 'Ichiro Tanaka'
WHERE email = 'tanaka@example.com';

Massenupdate für Zeilen, die einer Bedingung entsprechen

Zum Beispiel, wenn Sie E-Mail-Adressen einer bestimmten Domain zu example.jp ändern möchten, können Sie schreiben:

UPDATE temp_users 
SET email = REPLACE(email, 'example.com', 'example.jp')
WHERE email LIKE '%@example.com';

3-3. Daten aus einer temporären Tabelle löschen (DELETE)

Um Daten zu löschen, verwenden Sie die Anweisung DELETE.

Grundsyntax

DELETE FROM temp_table_name WHERE condition;

Beispiel: Daten eines bestimmten Benutzers löschen

DELETE FROM temp_users WHERE email = 'tanaka@example.com';

Alle Zeilen löschen (Unterschied zu TRUNCATE)

Wenn Sie alle Zeilen löschen möchten, können Sie schreiben:

DELETE FROM temp_users;

Im Gegensatz dazu können Sie bei regulären Tabellen oft alle Zeilen schneller mit TRUNCATE TABLE löschen. Allerdings können Sie TRUNCATE bei einer temporären Tabelle in MySQL nicht verwenden.

TRUNCATE TABLE temp_users; -- Error (cannot be used on temporary tables in MySQL)

Daher müssen Sie zum Entfernen aller Zeilen aus einer temporären Tabelle DELETE verwenden.

3-4. Hinweise beim Manipulieren von Daten in einer temporären Tabelle

1) Daten verschwinden, wenn die Sitzung endet

Eine temporäre Tabelle wird automatisch entfernt, wenn die Sitzung (Verbindung) endet,
sie ist daher nicht für Anwendungsfälle geeignet, die eine dauerhafte Datenspeicherung erfordern.

2) Nicht von anderen Sitzungen aus zugänglich

Eine temporäre Tabelle ist nur innerhalb der Sitzung gültig, die sie erstellt hat, und kann von anderen Sitzungen nicht zugegriffen werden.

SELECT * FROM temp_users;

Wenn Sie dieses SQL in einer anderen Sitzung ausführen, erhalten Sie den Fehler „Table ‚temp_users‘ doesn’t exist“.

3) Indizes auf temporären Tabellen werden nicht automatisch angewendet

Wenn Sie eine Tabelle mit CREATE TEMPORARY TABLE ... AS SELECT ... erstellen,
werden Indizes der Originaltabelle nicht übernommen. Bei Bedarf fügen Sie Indizes manuell mit ALTER TABLE hinzu.

ALTER TABLE temp_users ADD INDEX (email);

Zusammenfassung

In diesem Abschnitt haben wir die Datenmanipulation (INSERT, UPDATE, DELETE) für temporäre Tabellen behandelt.

✔ Kurze Zusammenfassung

• Verwenden Sie INSERT, um Daten hinzuzufügen ( INSERT INTO ... VALUES / INSERT INTO ... SELECT )
• Verwenden Sie UPDATE, um Daten zu ändern (bedingte Updates und Nutzung von REPLACE() )
• Verwenden Sie DELETE, um Daten zu entfernen ( DELETE FROM ... WHERE ; TRUNCATE ist nicht erlaubt)
• Die temporäre Tabelle wird entfernt, wenn die Sitzung endet
• Sie ist von anderen Sitzungen nicht zugänglich
• Indizes werden nicht automatisch übernommen; bei Bedarf manuell hinzufügen

4. Werden MySQL‑Temporärtabellen automatisch gelöscht? Wie man sie manuell löscht

Einführung

Im Gegensatz zu regulären Tabellen wird eine MySQL‑Temporärtabelle (Temporary Table) automatisch entfernt, wenn die Sitzung endet. Es gibt jedoch Fälle, in denen Sie sie manuell löschen müssen.

In diesem Abschnitt erklären wir wie die automatische Entfernung funktioniert und wie man temporäre Tabellen manuell löscht im Detail.

4-1. Wie die automatische Entfernung für temporäre Tabellen funktioniert

1) Wird automatisch entfernt, wenn die Sitzung endet

Eine MySQL‑Temporärtabelle wird automatisch entfernt, wenn die Sitzung (Datenbankverbindung), die sie erstellt hat, endet.
Dadurch müssen Sie sie in der Regel nicht manuell löschen.

Beispiel: Automatische Entfernung, wenn die Sitzung endet

-- Create a temporary table in a new session
CREATE TEMPORARY TABLE temp_users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    email VARCHAR(100)
);

-- Insert data
INSERT INTO temp_users (name, email) VALUES ('Taro Tanaka', 'tanaka@example.com');

-- End the session (disconnect the MySQL client)
EXIT;

An diesem Punkt wird die temporäre Tabelle temp_users automatisch entfernt.

2) Die temporäre Tabelle bleibt bestehen, solange die Sitzung aktiv ist

Da temporäre Tabellen pro Sitzung verwaltet werden, werden sie nicht entfernt, solange die Sitzung geöffnet bleibt.

SELECT * FROM temp_users; -- Data can be retrieved if the session is still active

Mit anderen Worten, die temporäre Tabelle bleibt im Speicher, bis Sie den MySQL‑Client schließen (oder das Programm die Verbindung trennt).

4-2. Wie man eine temporäre Tabelle manuell löscht

Sie können temporäre Tabellen auch manuell löschen.
In MySQL verwenden Sie DROP TEMPORARY TABLE, um eine temporäre Tabelle zu entfernen.

1) Verwenden Sie DROP TEMPORARY TABLE

DROP TEMPORARY TABLE temp_users;

Damit wird die temporäre Tabelle temp_users sofort entfernt.

2) IF EXISTS hinzufügen, um Fehler zu vermeiden

Falls die Tabelle nicht existiert, können Sie IF EXISTS verwenden, um einen Fehler zu vermeiden.

DROP TEMPORARY TABLE IF EXISTS temp_users;

Diese Syntax verhindert Fehler, selbst wenn die Tabelle nicht existiert.

3) Unterschied zu einem normalen DROP TABLE

Wenn Sie versuchen, eine temporäre Tabelle mit einem normalen DROP TABLE zu löschen, erhalten Sie möglicherweise einen Fehler wie den folgenden:

DROP TABLE temp_users;

Fehler:

ERROR 1051 (42S02): Unknown table 'temp_users'

Da MySQL reguläre Tabellen und temporäre Tabellen getrennt verwaltet, müssen Sie DROP TEMPORARY TABLE verwenden, wenn Sie eine temporäre Tabelle löschen.

4-3. Wie man bestätigt, dass eine temporäre Tabelle gelöscht wurde

1) Sie können dies nicht mit SHOW TABLES bestätigen

Eine temporäre Tabelle erscheint nicht in der Ausgabe von SHOW TABLES.

SHOW TABLES;

→ Temporäre Tabellen werden nicht aufgelistet

2) Bestätigung über INFORMATION_SCHEMA

Sie können prüfen, ob eine temporäre Tabelle existiert, indem Sie INFORMATION_SCHEMA abfragen.

SELECT TABLE_NAME 
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_SCHEMA = DATABASE() 
AND TABLE_NAME = 'temp_users';

Wenn diese Abfrage ein Ergebnis zurückgibt, bedeutet das, dass die temporäre Tabelle existiert.

4-4. Hinweise beim Löschen temporärer Tabellen

1) Temporäre Tabellen sind sitzungsabhängig

Sie können temporäre Tabellen mit demselben Namen in mehreren Sitzungen erstellen.
Sie können eine temporäre Tabelle, die von einer anderen Sitzung erstellt wurde, nicht löschen.

Beispiel

-- Created in session A
CREATE TEMPORARY TABLE temp_data (id INT);

-- Attempt to drop in session B
DROP TEMPORARY TABLE temp_data;

Ein Fehler tritt auf:

ERROR 1051 (42S02): Unknown table 'temp_data'

Eine temporäre Tabelle kann nur in der Sitzung gelöscht werden, in der sie erstellt wurde.

2) Möglicher Konflikt mit einer regulären Tabelle gleichen Namens

Hat eine temporäre Tabelle denselben Namen wie eine reguläre Tabelle,
hat die temporäre Tabelle Vorrang, und die reguläre Tabelle wird unsichtbar.

Beispiel

-- A regular table (users) exists
SELECT * FROM users;

-- Create a temporary table with the same name
CREATE TEMPORARY TABLE users (id INT, name VARCHAR(50));

-- This now references the temporary table users
SELECT * FROM users;

Lösung:

• Verwenden Sie ein Präfix wie temp_ für temporäre Tabellen, um Namenskonflikte zu vermeiden.

Zusammenfassung

In diesem Abschnitt haben wir erklärt, wie das Entfernen temporärer Tabellen funktioniert und wie man sie löscht.

✔ Kurze Zusammenfassung

• Temporäre Tabellen werden automatisch entfernt, wenn die Sitzung endet
• Sie bleiben erhalten, solange die Sitzung aktiv ist
• Um manuell zu löschen, verwenden Sie DROP TEMPORARY TABLE
• Fügen Sie IF EXISTS hinzu, um Fehler zu vermeiden
• Sie können temporäre Tabellen nicht mit SHOW TABLES bestätigen
• Sie können eine temporäre Tabelle nur in der Sitzung löschen, die sie erstellt hat
• Verwenden Sie ein Präfix, um Konflikte mit regulären Tabellen gleichen Namens zu vermeiden

5. 5 Praktische Anwendungsfälle für MySQL Temporäre Tabellen (inklusive Leistungsoptimierung)

Einführung

MySQL temporäre Tabellen ermöglichen das Speichern von Zwischendaten und vereinfachen komplexe Abfragen, wodurch die Datenbankleistung verbessert wird.

In diesem Abschnitt stellen wir fünf praktische Anwendungsfälle für temporäre Tabellen vor.
Wir erklären, wie sie in realen Szenarien eingesetzt werden können, zusammen mit Beispiel‑SQL.

5-1. Abfrageleistung optimieren (JOIN‑Aufwand reduzieren)

Problem

Bei der Verarbeitung großer Datensätze kann das direkte Ausführen von JOIN‑Operationen die Leistung beeinträchtigen.

Lösung

Verwenden Sie eine temporäre Tabelle, um Ziel­daten vorab zu filtern, bevor Sie den JOIN ausführen, und reduzieren Sie so den Verarbeitungsaufwand.

Beispiel: Bestelldaten für aktive Nutzer abrufen

-- First, store only active users in a temporary table
CREATE TEMPORARY TABLE temp_active_users AS
SELECT id, name FROM users WHERE status = 'active';

-- Perform JOIN using the temporary table
SELECT o.order_id, t.name, o.total_price
FROM orders o
JOIN temp_active_users t ON o.customer_id = t.id;

Vorteile

• Reduziert den JOIN‑Arbeitsaufwand, indem nur aktive Nutzer anstelle der gesamten users‑Tabelle gezielt werden
• Vereinfacht die Hauptabfrage, verbessert die Lesbarkeit

5-2. Temporäre Aggregationsverarbeitung

Problem

Das wiederholte Ausführen derselben Aggregationsabfrage kann die Leistung verringern.

Lösung

Speichern Sie Aggregationsergebnisse einmal in einer temporären Tabelle, um unnötige wiederholte Berechnungen zu vermeiden.

Beispiel: Monatliche Verkaufsdaten in einer temporären Tabelle speichern

-- Calculate monthly total sales and store in a temporary table
CREATE TEMPORARY TABLE temp_monthly_sales AS
SELECT DATE_FORMAT(order_date, '%Y-%m') AS month, SUM(total_price) AS total_sales
FROM orders
GROUP BY month;

-- Retrieve aggregated results
SELECT * FROM temp_monthly_sales WHERE total_sales > 100000;

Vorteile

• Aggregierte Daten mehrfach wiederverwenden
• Leistung verbessern, indem redundante Berechnungen vermieden werden

5-3. Zwischendaten für Batch‑Verarbeitung speichern

Problem

Beim Durchführen von Massenaktualisierungen oder -löschungen können Fehler während der Verarbeitung die Daten in einem inkonsistenten Zustand belassen.

Lösung

Verwenden Sie eine temporäre Tabelle, um Zwischendaten zu speichern und die Datenkonsistenz zu wahren.

Beispiel: Aktualisieren von Bestelldaten unter bestimmten Bedingungen

-- Store target rows in a temporary table
CREATE TEMPORARY TABLE temp_orders AS
SELECT order_id, total_price FROM orders WHERE status = 'pending';

-- Perform update based on the temporary table
UPDATE orders o
JOIN temp_orders t ON o.order_id = t.order_id
SET o.total_price = t.total_price * 1.1; -- Increase price by 10%

Vorteile

• Sicheres Aktualisieren nur ausgewählter Daten
• Einfache Überprüfung der Daten vor und nach Aktualisierungen

5-4. Verwaltung temporärer Benutzerdaten

Problem

Wenn temporäre benutzerspezifische Daten in einer regulären Tabelle gespeichert werden, können unnötige Daten im Laufe der Zeit anwachsen.

Lösung

Temporäre Tabellen entfernen Daten automatisch, wenn die Sitzung endet, und eliminieren so Wartungsaufwand.

Beispiel: Speichern von Suchergebnissen in einer temporären Tabelle

-- Store user-specific search results
CREATE TEMPORARY TABLE temp_search_results AS
SELECT * FROM products WHERE category = 'electronics';

-- Display search results
SELECT * FROM temp_search_results;

Vorteile

• Daten werden automatisch entfernt, wenn die Sitzung endet
• Temporäre Suchergebnisse können während der Sitzung wiederverwendet werden

5-5. Auswahl zwischen temporären Tabellen und Views

Problem

Beim Optimieren häufig ausgeführter Abfragen fragen Sie sich möglicherweise, ob Sie eine temporäre Tabelle oder eine VIEW verwenden sollen, insbesondere wenn temporäre Datenspeicherung erforderlich ist.

Lösung

• Wenn sich die Daten nicht häufig ändern → Verwenden Sie eine View (VIEW)
• Wenn sich die Daten häufig ändern oder Materialisierung benötigt wird → Verwenden Sie eine temporäre Tabelle

Beispiel: Verwendung einer temporären Tabelle

CREATE TEMPORARY TABLE temp_high_value_customers AS
SELECT customer_id, SUM(total_price) AS total_spent
FROM orders
GROUP BY customer_id
HAVING total_spent > 50000;

SELECT * FROM temp_high_value_customers;

Beispiel: Verwendung einer View

CREATE VIEW high_value_customers AS
SELECT customer_id, SUM(total_price) AS total_spent
FROM orders
GROUP BY customer_id
HAVING total_spent > 50000;

Vorteile

• Temporäre Tabellen speichern Daten physisch, was die Leistung verbessern kann
• Views sind praktisch für die Wiederverwendung von Abfragen, aber die Leistung kann bei großen Datensätzen abnehmen

Zusammenfassung

In diesem Abschnitt haben wir fünf praktische Anwendungsfälle für MySQL-Temporärtabellen vorgestellt.

✔ Kurze Zusammenfassung

1. Optimierung der Abfrageleistung (Reduzierung des JOIN-Aufwands) → Speichern Sie nur die erforderlichen Daten in einer temporären Tabelle, bevor Sie JOIN ausführen
2. Temporäre Aggregationsverarbeitung → Speichern Sie aggregierte Ergebnisse, um wiederholte Berechnungen zu vermeiden
3. Zwischendaten für Stapelverarbeitung → Sichere Handhabung großangelegter Aktualisierungen
4. Verwaltung temporärer Benutzerdaten → Daten werden automatisch entfernt, wenn die Sitzung endet
5. Auswahl zwischen temporären Tabellen und Views → Verwenden Sie temporäre Tabellen für sich ändernde Daten, Views für stabile Abfragewiederverwendung

6. Drei wichtige Vorsichtsmaßnahmen beim sicheren Einsatz von MySQL-Temporärtabellen

Einführung

MySQL-Temporärtabellen arbeiten unabhängig pro Sitzung und werden unter bestimmten Bedingungen automatisch entfernt, was sie zu einer praktischen Funktion macht. Eine unsachgemäße Verwendung kann jedoch zu Leistungsabfall oder unerwarteten Fehlern führen.

In diesem Abschnitt erklären wir drei wichtige Vorsichtsmaßnahmen, um den sicheren Einsatz von Temporärtabellen zu gewährleisten.

6-1. Vorsichtsmaßnahme 1: Nicht zu sehr auf die automatische Entfernung verlassen

Problem

Da Temporärtabellen automatisch entfernt werden, wenn die Sitzung endet, scheint es unnötig zu sein, sie explizit zu droppen. Dies kann jedoch manchmal zu unbeabsichtigten Problemen führen.

Beispiele für Probleme

• Langlaufende Verbindungen verbrauchen weiterhin Speicher
• Wenn eine Sitzung offen bleibt, werden temporäre Tabellen nicht entfernt und verbrauchen weiterhin Datenbankressourcen.
• Das Nicht‑Explizite Löschen kann zu Designfehlern führen
• Wenn ein Batch‑Prozess unerwartet neu verbindet, kann die temporäre Tabelle verschwinden und Fehler verursachen.

Lösung

• Temporäre Tabellen explizit löschen, wenn sie nicht mehr benötigt werden, mit DROP TEMPORARY TABLE
• Bei langlaufenden Verbindungen (z. B. Batch‑Jobs) temporäre Tabellen periodisch löschen

Beispiel: Temporäre Tabelle explizit löschen

DROP TEMPORARY TABLE IF EXISTS temp_users;

Wichtiger Punkt

• Das Hinzufügen von IF EXISTS verhindert Fehler, wenn die Tabelle nicht existiert.

6-2. Vorsichtsmaßnahme 2: Namenskonflikte mit regulären Tabellen vermeiden

Problem

Sie können eine temporäre Tabelle mit demselben Namen wie eine reguläre Tabelle erstellen. Wenn Sie dies tun, wird die reguläre Tabelle vorübergehend unsichtbar.

Beispiel für das Problem

-- A regular users table exists
SELECT * FROM users;

-- Create a temporary table with the same name
CREATE TEMPORARY TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);

-- This now returns data from the temporary table, not the regular one
SELECT * FROM users;

Solange die temporäre Tabelle existiert, ist die reguläre Tabelle mit demselben Namen verborgen, was zu unerwarteten Fehlern bei der Datenabfrage führen kann.

Lösung

• Verwenden Sie ein Präfix wie „temp_“ für temporäre Tabellennamen
• Verwenden Sie eine klare Benennungskonvention, um temporäre und reguläre Tabellen zu unterscheiden

Beispiel: Sichere Erstellung temporärer Tabellen

CREATE TEMPORARY TABLE temp_users (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);

Vorteile

• Die Verwendung des Präfixes temp_ verhindert Konflikte mit der regulären Tabelle users.
• Sie erleichtert das Unterscheiden von Tabellen im Anwendungscode.

6-3. Vorsichtsmaßnahme 3: Indizes und Constraints werden nicht automatisch übernommen

Problem

Wenn Sie eine Tabelle mit CREATE TEMPORARY TABLE ... AS SELECT ... erstellen, werden Indizes und Constraints der Originaltabelle nicht übernommen, was die Leistung mindern kann.

Beispiel für das Problem

-- Regular users table (with indexes)
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    email VARCHAR(100) UNIQUE,
    name VARCHAR(50)
);

-- Create temporary table (indexes are NOT inherited)
CREATE TEMPORARY TABLE temp_users AS
SELECT id, email, name FROM users;

In diesem Fall werden PRIMARY KEY– und UNIQUE-Constraints nicht auf temp_users übertragen, was Suchen verlangsamen und doppelte Daten zulassen kann.

Lösung

• Fügen Sie nach dem Erstellen der temporären Tabelle explizit Indizes hinzu
• Wenn Sie Spalten manuell mit CREATE TEMPORARY TABLE definieren, geben Sie die Indizes bereits beim Erstellen an

Beispiel: Indizes manuell hinzufügen

CREATE TEMPORARY TABLE temp_users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    email VARCHAR(100) UNIQUE,
    name VARCHAR(50)
);

ALTER TABLE temp_users ADD INDEX idx_email (email);

Dieser Ansatz ermöglicht es, eine temporäre Tabelle mit Indizes zu erstellen, die denen der Originaltabelle ähneln.

Zusammenfassung

In diesem Abschnitt haben wir drei wichtige Vorsichtsmaßnahmen für die sichere Verwendung temporärer Tabellen erläutert.

✔ Kurze Zusammenfassung

1. Verlassen Sie sich nicht zu sehr auf die automatische Entfernung

• Löschen Sie temporäre Tabellen explizit mit DROP TEMPORARY TABLE
• Entfernen Sie sie periodisch in langlaufenden Sitzungen

1. Vermeiden Sie Namenskonflikte mit regulären Tabellen

• Wenn eine reguläre Tabelle mit demselben Namen existiert, hat die temporäre Tabelle Vorrang
• Verwenden Sie ein Präfix wie temp_, um sie eindeutig zu unterscheiden

1. Indizes und Constraints werden nicht automatisch übernommen

• Bei CREATE TEMPORARY TABLE ... AS SELECT ... gehen Indizes verloren
• Fügen Sie nach der Erstellung Indizes manuell hinzu

Wenn Sie diese Punkte berücksichtigen, können Sie MySQL-Temporärtabellen sicher nutzen und gleichzeitig die Datenbankleistung verbessern.

7. 10 Häufig gestellte Fragen (FAQ) zu MySQL-Temporärtabellen

Einleitung

In diesem Abschnitt beantworten wir 10 häufig gestellte Fragen zu MySQL-Temporärtabellen.
Wir behandeln wie sie funktionieren, ihre Einschränkungen, Performance-Aspekte und Fehlerbehebung in praktischen Szenarien.

7-1. Fragen zu grundlegenden Spezifikationen

Q1. Kann eine Temporärtabelle von einer anderen Sitzung aus zugänglich sein?

A. Nein, das ist nicht möglich.
Eine Temporärtabelle ist nur innerhalb der Sitzung gültig, die sie erstellt hat, und kann nicht von anderen Sitzungen aus zugänglich sein.

-- Created in Session A
CREATE TEMPORARY TABLE temp_users (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);

-- Attempt to access from Session B (results in error)
SELECT * FROM temp_users;

Fehler:

ERROR 1146 (42S02): Table 'temp_users' doesn't exist

Falls Sie Daten über Sitzungen hinweg teilen müssen, verwenden Sie eine normale Tabelle.

Q2. Werden Temporärtabellen auf der Festplatte gespeichert?

A. Sie werden normalerweise im Speicher gespeichert, können aber unter bestimmten Bedingungen auf die Festplatte verschoben werden.
Falls die Tabellengröße tmp_table_size oder max_heap_table_size überschreitet, kann MySQL die Temporärtabelle auf der Festplatte mit InnoDB oder MyISAM erstellen.

SHOW VARIABLES LIKE 'tmp_table_size';

Um die Performance zu verbessern, konfigurieren Sie tmp_table_size angemessen.

Q3. Kann ich Indizes auf einer Temporärtabelle erstellen?

A. Ja, das können Sie.
Sie können PRIMARY KEY oder INDEX definieren, genau wie bei einer normalen Tabelle.

CREATE TEMPORARY TABLE temp_users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    email VARCHAR(100) UNIQUE,
    name VARCHAR(50)
);

ALTER TABLE temp_users ADD INDEX idx_email (email);

Allerdings werden bei Verwendung von CREATE TEMPORARY TABLE ... AS SELECT ... Indizes nicht übernommen, sodass Sie sie manuell hinzufügen müssen.

7-2. Fragen zu Performance und Verhalten

Q4. Gibt es Änderungen an Temporärtabellen in MySQL 8.0?

A. MySQL 8.0 hat Common Table Expressions (CTEs) mit der WITH-Klausel eingeführt.
Ab MySQL 8.0 können Sie temporäre Ergebnismengen mit CTEs verarbeiten, ohne eine Temporärtabelle explizit zu erstellen.

WITH temp_users AS (
    SELECT id, name FROM users WHERE status = 'active'
)
SELECT * FROM temp_users;

Die Verwendung von CTEs anstelle von Temporärtabellen kann Abfragen vereinfachen und den Speicherverbrauch reduzieren.

Q5. Was ist der Unterschied zwischen einer Temporärtabelle und einer MEMORY-Tabelle?

A. Eine MEMORY-Tabelle besteht über eine Sitzung hinaus, während eine Temporärtabelle das nicht tut.
Eine Temporärtabelle wird entfernt, wenn die Sitzung endet, wohingegen eine MEMORY-Tabelle bis zum Neustart des Servers (oder bis sie explizit gelöscht wird) bestehen bleibt.

CREATE TABLE memory_table (
    id INT PRIMARY KEY,
    name VARCHAR(50)
) ENGINE=MEMORY;

Wann welche verwenden?

• Temporärtabelle: Kurzfristige, sitzungsbezogene Verarbeitung
• MEMORY-Tabelle: Hochgeschwindigkeitszugriff mit serverweiter Persistenz

7-3. Fragen zu Löschung und Fehlerbehebung

Q6. Kann ich eine Temporärtabelle mit DROP TABLE löschen?

A. Nein, Sie müssen DROP TEMPORARY TABLE verwenden.
Verwenden Sie immer DROP TEMPORARY TABLE, wenn Sie eine Temporärtabelle entfernen.

DROP TEMPORARY TABLE temp_users;

Die Verwendung eines normalen DROP TABLE kann zu einem Fehler führen.

Q7. Warum zeigt SHOW TABLES keine Temporärtabellen an?

A. Temporärtabellen werden nicht von SHOW TABLES aufgelistet.
Um ihre Existenz zu prüfen, fragen Sie INFORMATION_SCHEMA ab.

SELECT TABLE_NAME 
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_SCHEMA = DATABASE() 
AND TABLE_NAME = 'temp_users';

Falls kein Ergebnis zurückgegeben wird, könnte die Temporärtabelle bereits entfernt worden sein.

Zusammenfassung

In diesem Abschnitt haben wir 10 häufig gestellte Fragen zu MySQL-Temporärtabellen behandelt.

✔ Quick recap

1. Temporäre Tabellen können nicht von anderen Sitzungen aus zugänglich sein
2. Sie werden im Speicher erstellt, können aber bei Größe auf die Festplatte verschoben werden
3. Indizes müssen manuell definiert werden, wenn AS SELECT verwendet wird
4. CTEs (WITH) sind in MySQL 8.0+ verfügbar
5. Im Gegensatz zu MEMORY-Tabellen verschwinden temporäre Tabellen am Ende der Sitzung
6. Verwenden Sie DROP TEMPORARY TABLE, um sie zu entfernen
7. SHOW TABLES zeigt temporäre Tabellen nicht an

8. Zusammenfassung: Wichtige Punkte für den effektiven Einsatz von MySQL-Temporärtabellen

Einführung

MySQL-Temporärtabellen sind ein mächtiges Werkzeug zum Speichern intermediärer Daten und Optimieren der Abfrageleistung.
Hier fassen wir die im gesamten Leitfaden diskutierten Schlüsselpunkte zusammen.

8-1. Grundlegende Konzepte von MySQL-Temporärtabellen

Was ist eine temporäre Tabelle?

• Existiert unabhängig pro Sitzung
• Wird automatisch entfernt, wenn die Sitzung endet
• Unterstützt INSERT, UPDATE und DELETE wie eine reguläre Tabelle

Grundlegende Erstellungssyntax

CREATE TEMPORARY TABLE temp_users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    email VARCHAR(100)
);

Haupteinsatzbereiche

• Temporäre Datenspeicherung
• Optimierung der Abfrageleistung
• Intermediäre Tabellen für Stapelverarbeitung
• Pro-Benutzer-Verwaltung temporärer Daten

8-2. Vorteile von Temporärtabellen

1) Verbesserung der Abfrageleistung

• Reduzierung der JOIN-Aufwände
• Vornehmen von Aggregationen im Voraus, um wiederholte Berechnungen zu reduzieren
• Vereinfachung von Abfragen durch Ausschluss unnötiger Daten

Beispiel: Reduzierung von JOIN-Aufwänden

CREATE TEMPORARY TABLE temp_active_users AS
SELECT id, name FROM users WHERE status = 'active';

SELECT o.order_id, t.name, o.total_price
FROM orders o
JOIN temp_active_users t ON o.customer_id = t.id;

2) Temporäre Speicherung und sitzungsbasierte Verwaltung

• Automatisch am Ende der Sitzung entfernt
• Ideal für kurzfristige Datenspeicherung
• Unabhängige Datenmanipulation ohne Beeinträchtigung anderer Sitzungen

Beispiel: Temporäre Speicherung von Suchergebnissen

CREATE TEMPORARY TABLE temp_search_results AS
SELECT * FROM products WHERE category = 'electronics';

SELECT * FROM temp_search_results;

3) Sichere Datenaktualisierungen

• Nützlich als intermediäre Tabelle in der Stapelverarbeitung
• Kann als Backup während Datenaktualisierungen dienen
• Effektiv für die Erstellung von Testdatensätzen

Beispiel: Sichere Datenaktualisierung

CREATE TEMPORARY TABLE temp_orders AS
SELECT order_id, total_price FROM orders WHERE status = 'pending';

UPDATE orders o
JOIN temp_orders t ON o.order_id = t.order_id
SET o.total_price = t.total_price * 1.1;

8-3. Nachteile und Vorsichtsmaßnahmen

1) Daten verschwinden am Ende der Sitzung

• Nicht geeignet für permanente Speicherung
• Verwenden Sie reguläre Tabellen für langfristige Persistenz

2) Können nicht über Sitzungen hinweg geteilt werden

• Nicht von anderen Verbindungen zugänglich
• Verwenden Sie reguläre Tabellen, wenn Daten über Benutzer hinweg geteilt werden

3) Indizes und Einschränkungen werden nicht automatisch geerbt

• CREATE TEMPORARY TABLE ... AS SELECT ... erstellt keine Indizes
• Fügen Sie Indizes manuell hinzu, falls erforderlich

  ALTER TABLE temp_users ADD INDEX idx_email (email);
  

8-4. Best Practices für sichere Nutzung

✅ Explizit löschen, wenn nicht mehr benötigt

DROP TEMPORARY TABLE IF EXISTS temp_users;

✅ Namenskonflikte mit regulären Tabellen vermeiden

• Verwenden Sie ein temp_ Präfix

  CREATE TEMPORARY TABLE temp_users (...);
  

✅ Mit Leistung im Sinn designen

• Wenn die Tabelle groß wird und auf die Festplatte verschoben wird, erwägen Sie die Anpassung von tmp_table_size

  SHOW VARIABLES LIKE 'tmp_table_size';
  

8-5. Temporärtabellen vs. Alternativen (Views und CTEs)

Es ist auch wichtig zu berücksichtigen, wann temporäre Tabellen im Vergleich zu Views (VIEW) oder CTEs (Common Table Expressions) verwendet werden sollen.

Zusammenfassung

In diesem Leitfaden haben wir alle wichtigen Aspekte von MySQL-Temporärtabellen behandelt.

✔ Kurze Zusammenfassung

• Temporäre Tabellen werden automatisch entfernt, wenn die Sitzung endet
• Sie helfen, die Leistung zu optimieren, indem sie JOIN- und Aggregationsaufwand reduzieren
• Nützlich für Batch‑Verarbeitung, temporäre Suchergebnisse und Testdaten
• Sie können nicht über Sitzungen hinweg geteilt werden, und Indizes müssen bei Bedarf manuell hinzugefügt werden
• Die Wahl zwischen temporären Tabellen, Views und CTEs ermöglicht flexibles Datenmanagement

Sie haben nun alle Abschnitte des MySQL‑Temporärtabelle‑Leitfadens abgeschlossen! 🎉
Verwenden Sie diese Referenz, um temporäre Tabellen in Ihren MySQL‑Projekten effektiv zu nutzen.