How to Truncate All Tables in Mysql

1. Truncate All Tables in Mysql From the command line
2. Use truncate script for all tables

Today I will show you how to truncate all tables in Mysql.TRUNCATE TABLE is used when you want to delete an entire table. TRUNCATE is a DML statement type meaning that it can not be rolled back once it is committed.

There are two ways to do truncate all tables in MySQL.

Truncate All Tables in Mysql From the command line

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

Let’s see what this code does in general: mysql mydatabase -Nse 'show tables' prints all tables from mydb database one by one. while read table execute a loop for each table in the database.

mysql db -e "truncate table $table" executes truncate query for each $table variable value.

Use truncate script for all tables

CREATE TABLE table_name (table_id INT);
CREATE TABLE table_name1 (table_id INT);
insert into table_name values (1);
insert into table_name values (2);
insert into table_name1 values (34);
insert into table_name1 values (35);
SELECT Concat('TRUNCATE TABLE ', table_name, ';') 
FROM INFORMATION_SCHEMA.TABLES

Let’s check if the tables are now empty or not:

SELECT * FROM table_name;

Output:

Empty set (0.01 sec)

Note that if you have tables that have foreign keys referenced to each other, then before executing this script, you need to make sure that you have disabled foreign keys.

SET FOREIGN_KEY_CHECKS=0;

And after you truncate your tables, you can enable it.

SET FOREIGN_KEY_CHECKS=1;