For some work, the requirement is such that we want to retain the table and database structure while truncating all data in the multiple table at one go. Since Truncate Table_name only truncates one table at time. Is there a way to truncate multiple table? Help would be appreciated.
3 Answers
Unfortunately there's no solution to truncate all tables at once.
For those using PHP/PDO, I came up with the following solution:
$pdo->query('SET FOREIGN_KEY_CHECKS = 0;');
$tables = $pdo->prepare('SHOW TABLES');
$tables->execute();
foreach ($tables->fetchAll(\PDO::FETCH_COLUMN) as $table)
$pdo->query('TRUNCATE TABLE `'.$table.'`')->execute();
$pdo->query('SET FOREIGN_KEY_CHECKS = 1;');
Related