To truncate table we use TRUNCATE command As follows.
TRUNCATE TABLE <table_name>;
What if you want to TRUNCATE ALL tables from different databases in mysql ,It is possible using metadata information of database. INFORMATION_SCHEMA database created while installing mysql ,which contains all information like stores information about all the other databases that the MySQL server maintains.
Here Is the Query which gives output script with truncate commands for all table.You don’t have to write truncate command one by one for each table.
SELECT Concat('TRUNCATE TABLE ',table_schema,'.',TABLE_NAME, ';') FROM INFORMATION_SCHEMA.TABLES where table_schema in (db1_name,db2_name);
Use Query Result to truncate tables
Note: may be you will get this error:
ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails
That happens if there are tables with foreign keys references to the table you are trying to drop.
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