How to truncate all tables in MySQL?

Using SQL Query Editor:

First, you have to temporarily disable the foreign key constraints in order for the truncate statement to work:

SET FOREIGN_KEY_CHECKS=0;

List out all the tables in the target database:

SELECT
    TABLE_NAME
FROM
    information_schema.tables
WHERE
    table_schema = 'db_name';

Then truncate all tables, on by one:

TRUNCATE TABLE table1;

TRUNCATE TABLE table2;

TRUNCATE TABLE table3;

Or you can print out the truncate statements all at once by executing this query:

SELECT
    Concat('TRUNCATE TABLE ', TABLE_NAME)
FROM
    INFORMATION_SCHEMA.TABLES
WHERE
    table_schema = 'db_name';

Then copy and paste the results into the query editor and execute.

After you finish, turn on the foreign key constraint:

SET FOREIGN_KEY_CHECKS=1;

Using command line:

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

Using TablePlus GUI Tool:

• Connect to the target database
• Select all tables from the left panel
• Right-click and choose truncate
• Hit Cmd + S to commit changes to the server

---

Need a good GUI Tool for MySQL? TablePlus is a modern, native tool with an elegant UI that allows you to simultaneously manage multiple databases such as MySQL, PostgreSQL, SQLite, Microsoft SQL Server and more.


Download TablePlus free.