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

Truncate all tables in TablePlus GUI Tool MySQL

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.

TablePlus GUI Tool MySQL