MySQL DROP wszystkie tabele, ignorując klucze obce

Czy istnieje dobry łatwy sposób na usunięcie wszystkich tabel z bazy danych MySQL, ignorując wszelkie ograniczenia dotyczące kluczy obcych, które mogą tam być?

Uważam, że wygenerowany zestaw instrukcji drop jest użyteczny i polecam następujące poprawki:

1. Ogranicz generowane upuszczenia do bazy danych w następujący sposób:

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

Uwaga 1: To nie wykonuje instrukcji DROP, po prostu daje ci ich listę.Będziesz musiał wyciąć i wkleić dane wyjściowe do silnika SQL, aby je wykonać.

Uwaga 2: Jeśli masz WIDOKI, będziesz musiał je poprawić DROP TABLE `VIEW_NAME`DROP VIEW `VIEW_NAME` ręcznie instrukcję .

2. Uwaga: według http://dev.mysql.com/doc/refman/5.5/en/drop-table.html upuszczanie kaskadą jest bezcelowe / mylące:

„RESTRICT i CASCADE mogą ułatwiać portowanie. W MySQL 5.5 nic nie robią”.

Dlatego, aby instrukcje drop działały, jeśli potrzebujesz:

SET FOREIGN_KEY_CHECKS = 0

Spowoduje to wyłączenie sprawdzania integralności referencyjnej - więc po zakończeniu upuszczania, którego potrzebujesz, będziesz musiał zresetować sprawdzanie klucza za pomocą

SET FOREIGN_KEY_CHECKS = 1

3. Ostateczne wykonanie powinno wyglądać następująco:

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

Uwaga: aby łatwiej użyć wyjścia SELECT, może pomóc opcja mysql -B.

Od 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;

(Zauważ, że to wyjaśnia, jak wyłączyć sprawdzanie klucza obcego, aby móc upuścić tabele w dowolnej kolejności. Nie odpowiada, jak automatycznie generować instrukcje drop-table dla wszystkich istniejących tabel i wykonywać je w jednym skrypcie. Odpowiedź Jeana robi.)

Oto zmodyfikowana procedura przechowywana SurlyDre, tak aby klucze obce były ignorowane:

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`;

każde powyższe podejście obejmuje znacznie więcej pracy niż ta AFAICT ...

( mysqldump --add-drop-table --no-data -u root -p database | grep 'DROP TABLE' ) > ./drop_all_tables.sql
mysql -u root -p database < ./drop_all_tables.sql

Z tej odpowiedzi ,

wykonać:

  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;

Spowoduje to usunięcie tabel z aktualnie używanej bazy danych. Możesz ustawić bieżącą bazę danych za pomocą use.

W przeciwnym razie zaakceptowana odpowiedź Diona jest prostsza, z tym wyjątkiem, że musisz ją wykonać dwa razy, po pierwsze, aby uzyskać zapytanie, a po drugie, aby wykonać zapytanie. Podałem kilka głupich tyknięć, aby uciec od znaków specjalnych w nazwach db i tabel.

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

Oto rozwiązanie oparte na kursorze. Trochę długa, ale działa jako pojedyncza partia 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`;

Możesz to zrobić:

select concat('drop table if exists ', table_name, ' cascade;')
  from information_schema.tables;

Następnie uruchom wygenerowane zapytania. Porzucą każdą tabelę w bieżącej bazie danych.

Oto pomoc dotycząca drop tablepoleceń.

Wymyśliłem tę modyfikację w odpowiedzi Dion Truter, aby ułatwić wiele stolików:

SET GROUP_CONCAT_MAX_LEN = 10000000;
SELECT CONCAT('SET FOREIGN_KEY_CHECKS=0;\n', 
              GROUP_CONCAT(CONCAT('DROP TABLE IF EXISTS `', table_name, '`')
                           SEPARATOR ';\n'),
              ';\nSET FOREIGN_KEY_CHECKS=1;')
FROM information_schema.tables
WHERE table_schema = 'SchemaName';

