09.12Drop or Empty/Truncate all Tables in a MySQL Database
Unfortunately, no command exists in MySQL to drop or truncate all tables in a database. You can drop or truncate each table in a database one-by-one, but even that is cumbersome, as you can’t even pass a comma-delimited list of tables to use.
There’s no command like
drop all tables;, neither is there a command that will allow you to drop or truncate multiple tables at once like
drop table users, addresses, locations;. You’re left with doing it line by line in a tedious, repetitive manner like so:
drop table users; drop table addresses; drop table locations;
If you have a lot of tables, this simply doesn’t work.
If you have a Bash-like shell, you can do it in a much more efficient way via the
mysql command and the
mysqldump command and some regular expression hackery:
mysqldump -u username --password=password --add-drop-table --no-data database_name | grep ^DROP | mysql -u username --password=password database_name
Winning! If you want to do the same thing for truncating all databases, look no further:
mysqldump -u username --password=password --add-drop-table --no-data database_name | grep ^DROP | sed "s:^DROP TABLE IF EXISTS:TRUNCATE TABLE:g" | mysql -u username --password=password database_name
Now that you’ve emptied your database, it’s time to weep that you forgot to make a backup