Tronca tutte le tabelle in un database MySQL in un unico comando?

Esiste una query (comando) per troncare tutte le tabelle in un database in un’unica operazione? Voglio sapere se posso farlo con una singola query.

Drop (cioè rimuovere tabelle)

mysql -Nse 'show tables' DATABASE_NAME | while read table; do mysql -e "drop table $table" DATABASE_NAME; done 

Troncare (cioè tabelle vuote)

 mysql -Nse 'show tables' DATABASE_NAME | while read table; do mysql -e "truncate table $table" DATABASE_NAME; done 

troncare più tabelle di database sull’istanza Mysql

 SELECT Concat('TRUNCATE TABLE ',table_schema,'.',TABLE_NAME, ';') FROM INFORMATION_SCHEMA.TABLES where table_schema in ('db1_name','db2_name'); 

Usa risultato query per troncare le tabelle

Nota: potresti avere questo errore:

 ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails 

Ciò accade se ci sono tabelle con riferimenti a chiavi esterne alla tabella che stai tentando di eliminare / troncare.

Prima di troncare le tabelle Tutto ciò che devi fare è:

 SET FOREIGN_KEY_CHECKS=0; 

Tronca le tue tabelle e cambiala in

 SET FOREIGN_KEY_CHECKS=1; 

Usa phpMyAdmin in questo modo:

Vista Database => Controlla tutto (tabelle) => Vuoto

Se si desidera ignorare i controlli delle chiavi esterne, è ansible deselezionare la casella che dice:

[] Abilita i controlli delle chiavi esterne

Avrai bisogno di eseguire atleast versione 4.5.0 o successiva per ottenere questa casella di controllo.

Non è MySQL CLI-fu, ma hey, funziona!

MS SQL Server 2005+ (rimuovere PRINT per l’esecuzione effettiva …)

 EXEC sp_MSforeachtable 'PRINT ''TRUNCATE TABLE ?''' 

Se la piattaforma di database supporta le viste INFORMATION_SCHEMA, prendere i risultati della seguente query ed eseguirli.

 SELECT 'TRUNCATE TABLE ' + TABLE_NAME FROM INFORMATION_SCHEMA.TABLES 

Prova questo per MySQL:

 SELECT Concat('TRUNCATE TABLE ', TABLE_NAME) FROM INFORMATION_SCHEMA.TABLES 

L’aggiunta di un punto e virgola a Concat semplifica l’utilizzo, ad esempio, da mysql workbench.

 SELECT Concat('TRUNCATE TABLE ', TABLE_NAME, ';') FROM INFORMATION_SCHEMA.TABLES 

Ho trovato questo per eliminare tutte le tabelle in un database:

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

Utile se sei limitato dalla soluzione di hosting (non in grado di eliminare un intero database).

L’ho modificato per troncare i tavoli. Non c’è “–add-truncate-table” per mysqldump, quindi ho fatto:

 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 

funziona per me – edita, correggendo un refuso nell’ultimo comando

Questo stamperà il comando per troncare tutte le tabelle:

 SELECT GROUP_CONCAT(Concat('TRUNCATE TABLE ',table_schema,'.',TABLE_NAME) SEPARATOR ';') FROM INFORMATION_SCHEMA.TABLES where table_schema in ('my_db'); 
 SET FOREIGN_KEY_CHECKS = 0; SELECT @str := CONCAT('TRUNCATE TABLE ', table_schema, '.', table_name, ';') FROM information_schema.tables WHERE table_type = 'BASE TABLE' AND table_schema IN ('db1_name','db2_name'); PREPARE stmt FROM @str; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET FOREIGN_KEY_CHECKS = 1; 

