MySQL DROP alle Tabellen, Fremdschlüssel ignorieren
Gibt es eine einfache Möglichkeit, alle Tabellen aus einer MySQL-Datenbank zu löschen und dabei eventuelle Fremdschlüsseleinschränkungen zu ignorieren?
Ich fand die generierten drop-Anweisungen nützlich und empfehle diese Tweaks:
- Begrenzen Sie die generierten Ablagen wie folgt auf Ihre Datenbank:
SELECT concat('DROP TABLE IF EXISTS `', table_name, '`;')
FROM information_schema.tables
WHERE table_schema = 'MyDatabaseName';
Hinweis: Dadurch werden die DROP-Anweisungen nicht ausgeführt, sondern es wird lediglich eine Liste von ihnen angezeigt. Sie müssen die Ausgabe ausschneiden und in Ihre SQL-Engine einfügen um sie auszuführen.
- Beachten Sie, dass laut http://dev.mysql.com/doc/refman/5.5/en/drop-table.html das Löschen mit Cascade sinnlos/irreführend ist:
"RESTRICT und CASCADE dürfen die Portierung vereinfachen. In MySQL 5.5 tun sie nichts."
Daher, damit die drop-Anweisungen funktionieren, wenn Sie Folgendes benötigen:
SET FOREIGN_KEY_CHECKS = 0
Dadurch werden die referenziellen Integritätsprüfungen deaktiviert. Wenn Sie mit dem Durchführen der erforderlichen Löschvorgänge fertig sind, möchten Sie die Schlüsselprüfung mit zurücksetzen
SET FOREIGN_KEY_CHECKS = 1
- Die endgültige Ausführung sollte wie folgt aussehen:
SET FOREIGN_KEY_CHECKS = 0;
-- Your semicolon separated list of DROP statements here
SET FOREIGN_KEY_CHECKS = 1;
NB: um die Ausgabe von SELECT einfacher zu nutzen, kann die Option mysql -B helfen.
From 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;
(Beachten Sie, dass hiermit das Deaktivieren von Fremdschlüsselprüfungen zum Löschen der Tabellen in beliebiger Reihenfolge beantwortet wird. Es wird nicht beantwortet, wie automatisch Anweisungen zum Löschen von Tabellen für alle vorhandenen Tabellen generiert und in einem einzigen Skript ausgeführt werden. Jean antwortet .)
Hier ist die gespeicherte Prozedur von SurlyDre, die so geändert wurde, dass Fremdschlüssel ignoriert werden:
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`;
jeder Ansatz beinhaltet viel mehr Arbeit als dieser 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
Von diese Antwort ,
ausführen:
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;
Dadurch werden Tabellen aus der aktuell verwendeten Datenbank gelöscht. Sie können die aktuelle Datenbank mit use einstellen.
Andernfalls ist die von Dion akzeptierte Antwort einfacher, es sei denn, Sie müssen sie zweimal ausführen, um die Abfrage zu erhalten, und zum zweiten, um die Abfrage auszuführen. Ich habe ein paar dumme Häkchen gesetzt, um Sonderzeichen in DB- und Tabellennamen zu umgehen.
SELECT CONCAT('DROP TABLE IF EXISTS `', table_schema, '`.`', table_name, '`;')
FROM information_schema.tables
WHERE table_schema = 'dbName'; --your db name here
Hier ist eine Cursor-basierte Lösung. Ein bisschen langwierig, funktioniert aber als einzelner SQL-Batch:
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`;
Du kannst tun:
select concat('drop table if exists ', table_name, ' cascade;')
from information_schema.tables;
Führen Sie dann die generierten Abfragen aus. Sie löschen jede einzelne Tabelle in der aktuellen Datenbank.
Hier ist eine Hilfe zum Befehl drop table.
Ich habe mir diese Modifikation für Dion Truters Antwort ausgedacht, um es mit vielen Tabellen einfacher zu machen:
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';
Dies gibt das gesamte Objekt in einem Feld zurück, sodass Sie alle Tabellen einmal kopieren und löschen können (verwenden Sie Copy Field Content (unquoted) in Workbench). Wenn Sie viele Tabellen haben, können Sie einige Grenzen für GROUP_CONCAT() erreichen. Wenn ja, erhöhen Sie die Variable max len (und ggf. max_allowed_packet).
Ein Einzeiler zum Löschen aller Tabellen aus einer bestimmten Datenbank:
echo "DATABASE_NAME"| xargs -I{} sh -c "mysql -Nse 'show tables' {}| xargs -I[] mysql -e 'SET FOREIGN_KEY_CHECKS=0; drop table []' {}"
Wenn Sie dies ausführen, werden alle Tabellen aus der Datenbank DATABASE_NAME gelöscht.
Und das Schöne daran ist, dass der Datenbankname nur einmal explizit geschrieben wird.
Hier ist eine automatisierte Möglichkeit, dies über ein Bash-Skript zu tun:
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 -u$user -p$password $dbName -e "show tables" | grep -v Tables_in` ; do echo $i && mysql -u$user -p$password $dbName -e "SET FOREIGN_KEY_CHECKS = 0; drop table $i ; SET FOREIGN_KEY_CHECKS = 1" ; done
Unter Linux (oder einem anderen System, das Piping, Echo und Grep unterstützt) können Sie dies mit einer einzigen Zeile tun:
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;
Ich weiß, dass dies eine alte Frage ist, aber ich denke, dass diese Methode schnell und einfach ist.
Wenn ich über ein Thema google, komme ich immer zu dieser SO Frage. Daher funktioniert hier MySQL-Code, der BEIDE Tabellen und Ansichten löscht:
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`;
In einer Linux-Shell wie 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"
Dadurch werden die Befehle generiert und sofort an eine zweite Client-Instanz weitergeleitet, die die Tabellen löscht.
Das Schlaue wird natürlich von anderen Antworten hier kopiert - ich wollte nur einen kopier- und klebefähigen Einzeiler (ish), um den Job tatsächlich zu erledigen der OP wollte.
Hinweis Natürlich müssen Sie Ihre Anmeldeinformationen auch in diesen mysql-Befehlen (zweimal) eingeben, es sei denn, Sie haben eine sehr niedrige Sicherheitseinstellung. (Oder Sie könnten Ihren mysql-Befehl aliasen, um Ihre creds einzuschließen.)
Setzen Sie hier einfach ein nützliches Kommentar von Jonathan Watt ein, um alle Tabellen zu löschen
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
Es hilft mir und ich hoffe es könnte nützlich sein
In PHP ist es so einfach wie:
$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');
Denken Sie daran, YOURDB in den Namen Ihrer Datenbank und natürlich den Benutzer/Pass zu ändern.
Aufbauend auf der Antwort von @Dion Truter und @Wade Williams löscht das folgende Shell-Skript alle Tabellen, nachdem es zuerst gezeigt hat, was ausgeführt werden soll, und gibt Ihnen die Möglichkeit, mit Strg-C abzubrechen.
#!/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} [email protected] >> ${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} [email protected] < ${TMPFILE}
echo "Exit status is ${?}."
rm ${TMPFILE}
Dies ist ein ziemlich alter Beitrag, aber keine der Antworten hier hat meiner Meinung nach wirklich die Frage beantwortet, also hoffe ich, dass mein Beitrag den Menschen hilft!
Ich habe diese Lösung bei einer anderen Frage gefunden, die für mich sehr gut funktioniert:
mysql -Nse 'show tables' DB_NAME | while read table; do mysql -e "SET FOREIGN_KEY_CHECKS=0; truncate table \`$table\`" DB_NAME; done
Das wird tatsächlich alle Ihre Tabellen in der Datenbank DB_NAME leeren und nicht nur die TRUNCATE Befehlszeile anzeigen.
Hoffe das hilft!
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"
Einfach und klar (kann sein).
Könnte keine ausgefallene Lösung sein, aber das hat mir geholfen und meinen Tag gerettet.
Funktioniert für die Serverversion: 5.6.38 MySQL Community Server (GPL)
Schritte, denen ich gefolgt bin:
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
MySQL Shell
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>
Diese Lösung basiert auf der @ SkyLeach-Antwort, unterstützt jedoch das Löschen von Tabellen mit Fremdschlüsseln.
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