http://www.perlmonks.org?node_id=994505


in reply to Re: Deleting all from all tables in MYSQL
in thread Deleting all from all tables in MYSQL

it is a huge database and just for testing purposes, so speed isn't a concern, because everytime it runs, it still takes about 2 hours to run.

Replies are listed 'Best First'.
Re^3: Deleting all from all tables in MYSQL
by Tux (Canon) on Sep 20, 2012 at 06:55 UTC

    In that case, dropping the database and recreating it is most likely the fastest solution. Database drops in PostgreSQL and MySQL/MariaDB are almost instant. In Oracle it takes ages to (re)create a database.

    To get all tables, why not use the DBI::tables? That way you don't have to dig into database specific calls.

    If you still want to delete in stead of re-create, why use a prepare/execute combo instead of just do?

        $dbh->do ("truncate table $_") for $dbh->tables (undef, undef, undef, "TABLE");

    or

        $dbh->do ("delete   from  $_") for $dbh->tables (undef, undef, undef, "TABLE");

    In other databases, you could use delete from table cascade, but iirc that syntax is not supported by mysql where you'd have to create the table to know about cascading deletes.


    Enjoy, Have FUN! H.Merijn