Monday, October 09, 2017

Truncate all tables in a MySQL database in one command? - Stack Overflow

Truncate all tables in a MySQL database in one command? - Stack Overflow



truncate multiple database tables on Mysql instance
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/truncate.
Before truncating tables All you need to do is:
SET FOREIGN_KEY_CHECKS=0;
Truncate your tables and change it back to
SET FOREIGN_KEY_CHECKS=1;