Ho trovato più semplice fare qualcosa come il codice qui sotto, basta sostituire i nomi dei tavoli con i propri. importante assicurarsi che l’ultima riga sia sempre 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; 
  1. Per troncare una tabella, è necessario eliminare i vincoli di chiave esterna mappati alle colonne in questa tabella da altre tabelle (in effetti su tutte le tabelle nello specifico DB / Schema).
  2. Quindi, tutti i vincoli di chiave esterna devono essere eliminati inizialmente seguito dal troncamento della tabella.
  3. Facoltativamente, utilizzare la tabella di ottimizzazione (in mysql, esp del motore di innodb) per recuperare lo spazio / la dimensione dei dati utilizzati sul sistema operativo dopo il troncamento dei dati.
  4. Una volta eseguito il troncamento dei dati, creare nuovamente gli stessi vincoli di chiave esterna nella stessa tabella. Vedi sotto uno script che genererebbe lo script per eseguire le operazioni di cui sopra.

     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='' UNION SELECT CONCAT('TRUNCATE TABLE ',TABLE_SCHEMA,'.',TABLE_NAME,';') FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='
    ' AND TABLE_TYPE='BASE TABLE' UNION SELECT CONCAT('OPTIMIZE TABLE ',TABLE_SCHEMA,'.',TABLE_NAME,';') FROM INFORMATION_SCHEMA.TABLES WHERE 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='
    ' INTO OUTFILE "C:/DB Truncate.sql" LINES TERMINATED BY '\n';

Ora, esegui lo script Db Truncate.sql generato

Benefici. 1) Spazio su disco di recupero 2) Non necessario per eliminare e ricreare il DB / Schema con la stessa struttura

Inconvenienti. 1) I vincoli FK dovrebbero essere i nomi nella tabella con il nome che contiene “FK” nel nome del vincolo.

se si utilizza SQL Server 2005, esiste una stored procedure nascosta che consente di eseguire un comando o un insieme di comandi su tutte le tabelle all’interno di un database. Ecco come si chiamerà TRUNCATE TABLE con questa stored procedure:

 EXEC [sp_MSforeachtable] @command1="TRUNCATE TABLE ?" 

Ecco un buon articolo che elabora ulteriormente.

Per MySql, tuttavia, è ansible utilizzare mysqldump e specificare le --add-drop-tables e --no-data per eliminare e creare tutte le tabelle ignorando i dati. come questo:

 mysqldump -u[USERNAME] -p[PASSWORD] --add-drop-table --no-data [DATABASE] 

guida all’uso di mysqldump da dev.mysql

Utilizzare questo e formare la query

 SELECT Concat('TRUNCATE TABLE ',table_schema,'.',TABLE_NAME, ';') FROM INFORMATION_SCHEMA.TABLES where table_schema in (db1,db2) INTO OUTFILE '/path/to/file.sql'; 

Ora usa questo per usare questa query

 mysql -u username -p  

se ottieni un errore come questo

 ERROR 1701 (42000) at line 3: Cannot truncate a table referenced in a foreign key constraint 

il modo più semplice per passare è nella parte superiore del file aggiungere questa riga

 SET FOREIGN_KEY_CHECKS=0; 

che dice che non vogliamo controllare i vincoli di chiave esterna mentre si passa attraverso questo file.

Tronnerà tutte le tabelle nei database db1 e bd2.

No. Non esiste un singolo comando per troncare tutte le tabelle mysql contemporaneamente. Dovrai creare un piccolo script per troncare le tabelle una alla volta.

ref: http://dev.mysql.com/doc/refman/5.0/en/truncate-table.html

qui per lo so qui

  SELECT Concat('TRUNCATE TABLE ',table_schema,'.',TABLE_NAME, ';') FROM INFORMATION_SCHEMA.TABLES where table_schema in ('databasename1','databasename2'); 

Se non è ansible eliminare o aggiornare una riga padre: un vincolo di chiave esterna non riesce

Ciò accade se ci sono tabelle con riferimenti a chiavi esterne alla tabella che stai tentando di eliminare / troncare.

Prima di troncare le tabelle Tutto ciò che devi fare è:

 SET FOREIGN_KEY_CHECKS=0; 

Tronca le tue tabelle e cambiala in

 SET FOREIGN_KEY_CHECKS=1; 

utente questo codice php

  $truncate = mysql_query("SELECT Concat('TRUNCATE TABLE ',table_schema,'.',TABLE_NAME, ';') as tables_query FROM INFORMATION_SCHEMA.TABLES where table_schema in ('databasename')"); while($truncateRow=mysql_fetch_assoc($truncate)){ mysql_query($truncateRow['tables_query']); } ?> 

controllare i dettagli qui link

