Alle Tabellen in einer MySQL-Datenbank mit einem einzigen Befehl abschneiden?

Question

Gibt es eine Abfrage (einen Befehl), um alle Tabellen in einer Datenbank in einem Arbeitsgang abzuschneiden? Ich möchte wissen, ob ich dies mit einer einzigen Abfrage tun kann.

Answer 1

Wenn Sie SQL Server 2005 verwenden, gibt es eine versteckte gespeicherte Prozedur, mit der Sie einen Befehl oder eine Reihe von Befehlen für alle Tabellen in einer Datenbank ausführen können. So rufen Sie auf TRUNCATE TABLE mit dieser gespeicherten Prozedur:

EXEC [sp_MSforeachtable] @command1="TRUNCATE TABLE ?"

Hier ist ein guter Artikel, der weitere Einzelheiten enthält.

Für MySql können Sie jedoch mysqldump verwenden und die --add-drop-tables et --no-data Optionen, um alle Tabellen zu löschen und zu erstellen, wobei die Daten ignoriert werden. so:

mysqldump -u[USERNAME] -p[PASSWORD] --add-drop-table --no-data [DATABASE]

mysqldump-Verwendungsanleitung von dev.mysql

Answer 2

Hier für ich weiß hier

   SELECT Concat('TRUNCATE TABLE ',table_schema,'.',TABLE_NAME, ';') 
    FROM INFORMATION_SCHEMA.TABLES where  table_schema in ('databasename1','databasename2');

Wenn eine übergeordnete Zeile nicht gelöscht oder aktualisiert werden kann: eine Fremdschlüssel-Beschränkung schlägt fehl

Das passiert, wenn es Tabellen mit Fremdschlüsselverweisen auf die Tabelle gibt, die Sie zu löschen/abzuschneiden versuchen.

Bevor Sie Tabellen abschneiden, müssen Sie nur Folgendes tun:

SET FOREIGN_KEY_CHECKS=0;

Kürzen Sie Ihre Tabellen und ändern Sie sie zurück in

SET FOREIGN_KEY_CHECKS=1; 

diesen php-Code verwenden

    $truncate = mysql_query("SELECT Concat('TRUNCATE TABLE ',table_schema,'.',TABLE_NAME, ';') as tables_query FROM INFORMATION_SCHEMA.TABLES where table_schema in ('databasename')");

    while($truncateRow=mysql_fetch_assoc($truncate)){

        mysql_query($truncateRow['tables_query']);

    }
?>

Details hier prüfen Link

Answer 3

Verwenden Sie dies und bilden Sie die Abfrage

SELECT Concat('TRUNCATE TABLE ',table_schema,'.',TABLE_NAME, ';') 
FROM INFORMATION_SCHEMA.TABLES where  table_schema in (db1,db2)
INTO OUTFILE '/path/to/file.sql';

Verwenden Sie nun diese Abfrage

mysql -u username -p </path/to/file.sql

wenn Sie eine Fehlermeldung wie diese erhalten

ERROR 1701 (42000) at line 3: Cannot truncate a table referenced in a foreign key constraint

Am einfachsten ist es, wenn Sie am Anfang Ihrer Datei folgende Zeile einfügen

SET FOREIGN_KEY_CHECKS=0;

was besagt, dass wir die Fremdschlüssel-Beschränkungen nicht überprüfen wollen, während wir diese Datei durchgehen.

Es werden alle Tabellen in den Datenbanken db1 und bd2 abgeschnitten.

Answer 4

An von battousaix ist perfekt! Ich habe gerade seine Antwort verwendet und den endgültigen Arbeitsbefehl zum Abschneiden von Datenbanktabellen erstellt.

mysql -P 3306 -h YOUR_HOST_HERE -u YOUR_USERNAME_HERE -pYOUR_PASSWORD_HERE -Nse 'show tables' DATABASE_NAME | while read table; do mysql -P 3306 -h YOUR_HOST_HERE -u YOUR_USERNAME_HERE -pYOUR_PASSWORD_HERE -e "SET FOREIGN_KEY_CHECKS = 0; truncate table $table" DATABASE_NAME; done

Der obige Befehl funktioniert perfekt für den MySQL-Server.

Außerdem ist es inbegriffen.

SET FOREIGN_KEY_CHECKS = 0

Answer 5

Das hat bei mir funktioniert. Ändern Sie die Datenbank, den Benutzernamen und das Passwort entsprechend.

mysql -Nse 'show tables' -D DATABASE -uUSER -pPWD | while read table; do echo "SET FOREIGN_KEY_CHECKS = 0;drop table \`$table\`;SET FOREIGN_KEY_CHECKS = 1;"; done | mysql DATABASE -uUSER -pPWD