How to TRUNCATE multiple Tables in MySQL

In this post we will learn about how can we truncate multiple tables in MySQL database management system.

truncate multiple tables in MySQL

Why we cannot TRUNCATE Multiple Tables in MySQL directly:

The basic syntax of TRUNCATE is:

TRUNCATE  TABLE  <table_name>;

In this case we can truncate only one table at a time from a single database.

What if we need to TRUNCATE multiple tables from different databases in MySQL which could include more than 1000 tables? Do you want write this line by line? I guess no, so let’s find out how to do this:

Solution:

This can be achieved using metadata information of  database. INFORMATION_SCHEMA database holds this information, it contains all the information like information about all the databases that the MySQL server maintains, information of all the users that can access the MySQL server etc.

Use the following query to get the list of tables from multiple databases:

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

Now execute the result of this query to truncate all the required tables.

We can also execute this query in a way so that we do not need to copy paste the output of it, simple take the ouput in a file and execute it.

mysql -uuser -p -e"SELECT Concat('TRUNCATE TABLE ',table_schema,'.',TABLE_NAME, ';') FROM INFORMATION_SCHEMA.TABLES where table_schema in (db1_name,db2_name);" > ouput.sql

Now we have output of this query in output.sql, now execute this:
mysql -uuser -p database_name< output.sql

Note: We may get this error:
ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails

That happen if there are tables with foreign keys references to the table you are trying to drop.

For this you can read this post to know the details:

MySQL tables with Foreign Keys

For the quick answer:

Before truncating tables All you need to check  FOREIGN_KEY_CHECKS variable value

SET FOREIGN_KEY_CHECKS=0;   (This will  disable  FOREIGN KEY check)

Truncate your tables and change it back to
SET FOREIGN_KEY_CHECKS=1;

Hope this helps, comment for any suggestion, concerns.

Leave a Comment