http://qs321.pair.com?node_id=994479

justin423 has asked for the wisdom of the Perl Monks concerning the following question:

Can somebody tell me where I am going wrong with this code? I am trying to delete all the data from every table in the database by importing the data coming from show tables into a query that deletes all the data. (there is probably another way, using the informa_schema, I'll try that if nobody can answer this one) Thanks
my $query = $dbh->prepare('SHOW TABLES'); if (defined($query)) { $query->execute(); my @row; while (@row = $query->fetchrow_array()) { $sql = qq|delete from ?|; $sth = $dbh->prepare($sql) or die "Cannot prepare: " . $db +h->errstr(); $sth->execute($row[0]) or die "Cannot execute: " . $sth->e +rrstr(); } } $query->finish();

Replies are listed 'Best First'.
Re: Deleting all from all tables in MYSQL
by fishmonger (Chaplain) on Sep 19, 2012 at 17:02 UTC
Re: Deleting all from all tables in MYSQL
by herveus (Prior) on Sep 19, 2012 at 17:00 UTC
    Howdy!

    You can't use placeholders that way. MySQL can't parse a statement like "delete from ?", so you can't prepare it. You are pretty much stuck with "delete from $row[0]".

    yours,
    Michael
      Thanks. So would this work instead?
      my $query = $dbh->prepare('SHOW TABLES'); if (defined($query)) { $query->execute(); my @row; while (@row = $query->fetchrow_array()) { $sql = qq|delete from $row[0]|; $sth = $dbh->prepare($sql) or die "Cannot prepare: " . $dbh->errstr(); $sth->execute() or die "" . $sth->errstr();
Re: Deleting all from all tables in MYSQL
by Anonymous Monk on Sep 19, 2012 at 17:24 UTC
    If you have referential integrity on your database it might slow things down a lot.   Consider dropping the database and re-creating it, perhaps by executing an external script which could be easily changed.   Perhaps you do not actually need Perl code to do this.
      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.

        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
Re: Deleting all from all tables in MYSQL
by irirgem (Novice) on Sep 19, 2012 at 21:49 UTC

    I would try doing this:

    my $sqlString = 'delete from %s;'; $dbh->begin_work; my @tables = ('table1','table2','table3'); foreach my $table (@tables) { my $sth = sprintf($sqlString, $table); $dbh->do( $dth ); } $dbh->commit;

    This would speed it up some, but double-check the DBI doco on cpan for the 'begin_work' function before you do it on how it will react since I think it depends on the database you use and whether it supports transactions or not.

    The last thing you could try could be a drop/create where you basically drop the old table and re-create it from scratch... That will definately speed it up, but some people get twitchy when doing it :)
    Something like

    $dth->do("drop table0"); $dth->do("create table0 ( col1 def, col2 def.....)"); etc....