Truncar todas as tabelas em um database MySQL em um comando?

Existe uma consulta (comando) para truncar todas as tabelas em um database em uma operação? Eu quero saber se posso fazer isso com uma única consulta.

Soltar (ou seja, remover tabelas)

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

Truncar (ou seja, tabelas vazias)

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

truncar várias tabelas de database na instância do Mysql

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

Use o resultado da consulta para truncar tabelas

Nota: pode ser que você receba este erro:

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

Isso acontece se houver tabelas com referências de foreign keys à tabela que você está tentando eliminar / truncar.

Antes de truncar tabelas Tudo o que você precisa fazer é:

 SET FOREIGN_KEY_CHECKS=0; 

Trunque suas tabelas e mude de volta para

 SET FOREIGN_KEY_CHECKS=1; 

Use o phpMyAdmin desta maneira:

Visualização do database => Verificar tudo (tabelas) => Vazio

Se você quiser ignorar as verificações de foreign keys, desmarque a checkbox que diz:

[] Ativar verificações de chave estrangeira

Você precisará estar executando pelo menos a versão 4.5.0 ou superior para obter essa checkbox de seleção.

Não é MySQL CLI-fu, mas ei, funciona!

MS SQL Server 2005+ (remover PRINT para execução real …)

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

Se sua plataforma de database suportar visualizações INFORMATION_SCHEMA, obtenha os resultados da consulta a seguir e execute-os.

 SELECT 'TRUNCATE TABLE ' + TABLE_NAME FROM INFORMATION_SCHEMA.TABLES 

Tente isto para o MySQL:

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

Adicionar um ponto-e-vírgula ao Concat facilita o uso, por exemplo, do ambiente de trabalho do mysql.

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

Eu encontrei isso para soltar todas as tabelas em um database:

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

Útil se você está limitado pela solução de hospedagem (não é possível soltar um database inteiro).

Eu modifiquei para truncar as tabelas. Não existe “–add-truncate-table” para mysqldump, então eu fiz:

 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 

funciona para mim – edite, corrigindo um erro de digitação no último comando

Isto irá imprimir o comando para truncar todas as tabelas:

 SELECT GROUP_CONCAT(Concat('TRUNCATE TABLE ',table_schema,'.',TABLE_NAME) SEPARATOR ';') FROM INFORMATION_SCHEMA.TABLES where table_schema in ('my_db'); 

Eu achei mais simples apenas fazer algo como o código abaixo, apenas substitua os nomes das tabelas por seus próprios. importante certifique-se que a última linha é 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; 
 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; 
  1. Para truncar uma tabela, deve-se eliminar as restrições de chave estrangeira mapeadas para as colunas nesta tabela de outras tabelas (na verdade, em todas as tabelas no DB / Schema específico).
  2. Portanto, todas as restrições de chave estrangeira devem ser descartadas inicialmente seguidas por truncamento de tabela.
  3. Opcionalmente, use a tabela de otimização (no mysql, innodb engine esp) para recuperar o espaço / tamanho de dados usados ​​para o SO após o truncamento de dados.
  4. Depois que o truncamento de dados é executado, crie as mesmas restrições de chave estrangeira novamente na mesma tabela. Veja abaixo um script que geraria o script para realizar as operações acima.

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

Agora, execute o script Db Truncate.sql gerado

Benefícios 1) Recuperar espaço em disco 2) Não é necessário descartar e recriar o DB / Schema com a mesma estrutura

Desvantagens 1) Restrições FK devem ser nomes na tabela com o nome contendo ‘FK’ no nome da restrição.

se estiver usando o sql server 2005, há um procedimento armazenado oculto que permite executar um comando ou um conjunto de comandos em todas as tabelas dentro de um database. Aqui está como você chamaria TRUNCATE TABLE com este procedimento armazenado:

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

Aqui está um bom artigo que elabora mais.

Para MySql, no entanto, você poderia usar mysqldump e especificar as --add-drop-tables e --no-data para eliminar e criar todas as tabelas que ignoram os dados. como isso:

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

Guia de uso do mysqldump do dev.mysql

Use isso e forme a consulta

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

Agora use isso para usar essa consulta

 mysql -u username -p  

se você receber um erro como este

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

a maneira mais fácil de passar é no topo do seu arquivo adicionar esta linha

 SET FOREIGN_KEY_CHECKS=0; 

que diz que não queremos verificar as restrições de chave estrangeira ao passar por este arquivo.

Ele irá truncar todas as tabelas nos bancos de dados db1 e bd2.

Não. Não existe um único comando para truncar todas as tabelas do mysql de uma só vez. Você terá que criar um pequeno script para truncar as tabelas uma a uma.

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

aqui para eu sei aqui

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

Se não é possível excluir ou atualizar uma linha pai: uma restrição de chave estrangeira falha

Isso acontece se houver tabelas com referências de foreign keys à tabela que você está tentando eliminar / truncar.

Antes de truncar tabelas Tudo o que você precisa fazer é:

 SET FOREIGN_KEY_CHECKS=0; 

Trunque suas tabelas e mude de volta para

 SET FOREIGN_KEY_CHECKS=1; 

