insertメソッドで行を挿入

DBIx::Custom
here

テーブルに行を挿入するにはinsertメソッドを使用します。

$dbi->insert({id => 1, title => 'Perl'}, table => 'book');

第一引数には挿入したいデータをハッシュリファレンスで指定します。対象のテーブルをtableで指定します。次のようなSQLが発行されます。

insert into book (id, title) values (?, ?);

対応する値がプレースホルダに埋め込まれます。

定数の挿入

パラメータの中にプレースホルダではなくて定数を埋め込みたい場合は、スカラのリファレンスを指定することができます。

$dbi->insert({id => 1, ctime => \"now()"}, table => 'book');

次のようなSQLが実行されます。

insert into book (id, ctime) values (?, now());

insertメソッドのオプション

insertメソッドではexecuteメソッドのすべてのオプションを利用することができます。オプションについては「executeメソッド」のオプションの項目を参考にしてください。

それに加えて以下のオプションを使用することができます。

挿入時の現在時刻の設定(生成時刻) ctime

insertを実行したときに、指定した列に現在時刻を設定するにはctimeオプションを使用します。

ctime => 'created_time'

デフォルトのフォーマットは「YYYY-mm-dd HH:MM:SS」という形式です。now属性を設定すれば、これを変更することができます。

IDの指定 id

executeメソッドと同じようにidを指定することができます。primary_keyが設定されている必要があります。

id => 4
id => [4, 5]

primary_keyで設定されている列名を利用して、挿入するデータが作成されます。

$dbi->insert(
  {title => 'Perl', author => 'Ken'}
  primary_key => ['id1', 'id2'],
  id => [4, 5],
  table => 'book'
);

以下のinsert文と同じ意味になります。

$dbi->insert(
  {id1 => 4, id2 => 5, title => 'Perl', author => 'Ken'},
  table => 'book'
);

プレフィックス prefix

prefixオプションを使用すると、insert文の中の「insert」と「into テーブル名」の間に好きな文字列を追加できます。

prefix => 'or replace'

以下のようなSQLが作成されます。

insert or replace into book

テーブル名 table

tableオプションでテーブル名を指定します。テーブル名は必須です。

table => 'book'

挿入時の現在時刻の設定(更新時刻) mtime

このオプションはupdateメソッドのmtimeオプションと同じです。指定した行に現在時刻を設定することができます。この時刻はnow属性で変更することができます。

mtime => 'modified_time'

insertする場合した場合というのは、それが最初の更新時刻でもあるので、ctimeで設定した列とmtimeに設定した両方の列に同じ現在時刻が挿入されるようになっています。

プレースホルダーのラッピング wrap

insertのvaluesの部分でデータベースの関数を利用した場合はwrapオプションを利用すると解決することができます。プレースホルダの部分を指定したコードリファレンスで変換してくれます。

wrap => {price => sub { "$_[0] + 5" }}

以下のようなinsert文を実行するとします。

$dbi->insert(
  {id => 1, price => 100},
  table => 'book',
  wrap => {price => sub { "$_[0] + 5" }}
);

するとpriceのプレースホルダの部分がwrapで指定したコードリファレンスを使って置き換えられます。

insert into book (id, price) values (?, ? + 5 );

「大量のインサート」を「高速化」する方法

DBIx::Customは便利なDBIのラッパーですが、大量のinsertなどを連続して行う場合の性能に関しては、生のDBIに比べて性能がかなり劣化します。

そのような場合は複数のデータを一度に挿入しましょう。DBIx::Customのinsertメソッドには、複数のデータを一度に渡すことができます。

my @params = [{id => 1, title => 'Perl'}, {id => 2, title => 'Ruby'}];
$dbi->insert(\@params, table => 'book');

大量のinsertを行うときのパフォーマンスの劣化は、DBIと比較して小さな劣化に収まります。

idオプションが使用できないといことが、複数insertにおける制限です。またフィルタ、タイプルール、バインドタイプのオプションのすべてがない場合には、insertはさらに高速化されます。タイプルールがすでに設定されている場合は、type_rule_offに1に設定することで、タイプルールを無効にすることができます。

パラメーターの最初のハッシュの値によって、生成されるSQLが決定するということに注意してください。つまり、残りのパラメータには、最初のパラメータと同じ列が含まれている必要があるということに、注意してください。

