マスターデータの書きかえ

メンテナンスなどで、マスターデータを書きかえるときは、

use strict;
use warnings;
use DBIx::Handler;

my $db = DBIx::Handler->new("dbi:mysql:test;", "root", "", { RaiseError => 1});
$db->dbh->do(q{ CREATE TABLE IF NOT EXISTS hoge (
    id int unsigned not null auto_increment primary key,
    test int unsigned not null
) ENGINE=InnoDB CHARSET=utf8 });

my $txn_guard = $db->txn_scope;
$db->dbh->do(q{TRUNCATE TABLE hoge});
for my $counter ( 1..10000) {
    $db->dbh->do(q{ INSERT INTO hoge (test) VALUES (?) }, {}, $counter);
}
$txn_guard->commit;

とか書きがち。
ただこの処理の場合、hogeテーブルの書きこみは激しくなりがちだし、トランザクションも長いあいだ貼られるので、hogeテーブルがかなり頻繁に参照されていたりすると、参照性能に影響がでたりする。

rename tableで2つのテーブル入れかえ、を使い、

use strict;
use warnings;
use DBIx::Handler;

my $db = DBIx::Handler->new("dbi:mysql:test;", "root", "", { RaiseError => 1});
$db->dbh->do(q{ DROP TABLE IF EXISTS hoge_old });
$db->dbh->do(q{ CREATE TABLE IF NOT EXISTS hoge (
    id int unsigned not null auto_increment primary key,
    test int unsigned not null
) ENGINE=InnoDB CHARSET=utf8 });
$db->dbh->do(q{ CREATE TABLE IF NOT EXISTS hoge_new like hoge });

for my $counter ( 1..10000) {
    $db->dbh->do(q{ INSERT INTO hoge_new (test) VALUES (?) }, {}, $counter);
}

my $txn_guard = $db->txn_scope;
$db->dbh->do(q{ RENAME TABLE hoge TO hoge_old, hoge_new TO hoge });
$txn_guard->commit;

このようにすると、hoge_oldにバックアップも残るので、何か差しかえたデータが変だというときは、戻せるし、トランザクションを貼る時間は、rename tableのときだけなのでよいのかなと思います。

job queueなどから実行されて、同時実行されるケースも考慮する必要がある場合は(そうそうないだろうけど)、

use strict;
use warnings;
use DBIx::Handler;

my $db = DBIx::Handler->new("dbi:mysql:test;", "root", "", { RaiseError => 1});
$db->dbh->do(q{ CREATE TABLE IF NOT EXISTS hoge (
    id int unsigned not null auto_increment primary key,
    test int unsigned not null
) ENGINE=InnoDB CHARSET=utf8 });
my $new_table_name = $db->dbh->selectcol_arrayref(q{ SELECT CONCAT('hoge_tmp_', CONNECTION_ID()) });
if ( $new_table_name && $new_table_name->[0] ) {
    $new_table_name = $new_table_name->[0];
}
$db->dbh->do(sprintf(q{ CREATE TABLE IF NOT EXISTS %s like hoge }, $new_table_name));

for my $counter ( 1..10000) {
    $db->dbh->do(sprintf(q{ INSERT INTO %s (test) VALUES (?) }, $new_table_name), {}, $counter);
}

my $txn_guard = $db->txn_scope;
my $backup_table_name = $db->dbh->selectcol_arrayref(q{ SELECT CONCAT('hoge_backup_', UNIX_TIMESTAMP()) });
if ( $backup_table_name && $backup_table_name->[0] ) {
    $backup_table_name = $backup_table_name->[0];
}
$db->dbh->do(sprintf(q{ RENAME TABLE hoge TO %s, %s TO hoge }, $backup_table_name, $new_table_name));
$txn_guard->commit;

CONNECTION_ID()関数を用いて、接続にユニークなテーブル名をつくり、それを元に同様にやるとよさげ。

ただし、テーブルが大量に残ってしまうので、お掃除プログラムが別途必要にはなりそう。