MySQL DROP tutte le tabelle, ignorando le chiavi esterne

C’è un modo molto semplice per eliminare tutte le tabelle da un database MySQL, ignorando eventuali vincoli di chiave esterna che potrebbero esserci?

Ho trovato utile l’insieme di istruzioni di rilascio generato e raccomando queste modifiche:

  1. Limita le gocce generate al tuo database in questo modo:
 SELECT concat('DROP TABLE IF EXISTS `', table_name, '`;') FROM information_schema.tables WHERE table_schema = 'MyDatabaseName'; 

Nota: questo non esegue le istruzioni DROP, ma fornisce solo un elenco di esse. Dovrai tagliare e incollare l’output nel tuo motore SQL per eseguirli.

  1. Nota, per http://dev.mysql.com/doc/refman/5.5/en/drop-table.html , l’eliminazione a cascata è inutile / fuorviante:

“RESTRICT e CASCADE sono autorizzati a semplificare il porting. In MySQL 5.5 non fanno nulla.”

Pertanto, affinché le istruzioni di rilascio funzionino se è necessario:

 SET FOREIGN_KEY_CHECKS = 0 

Questo disabiliterà i controlli di integrità referenziale – così quando hai terminato di eseguire i drop necessari, dovrai reimpostare il key checking con

 SET FOREIGN_KEY_CHECKS = 1 
  1. L’esecuzione finale dovrebbe essere simile a:
 SET FOREIGN_KEY_CHECKS = 0; -- Your semicolon separated list of DROP statements here SET FOREIGN_KEY_CHECKS = 1; 

NB: per usare l’output di SELECT più facilmente, l’opzione mysql -B può aiutare.

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

(Si noti che questo spiega come disabilitare i controlli delle chiavi esterne per poter eliminare le tabelle in ordine arbitrario, non risponde come generare automaticamente istruzioni drop-table per tutte le tabelle esistenti ed eseguirle in un singolo script. lo fa.)

Ecco la stored procedure di SurlyDre modificata in modo che le chiavi esterne vengano ignorate:

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

Da questa risposta ,

eseguire:

  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; 

Ciò elimina le tabelle dal database attualmente in uso. È ansible impostare il database corrente usando l’ use .


In caso contrario, la risposta accettata di Dion è più semplice, tranne che è necessario eseguirla due volte, prima per ottenere la query e poi per eseguire la query. Ho fornito alcuni stupidi back-tick per sfuggire ai caratteri speciali nei nomi db e table.

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

ogni approccio sopra include molto più lavoro di questo 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 

Ecco una soluzione basata su cursore. Un po ‘lungo ma funziona come un singolo batch 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`; 

Tu puoi fare:

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

Quindi eseguire le query generate. Lasceranno ogni singola tabella nel database corrente.

Ecco qualche aiuto sul comando drop table .

Ho trovato questa modifica sulla risposta di Dion Truter per rendere più semplice con molte tabelle:

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

Ciò restituisce l’intera cosa in un campo, quindi è ansible copiare una volta ed eliminare tutte le tabelle (utilizzare Copy Field Content (unquoted) in Workbench). Se hai un sacco di tabelle, potresti colpire alcuni limiti su GROUP_CONCAT() . In tal caso, aumentare la variabile max len (e max_allowed_packet , se necessario).

Ecco un modo automatico per farlo tramite uno script 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 -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 

Se in Linux (o qualsiasi altro sistema che supporta piping, echo e grep) puoi farlo con una sola riga:

 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; 

So che questa è una vecchia domanda, ma penso che questo metodo sia veloce e semplice.

In PHP è facile come:

 $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'); 

Ricordati di cambiare YOURDB con il nome del tuo database e ovviamente con l’utente / pass.

Un unico supporto per eliminare tutte le tabelle da un determinato database:

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

L’esecuzione di questo eliminerà tutte le tabelle dal database DATABASE_NAME.

E una cosa bella di questo è che il nome del database è scritto solo una volta esplicitamente.

In una shell Linux come 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" 

Questo genererà i comandi, quindi li condurrà immediatamente a una seconda istanza client che eliminerà le tabelle.

Il bit intelligente è naturalmente copiato da altre risposte qui – Volevo solo un one-liner copia-e-pasteable (ish) per fare effettivamente il lavoro che l’OP voleva.

Nota ovviamente dovrai inserire le tue credenziali in (due volte) anche in questi comandi mysql, a meno che tu non abbia una configurazione di sicurezza molto bassa. (o si potrebbe alias il tuo comando mysql per includere i tuoi creds.)

Googling su topic mi porta sempre a questa domanda SO quindi qui sta lavorando codice mysql che cancella ENTRAMBI le tabelle e le viste:

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

Basandosi sulla risposta di @Dion Truter e @Wade Williams, il seguente script di shell farà cadere tutte le tabelle, dopo aver prima mostrato cosa sta per essere eseguito, e dandoti la possibilità di abortire usando 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} [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} 

Basta inserire qui qualche utile commento fatto da Jonathan Watt per eliminare tutti i tavoli

 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 

Mi aiuta e spero possa essere utile

Questo è un post piuttosto vecchio, ma nessuna delle risposte qui ha risposto alla domanda a mio avviso, quindi spero che il mio post aiuti le persone!

Ho trovato questa soluzione su un’altra domanda che funziona davvero bene per me:

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

Questo svuoterà effettivamente tutte le tue tabelle nel database DB_NAME e non solo visualizzerà la riga di comando TRUNCATE .

Spero che questo ti aiuti!

Questa soluzione è basata sulla risposta di @SkyLeach ma con il supporto delle tabelle di rilascio con chiavi esterne.

 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 

Io uso il seguente con un server 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!!!!') 

Sostituisci YOUR_DATABASE con il nome del tuo database o rimuovi l’intera istruzione IF (mi piace la sicurezza aggiunta).

La migliore soluzione per me finora

Seleziona Database -> Clic destro -> Attività -> Genera script – aprirà la procedura guidata per la generazione di script. Dopo aver scelto gli oggetti nell’opzione Scripting set fai clic su Advanced Button . In “Script DROP e CREATE” seleziona Script DROP .

Esegui script.