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

Ich habe dies gefunden, um alle Tabellen in einer Datenbank zu löschen:

mysqldump -uUSERNAME -pPASSWORD --add-drop-table --no-data DATABASENAME | grep ^DROP | mysql -uUSERNAME -pPASSWORD DATABASENAME

Nützlich, wenn Sie durch Ihre Hosting-Lösung eingeschränkt sind (Sie können nicht eine ganze Datenbank löschen).

Ich habe es geändert, um die Tabellen zu kürzen. Es gibt kein "--add-truncate-table" für mysqldump, also habe ich es getan:

mysqldump -uUSERNAME -pPASSWORD --add-drop-table --no-data DATABASENAME | grep ^DROP | sed -e 's/DROP TABLE IF EXISTS/TRUNCATE TABLE/g' | mysql -uUSERNAME -pPASSWORD DATABASENAME

funktioniert für mich --edit, Korrektur eines Tippfehlers im letzten Befehl

Answer 2

Ich fand es am einfachsten, so etwas wie den unten stehenden Code zu machen, ersetzen Sie einfach die Tabellennamen durch Ihre eigenen. wichtig m SET FOREIGN_KEY_CHECKS=1;

SET FOREIGN_KEY_CHECKS=0;
TRUNCATE `table1`;
TRUNCATE `table2`;
TRUNCATE `table3`;
TRUNCATE `table4`;
TRUNCATE `table5`;
TRUNCATE `table6`;
TRUNCATE `table7`;
SET FOREIGN_KEY_CHECKS=1;

Answer 3

Dadurch wird der Befehl zum Abschneiden aller Tabellen gedruckt:

SELECT GROUP_CONCAT(Concat('TRUNCATE TABLE ',table_schema,'.',TABLE_NAME) SEPARATOR ';') FROM INFORMATION_SCHEMA.TABLES where table_schema in ('my_db');

Answer 4

1. Um eine Tabelle abzuschneiden, muss man die Fremdschlüssel-Beschränkungen, die den Spalten dieser Tabelle zugeordnet sind, in anderen Tabellen (und zwar in allen Tabellen des betreffenden DB/Schemas) aufheben.
2. Daher müssen zunächst alle Fremdschlüssel-Beschränkungen gelöscht und anschließend die Tabelle gekürzt werden.
3. Optional kann die Optimierungs-Tabelle (in mysql, innodb engine esp) verwendet werden, um nach der Datenkürzung den verwendeten Speicherplatz/Größe für das Betriebssystem zurückzugewinnen.

4. Sobald die Daten abgeschnitten sind, erstellen Sie dieselben Fremdschlüssel-Beschränkungen erneut für dieselbe Tabelle. Nachfolgend finden Sie ein Skript, das das Skript für die Durchführung der oben genannten Vorgänge generiert.

   SELECT CONCAT('ALTER TABLE ',TABLE_SCHEMA,'.',TABLE_NAME,' DROP FOREIGN KEY ',CONSTRAINT_NAME,';') FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
   WHERE CONSTRAINT_TYPE='FOREIGN KEY' AND TABLE_SCHEMA='<TABLE SCHEMA>'
   UNION
   SELECT CONCAT('TRUNCATE TABLE ',TABLE_SCHEMA,'.',TABLE_NAME,';') FROM INFORMATION_SCHEMA.TABLES
   WHERE TABLE_SCHEMA='<TABLE SCHEMA>' AND TABLE_TYPE='BASE TABLE'
   UNION
   SELECT CONCAT('OPTIMIZE TABLE ',TABLE_SCHEMA,'.',TABLE_NAME,';') FROM INFORMATION_SCHEMA.TABLES
   WHERE TABLE_SCHEMA='<TABLE SCHEMA>' AND TABLE_TYPE='BASE TABLE'
   UNION
   SELECT CONCAT('ALTER TABLE ',TABLE_SCHEMA,'.',TABLE_NAME,' ADD CONSTRAINT ',CONSTRAINT_NAME,' FOREIGN KEY(',COLUMN_NAME,')',' REFERENCES ',REFERENCED_TABLE_NAME,'(',REFERENCED_COLUMN_NAME,');') FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
   WHERE CONSTRAINT_NAME LIKE 'FK%' AND TABLE_SCHEMA='<TABLE SCHEMA>'
   INTO OUTFILE "C:/DB Truncate.sql" LINES TERMINATED BY '\n';

Führen Sie nun das generierte Skript Db Truncate.sql aus

Vorteile. 1) Rückgewinnung von Speicherplatz 2) Die DB/Schema muss nicht gelöscht und mit der gleichen Struktur neu erstellt werden

Nachteilig. 1) FK-Beschränkungen sollten Namen in der Tabelle sein, deren Name "FK" im Beschränkungsnamen enthält.

Answer 5

Hier ist meine Variante, um "eine Anweisung zu haben, um sie alle abzuschneiden".

Zunächst verwende ich eine separate Datenbank mit dem Namen "util" für meine gespeicherten Hilfsprozeduren. Der Code meiner gespeicherten Prozedur, um alle Tabellen abzuschneiden, lautet:

DROP PROCEDURE IF EXISTS trunctables;
DELIMITER ;;
CREATE  PROCEDURE trunctables(theDb varchar(64))
BEGIN
    declare tname varchar(64);
    declare tcursor CURSOR FOR 
    SELECT table_name FROM information_schema.tables WHERE table_type <> 'VIEW' AND table_schema = theDb;
    SET FOREIGN_KEY_CHECKS = 0; 
    OPEN tcursor;
    l1: LOOP
        FETCH tcursor INTO tname;
        if tname = NULL then leave l1; end if;
        set @sql = CONCAT('truncate `', theDB, '`.`', tname, '`');
        PREPARE stmt from @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    END LOOP l1;
    CLOSE tcursor;
    SET FOREIGN_KEY_CHECKS = 1; 
END ;;
DELIMITER ;

Sobald Sie diese gespeicherte Prozedur in Ihrer util-Datenbank haben, können Sie sie wie folgt aufrufen

call util.trunctables('nameofdatabase');

was jetzt genau eine Aussage ist :-)