MySQL Lists are EOL. Please join:

List:MySQL and Perl« Previous MessageNext Message »
From:Giuseppe Maxia Date:December 3 2003 9:53am
Subject:Re: bulk inserts
View as plain text  
> Starting from MySQL 4, you can do bulk inserts like:

Actually, they have available for long, long time. I remember using them
with MySQL 3.23.30

> INSERT INTO table VALUES (a), (b), (c);
>
>Is there anything in DBD::mysql to take advantage of this ?
 
Not explicitly. But you can use a Perl idiom to create multi-values
INSERT statements:

my @values = (
    ["A", "B", "D", "0"],    ["E", "F", "G", "1"],
    ["H", "I", "J", "2"],    ["K", "L", "M", "3"],
    ["N", "O", "P", "4"],    ["Q", "R", "S", "5"],
    ["T", "U", "V", "6"],    ["W", "X", "Y", "7"],
);

my $query = "INSERT INTO mytable VALUES \n"
  . join(",\n", ( map { "(". join ( ", ", 
                       map {$dbh->quote($_)} @$_). ")" 
                      }
                 @values));
print "$query;\n";

__END__

it will print

INSERT INTO mytable VALUES
('A', 'B', 'D', '0'),
('E', 'F', 'G', '1'),
('H', 'I', 'J', '2'),
('K', 'L', 'M', '3'),
('N', 'O', 'P', '4'),
('Q', 'R', 'S', '5'),
('T', 'U', 'V', '6'),
('W', 'X', 'Y', '7')

See http://gmax.oltrelinux.com/dbirecipes.html for more DBI idioms


-- 
  ____ ____  _____ _   _ 
 / _  |    \(____ ( \ / ) 
( (_| | | | / ___ |) X ( 
 \___ |_|_|_\_____(_/ \_) 
(_____| 
Sapere, saper fare, fare, far sapere
http://gmax.oltrelinux.com


Thread
bulk insertsUnknown Sender27 Nov
  • Re: bulk insertsJochen Wiedmann28 Nov
Re: bulk insertsGiuseppe Maxia3 Dec