556 votes

MySQL DROP toutes les tables, en ignorant les clés étrangères

Existe-t-il un moyen simple et efficace de supprimer toutes les tables d'une base de données MySQL, sans tenir compte des contraintes de clé étrangère qui peuvent s'y trouver ?

8 votes

À moins que vous n'ayez beaucoup d'autres entités, pourquoi ne pas simplement DROP DATABASE et repartir de zéro ?

218 votes

Pour préserver les privilèges des utilisateurs.

7 votes

Je viens de réaliser qu'entre-temps vous avez obtenu une réponse de Dion Truter qui est plus complète que la mienne et je vous suggère de l'accepter à la place. (le "drop tous la partie "tables" n'est pas couverte par la mienne)

698voto

Dion Truter Points 884

J'ai trouvé l'ensemble des déclarations générées utiles, et je recommande ces modifications :

  1. Limitez les gouttes générées à votre base de données comme ceci :

    SELECT concat('DROP TABLE IF EXISTS ', table_name, ';') FROM information_schema.tables WHERE table_schema = 'MyDatabaseName';

Note 1 : Cette opération n'exécute pas les instructions DROP, elle vous en donne simplement la liste. Vous devrez couper et coller la sortie dans votre moteur SQL pour les exécuter.

Note 2 : Si vous avez des VIEWs, vous devrez corriger chaque DROP TABLE `VIEW_NAME` déclaration à DROP VIEW `VIEW_NAME` manuellement.

  1. Note, par http://dev.mysql.com/doc/refman/5.5/en/drop-table.html l'abandon avec la cascade est inutile / trompeur :

"RESTRICT et CASCADE sont autorisés pour faciliter le portage. Dans MySQL 5.5, ils ne font rien."

Par conséquent, pour que les déclarations de chute fonctionnent si vous en avez besoin :

SET FOREIGN_KEY_CHECKS = 0

Cela désactivera les vérifications de l'intégrité référentielle - ainsi, lorsque vous aurez terminé d'effectuer les abandons dont vous avez besoin, vous voudrez réinitialiser la vérification des clés avec

SET FOREIGN_KEY_CHECKS = 1
  1. L'exécution finale devrait ressembler à ceci :

    SET FOREIGN_KEY_CHECKS = 0; -- Your semicolon separated list of DROP statements here SET FOREIGN_KEY_CHECKS = 1;

NB : pour utiliser plus facilement la sortie de SELECT, l'option mysql -B peut aider.

8 votes

@Timmm : J'ai écrit 3 gouttes dans ma réponse - cependant, cela ne les exécute pas non plus. Vous devez les copier depuis Stackoverflow et les coller dans votre MySQL Workbench ou autre. Avec la sélection ci-dessus, vous obtenez toutes les gouttes correspondantes "gratuitement". Il vous suffit de les copier-coller.

4 votes

Oui je sais, mais je voulais l'utiliser dans un script. Ce que j'ai fini par faire en fait c'est DROP DATABASE foo; CREATE DATABASE foo; ce qui n'est pas tout à fait la même chose mais a fonctionné pour moi.

2 votes

Ce n'est pas pratique quand il y a des centaines de tables, mais c'est mieux que rien si la recréation de la base de données n'est pas une option.

160voto

chiccodoro Points 5881

De http://www.devdaily.com/blog/post/mysql/drop-mysql-tables-in-any-order-foreign-keys :

SET FOREIGN_KEY_CHECKS = 0;
drop table if exists customers;
drop table if exists orders;
drop table if exists order_details;
SET FOREIGN_KEY_CHECKS = 1;

(Notez que cela répond à la question de savoir comment désactiver les vérifications des clés étrangères afin de pouvoir déposer les tables dans un ordre arbitraire. Il ne répond pas à la question de savoir comment générer automatiquement des instructions de suppression de table pour toutes les tables existantes et les exécuter dans un seul script. La réponse de Jean fait.)

17 votes

Si vous utilisez MySQL Workbench, vous pouvez éviter de devoir taper tous les noms de table en sélectionnant toutes les tables dans la colonne de gauche, en faisant un clic droit, puis en choisissant l'option "drop tables". Cela générera le SQL que vous pouvez copier et coller entre les instructions SET FOREGIN_KEY_CHECKS - probablement similaire dans d'autres interfaces graphiques.

0 votes

Merci utile, et @chris super utile, devrait ajouter une réponse

127voto

Voici la procédure stockée de SurlyDre modifiée pour que les clés étrangères soient ignorées :

DROP PROCEDURE IF EXISTS `drop_all_tables`;

DELIMITER $$
CREATE PROCEDURE `drop_all_tables`()
BEGIN
    DECLARE _done INT DEFAULT FALSE;
    DECLARE _tableName VARCHAR(255);
    DECLARE _cursor CURSOR FOR
        SELECT table_name 
        FROM information_schema.TABLES
        WHERE table_schema = SCHEMA();
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET _done = TRUE;

    SET FOREIGN_KEY_CHECKS = 0;

    OPEN _cursor;

    REPEAT FETCH _cursor INTO _tableName;

    IF NOT _done THEN
        SET @stmt_sql = CONCAT('DROP TABLE ', _tableName);
        PREPARE stmt1 FROM @stmt_sql;
        EXECUTE stmt1;
        DEALLOCATE PREPARE stmt1;
    END IF;

    UNTIL _done END REPEAT;

    CLOSE _cursor;
    SET FOREIGN_KEY_CHECKS = 1;
END$$

DELIMITER ;

call drop_all_tables(); 

DROP PROCEDURE IF EXISTS `drop_all_tables`;

2 votes

Fonctionne sur les tables, ne parvient pas à supprimer les vues. Cela m'a quand même permis de réduire considérablement ma saisie :) merci.

1 votes

7 ans plus tard, je dirais que vous devriez emballer _tableName avec des guillemets. Sinon, cela échouera sur certaines tables comme group ou d'autres mots-clés.

0 votes

Prenez soin des vues ! SELECT nom_table FROM information_schema.TABLES WHERE table_schema = SCHEMA() AND table_type="BASE TABLE" ;

41voto

nawfal Points 13500

De cette réponse ,

exécuter :

  use `dbName`; --your db name here
  SET FOREIGN_KEY_CHECKS = 0; 
  SET @tables = NULL;
  SET GROUP_CONCAT_MAX_LEN=32768;

  SELECT GROUP_CONCAT('`', table_schema, '`.`', table_name, '`') INTO @tables
  FROM   information_schema.tables 
  WHERE  table_schema = (SELECT DATABASE());
  SELECT IFNULL(@tables, '') INTO @tables;

  SET        @tables = CONCAT('DROP TABLE IF EXISTS ', @tables);
  PREPARE    stmt FROM @tables;
  EXECUTE    stmt;
  DEALLOCATE PREPARE stmt;
  SET        FOREIGN_KEY_CHECKS = 1;

Cette opération supprime les tables de la base de données en cours d'utilisation. Vous pouvez définir la base de données actuelle en utilisant use .


Ou sinon, la réponse acceptée de Dion est plus simple, sauf que vous devez l'exécuter deux fois, d'abord pour obtenir la requête, et ensuite pour exécuter la requête. J'ai fourni quelques back-ticks idiots pour échapper aux caractères spéciaux dans les noms de db et de table.

  SELECT CONCAT('DROP TABLE IF EXISTS `', table_schema, '`.`', table_name, '`;')
  FROM   information_schema.tables
  WHERE  table_schema = 'dbName'; --your db name here

2 votes

Une solution courte et propre. Bien meilleure que l'alternative de la procédure.

20voto

SurlyDre Points 93

Voici une solution basée sur un curseur. C'est un peu long mais cela fonctionne comme un seul lot SQL :

DROP PROCEDURE IF EXISTS `drop_all_tables`;

DELIMITER $$
CREATE PROCEDURE `drop_all_tables`()
BEGIN
    DECLARE _done INT DEFAULT FALSE;
    DECLARE _tableName VARCHAR(255);
    DECLARE _cursor CURSOR FOR
        SELECT table_name 
        FROM information_schema.TABLES
        WHERE table_schema = SCHEMA();
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET _done = TRUE;

    OPEN _cursor;

    REPEAT FETCH _cursor INTO _tableName;

    IF NOT _done THEN
        SET @stmt_sql = CONCAT('DROP TABLE ', _tableName);
        PREPARE stmt1 FROM @stmt_sql;
        EXECUTE stmt1;
        DEALLOCATE PREPARE stmt1;
    END IF;

    UNTIL _done END REPEAT;

    CLOSE _cursor;

END$$

DELIMITER ;

call drop_all_tables(); 

DROP PROCEDURE IF EXISTS `drop_all_tables`;

2 votes

Bien, mais il ne gère pas les clés étrangères malheureusement.

Prograide.com

Prograide est une communauté de développeurs qui cherche à élargir la connaissance de la programmation au-delà de l'anglais.
Pour cela nous avons les plus grands doutes résolus en français et vous pouvez aussi poser vos propres questions ou résoudre celles des autres.

Powered by:

X