Trovo che TRUNCATE TABLE .. abbia problemi con i vincoli di chiave esterna, anche dopo un NOCHECK CONSTRAINT ALL, quindi uso invece una istruzione DELETE FROM. Ciò significa che i semi di id quadro non vengono ripristinati, è sempre ansible aggiungere un DBCC CHECKIDENT per ottenere ciò.

I Utilizzare il codice seguente per stampare nella finestra del messaggio lo sql per il troncamento di tutte le tabelle nel database, prima di eseguirlo. Rende solo un po ‘più difficile fare un errore.

 EXEC sp_MSforeachtable 'PRINT ''ALTER TABLE ? NOCHECK CONSTRAINT ALL''' EXEC sp_MSforeachtable 'print ''DELETE FROM ?''' EXEC sp_MSforeachtable 'print ''ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all''' 

Non sono sicuro, ma penso che ci sia un comando con il quale è ansible copiare lo schema del database in un nuovo database, dopo averlo fatto è ansible eliminare il vecchio database e dopo di ciò è ansible copiare nuovamente lo schema del database sul vecchio nome.

Ecco una procedura che dovrebbe troncare tutte le tabelle nel database locale.

Fammi sapere se non funziona e cancellerò questa risposta.

Non testato

 CREATE PROCEDURE truncate_all_tables() BEGIN -- Declare local variables DECLARE done BOOLEAN DEFAULT 0; DECLARE cmd VARCHAR(2000); -- Declare the cursor DECLARE cmds CURSOR FOR SELECT CONCAT('TRUNCATE TABLE ', TABLE_NAME) FROM INFORMATION_SCHEMA.TABLES; -- Declare continue handler DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1; -- Open the cursor OPEN cmds; -- Loop through all rows REPEAT -- Get order number FETCH cmds INTO cmd; -- Execute the command PREPARE stmt FROM cmd; EXECUTE stmt; DROP PREPARE stmt; -- End of loop UNTIL done END REPEAT; -- Close the cursor CLOSE cmds; END; 

So che questo non è esattamente un comando, ma il risultato desiderato può essere ottenuto da phpMyAdmin seguendo questi passaggi:

  1. Seleziona (tutte) le tabelle da rimuovere (Seleziona tutto)
  2. Seleziona “Elimina” / “Tronca” dall’elenco “Con selezionato:”
  3. Nella pagina di conferma (“Vuoi davvero:”) copiare la query (tutto con lo sfondo rosso)
  4. Vai in alto e fai clic su SQL e scrivi: “SET FOREIGN_KEY_CHECKS = 0;” quindi incollare la query precedentemente copiata
  5. Clicca “Vai”

L’idea è di ottenere rapidamente tutte le tabelle dal database (che fai in 5 secondi e 2 clic), ma prima disabilita i controlli delle chiavi esterne. Nessuna CLI e nessuna copia del database e aggiunta di nuovo.

 TB=$( mysql -Bse "show tables from DATABASE" ); for i in ${TB}; do echo "Truncating table ${i}"; mysql -e "set foreign_key_checks=0; set unique_checks=0;truncate table DATABASE.${i}; set foreign_key_checks=1; set unique_checks=1"; sleep 1; done 

David,

Grazie per aver dedicato del tempo a formattare il codice, ma è così che dovrebbe essere applicato.

-Kurt

Su una macchina UNIX o Linux:

Assicurati di essere in una shell bash. Questi comandi devono essere eseguiti, dalla riga di comando come segue.

Nota:

Conservo le credenziali nel mio file ~ / .my.cnf, quindi non ho bisogno di fornirle sulla riga di comando.

Nota:

cpm è il nome del database

Sto mostrando solo un piccolo campione dei risultati, da ciascun comando.

Trova i tuoi vincoli di chiave esterna:

 [email protected]:~$ mysql -Bse "select concat(table_name, ' depends on ', referenced_table_name) from information_schema.referential_constraints where constraint_schema = 'cpm' order by referenced_table_name" 
  1. approval_external_system dipende da approval_request
  2. l’indirizzo dipende dal cliente
  3. l’identificazione del cliente dipende dal cliente
  4. external_id dipende dal cliente
  5. le credenziali dipendono dal cliente
  6. email_address dipende dal cliente
  7. approval_request dipende dal cliente
  8. customer_status dipende dal cliente
  9. customer_image dipende dal cliente

