Bulk insert 用のクエリ作るくん

bulk.pl

#!/usr/bin/env perl

use strict;
use warnings;
use v5.10;
use Data::Dumper;

my $max_count = 2;
my $columns = [qw/fuji taka nasubi and okinawa/];
my $table = 'tokyo_no_dream';
my $insert_data = [
    +['a'..'e'],
    +['A'..'E'],
    +[1..5],
    +[6..10],
    +[11..15],
];
my $data_count = @$insert_data;

my $span = int($data_count / $max_count);
my $last = $data_count - $span * $max_count;

my $columns_query = join ',', @$columns;
my $bind_count = @$columns;
my $bind_query = join ',', map { '?' } 1 .. $bind_count;

my $rows = 0;
if ($data_count > $max_count) {
    for my $i (1 .. $span) {
        my $brackets = join ',', map { "(${bind_query})" } 1 .. $max_count;
        my $query = "insert into ${table} (${columns_query}) values ${brackets}";
        say $query;
        say Dumper map { @$_ } @{$insert_data}[($i - 1) * $max_count .. $max_count * $i - 1];
    }
    my $brackets = join ',', map { "(${bind_query})" } 1 .. $last;
    my $query = "insert into ${table} (${columns_query}) values ${brackets}";
    say $query;
    say Dumper map { @$_ } @{$insert_data}[$span * $max_count .. $data_count - $last];

    exit;
}

my $brackets = join ',', map { "(${bind_query})" } 1 .. $last;
my $query = "insert into ${table} (${columns_query}) values ${brackets}";

say $query;

11行-15行 こんな感じにデータを与えていると、その数に合わせて、クエリを作る。
しかし、クエリの長さもSQLによっては決まっているので、バインドするデータの個数を $max_count で制限している

実行結果はこんな感じ

insert into tokyo_no_dream (fuji,taka,nasubi,and,okinawa) values (?,?,?,?,?),(?,?,?,?,?)
$VAR1 = 'a';
$VAR2 = 'b';
$VAR3 = 'c';
$VAR4 = 'd';
$VAR5 = 'e';
$VAR6 = 'A';
$VAR7 = 'B';
$VAR8 = 'C';
$VAR9 = 'D';
$VAR10 = 'E';

insert into tokyo_no_dream (fuji,taka,nasubi,and,okinawa) values (?,?,?,?,?),(?,?,?,?,?)
$VAR1 = 1;
$VAR2 = 2;
$VAR3 = 3;
$VAR4 = 4;
$VAR5 = 5;
$VAR6 = 6;
$VAR7 = 7;
$VAR8 = 8;
$VAR9 = 9;
$VAR10 = 10;

insert into tokyo_no_dream (fuji,taka,nasubi,and,okinawa) values (?,?,?,?,?)
$VAR1 = 11;
$VAR2 = 12;
$VAR3 = 13;
$VAR4 = 14;
$VAR5 = 15;