Perl DBI Multi Insert

Iš Žinynas.
Jump to navigation Jump to search

Tiesiog būdas, kai reikia daug įrašų įrašyti viena SQL INSERT užklausa...

#!/usr/bin/perl -w
# Perl-MySQL MultiInsert With DBI Interface PoC
use strict;
use DBI;
my $dbh = DBI->connect("DBI:mysql:testdb:host.eofnet.lt", "testuser", "testpassw",{ PrintError => 1, RaiseError => 0 }) or die "can't connect\n";
$dbh->do(qq{drop table if exists testdbi});
$dbh->do(qq{create table testdbi
    (
 `id` bigint(20) NOT NULL,
  `data` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `value` text COLLATE utf8_unicode_ci,
  `host` text COLLATE utf8_unicode_ci NOT NULL,
  `log_time` text COLLATE utf8_unicode_ci NOT NULL,
  `log_host` text COLLATE utf8_unicode_ci NOT NULL,
  `log_process` text COLLATE utf8_unicode_ci NOT NULL,
  `log_key` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `address` text COLLATE utf8_unicode_ci NOT NULL,
  `log_status` text COLLATE utf8_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
    });
my @records = ( [ 'blastpit', '11:12', 'blastpit', 'smtp', 'AD1FC4B', 'justinas@eofnet.lt', 'OK' ], [ 'blastpit', '11:13', 'blastpit', 'smtp', 'AD1CC4B', 'test@eofnet.lt', 'ERR' ] );
push(@records,( [ 'naujashost', '11:15', 'blastpitas', 'smtp', 'ADV3C1' , 'heh@eofnet.lt', 'UNKNOWN' ] ));
push(@records,( [ 'naujashost2', '11:16', 'blastpitas', 'smtp', 'BDV3C1' , 'heh0@eofnet.lt', 'UNKNOWN' ] ));
my $values = join ", ", ("( ?, ?, ?, ?, ?, ?, ? )") x @records;
my $query  = "INSERT INTO testdbi (host,log_time,log_host,log_process,log_key,address,log_status) VALUES $values";
my $sth    = $dbh->prepare($query);
$sth->execute(map { @$_ } @records);
use Data::Dumper qw(Dumper); # dumperis skirta isvesti duomenis pvz, dump array contents
print Dumper \@records; # print array
undef @records; # array isvalymas

Screenshot at Oct 26 10-18-45.png