So löschen Sie alle Tabellen in MySQL

Um alle Tabellen zu löschen und die Datenbank selbst zu behalten, erzeugen Sie eine geprüfte DROP TABLE-Anweisung für die BASE TABLE-Objekte des Schemas, führen sie in derselben Sitzung mit vorübergehend deaktivierten FOREIGN_KEY_CHECKS aus und prüfen anschließend das Ergebnis. Dabei werden Definitionen und Daten dauerhaft entfernt: Sichern Sie die Daten und vergewissern Sie sich, dass das gewählte Schema entbehrlich ist.

Verwenden Sie für diese Aufgabe DROP TABLE

DROP TABLE entfernt Tabellen; im Unterschied dazu entfernt DELETE nur Zeilen und TRUNCATE leert eine Tabelle. DROP DATABASE ist weiterreichend und kommt nur infrage, wenn auch der Datenbankcontainer und Schemaobjekte verschwinden sollen. Das Löschen einer Tabelle entfernt außerdem Trigger und führt zu einem impliziten Commit; ein Rollback im Test ist daher keine Absicherung.

Sie benötigen für die entfernten Tabellen das Privileg DROP. Arbeiten Sie in einem benannten Schema, verwenden Sie eine Sicherung oder eine bekannte Testdatenbank und behalten Sie Vorbereitung, Löschen und Wiederherstellung in derselben MySQL-Verbindung.

Eine quotierte Liste von Basistabellen erzeugen

INFORMATION_SCHEMA.TABLES unterscheidet BASE TABLE von Views. Der Filter table_type = 'BASE TABLE' verhindert deshalb, dass zu behaltende Views als Tabellen behandelt werden. Die Abfrage setzt außerdem Backticks um Bezeichner und verdoppelt einen Backtick innerhalb eines Tabellennamens.

Setzen Sie group_concat_max_len passend zur Zahl und Länge der Namen im Schema. Der Ausdruck IF erzeugt bei einem leeren Schema eine harmlose Meldung statt eines DROP TABLE ohne Namen. Prüfen Sie den erzeugten Wert in @drop_sql, bevor Sie ihn in einer produktionsnahen Umgebung ausführen.

FK-verknüpfte Basistabellen in einer Sitzung löschen

Die Reihenfolge von Fremdschlüsseln kann einzelne Löschvorgänge scheitern lassen. Speichern Sie den aktuellen Sitzungswert, deaktivieren Sie Prüfungen nur für diese kontrollierte Sitzung, führen Sie die erzeugte Anweisung aus und stellen Sie den Wert wieder her. Das Folgende ist das exakte MySQL-8.4.11-Fixture dieses Artikels: Es erstellt zwei FK-verknüpfte Tabellen und eine View, löscht nur die Tabellen und gibt die Zählwerte davor und danach aus.

#!/bin/sh
# Verify that a generated DROP TABLE statement removes FK-linked base tables.
# A view is retained deliberately so the fixture also proves the table/view boundary.
set -eu

fixture_tmp=$(mktemp -d)
trap 'kill "$server_pid" 2>/dev/null || true; rm -rf "$fixture_tmp"' EXIT INT TERM
mysqld --no-defaults --initialize-insecure --datadir="$fixture_tmp/data" --user=root >/dev/null 2>&1
mysqld --no-defaults --datadir="$fixture_tmp/data" --socket="$fixture_tmp/mysql.sock" \
  --pid-file="$fixture_tmp/mysql.pid" --skip-networking --user=root >/dev/null 2>&1 &
server_pid=$!
for _ in $(seq 1 30); do
  mysqladmin --no-defaults --socket="$fixture_tmp/mysql.sock" ping >/dev/null 2>&1 && break
  sleep 1
done
mysqladmin --no-defaults --socket="$fixture_tmp/mysql.sock" ping >/dev/null 2>&1

mysql --no-defaults --batch --raw --skip-column-names --socket="$fixture_tmp/mysql.sock" <<'SQL'
CREATE DATABASE drop_all_demo;
USE drop_all_demo;
CREATE TABLE parent (id INT PRIMARY KEY);
CREATE TABLE child (
  id INT PRIMARY KEY,
  parent_id INT,
  CONSTRAINT child_parent_fk FOREIGN KEY (parent_id) REFERENCES parent(id)
);
CREATE VIEW parent_ids AS SELECT id FROM parent;

SELECT COUNT(*)
FROM information_schema.tables
WHERE table_schema = DATABASE() AND table_type = 'BASE TABLE';

SET SESSION group_concat_max_len = 1048576;
SELECT GROUP_CONCAT(
         CONCAT('`', REPLACE(table_name, '`', '``'), '`')
         ORDER BY table_name SEPARATOR ', '
       )
INTO @table_list
FROM information_schema.tables
WHERE table_schema = DATABASE() AND table_type = 'BASE TABLE';
SET @drop_sql = IF(
  @table_list IS NULL,
  'SELECT ''No base tables found''',
  CONCAT('DROP TABLE IF EXISTS ', @table_list)
);

SET @saved_fk_checks = @@SESSION.foreign_key_checks;
SET SESSION foreign_key_checks = 0;
PREPARE drop_statement FROM @drop_sql;
EXECUTE drop_statement;
DEALLOCATE PREPARE drop_statement;
SET SESSION foreign_key_checks = @saved_fk_checks;