Elenca le tabelle e i conteggi delle righe:

 [email protected]:~$ mysql -Bse "SELECT table_name, table_rows FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'cpm'" | cat -n 1 address 297 2 approval_external_system 0 3 approval_request 0 4 country 189 5 credential 468 6 customer 6776 7 customer_identification 5631 8 customer_image 2 9 customer_status 13639 

Tronca le tue tabelle:

 [email protected]:~$ TB=$( mysql -Bse "show tables from cpm" ); for i in ${TB}; do echo "Truncating table ${i}"; mysql -e "set foreign_key_checks=0; set unique_checks=0;truncate table cpm.${i}; set foreign_key_checks=1; set unique_checks=1"; sleep 1; done 
  1. Troncare l’indirizzo della tabella
  2. Troncare la tabella approval_external_system
  3. Troncare la tabella approval_request
  4. Paese del tavolo troncante
  5. Troncare le credenziali della tabella
  6. Truncating table customer
  7. Troncare la tabella customer_identification
  8. Troncare la tabella customer_image
  9. Troncare la tabella customer_status

Verifica che funzioni:

 [email protected]:~$ mysql -Bse "SELECT table_name, table_rows FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'cpm'" | cat -n 1 address 0 2 approval_external_system 0 3 approval_request 0 4 country 0 5 credential 0 6 customer 0 7 customer_identification 0 8 customer_image 0 9 customer_status 0 10 email_address 0 

Su una casella di Windows:

NOTA:

cpm è il nome del database

 C:\>for /F "tokens=*" %a IN ('mysql -Bse "show tables" cpm') do mysql -e "set foreign_key_checks=0; set unique_checks=0; truncate table %a; foreign_key_checks=1; set unique_checks=1" cpm 

La seguente query MySQL produrrà essa stessa una singola query che troncerà tutte le tabelle in un determinato database. Ignora le chiavi esterne:

 SELECT CONCAT( 'SET FOREIGN_KEY_CHECKS=0; ', GROUP_CONCAT(dropTableSql SEPARATOR '; '), '; ', 'SET FOREIGN_KEY_CHECKS=1;' ) as dropAllTablesSql FROM ( SELECT Concat('TRUNCATE TABLE ', table_schema, '.', TABLE_NAME) AS dropTableSql FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = 'DATABASE_NAME' ) as queries 
 mysqldump -u root -p --no-data dbname > schema.sql mysqldump -u root -p drop dbname mysqldump -u root -p < schema.sql 

Soln 1)

 mysql> select group_concat('truncate',' ',table_name,';') from information_schema.tables where table_schema="db_name" into outfile '/tmp/a.txt'; mysql> /tmp/a.txt; 

Soln 2)

 - Export only structure of a db - drop the database - import the .sql of structure 

— modificare —-

 earlier in solution 1, i had mentioned concat() instead of group_concat() which would have not returned the desired result 

Ecco la mia variante per avere “una dichiarazione per troncarli tutti”.

Innanzitutto, sto utilizzando un database separato chiamato “util” per le mie stored procedure di supporto. Il codice della mia stored procedure per troncare tutte le tabelle è:

 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 ; 

Una volta che hai memorizzato questa procedura nel tuo database util, puoi chiamarlo come

 call util.trunctables('nameofdatabase'); 

che ora è esattamente una dichiarazione 🙂

Un’idea potrebbe essere quella di abbandonare e ricreare i tavoli?

MODIFICARE:

@ Jonathan Leffler: vero

Altri suggerimenti (o caso in cui non è necessario troncare TUTTE le tabelle):

Perché non creare semplicemente una stored procedure di base per troncare tabelle specifiche

 CREATE PROCEDURE [dbo].[proc_TruncateTables] AS TRUNCATE TABLE Table1 TRUNCATE TABLE Table2 TRUNCATE TABLE Table3 GO 
 error; } $conn->close(); ?> 

Ecco lo snippet di codice che uso per cancellare una tabella. Cambia semplicemente $ conn e TABLE_NAME.