Perl DBI insert multiple rows using mysql native multiple insert ability

Question

Has anyone seen a DBI-type module for Perl which capitalizes, easily, on MySQL's multi-insert syntax

insert into TBL (col1, col2, col3) values (1,2,3),(4,5,6),...?

I've not yet found an interface which allows me to do that. The only thing I HAVE found is looping through my array. This method seems a lot less optimal vs throwing everything into a single line and letting MySQL handle it. I've not found any documentation out there IE google which sheds light on this short of rolling my own code to do it.

TIA

Answer 1

There are two approaches. You can insert (?, ?, ?) a number of times based on the size of the array. The text manipulation would be something like:

my $sql_values = join( ' ', ('(?, ?, ?)') x scalar(@array) );

Then flatten the array for calling execute(). I would avoid this way because of the thorny string and array manipulation that needs to be done.

The other way is to begin a transaction, then run a single insert statement multiple times.

my $sql = 'INSERT INTO tbl (col1, col2, col3)';
$dbh->{AutoCommit} = 0;
my $sth = $dbh->prepare_cached( $sql );
$sth->execute( @$_ ) for @array;
$sth->finish;
$dbh->{AutoCommit} = 1;

This is a bit slower than the first method, but it still avoids reparsing the statement. It also avoids the subtle manipulations of the first solution, while still being atomic and allowing disk I/O to be optimized.