ベンチマークスクリプトとベンチマークの結果です。データベースにはSQLiteを使用しました。

use strict;
use warnings;
use Benchmark qw/timethese cmpthese/;

use DBIx::Custom;
my $dbi = DBIx::Custom->connect(
  dsn => 'dbi:SQLite:dbname=:memory:',
);

$dbi->execute("create table book (id, title)");

# 性能の比較
my $result = timethese(100, {
  # 通常
  normal => sub {
    for my $i (1 .. 100) {
      my $param = {id => $i, title => 'Perl'};
      $dbi->insert($param, table => 'book');
    }
  },
  # 複数まとめてインサート
  multiple => sub {
    my @params;
    for my $i (1 .. 100) {
      push @params, {id => $i, title => 'Perl'};
    }
    $dbi->insert(\@params, table => 'book');
  },
    # 生のDBIでステートメントハンドルを再利用
    raw => sub {
      my $sth;
      for my $i (1 .. 100) {
        $sth ||= $dbi->dbh->prepare('insert into book (id, title) values (?, ?)');
        my $id = $i;
        my $title = 'Perl';
        $sth->execute($i, $title);
      }
    }
});
cmpthese($result);

ベンチマークの結果は、生のDBIを利用するものよりも、若干パフォーマンスが落ちる程度で収まっています。通常のinsertを使う場合に比較して、10倍近い差があるので、大量のインサートを同時に行う場合に、利用するとよいと思います。

           Rate   normal multiple      raw
normal   22.3/s       --     -90%     -92%
multiple  213/s     855%       --     -23%
raw       278/s    1147%      31%       --

バルクインサートで高速化

データベースがバルクインサートをサポートしている場合は、バルクインサートを行うことができます。現在のところMySQLとPostgreSQLで利用することができます。データベースの機能を使って、複数のデータを一度に挿入できるので、さらなる高速化が期待できます。

$dbi->insert($params, table => 'book', bulk_insert => 1);

MySQLでのbulk_insertでのベンチマーク

以下はベンチマークスクリプトです。

use strict;
use warnings;
use Benchmark qw/timethese cmpthese/;

use DBIx::Custom;

my $dbi = DBIx::Custom->connect(dsn => 'dbi:mysql:database=usertest',
user => 'root', connector => 1);

# 性能の比較
my $result = timethese(100, {
  normal => sub {
    $dbi->connector->txn(sub {
      $dbi->delete_all(table => 'book');
      for my $i (1 .. 100) {
        my $param = {id => $i, title => 'Perl'};
        $dbi->insert($param, table => 'book');
      }
    });
  },
  multiple => sub {
    $dbi->connector->txn(sub {
      $dbi->delete_all(table => 'book');
      my @params;
      for my $i (1 .. 100) {
        push @params, {id => $i, title => 'Perl'};
      }
      $dbi->insert(\@params, table => 'book');
    });
  },
  bulk_insert => sub {
    $dbi->connector->txn(sub {
      $dbi->delete_all(table => 'book');
      my @params;
      for my $i (1 .. 100) {
        push @params, {id => $i, title => 'Perl'};
      }
      $dbi->insert(\@params, table => 'book', bulk_insert => 1);
    });
  },
  raw => sub {
    $dbi->connector->txn(sub {
      $dbi->delete_all(table => 'book');
      my $sth;
      for my $i (1 .. 100) {
        $sth ||= $dbi->dbh->prepare('insert into book (id, title) values (?, ?)');
        my $id = $i;
        my $title = 'Perl';
        $sth->execute($i, $title);
      }
    });
  }
});

cmpthese($result);

ベンチマークの結果で、bulk_insertを使うと、通常のinsertの通常の5倍くらいのパフォーマンスがでるようです。

              Rate      normal    multiple         raw bulk_insert
normal      24.0/s          --        -72%        -76%        -83%
multiple    86.2/s        259%          --        -16%        -40%
raw          102/s        326%         18%          --        -29%
bulk_insert  143/s        496%         66%         40%          --

もしバルクインサートでパフォーマンスがでないときは、データベースのinsertのためのメモリ領域に不足がないかを一度確認してください。このメモリ領域が足りない場合は、スワップイン・スワップアウトが発生して、とても遅くなります。

DBIx::Custom