Zwraca to całą rzecz w jednym polu, więc możesz skopiować raz i usunąć wszystkie tabele (użyj Copy Field Content (unquoted)w Workbench). Jeśli masz dużo stolików, możesz przekroczyć pewne limity GROUP_CONCAT(). Jeśli tak, zwiększ maksymalną wartość zmiennej len (i max_allowed_packet, jeśli to konieczne).

Googling na ten temat zawsze prowadzi mnie do tego SO pytania, więc tutaj działa kod mysql, który usuwa OBA tabele i widoki:

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_sql1 = CONCAT('DROP TABLE IF EXISTS ', _tableName);
        SET @stmt_sql2 = CONCAT('DROP VIEW IF EXISTS ', _tableName);

        PREPARE stmt1 FROM @stmt_sql1;
        PREPARE stmt2 FROM @stmt_sql2;

        EXECUTE stmt1;
        EXECUTE stmt2;

        DEALLOCATE PREPARE stmt1;
        DEALLOCATE PREPARE stmt2;
    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`;

Jeden linijka do usunięcia wszystkich tabel z danej bazy danych:

echo "DATABASE_NAME"| xargs -I{} sh -c "mysql -Nse 'show tables' {}| xargs -I[] mysql -e 'SET FOREIGN_KEY_CHECKS=0; drop table []' {}"

Uruchomienie tego spowoduje usunięcie wszystkich tabel z bazy danych DATABASE_NAME.

Zaletą tego jest to, że nazwa bazy danych jest zapisywana jednoznacznie tylko raz.

Oto zautomatyzowany sposób, aby to zrobić za pomocą skryptu bash:

host=$1
dbName=$2
user=$3
password=$4

if [ -z "$1" ]
then
    host="localhost"
fi

# drop all the tables in the database
for i in `mysql -h$host -u$user -p$password $dbName -e "show tables" | grep -v Tables_in` ; do  echo $i && mysql -h$host -u$user -p$password $dbName -e "SET FOREIGN_KEY_CHECKS = 0; drop table $i ; SET FOREIGN_KEY_CHECKS = 1" ; done

Jeśli w systemie Linux (lub innym systemie obsługującym potokowanie, echo i grep) możesz to zrobić za pomocą jednej linii:

echo "SET FOREIGN_KEY_CHECKS = 0;" > temp.txt; \
mysqldump -u[USER] -p[PASSWORD] --add-drop-table --no-data [DATABASE] | grep ^DROP >> temp.txt; \
echo "SET FOREIGN_KEY_CHECKS = 1;" >> temp.txt; \
mysql -u[USER] -p[PASSWORD] [DATABASE] < temp.txt;

Wiem, że to stare pytanie, ale myślę, że ta metoda jest szybka i prosta.

W php jest to tak proste jak:

$pdo = new PDO('mysql:dbname=YOURDB', 'root', 'root');

$pdo->exec('SET FOREIGN_KEY_CHECKS = 0');

$query = "SELECT concat('DROP TABLE IF EXISTS ', table_name, ';')
          FROM information_schema.tables
          WHERE table_schema = 'YOURDB'";

foreach($pdo->query($query) as $row) {
    $pdo->exec($row[0]);
}

$pdo->exec('SET FOREIGN_KEY_CHECKS = 1');

Pamiętaj tylko, aby zmienić YOURDB na nazwę bazy danych i oczywiście na użytkownika / hasło.

W powłoce Linux, takiej jak bash / zsh:

DATABASE_TO_EMPTY="your_db_name";
{ echo "SET FOREIGN_KEY_CHECKS = 0;" ; \
  mysql "$DATABASE_TO_EMPTY" --skip-column-names -e \
  "SELECT concat('DROP TABLE IF EXISTS ', table_name, ';') \
   FROM information_schema.tables WHERE table_schema = '$DATABASE_TO_EMPTY';";\
  } | mysql "$DATABASE_TO_EMPTY"

Spowoduje to wygenerowanie poleceń, a następnie natychmiast potokuje je do drugiej instancji klienta, która usunie tabele.

Sprytny kawałek jest oczywiście kopiowany z innych odpowiedzi tutaj - chciałem tylko kopiować i wklejać jedno-liniowy (ish), aby faktycznie wykonać zadanie, które chciał OP.

Pamiętaj oczywiście, że w tych komendach mysql będziesz musiał (dwukrotnie) podać swoje dane uwierzytelniające, chyba że masz bardzo niskie ustawienia bezpieczeństwa. (lub możesz użyć aliasu polecenia mysql, aby dołączyć swoje poświadczenia.)

Wystarczy umieścić tutaj użyteczny komentarz Jonathana Watta, aby usunąć wszystkie stoły

MYSQL="mysql -h HOST -u USERNAME -pPASSWORD DB_NAME"
$MYSQL -BNe "show tables" | awk '{print "set foreign_key_checks=0; drop table `" $1 "`;"}' | $MYSQL
unset MYSQL

Pomaga mi i mam nadzieję, że może się przydać

Usuń wszystkie tabele z bazy danych za pomocą jednego wiersza z wiersza polecenia:

mysqldump -u [user_name] -p[password] -h [host_name] --add-drop-table --no-data [database_name] | grep ^DROP | mysql -u [user_name] -p[password] -h [host_name] [database_name]

Gdzie [nazwa_użytkownika], [hasło], [nazwa_hosta] i [nazwa_bazy_danych] muszą zostać zastąpione rzeczywistymi danymi (użytkownik, hasło, nazwa hosta, nazwa bazy danych).

To dość stary post, ale moim zdaniem żadna z odpowiedzi tutaj nie odpowiada na pytanie, więc mam nadzieję, że mój post pomoże ludziom!

Znalazłem to rozwiązanie w innym pytaniu, które działa dla mnie naprawdę dobrze:

mysql -Nse 'show tables' DB_NAME | while read table; do mysql -e "SET FOREIGN_KEY_CHECKS=0; truncate table \`$table\`" DB_NAME; done

To faktycznie opróżni wszystkie tabele w bazie danych DB_NAME, a nie tylko wyświetliTRUNCATE wiersz poleceń.

Mam nadzieję że to pomoże!

Opierając się na odpowiedziach @Dion Truter i @Wade Williams, następujący skrypt powłoki usunie wszystkie tabele, po uprzednim pokazaniu, co ma zamiar uruchomić, i daje szansę przerwania za pomocą Ctrl-C.

#!/bin/bash

DB_HOST=xxx
DB_USERNAME=xxx
DB_PASSWORD=xxx
DB_NAME=xxx

CMD="mysql -sN -h ${DB_HOST} -u ${DB_USERNAME} -p${DB_PASSWORD} ${DB_NAME}"

# Generate the drop statements
TMPFILE=/tmp/drop-${RANDOM}.sql
echo 'SET FOREIGN_KEY_CHECKS = 0;' > ${TMPFILE}
${CMD} $@ >> ${TMPFILE} << ENDD
    SELECT concat('DROP TABLE IF EXISTS \`', table_name, '\`;')
    FROM information_schema.tables
    WHERE table_schema = '${DB_NAME}';
ENDD
echo 'SET FOREIGN_KEY_CHECKS = 1;' >> ${TMPFILE}

# Warn what we are about to do
echo
cat ${TMPFILE}
echo
echo "Press ENTER to proceed (or Ctrl-C to abort)."
read

# Run the SQL
echo "Dropping tables..."
${CMD} $@ < ${TMPFILE}
echo "Exit status is ${?}."
rm ${TMPFILE}

To rozwiązanie opiera się na odpowiedzi @SkyLeach, ale obsługuje usuwanie tabel z kluczami obcymi.

echo "SET FOREIGN_KEY_CHECKS = 0;" > ./drop_all_tables.sql
mysqldump --add-drop-table --no-data -u user -p dbname | grep 'DROP TABLE' >> ./drop_all_tables.sql
echo "SET FOREIGN_KEY_CHECKS = 1;" >> ./drop_all_tables.sql
mysql -u user -p dbname < ./drop_all_tables.sql

DB="your database name" \
    && mysql $DB < "SET FOREIGN_KEY_CHECKS=0" \
    && mysqldump --add-drop-table --no-data $DB | grep 'DROP TABLE' | grep -Ev "^$" | mysql $DB \
    && mysql $DB < "SET FOREIGN_KEY_CHECKS=1"

Proste i jasne (może być).

To może nie być wymyślne rozwiązanie, ale zadziałało to i uratowało mi dzień.

Pracował dla wersji serwera: 5.6.38 MySQL Community Server (GPL)

Kroki, które wykonałem:

 1. generate drop query using concat and group_concat.
 2. use database
 3. disable key constraint check
 4. copy the query generated from step 1
 5. enable key constraint check
 6. run show table

Powłoka MySQL

mysql> SYSTEM CLEAR;
mysql> SELECT CONCAT('DROP TABLE IF EXISTS `', GROUP_CONCAT(table_name SEPARATOR '`, `'), '`;') AS dropquery FROM information_schema.tables WHERE table_schema = 'emall_duplicate';
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| dropquery                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| DROP TABLE IF EXISTS `admin`, `app`, `app_meta_settings`, `commission`, `commission_history`, `coupon`, `email_templates`, `infopages`, `invoice`, `m_pc_xref`, `member`, `merchant`, `message_templates`, `mnotification`, `mshipping_address`, `notification`, `order`, `orderdetail`, `pattributes`, `pbrand`, `pcategory`, `permissions`, `pfeatures`, `pimage`, `preport`, `product`, `product_review`, `pspecification`, `ptechnical_specification`, `pwishlist`, `role_perms`, `roles`, `settings`, `test`, `testanother`, `user_perms`, `user_roles`, `users`, `wishlist`; |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> USE emall_duplicate;
Database changed
mysql> SET FOREIGN_KEY_CHECKS = 0;                                                                                                                                                   Query OK, 0 rows affected (0.00 sec)

// copy and paste generated query from step 1
mysql> DROP TABLE IF EXISTS `admin`, `app`, `app_meta_settings`, `commission`, `commission_history`, `coupon`, `email_templates`, `infopages`, `invoice`, `m_pc_xref`, `member`, `merchant`, `message_templates`, `mnotification`, `mshipping_address`, `notification`, `order`, `orderdetail`, `pattributes`, `pbrand`, `pcategory`, `permissions`, `pfeatures`, `pimage`, `preport`, `product`, `product_review`, `pspecification`, `ptechnical_specification`, `pwishlist`, `role_perms`, `roles`, `settings`, `test`, `testanother`, `user_perms`, `user_roles`, `users`, `wishlist`;
Query OK, 0 rows affected (0.18 sec)

mysql> SET FOREIGN_KEY_CHECKS = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW tables;
Empty set (0.01 sec)

mysql> 

Korzystam z następujących na serwerze MSSQL:

if (DB_NAME() = 'YOUR_DATABASE') 
begin
    while(exists(select 1 from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where CONSTRAINT_TYPE='FOREIGN KEY'))
    begin
         declare @sql nvarchar(2000)
         SELECT TOP 1 @sql=('ALTER TABLE ' + TABLE_SCHEMA + '.[' + TABLE_NAME + '] DROP CONSTRAINT [' + CONSTRAINT_NAME + ']')
         FROM information_schema.table_constraints
         WHERE CONSTRAINT_TYPE = 'FOREIGN KEY'
         exec (@sql)
         PRINT @sql
    end

    while(exists(select 1 from INFORMATION_SCHEMA.TABLES))
    begin
         declare @sql2 nvarchar(2000)
         SELECT TOP 1 @sql2=('DROP TABLE ' + TABLE_SCHEMA + '.[' + TABLE_NAME + ']')
         FROM INFORMATION_SCHEMA.TABLES
        exec (@sql2)
        PRINT @sql2
    end
end
else
    print('Only run this script on the development server!!!!')

Zamień YOUR_DATABASE na nazwę bazy danych lub usuń całą instrukcję IF (podoba mi się dodatkowe bezpieczeństwo).

Najlepsze rozwiązanieJak dotąd dla mnie

Wybierz Baza danych -> Kliknij prawym przyciskiem myszy -> Zadania -> Generuj skrypty - otworzy się kreator do generowania skryptów. Po wybraniu obiektów w zestawie opcji Skrypty kliknij przycisk Zaawansowane . W obszarze „Skrypt DROP i UTWÓRZ” wybierz Skrypt DROP .

Uruchom skrypt.