usuário este código 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']); } ?> 

verifique detalhes aqui link

Eu acho que TRUNCATE TABLE .. tem problemas com restrições de chave estrangeira, mesmo depois de um NOCHECK CONSTRAINT ALL, então eu uso uma instrução DELETE FROM. Isso significa que as sementes de identidade não são redefinidas, você pode sempre adicionar um DBCC CHECKIDENT para conseguir isso.

Eu uso o código abaixo para imprimir na janela de mensagem o sql para truncar todas as tabelas no database, antes de executá-lo. Apenas torna um pouco mais difícil cometer um erro.

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

Não tenho certeza, mas acho que há um comando usando o qual você pode copiar o esquema do database em um novo database. Depois disso, você pode excluir o database antigo e copiar novamente o esquema do database para o nome antigo.

Aqui está um procedimento que deve truncar todas as tabelas no database local.

Deixe-me saber se não funciona e vou deletar essa resposta.

Não testado

 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; 

Eu sei que este não é exatamente um comando, mas o resultado desejado pode ser alcançado a partir do phpMyAdmin seguindo estes passos:

  1. Selecione (todas) as tabelas a serem removidas (marque todas)
  2. Selecione “Drop” / “Truncate” na lista “With selected:”
  3. Na página de confirmação (“Você realmente quer:”) copiar a consulta (tudo com o fundo vermelho)
  4. Vá no topo e clique em SQL e escreva: “SET FOREIGN_KEY_CHECKS = 0;” em seguida, cole a consulta previamente copiada
  5. Clique em “Go”

A idéia é obter rapidamente todas as tabelas do database (o que você faz em 5 segundos e 2 cliques), mas desabilite primeiro as verificações de chave estrangeira. Sem CLI e sem descartar o database e adicioná-lo novamente.

 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,

Obrigado por dedicar um tempo para formatar o código, mas é assim que ele deve ser aplicado.

-Kurt

Em uma checkbox UNIX ou Linux:

Certifique-se de que você está em uma concha bash. Estes comandos devem ser executados a partir da linha de comando, como segue.

Nota:

Eu armazeno minhas credenciais no meu arquivo ~ / .my.cnf, então não preciso fornecê-las na linha de comando.

Nota:

cpm é o nome do database

Estou mostrando apenas uma pequena amostra dos resultados, de cada comando.

Encontre suas restrições de chave estrangeira:

 klarsen@Chaos:~$ 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 depende de approval_request
  2. endereço depende do cliente
  3. customer_identification depende do cliente
  4. external_id depende do cliente
  5. credencial depende do cliente
  6. email_address depende do cliente
  7. pedido de aprovação depende do cliente
  8. customer_status depende do cliente
  9. customer_image depende do cliente

Listar as contagens de tabelas e linhas:

 klarsen@Chaos:~$ 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 

Trunque suas tabelas:

 klarsen@Chaos:~$ 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. Truncando o endereço da tabela
  2. Tabela de truncagem approval_external_system
  3. Truncando a aprovação de tabela_de_aprovação
  4. Truncando o país da mesa
  5. Truncando a credencial da tabela
  6. Truncando o cliente da tabela
  7. Tabela truncada customer_identification
  8. Tabela truncada customer_image
  9. Tabela de truncagem customer_status

Verifique se funcionou:

 klarsen@Chaos:~$ 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 

Em uma checkbox do Windows:

NOTA:

cpm é o nome do 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 

A seguinte consulta MySQL irá produzir uma única consulta que irá truncar todas as tabelas em um dado database. Ele ignora as chaves FOREIGN:

 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 

– editar —-

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

Aqui está a minha variante para ter “uma declaração para truncar todos eles”.

Primeiro, estou usando um database separado chamado ‘util’ para meus stored procedures auxiliares. O código do meu procedimento armazenado para truncar todas as tabelas é:

 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 ; 

Depois de ter este procedimento armazenado em seu database de utilitários, você pode chamá-lo como

 call util.trunctables('nameofdatabase'); 

que agora é exatamente uma declaração 🙂

Uma ideia poderia ser simplesmente soltar e recriar as tabelas?

EDITAR:

@ Jonathan Leffler: Verdadeiro

Outra sugestão (ou caso você não precise truncar todas as tabelas):

Por que não criar um procedimento armazenado básico para truncar tabelas específicas?

 CREATE PROCEDURE [dbo].[proc_TruncateTables] AS TRUNCATE TABLE Table1 TRUNCATE TABLE Table2 TRUNCATE TABLE Table3 GO 
 < ?php // connect to database $conn=mysqli_connect("localhost","user","password","database"); // check connection if (mysqli_connect_errno()) { exit('Connect failed: '. mysqli_connect_error()); } // sql query $sql =mysqli_query($conn,"TRUNCATE " . TABLE_NAME); // Print message if ($sql === TRUE) { echo 'data delete successfully'; } else { echo 'Error: '. $conn->error; } $conn->close(); ?> 

Aqui está um trecho de código que eu uso para limpar uma tabela. Basta alterar as informações de $ conn e TABLE_NAME.