SELECT COUNT(*)
FROM information_schema.tables
WHERE table_schema = DATABASE() AND table_type = 'BASE TABLE';
SELECT COUNT(*)
FROM information_schema.tables
WHERE table_schema = DATABASE() AND table_type = 'VIEW';
SELECT @@SESSION.foreign_key_checks;
SQL

2
0
1
1

Die Ausgabe bedeutet: zunächst zwei Basistabellen, nach dem Löschen keine, weiterhin eine View und FOREIGN_KEY_CHECKS wieder im ursprünglich aktivierten Zustand. Diese Strategie entfernt keine Views, gespeicherten Prozeduren, Events, Benutzer oder die Datenbank selbst.

Das Schema nach dem Löschen prüfen

Führen Sie dieselbe INFORMATION_SCHEMA.TABLES-Zählung wie im Fixture aus und erwarten Sie null BASE TABLE-Zeilen. SHOW FULL TABLES FROM your_database ist ebenfalls nützlich, wenn Sie prüfen müssen, ob verbleibende Objekte Views sind. Falls Ihr Skript FOREIGN_KEY_CHECKS ändert, prüfen Sie den Sitzungswert vor dem Schließen der Verbindung; andere Sitzungen übernehmen ihn nicht.

Die Fehlergrenze bei Fremdschlüsseln verstehen

Ohne vorübergehend deaktivierte Prüfungen weist MySQL den Versuch zurück, eine referenzierte Elterntabelle zu löschen. Dieses exakte Grenzfall-Fixture endet mit Fehler 3730 und zeigt, warum eine ungeordnete Liste von DROP TABLE-Befehlen scheitern kann.

#!/bin/sh
# Demonstrate the foreign-key failure when a referenced table is dropped normally.
set -eu

fixture_tmp=$(mktemp -d)
trap 'kill "$server_pid" 2>/dev/null || true; rm -rf "$fixture_tmp"' EXIT INT TERM
mysqld --no-defaults --initialize-insecure --datadir="$fixture_tmp/data" --user=root >/dev/null 2>&1
mysqld --no-defaults --datadir="$fixture_tmp/data" --socket="$fixture_tmp/mysql.sock" \
  --pid-file="$fixture_tmp/mysql.pid" --skip-networking --user=root >/dev/null 2>&1 &
server_pid=$!
for _ in $(seq 1 30); do
  mysqladmin --no-defaults --socket="$fixture_tmp/mysql.sock" ping >/dev/null 2>&1 && break
  sleep 1
done
mysqladmin --no-defaults --socket="$fixture_tmp/mysql.sock" ping >/dev/null 2>&1

mysql --no-defaults --batch --raw --skip-column-names --socket="$fixture_tmp/mysql.sock" <<'SQL'
CREATE DATABASE fk_boundary_demo;
USE fk_boundary_demo;
CREATE TABLE parent (id INT PRIMARY KEY);
CREATE TABLE child (id INT PRIMARY KEY, parent_id INT, CONSTRAINT child_parent_fk FOREIGN KEY (parent_id) REFERENCES parent(id));
DROP TABLE parent;
SQL

Die aufgezeichnete Diagnose lautet ERROR 3730 (HY000): MySQL meldet, dass parent von child_parent_fk in child referenziert wird. Nutzen Sie deaktivierte Prüfungen nicht, um ein unbekanntes Abhängigkeitsproblem zu umgehen, sondern nur nachdem klar ist, dass alle Basistabellen gelöscht werden sollen.

Alternativen für entbehrliche Datenbanken und Kommandozeilenautomatisierung

Bei einem vollständig entbehrlichen Schema kann das Löschen und Neuerstellen der Datenbank einfacher sein, ist aber weiterreichend als das Löschen von Tabellen. Es benötigt passende Privilegien auf Datenbankebene und entfernt Objekte, die die Basistabellenmethode bewusst stehen lässt. Beschreiben Sie es nicht als Methode zum Erhalten von Views, Routinen oder Events.

mysqldump --add-drop-table --no-data kann ein shell-taugliches Löschskript erzeugen; nach Prüfung eignet es sich für Automatisierung. Geben Sie kein Passwort direkt in der Befehlszeile an, prüfen Sie die erzeugten Anweisungen und behandeln Sie Zugangsdaten und Portabilität getrennt von der empfohlenen SQL-Methode.

Grenzen und Fehlerbehebung

Wenn keine Tabellen gelöscht werden, prüfen Sie zuerst das ausgewählte Schema und den Filter BASE TABLE. Ein sehr großes Schema kann die GROUP_CONCAT-Grenze überschreiten; erhöhen Sie sie gezielt oder erzeugen und prüfen Sie Anweisungen stapelweise. Ein Berechtigungsfehler bedeutet fehlendes DROP-Recht. Views bleiben absichtlich bestehen; löschen Sie sie nur separat, wenn das Teil der freigegebenen Bereinigung ist. Da DROP TABLE implizit committet, stellen Sie Daten aus einem Backup wieder her, statt einen Rollback zu erwarten.