Mass inserts?

[Alexander Karelas]

I'm looking for a way to INSERT 100 rows in one MySQL command, rather
that execute a single INSERT after another, and wait for 100 round-trips
to the database. With RDBO.

Is it possible?

Will it ever be possible?

If not, why not?

Thanks.

[Peter Karman]

Alexander Karelas wrote on 5/6/16, 4:05 PM:

I've not found a way to do that with RDBO -- very mysql-specific, ime.

Here's one way we did it using RDBO only as a way to get the db handle.

https://github.com/publicinsightnetwork/audience-insight-repository/blob/master/lib/shared/perl/MySQLImporter.pm

example:

use MySQLImporter;

my $dbh = Thing->new->retain_dbh;

my $importer = MySQLImporter->new(
table_name => 'things',
columns => [ qw/ foo bar baz / ],
delete_first => 1,
dbh => $dbh,
);

for my $thing (@list_of_things) {
for my $k ( @{ $importer->columns } ) {
$importer->buffer( $thing->{$k} );
}
$importer->end_record();
}

$importer->load();


--
Peter Karman . http://peknet.com/ . pe...@peknet.com

[Perrin Harkins]

I've done some timings of various methods. The fastest bulk inserts to MySQL are from writing out a CSV with Text::CSV_XS and then using LOAD DATA LOCAL INFILE, which works fine with DBI's ->do(). With a large amount of data, this is much faster than any other option.

Second best, but good enough for a small update like 100 rows, is the MySQL multi-row insert statement. You could probably extend Rose to do that without too much hassle. Postgres supports a similar syntax for it, so it's not a totally db-specific addition. It's quite a bit faster than separate inserts.

--
Source: https://github.com/siracusa/rose
CPAN: http://search.cpan.org/dist/Rose-DB-Object
--- You received this message because you are subscribed to the Google Groups "Rose::DB::Object" group.
To unsubscribe from this group and stop receiving emails from it, send an email to rose-db-objec...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Alexander Karelas]

The trouble is, I don't know RDBO so well, so as to extend it.

So the reason that it's not being implemented by J.S. is that multi-row insert statements are not standard SQL?

Thanks,

- Alex