Truncate all tables in a database in Mysql

Asked

Viewed 6,718 times

8

There are ways to use the TRUNCATE to clear all bank tables? 

TRUNCATE tabela;

This command cleans one by one.

mysql sql

1 answer

9


Withdrawal solution of that response in the OS:

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

Or through sql command:

SELECT Concat('TRUNCATE TABLE ', table_schema, '.', table_name, ';') 
    FROM INFORMATION_SCHEMA.TABLES where table_schema in ('seuDB'); //pode listar vários DBs

You can make several filters:

SELECT Concat('TRUNCATE TABLE ', table_schema, '.', table_name, ';') 
    FROM INFORMATION_SCHEMA.TABLES where table_schema in ('seuDB') AND table_name NOT IN ('tabela1', 'tabela2', ...);

I put in the Github for future reference.

Browser other questions tagged mysql sql

You are not signed in. Login or sign up in order to post.