Is there a way to TRUNCATE most tables in a MySQL schema?

Question

I'm looking for a query (or series of) to TRUNCATE all tables in my schema (which has a few hundred tables) EXCEPT for a 4 specific ones. How might I go about doing that? Thanks!

Answer 1

I believe you'll have to write a script in whatever language you like the most. You can get a list of the tables in the schema from the information_schema db, then iterate over them, truncating any that you feel like.

Query would be something like:

SELECT table_name FROM information_schema.tables WHERE table_schema = 'test' AND table_name NOT IN ('table1', 'table2');

Edit: Here's an example using Perl:

use strict;
use warnings;
use DBI;

my $dbh = DBI->connect("some_dsn");

my $sth = $dbh->prepare(q{SELECT table_name FROM information_schema.tables WHERE table_schema = 'test' AND table_name NOT IN ('table1', 'table2')});
$sth->execute();
$sth->bind_columns(\my $table_name);

while($sth->fetch) { $dbh->do(q{TRUNCATE TABLE } . $table_name) }

Answer 2

Another method could be that you copy those four tables in a new schema and then delete the original database schema.

Answer 3

*nix one-liner:

for i in `mysql -e "show tables MY_DB" | grep -vE "(table1|table2)"`; do mysql -e"TRUNCATE ${i}" MY_DB; done