In this post we will learn about how can we truncate multiple tables in MySQL database management system.
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.